Show multiple table rows in a table

Question

I have 2 tables called 0_vrs_american and 0_vrs_europe, and I need to display the rows of these tables in a single html table. So I wrote this code:

<?php
$con=mysqli_connect("localhost","aaa","bbb","my_mk7vrlist");

$result = mysqli_query($con,"SELECT 0_vrs_american.*, 0_vrs_europe.* FROM 0_vrs_american, 0_vrs_europe ORDER BY `vrs` DESC LIMIT 0 , 200");

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $x . "</td>";
  echo "<td>" . $row['playername'] . "</td>";
  echo "<td>" . $row['contactable'] . "</td>";
  echo "<td>" . $row['vrs'] . "</td>";     
  echo "</tr>";
  $x = $x+1;
  }

mysqli_close($con);
?>

I am pretty new with MySQL and so I googled about this topic and I found a syntax like the one you can see above. By the way I have no results in my page because the table is empty.

So: I must display in a HTML table the content of both sql tables, and the rows must be sorted according with the vrs (number that goes from 50000 to 99000). How could I solve my problem?

You try to match the rows of the two tables in the code. Is it really what you want? Don't you rather need to display 8 rows if the tables have 5 and 3 rows respectively? BTW: mysqli functions are outdated, use PDO; and always output document strings through htmlspecialchars() unless you are completely positive that it may not be misinterpreted as HTML code! — Levente Pánczél, Mar 01 '14 at 00:10
@Ohgodwhy: http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons - I stand corrected that "outdated" may not be the right term, but a beginner is better off learning and using PDO and then reaching for mysqli for particular reasons (if need be) than the other way around. — Levente Pánczél, Mar 01 '14 at 00:21

score 2 · Accepted Answer · answered Mar 01 '14 at 00:17

An alternative to the above is to select all the rows from those two tables as an inner select, and then order by vrs in the returned result set.

SELECT  *
    FROM
    (
       SELECT * FROM 0_vrs_american
       UNION
       SELECT * FROM 0_vrs_europe
    ) AS a
ORDER BY vrs

score 0 · Answer 2 · answered Mar 01 '14 at 00:21

at first U need to define $x before the loop then use the query like this

$result = mysqli_query($con,"SELECT 0_vrs_american.*, 0_vrs_europe.* FROM 0_vrs_american, 0_vrs_europe ORDER BY `vrs` DESC LIMIT 0 , 200") or die (__LINE__." ".mysqli_error($con));

so you will see the error and the line of the query

i just add or die (__LINE__." ".mysqli_error($con))

score -1 · Answer 3 · answered Mar 01 '14 at 00:11

-1

Separate the 2 queries and their results
merge both results into an array
sort the array as needed
output the array (generate table rows)

answered Mar 01 '14 at 00:11

Manny Ramirez

149
1
6

Show multiple table rows in a table

3 Answers3