Logic using multiple DBs

imnsi

Right now I am trying to pull for 5 different databases based upon one thing, the userID, this is the first time I have tried a select statment of such a complexity, but from what I have been reading in this forum I think it should work:

<?php

db_connect();

$sql = "SELECT *
FROM payments, invoices, siteBin, sites 
WHERE payments.invoiceID=invoices.invoiceID
AND invoices.siteID=sites.siteID
AND siteBin.userID=$userID
AND sites.siteID=siteBin.siteID
;";
$query = mysql_query($sql) or die (mysql_error());

while(  $result = mysql_fetch_array($query)){

  ?>

  <tr>
    <td><?php echo $result['sites']['siteName'];?></td>
    <td><?php echo $result['invoice']['name'];?></td>
    <td><?php echo $result['payments']['cost'];?></td>
    <td><?php echo $result['payments']['dueDate'];?></td>
    <td><?php echo $result['payments']['status'];?></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>

  <?

  };

  ?>

is this the correct method? does syntax and logic look like its there? im stuck because nothing is showing up.

Any help is greatly appreciated.

toplay

When you say for "5 different databases" I assume you meant to say 5 different tables (because there's a difference in the syntax used).

What's returned by $result will be a one dimensional array and not two. So, this and similar lines won't work:
echo $result['sites']['siteName'];

Do yourself a favor and in the while loop do:
echo '<pre>', print_r($result, TRUE), '</pre>';

and you'll see what is being returned.

hth.

🙂

nemonoman

Or more simply, change your select, and reference the resulting $result array properly:

$sql = "SELECT sites.siteName,
invoice.name,
payments.cost,
payments.dueDate,
payments.status

FROM payments, invoices, siteBin, sites
WHERE payments.invoiceID=invoices.invoiceID
AND invoices.siteID=sites.siteID
AND siteBin.userID=$userID
AND sites.siteID=siteBin.siteID
;";
$query = mysql_query($sql) or die (mysql_error());

while( $result = mysql_fetch_array($query)){

imnsi

yes I meant 5 different tables, and yes that print_r() function helped so much, what I was doing wrong was trying to pull from a 2d array, as you mentioned and it seems to be working fine now, thank you so much!

toplay

You're welcome imnsi.

nemonoman's way is of course the correct and proper syntax to use. However, if all the column names are unique across the tables used, then you don't need to specify "sites.siteName," etc. You just needed to fix what you were echoing out. And by giving you the print_r() clue, you fixed it on your own. Well, done!

🙂