Jump to content

fetching information from multiple tables and put it on one page


joemal

Recommended Posts

Hi,

 

I am wanting to bring information from a number of different tables. For an example I have used two tables.

 

The tables provide results from motorsport. I am wanting a page to display both sets of data WHERE skill is 'novice' for the novice championship. the novice championship includes events from cautotest and ctrial hence why i need both tables.

 

I have a table called cautotest and ctrial

 

They both contain the same structure apart from ctrial has more "rounds".

 

This is the coding for the page i have;

<?php
$cnovice_results = mysql_query("SELECT * FROM cautotest, ctrial WHERE skill ='N'");
if(mysql_num_rows($novice_results) == 0) { echo "<p>No Results Available."; } else {

echo "<table width=\"800\" border=\"0\" cellpadding=\"2\" cellspacing=\"2\" class=\"greywritinglight\" align=\"center\">
<tr align=\"center\">
<td>Competitor</td>
<td>Vehicle</td>
<td>Class</td>
<td>autotest1</td>
<td>autotest2</td>
<td>trial1</td>
<td>autotest3</td>
<td>trial2</td>
<td>Total</td>
</tr>";

$x=1;
while($results_row = mysql_fetch_array($cnovice_results))
{
			if($x%2): $rowbgcolor = "#FFFFFF"; else: $rowbgcolor = "#EEEEEE"; endif;
echo "<tr align=\"center\" bgcolor=\"" .$rowbgcolor. "\">";
echo "<td>" . $results_row['competitor'] . "</td>";
echo "<td>" . $results_row['vehicle'] .  "</td>";
echo "<td>" . $results_row['class'] . "</td>";
echo "<td>" . $results_row['autotest1'] . "</td>";
echo "<td>" . $results_row['autotest2'] . "</td>";
echo "<td>" . $results_row['trial1'] . "</td>";
echo "<td>" . $results_row['autotest3'] . "</td>";
echo "<td>" . $results_row['trial2'] . "</td>";
echo "<td>" . $results_row['total'] . "</td>";
echo "</tr>";
$x++;
}
echo "</table>";
}
?>

Any help is appriciated

Link to comment
Share on other sites

you will need to use a UNION query to retrieve similar meaning data from multiple tables - http://dev.mysql.com/doc/refman/5.0/en/union.html

 

AND you will need to specify the exact list of columns you want to SELECT in each query (forget about using SELECT * ) so that the corresponding data from each table will be end up in the same column in the result set.

Link to comment
Share on other sites

  • 4 weeks later...

Your query just creating the cartesian product of both tables. Also, the where-statement is unclear, who should the query execution know, which column "skill" is meant: the one from table cautotest, or from table ctrail?

 

I assume, you are looking for something like that:

 

SELECT * FROM cautotest WHERE skill ='N'

union

SELECT * FROM ctrial WHERE skill ='N'

 

! But this statement will only work, if both table will have the same columns. If not, you have to name the columns instead of the "*"-

 

Hope, it helps....

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.