joining 3 tables

[reub77]

I'm joining 3 tables at the id field in mySQL. I get an error.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/shabangw/public_html/invoices.php on line 51


This is my query:

$query = "SELECT currprojects.companyName, contracts.dueDate, invoices.amount, currprojects.jobSummary, invoices.invoiceDate, invoices.paid FROM currProjects, contracts, invoices WHERE currprojects.id = contracts.id AND currprojects.id = invoices.id AND invoices.id = contracts.id";

Does anything look wrong? How can I make it work? I've never joined 3 tables. Only 2 and I followed the same logic that I would for 2 but added a little more. Help me if you can.

[Spookster]

At first glance I do not see anything wrong with your query. Post the other code where you are trying to get the number of rows returned. That error can occur when the query doesn't return anything but there also might be an error elsewhere so post all pertinent code.

[reub77]

I've modified it a little since I posted it, but here it is:

<?PHP
$conn = db_connect();
//declare query
$query = "SELECT currprojects.companyName, contracts.dueDate, invoices.amount, currprojects.jobSummary, invoices.invoiceDate, invoices.invStatus FROM currProjects, contracts, invoices WHERE currprojects.id = contracts.id AND currprojects.id = invoices.id AND invoices.id = contracts.id";
//Execute Query
$result = mysql_query($query);
$x = 0;
while ($x < mysql_num_rows($result)) :
$company = mysql_result($result, $x, 'companyName');
$due = mysql_result($result, $x, 'dueDate');
$Amount = mysql_result($result, $x, 'amount');
$sent = mysql_result($result, $x, 'invoiceDate');
$job_sum = mysql_result($result, $x, 'jobSummary');
$invStat = mysql_result($result, $x, 'invStatus');
print "<tr class='tabletext'>";
print "<td><div align='left'>$company&nbsp;</div></td><td><div align='center'>$due</div></td><td><div align='center'>$Amount</div></td><td><div align='center'>$sent</div></td><td><div align='left'>$job_sum</div></td><td><div align='center'>$paid</div></td><td><div align='center'><img src='images/magnify.jpg' alt='View' width='25' height='19'></div></td><td valign='middle'><div align='center'><img src='images/Gold-Coin.jpg' alt='Paid' width='25' height='25'></div></td></tr><tr><td colspan='8'><div align='center'><img src='images/tableLine.jpg' width='710' height='5'></div></td>";
print "</tr>";
$x++;
endwhile;
print "</table>";
mysql_close();
if(!$result)
return("Query could not be executed"); // if query error
if(mysql_num_rows($result)>0) {
// if the values match values in the database
return("true");
}
?>

[Spookster]

The easiest way to debug this would be to verify that your query is valid. If you have phpMyAdmin run your query in there first to ensure it does in fact work and return records.

[reub77]

It was just that I didn't refer to my table with the right name. I was thinking though that maybe a more effective way to do what I'm doing would be to use the JOIN option. But I don't know how it works so I just stuck with what I knew. How could you use the same code with the JOIN option? And is it considered better?