How to order in two PHP related MySQL queries?
I am creating a dropdown form based on the results of two MySQL cross-reference queries, however I am unable to alphabetically order the list correctly.
I take the ID number from the first table "list1", but then I need to get its name from the second table "list2" and display them in alphabetical order.
I tried ORDER BY, but it doesn't make sense in any query.
<select name="select">
<?php
$sql = "SELECT DISTINCT id FROM list1";
$query = mysqli_query($connection, $sql) or die (mysqli_error());
while ($row= mysqli_fetch_array($query)) {
$id = $row["id"];
// *** get the ID name from second table ***
$sql2 = "SELECT * FROM list2 WHERE id='$id' ORDER BY name ASC LIMIT 1";
$query2 = mysqli_query($connection, $sql2) or die (mysqli_error());
while ($row2 = mysqli_fetch_array($query2)) {
$name = $row2["name"];
echo '<option value="'.$id.'">'.$name.'</option>';
} // end while
// *** end get name ***
} // end while
?>
</select>
+3
source to share
1 answer
Just combine the two queries into one:
select col1, col2 etc
from list2 where list2.id in (SELECT DISTINCT id FROM list1)
order by name asc
This gives you the same list that you would get using your queries and subsequent subqueries, but still, and you can order the entire result if needed.
<select name="select">
<?php
$sql2 = " select col1, col2 etc
from list2 where list2.id in (SELECT DISTINCT id FROM list1)
order by name asc
";
$query2 = mysqli_query($connection, $sql2) or die (mysqli_error());
while ($row2 = mysqli_fetch_array($query2)) {
$name = $row2["name"];
echo '<option value="'.$id.'">'.$name.'</option>';
} // end while
?>
</select>
Just note that you probably shouldn't use select *
when joining tables like this. Just select the columns you want.
+1
source to share