[RESOLVED] Show number of records held

[roscor]

Hi Everyone, Wishing you all a happy new year.

I am trying to sort a probably simple query with no joy hope someone can help.

I want to display the numbers of records shown in each County on my database

mysql_connect($host,$username,$password);
mysql_select_db($database) or die( "Unable to select database");


$query="SELECT county 'Bedfordshire', 'Berkshire' FROM t_data";
$result=mysql_query($query);
$num=mysql_num_rows($result);

while ($row = mysql_fetch_array($result)) {


$Bedfordshire = $row["Bedfordshire"];
$Berkshire = $row["Berkshire"];

}

?>

How dow I show the number of records on Bedfordshire, Berkshire etc. I am echo'ing $Bedfordshire which shows the data not the number of Bedfordshire records held.

any help would be gratly appreciated,

yep still a newbie.

cheers roscor

[laserlight]

EDIT:

Sorry, I did not see it carefully. Okay you are trying to find the number of rows in the t_data table where the county column has certain values?

[roscor]

I have a table called t-data with a collum called county. I want to display how many records are held on each county, eg

Bedfordshire (26)
Berkshire (2)

when the user links on the above it then takes them to the full records. At the moment all I can achive from

$query="SELECT * FROM t_data WHERE county='$county'";

//$query="SELECT * FROM t_data WHERE county='$county' ";

$result=mysql_query($query);
$num=mysql_num_rows($result);

if ($num==0) {


$empty = "No Matching";

}
 else {
$num_rows = mysql_num_rows($result);

and <? echo $num_rows; ?> shows all records held on collum county.

[laserlight]

Ah, then you are probably looking to write something like:

$result = mysql_query("SELECT county, COUNT(county) AS record_count
                      FROM t_data GROUP BY county");

while ($row = mysql_fetch_assoc($result)) {
    echo $row['county'] . ': ' . $row['record_count'] . "<br />\n";
}

[roscor]

Thats great laserlight, it works well. I have been trying to get it to work within a table as such

<td width="700" align="center"><font size="2" face="Verdana">Browse by County</font></td>
    </tr><tr><td width="700"><table width="700" class="style4" cellpadding="0" cellspacing="0" align="center"><tbody>
<tr><td width=""><img src="images/f_arrow2.gif" alt="">&nbsp;&nbsp;&nbsp;<a href="result.php?county=Bedfordshire" class="menu"> Bedfordshire&nbsp;(&nbsp;<? echo $row['county'] . ': ' . $row['record_count'] . "<br />\n"; ?>&nbsp;)</a></td>

<td width=""><img src="images/f_arrow2.gif" alt="">&nbsp;&nbsp;&nbsp;<a href="result.php?county=Berkshire"class="menu"> Berkshire&nbsp;(&nbsp;<? echo $row['county'] . ': ' . $row['record_count'] . "<br />\n"; ?>&nbsp;)</a></td>

etc,

I have had no joy, How can I display the $row['county']; with $row['record_count']; in each <td>, I'm stumped

[laserlight]

You seem to be thinking in terms of specific data, e.g. "Berkshire" instead "county name". A simple approach that mixes PHP with the HTML is to print the start of the table, then enter into the loop that extracts row by row from the result set into an array. Each row in the result set would then form a row in the table. After the loop ends, you print the end of the table.

$result = mysql_query("SELECT county, COUNT(county) AS record_count
                      FROM t_data GROUP BY county");

echo '<table>';
while ($row = mysql_fetch_assoc($result)) {
    echo '<tr><td>' . $row['county'] . '</td><td>'
         . $row['record_count'] . '</td></tr>';
}
echo '</table>';

[roscor]

Laserlight worked a treat, I will play for a while to get the look I am after,

thanks for your help

cheers

roscor