List Cities And How Many Records Match That City By 3

[derekshull]

I want to list out the cities that are from the SQL statement in rows of 3 but I'm doing something wrong. Can someone check me on this?

 

I want it to look like this:

 

City, State (#) City, State (#) City, State (#)

 

City, State (#) City, State (#) City, State (#)

 

City, State (#) City, State (#) City, State (#)

 

City, State (#) City, State (#) City, State (#)

 

 

$fquery = mysql_query("SELECT state, city, count(city) as num FROM needs WHERE status='posted' GROUP BY state, city ORDER BY state, city");


echo "<table><tr>";
$i=0;
while ($frows = mysql_fetch_array($fquery)) {


$fcity = $frows['city'];
$fstate = $frows['state'];
$fcitycount = $frows['num'];  // num is holding your count by city


if ($i == 0) {
echo "<td><a href='node/browseresults.php?city=$fcity&state=$fstate'>$fcity, $fstate ($fcitycount)</a>  $i</td>";
++$i;
}
if ($i < 3) {
echo "<td></td><td><a href='node/browseresults.php?city=$fcity&state=$fstate'>$fcity, $fstate ($fcitycount)</a>  $i</td>";
++$i;
}
if ($i == 2) {
echo "</tr><tr></tr><tr></tr><tr><td><a href='node/browseresults.php?city=$fcity&state=$fstate'>$fcity, $fstate ($fcitycount)</a> $i</td>";
$i = 0;
}


}


echo "</tr></table>";

[mrMarcus]

Untested:

 

<?php
$fquery = "SELECT state, city, count(city) as num FROM needs WHERE status='posted' GROUP BY state, city ORDER BY state, city";
if ($result = mysql_query($fquery)) {
$num_rows = mysql_num_rows($result);

echo "<table><tr>";
$i = 1;
$cols = 3;

while ($frows = mysql_fetch_array($result)) {
	$fcity = $frows['city'];
	$fstate = $frows['state'];
	$fcitycount = $frows['num'];  // num is holding your count by city

	echo "<td><a href='node/browseresults.php?city=$fcity&state=$fstate'>$fcity, $fstate ($fcitycount)</a>  $i</td>";
	echo ($i < $num_rows) ? ((($i % $col) == 0) ? '</tr><tr>' : '') : '';
	$i++;
}
echo "</tr></table>";
}

[derekshull]

That did not work :-/

[mrMarcus]

Typo in my code.

 

Replace:

 

echo ($i < $num_rows) ? ((($i % $col) == 0) ? '</tr><tr>' : '') : '';

 

with:

 

echo ($i < $num_rows) ? ((($i % $cols) == 0) ? '</tr><tr>' : '') : '';

[derekshull]

It worked! A little styling and it looks beautiful! Thanks! Once quick question:

 

I want to seperate them by state as well...so if it's displaying a different state than the last record then I want it to go to a new line as well. Like this:

 

City, State (#) City, State (#) City, State (#)

 

City, KY (#) City, KY (#) City, KY (#)

 

City, KY (#) City, KY (#)

 

City, AR (#) City, AR (#)

 

City, MO (#) City, MO (#) City, MO (#)

[derekshull]

Nevermind I figured that last one out. Thanks!