Database Join Help

willl

Hi I'm getting the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\phpdev5\www\tracking\display-contents.php on line 20

From this code:

<?php 

include 'connect.php';

$company = $_POST['company'];

echo "Results for ".$company."<br /><br />";

$query = mysql_query("SELECT * FROM link_log WHERE link_log.company_id = link_company.id;");

echo "<table cellspacing=\"0\" cellpadding=\"0\">\n";

echo "<tr>\n";

while($row = mysql_fetch_array($query)) {
echo "<td>".$company."</td><td>$row[date]<\td>\n";
}

echo "</tr>\n";

echo "</table>\n";

?>

Can anyone spot what is wrong with this? 😕

Thanks

Will

devinemke

change this:

$query = mysql_query("SELECT * FROM link_log WHERE link_log.company_id = link_company.id;");

to this:

$query = mysql_query("SELECT * FROM link_log WHERE link_log.company_id = link_company.id") or exit(mysql_error());

and see what mySQL tells you is wrong

willl

Ok it says:

Unknown table 'link_company' in where clause

There is definatley a table created called 'link_company'.

Any suggs?

Will

willl

Can anybody see whats wrong with this??

:bemused:

tomhath

link_company is in your database, but it's not in your query.
You need to JOIN to it (I didn't test this):

 $query = mysql_query("SELECT link_log.* FROM link_log INNER JOIN link_company ON  link_log.company_id = link_company.id WHERE 1") or exit(mysql_error());

willl

That worked perfectly - thanks for that.

I'm just trying to understand the statement you gave me;

How is the "WHERE 1" but used?

Will

tomhath

You shouldn't need the "WHERE 1", it doesn't really do anything. If the query runs without it you can just delete that part. Sometimes the DBMS chokes on a SQL query if there's no WHERE clause, and sometimes a person reading the code will wonder if you accidentally left the WHERE clause off.