Page 1 of 1

help with php query problem please

Posted: Sun Sep 28, 2008 7:05 am
by 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

Code: Select all

$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'];

Re: help with php query problem please

Posted: Sun Sep 28, 2008 9:13 am
by Stryks
Couldn't you do something like ...

Code: Select all

$querynew   = "SELECT category, COUNT(id) AS numrows FROM table GROUP BY category";


I dunno .. I think that should work. It should return a row for each category with the category name and the number of rows per category. You could also apply a ORDER BY category in order to sort the list.

Once done, you can just do a foreach on the result set and access the categories and counts as you go.

Hope that helps, or puts you on the right track at least.