PHP / MYSQL Inner Joins or Various Loops

PHP / MYSQL Inner Joins or Various Loops

The code below shows how you can get the first and last names for users. The first output is an inner join while the second output is two while loops. It is two ways to get the same results. However, the single query looks a little cleaner. This inner join was done with two tables, but, you could use more tables if you wanted data such as login name from another table.

<?php
include('connect.inc');
$db = public_db_connect();

$command = "SELECT t1.firstname, t1.lastname FROM table_sort as t1, table_sort2 as t2 WHERE t1.id = t2.id ";
$result = mysqli_query($db, $command);
while($row = mysqli_fetch_assoc($result)){
$first = $row['firstname'];
$last = $row['lastname'];
echo "<br/>".$first."-".$last;
}

//or 

//run multiple selects

$command = "SELECT id from table_sort2";
$result = mysqli_query($db, $command);
while($row = mysqli_fetch_assoc($result)){
$id = $row['id'];

$command_b = "SELECT firstname, lastname from table_sort WHERE id = '$id' ";
$result_b = mysqli_query($db, $command_b);
while($row = mysqli_fetch_assoc($result_b)){
$first = $row['firstname'];
$last = $row['lastname'];
echo "<br/>".$first."-".$last;

}

}

?>

September 23, 2012 by kent