fetching information from multiple tables and put it on one page

[joemal]

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

[mac_gyver]

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.

[phpPeter]

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....