Outputing Data From 2 queries onto 1 html table

amar3d

Hi, I want to display data from 2 tables(members, dvds), on 1 html table, so when the user views the data they see something like this (email and name from members, dvd_name from dvds);

Email | Name | DVD Name
hello@hello.com|A Nother| Spiderman

Except my code seems to print the data out as like this

This probably is something really trivial but if someone could help i would be most greatfull, my code is pasted below, thanking you in advance,

-AD

<html>
<body bgcolor=>
<P align=center> Current Rentals </p>
</br>
</br>

<?php

$db="test";
$link = mysql_connect("localhost");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());

$query = "SELECT FROM members";
$result = mysql_query($query);
$query1 = "SELECT FROM dvds";
$result1 = mysql_query($query1);
echo "<div align=center><table border=1 cellspacing=0>\n";
echo "<tr>
<td>E-mail</td>
<td>Name</td>
<td>DVD Name</td>
</tr>\n";

while ($row = mysql_fetch_array ($result)) {
echo "
<tr>
<td>{$row['email']}</td>
<td >{$row["first_name"]} {$row["second_name"]}</td>
";
}
while ($row1 = mysql_fetch_array ($result1)) {
echo "

cheese

Hi amar3d,

This looks like a fun project..... loads of potential.

The reason why your data is coming out one under the other is simply because you are running the 2 while statements under each other.

//this will list all of the members but will put in a <tr> each new member
while ($row = mysql_fetch_array ($result)) { 
echo " 
<tr> 
<td>{$row['email']}</td> 
<td >{$row["first_name"]} {$row["second_name"]}</td> 
"; 
} 
//this will list all of the members but will put in a </tr> at the end of each DVD

while ($row1 = mysql_fetch_array ($result1)) { 
echo " 

<td >{$row1["dvd_name"]}</td> 
<tr> 
"; 
} 
echo '</table>' 
?>

I think you want Members on the left and DVD's on the right. If so, try this;

echo "<table>";
echo "<tr>";
echo "<td>";

//left hand side

echo "<table>";
while ($row = mysql_fetch_array ($result)) { 
echo "<tr>"; 
echo "<td>" . $row['email'] . "</td>"; 
echo "<td>" . $row["first_name"] . "&nbsp" . $row['second_name'] . "</td>"; 
echo "</tr> "; 
} 

echo "</table>";

//end of left

echo "</td>";
echo "<td>";

//right hand side

echo "<table>";

while ($row1 = mysql_fetch_array ($result1)) { 
echo "<tr>";
echo "<td>" . $row1['dvd_name'] . "</td>";
echo "</tr>"; 
} 
echo "</table>" ;

//end of right

echo "</td>";
echo "</tr>";
echo "</table>" ;

?>

Take this further and you could add a table that holds the User's ID number, the DVD title, an out date, an in date.... etc, so that you could click on the sure name to see the account, giving outstanding DVD's as well as a history of what they have had (could be useful for marketing emails, if they opted in to the system.

I will look back at this thread to see if I got the hang of what you wanted.

Cheers,

Neil.

amar3d

Hi Neil,

Thank you for your kind help, that was exactly what i wanted. As for your other suggestions thats more or less what i am doing so thank you for that, Cheers

Amar