manhattes Posted July 29, 2015 Share Posted July 29, 2015 Hi All, I am having trouble getting my script to work. I have two tables. Stocks Calendar The stocks table only has two fields, Company and Symbol The Calendar table has a field with the same title SName. I would like to return the matching rows in the calendar that contain the company name from the Stocks Table along with a date field. I think utilizing the LIKE command is best. $query = "SELECT * FROM Stocks"; $query2 = "SELECT * FROM Calendar"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)){ echo $row['SName'] ," ", $row['Symbol'] ; echo "<br />"; } $result2 = mysql_query($query2) or die(mysql_error()); while($row2 = mysql_fetch_assoc($result2)){ echo $row2['SName'] ," ", $row2[''] ; echo "<br />"; } ?> <tr> <td><p><?php echo $row['Symbol']; ?></p></td> <td><p><?php echo $row['SName']; ?></p></td> <td><p><?php echo $row['Primary Completion Date']; ?></p></td> </tr> </table> </body> </html> Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted July 29, 2015 Share Posted July 29, 2015 It is not recommended to run two separate queries. If you have data stored in both tables which relates then you should use a join. Example SELECT s.SName, s.Symbol, c.`Primary Completion Date` FROM Stocks s LEFT JOIN Calendar as c USING(SName) Also try not to have spaces in table names, either use camelcase or underscores. Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 the problem is Calendar.SName has more then just the name in the field. I have stripped out all special characters so I think the LIKE statement should work but I keep getting errors or hangups. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 29, 2015 Share Posted July 29, 2015 You should be joining on IDs (primary and foreign keys), not names anyway. Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 I am not trying to join IDs. I want a Fuzzy logic to make the determination. Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted July 29, 2015 Solution Share Posted July 29, 2015 Have you tried SELECT s.SName, s.Symbol, c.`Primary Completion Date` FROM Stocks s LEFT JOIN Calendar as c ON c.SName LIKE CONCAT('%', s.SName, '%') Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 $query = "SELECT s.SName, s.Symbol, c.`Primary Completion Date` FROM Stocks s LEFT JOIN Calendar as c LIKE %(SName)"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)){ echo $row['SName'] ," ", $row['Symbol'] ; echo "<br />"; Again, I have two tables. I basically want the script to only return the values in the calendar that have the same company in my stock list. Maybe I need to have the script create a 3rd table that creates a row for every match that is in the calendar? Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 (edited) @guru it says Lost connection to MySQL server during query $query = "SELECT s.SName, s.Symbol, c.`Primary Completion Date` FROM Stocks s LEFT JOIN Calendar as c ON c.SName LIKE CONCAT('%', s.SName, '%')"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)){ echo $row['SName'] ," ", $row['Symbol'] ; echo "<br />"; } Edited July 29, 2015 by manhattes Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 I got this to work: $query = "SELECT s.SName, s.Symbol, c.`Primary Completion Date` FROM Stocks s LEFT JOIN Calendar as c ON c.SName LIKE CONCAT('%', s.SName, '%' )LIMIT 30"; Is there a way to remove the limit? Quote Link to comment Share on other sites More sharing options...
Barand Posted July 29, 2015 Share Posted July 29, 2015 The delete key should do it. 2 Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 what do you mean the delete key should do it? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.