Displaying the town and then the persons name

ade234uk

I need a bit of help, if anyone can help me.

I have been scratching my head for the last few hours. I am trying to list the Towns with the peoples names from my database. At present I am getting the town and only one name. I need to display all the names. Here is the output I recieve form my php page.

Brighton
Peter (2)

Newhaven
John (1)

Peacehaven
Paul (1)

In Newhaven I have at least another two names that should be displayed at the moment it is only showing John.

Here is my code, I hope someone can help me. Many thanks.

$query = "SELECT town, name,
count(name) AS cnt
FROM peopletbl GROUP BY town";

$res = mysql_query($query) or die(mysql_error());
if(mysql_num_rows($res) > 0) {
while($row=mysql_fetch_array($res)) { ?>
<? echo $row['town']; ?> <? echo $row['name']; ?> (<? echo $row['cnt']; ?>) 
 
<? }
}
else {
echo "Nothing found"
} ?>

The output needs to be like this

Brighton
Peter (2)
Clare(1)

Newhaven
John (1)
David(1)
Robert(1)

Peacehaven
Paul (1)

Dark_Munk

You need the main while loop to display the town, then have a loop after that displaying all the names in that town. Then the loop will go to the next town, output the town name then the loop in the loop displays the name in the current town and so on... until there are no more towns to display. That's one way of sorting it, that's how i'd see it anyway.

And it might be better to shove a lot of data into variables because it's a very hard script to look at. There's simplier ways of doing what you did.

Piranha

Dark.Munk is right, but missed one thing. You have currently "GROUP BY town" in your SQL. With group by it only returns one result. You should user "ORDER BY town" instead, then it sorts the results by town.

ade234uk

I get you. How would you improve on the current script thou by shoving things in to variables?

Dark_Munk

Piranha wrote:
Dark.Munk is right, but missed one thing. You have currently "GROUP BY town" in your SQL. With group by it only returns one result. You should user "ORDER BY town" instead, then it sorts the results by town.

lol missed that one :p

and

ade234uk wrote:
I get you. How would you improve on the current script thou by shoving things in to variables?

It wouldn't improve the script, it's like commenting, makes the code easier to read. If you need to come back to the code later and you've put everything into clearly marked variables it makes it easier to modify and improve. Example:

if(mysql_num_rows($res) > 0) {

is easier looked at when put like this:

$numrows = mysql_num_rows($res);

if($numrows > 0) {

That way you can always clearly see what's going on. I'm quite simple minded and find it helps a lot. It also helps when you want help with code for others to read and see what's going on, especially us with primitive minds 😛 (example me).

ade234uk

I put a while loop inside the other while loop as suggested with some degree of success. It now displays the following.

Brighton
Peter (2)
Clare(1)

But then stops. I just need it now to output the next town and do the same again.

Here is my code below. I know its very rough and not very clear.

// Request results from tables
$result = mysql_query("SELECT * FROM peopletbl");

while ( $row = mysql_fetch_array($result) )
{
$row['town']; ?>

   <? $result = mysql_query("SELECT * FROM peopletbl WHERE town = '".$row['town']."'");  
	while ( $row = mysql_fetch_array($result) ) { 
	echo $row['name'];  ?><br>
             <? }} ?>

mourisj

The problem might be that your inner $result overwrites the outer one, so there are no more values left to be displayed.
Try to change those names, like $resul1, $resul2

Hope this helps

JJ Mouris

Installer

Try this:

$query = "SELECT town, name, COUNT(name) AS ct FROM peopletbl " .
         "GROUP BY name ORDER BY town";
$result = my_mysql_query($query);
$town = '';
while ($row = mysql_fetch_array($result)) {
    if ($row['town'] != $town) {
        $town = $row['town'];
        echo $town . '<br />';
    }
    echo $row['name'] . ' (' . $row['ct'] . ')<br />';
}

ade234uk

Excellent it works a treat thank you very much. I have always wondered how you do this. This example is great and I can at least see how it works one and for all.

Thank you again.

How do I now mark this thread as resolved lol