Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Sep 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    joining 3 tables

    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.

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,278
    Thanks
    4
    Thanked 83 Times in 82 Posts
    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.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #3
    New Coder
    Join Date
    Sep 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ok

    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");
    }
    ?>

  • #4
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,278
    Thanks
    4
    Thanked 83 Times in 82 Posts
    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.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #5
    New Coder
    Join Date
    Sep 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    found the problem

    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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •