Multiple Tables

softwaretech

Hello All,

I am trying to get data from two different databases and tables which I've been able to do. I'm then trying to put the information into a HTML table matched up...not so easy.

include("connection.php");


mysql_select_db('database1');

$retrieve = mysql_query("SELECT * FROM table_in_database1");


echo"<table width='100%' border='1'>";
echo"<tr><th>First Name</th><th>Last Name</th><th>Address</th><th>City</th><th>State</th><th>Zip Code</th><th>Phone</th><th>Email</th><th>Total Attending including Registeree</th><th>Names of Attendees</th><th>Number of Adults</th><th>Number of Children</th><th>Ages of Children</th><th>Fees Waived</th><th>Items Purchased</th><th>Date Paid</th></tr>";

while ($rows=mysql_fetch_assoc($retrieve)){
echo "<tr><td><center>{$rows['FirstName']}</center></td>";
echo "<td><center>{$rows['LastName']}</center></td>";
echo "<td><center>{$rows['Address']}</center></td>";
echo "<td><center>{$rows['City']}</center></td>";

echo "<td><center>{$rows['State']}</center></td>";

echo "<td><center>{$rows['ZipCode']}</center></td>";

echo "<td><center>{$rows['Phone']}</center></td>";

echo "<td><center>{$rows['Email']}</center></td>";

echo "<td><center>{$rows['TotalAttending']}</center></td>";

echo "<td><center>{$rows['otherAttendees']}</center></td>";

echo "<td><center>{$rows['numberOfAdults']}</center></td>";

echo "<td><center>{$rows['numberOfChildren']}</center></td>";

echo "<td><center>{$rows['agesOfChildren']}</center></td>";

echo "<td><center>{$rows['Waived']}</center></td>";

}

mysql_select_db('database2');

$retrieve=mysql_query("SELECT * FROM table_in_database2");

while ($rows=mysql_fetch_assoc($retrieve)){
	echo "<td><center>{$rows['activity']}</center></td>";
	echo "<td><center>{$rows['paymentdate']}</center></td></tr>";

}
echo "</table>";
mysql_close($connection);

I'm able to retrieve the data. The problem is on the data from database2, the table starts at the end of database1's table instead of at the top of the table.

For example:

</html>
<head>
</head>
<body>
<table width="100%" border="1">
<tr><td>This</td><td>is</td><td>an</td><td>example1</td></tr>
<tr><td>This</td><td>is</td><td>an</td><td>example2</td></tr>
<tr><td>This</td><td>is</td><td>an</td><td>example3</td></tr>
<tr><td>This</td><td>is</td><td>an</td><td>example4</td>
<td>This</td><td>is</td><td>the</td><td>problem</td></tr>
</table>
</body>
</html>

I get the issue in the HTML coding...but you can get an idea of what the error is supposed to look like.

samuelcook

If you want database2's information above database1's information, why aren't you calling database2 first?

NogDog

No sure what the exact problem is. Is there some one-to-one relationship between the two databases/tables that you want to display? If so, you may want to do it in one query, using a JOIN, and specifying the databases in the SQL:

SELECT * FROM database_1.table_name_1 AS t1
LEFT JOIN database_2.table_name_2  AS t2 ON t1.id1 = t2.id2

softwaretech

Thanks guys for the responses. Not that I'm trying to put one over another but match them up. I did try some join.... Like inner join.. That didn't work.... I'll try left and see if that helps.

BrightIdeas

I've done it before by just placing the 2nd query and the 2nd while loop (with the fetch array for your 2nd query getting data from your 2nd table) within the while loop of the 1st.

That does mean that for each time the 1st loop repeats, the 2nd sql query gets run, so I'm not sure if that's quite optimal... something tells me I'm slowing down the process by doing that:


$query1 = mysql_query('SELECT * FROM table1');
while($data1 = mysql_fetch_array($query1)) {
  $fn = $data1['firstname'];
  $ln = $data1['lastname'];

  $query2 = mysql_query('SELECT * FROM table2');
  while($data2 = mysql_fetch_array($query2)){
    $paymt = $data2['payment'];
    $paymtdate = $data2['paydate'];
    echo "
    <tr>
      <td>".$fn."</td>
      <td>".$ln."</td>
    </tr>
    <tr>
      <td>".$paymt."</td>
      <td>".$paymtdate."</td>
    </tr>   

    ";
  } // end while loop for query 2

} //end while loop for query 1

johanafm

Does the query in the nested loop, i.e. '... FROM table2 ...' really contain no WHERE clause? If so, you select all data from table2 for each and every row in table1. If this is really what you want, you should just select table2 data once, put it in an array and loop over this instead.

But, if you on the other hand DO have a WHERE clause in the second query looking something like

SELECT some, fields FROM table2 WHERE some_id = $some_id

then you can do this with one query

$q = 'SELECT some, fields FROM table1 INNER JOIN table2 ON table1.id = table2.corresponding_id_field';

It's possible you should use LEFT JOIN instead of INNER JOIN, since the outer joins lets you retrieve all rows from table1, wether they have any related rows in table2 or not. Table2 fields gets padded with null values.
But if you only wish to retrieve rows from table1 that actually have at least one corresponding row in table2, then you use an inner join.

BrightIdeas

I think I need to play around with JOIN scenario's more before I post any more on this subject... I feel I am missing an easier solution with a JOIN...