help with php sql query please

[ssnet12]

hi i am a new member here. i have joined looking for some help.

 

i am trying to select the numrows from my database table where category = somevalue.

 

below my code shows that i am querying where category = 'antiques-for-sale'

 

this code works fine for 1 category but i need to query all categorys

 

example i want to produce the numrows of each category,

 

results should show how many rows where category = antiques-for-sale, how many rows where category = bikes-for-sale, how many rows where category = boats-for-sale, etc

 

my code below only returns results for 1 category,(antiques-for-sale) is there a way of doing this without writing multiple sql query statements, basically i would like a sql query to bring back all results, but i cannot figure out how to do it. any help will be greatly appreciated. thanks steve

 

 

-------------------------------------------------------------------------

 

$querynew  = "SELECT COUNT(id) AS numrows FROM table WHERE category = 'antiques-for-sale'";

 

$resultnew  = mysql_query($querynew) or die('Error, query failed');

 

$row    = mysql_fetch_array($resultnew, MYSQL_ASSOC);

 

$catid1 = $row['numrows'];

[php.ajax.coder]

Try Something like this

 

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM person");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

mysql_close($con);
?>

[ssnet12]

hi thanks for your help but i couldnt get that code to work. does anyone else no how i do this?

 

thanks steve

[php.ajax.coder]

Try looking at this

 

http://uk2.php.net/manual/en/function.mysql-fetch-row.php

[ssnet12]

hi i have just tried looking at the link you gave me, thanks

 

but i still cant figure this out. when i use the echo in the code below it returns the amount of rows in the category 'antiques-for-sale'

 

i need the code below to echo the amount of rows in each category.  The code below only works for 1 category, how can i change this code to show amount of rows for multiple categorys.

 

many thanks steve

 

 

 

$querynew  = "SELECT COUNT(id) AS numrows FROM table where category = 'antiques-for-sale' ";

$resultnew  = mysql_query($querynew) or die('Error, query failed');

$row    = mysql_fetch_array($resultnew, MYSQL_ASSOC);

 

$catid1 = $row['numrows'];

 

echo $catid1;

[ssnet12]

Hi again

 

this is the only way i can figure out how to do it. but the code is long and there must be a better way.

 

if anyone can show me a better way. that would be greatly appreciated

 

thanks steve

 

ps code below

 

-------------------------------------------------------------------------------------

 

$querynew  = "SELECT COUNT(id) AS numrows FROM table where category = 'antiques-for-sale' ";

$resultnew  = mysql_query($querynew) or die('Error, query failed');

$row    = mysql_fetch_array($resultnew, MYSQL_ASSOC);

 

$catid1 = $row['numrows'];

 

$querynew  = "SELECT COUNT(id) AS numrows FROM table where category = 'baby-items-for-sale' ";

$resultnew  = mysql_query($querynew) or die('Error, query failed');

$row    = mysql_fetch_array($resultnew, MYSQL_ASSOC);

 

$catid2 = $row['numrows'];

 

$querynew  = "SELECT COUNT(id) AS numrows FROM table where category = 'bikes-for-sale' ";

$resultnew  = mysql_query($querynew) or die('Error, query failed');

$row    = mysql_fetch_array($resultnew, MYSQL_ASSOC);

 

$catid3 = $row['numrows'];

 

$querynew  = "SELECT COUNT(id) AS numrows FROM table where category = 'boats-for-sale' ";

$resultnew  = mysql_query($querynew) or die('Error, query failed');

$row    = mysql_fetch_array($resultnew, MYSQL_ASSOC);

 

$catid4 = $row['numrows'];

 

etc etc etc this code would have to go on for 200 categorys.