Using one table's row's as a variable to match similar entries in a 2nd table

[manhattes]

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>

[Ch0cu3r]

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.

[manhattes]

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.

[Barand]

You should be joining on IDs (primary and foreign keys), not names anyway.

[manhattes]

I am not trying to join IDs. I want a Fuzzy logic to make the determination.

[Barand]

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, '%')

[manhattes]

$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?

[manhattes]

@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

[manhattes]

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?

[Barand]

The delete key should do it.

[manhattes]

what do you mean the delete key should do it?