Query table by specific field as variable

[basher]

This my table 'numbers' layout and data

city, name, number

St. Louis, Marshall Faulk, 6455643
St. Louis, Chris Pronger, 9882112
St. Louis, Al MacInnis, 9981123
Chicago, Sammy Sosa, 2134982
Chicago, Alexi Zhamnov, 8890123
Detroit, Steve Yzerman, 1988734
Detroit, Brenden Shanahan
Dallas, Mike Modano, 9087892
Dallas, Derian Hatcher, 9823345
Hell, Bill Gates, 6666666

My query output would look like this:

Chicago
Sammy Sosa, 2134982
Alexi Zhamnov, 8890123

Dallas
Mike Modano, 9087892
Derian Hatcher, 9823345

Detroit
Steve Yzerman, 1988734
Brenden Shanahan

Hell
Bill Gates, 6666666

St. Louis
Marshall Faulk, 6455643
Chris Pronger, 9882112
Al MacInnis, 9981123

I would appreciate any direction.. thank you!

[Kudose]

What is it exactly that you are wanting to know?

[rheroux]

Try something like this:

<?php

// Define some variables...

$dbhost = "localhost";
$dbuser = "root";
$dbpass = "mypass";
$dbase = "database"; 
$table_name = "table";

$cx = mysql_connect($dbhost,$dbuser,$dbpass) or die ("Could not connect to the database server.");

mysql_select_db($dbase) or die ("Could not select the database.");

$sql = "SELECT DISTINCT city FROM ".$table_name;
$result = mysql_query($sql) or die ("Could not execute query.");

if(mysql_num_rows($result) == 0)
{
echo "Sorry, there are no records that match that query.";
}

else
{
while($row = mysql_fetch_array($result))
{
?>
<table width="400" cellpadding="5" cellspacing="0" border="0">
<tr>
<td>
<?=$row['city']?>
</td>
</tr>
<?php
$sql1 = "SELECT name, number FROM ".$table_name." WHERE city = '".$row['city']."'";
$result1 = mysql_query($sql1) or die ("Could not execute query.");
while($row1 = mysql_fetch_array($result1))
{
?>
<tr><td><?=$row1['name']?>, <?=$row1['number']?></td></tr>
<?php
}
?>
</table>
<?php
}
}
?>

[xblue]

just a guess (since this has been asked fairly often lately):

you'd like to select all data, order by city, but only display each city once and then list number and name pairs associated with this city?

[basher]

Yes. that is what I'd like to do. I searched the forums for GROUP BY but did not see post similar to what I want to accomplish.

[basher]

rheroux - you got it! Thats exactly what I wanted. I just added a few more WHERE clauses and BAM! done.

Thank you very much!

[xblue]

hi basher,

if there is a chance that your table grows and performance becomes an issue, you should not execute more queries than necessary. One is absolutely enough, you only need to keep track of the previous city.

$sql = "SELECT city, name, number  FROM $table_name ORDER BY city";
$result = mysql_query($sql) or die ("Could not execute query.");
$lastcity='';
while ($row=mysql_fetch_array($result)) {
    if ($row['city'] != $lastcity) {
        echo "<br>".$row['city']."<br>";
        $lastcity = $row['city'];
    }
    echo $row['name'].", ";
    echo $row['number']."<br>";
}
unset($lastcity);

[basher]

xblue - ok I am using your query and its great. I added a few changes to layout adding table, query change, field name, as well as added alternate cell colors. check it out:

$sql = "SELECT office, name, number, type FROM numbers WHERE (type='Mobile' OR type='Home') ORDER BY office";
$result = mysql_query($sql) or die ("Could not execute query.");
$lastoffice='';
while ($row=mysql_fetch_array($result)) {
    if ($row['office'] != $lastoffice) {
        echo "<tr><td colspan=\"3\" class=\"c3\" align=\"center\">".$row['office']."</td></tr>";
        $lastoffice = $row['office'];
    }
//Alternate Cells
$row_count++;
if($row_count%2==0){$fstyle="c1";}
else{$fstyle="c2";}

echo "<tr class=\"$fstyle\">";
echo "<td>".$row['name']."</td>";
echo "<td>".$row['type']."</td>";
echo "<td>".$row['number']."</td>";
}
unset($lastoffice);
?>

Thanks alot!

[rheroux]

Ah, always good to see a better answer. 😉