joemal Posted July 4, 2014 Share Posted July 4, 2014 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 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted July 5, 2014 Share Posted July 5, 2014 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. Quote Link to comment Share on other sites More sharing options...
phpPeter Posted August 1, 2014 Share Posted August 1, 2014 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.... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.