Help with Query

manuela

Hi to All,

id category period title
...............................................................................................................

1 advertising 2000-2201 Adv la lunga
2 quadri 2000-2201 La notte
3 riviste 1 1999 Rivista 01
4 quadri 2000-2201 Il giorno
5 advertising 199 Adv 02
.. .... .... .....

I need a query for the above table (called catalogue) that selects all the categories (example advertising),count total amount of records for any of them an loops printing all the data for that categorie, like:

[avertising]
totale record
id..
period
title

[quadri]
totale record
id..
period
title

I thought to use COUNT and GROUP BY...
Any Idea?

Thanks in advance ;
R.
Manuela

bogu

manuela wrote:
I thought to use COUNT and GROUP BY...
Any Idea?

That is the idea ..

COUNT(category)
GROUP BY category

But what will u do with the others records?
So, u should extract all of them into an array and parse the array to find total records from one category ...

manuela

Thanks, for your answer.
I tried with somenthing like this:

mysql_select_db($database_db_kuz, $db_kuz);
$query = "SELECT * FROM catalogue";
$result= mysql_query($query);

while($cnt=mysql_fetch_array($result)){
$cat =$cnt['category'];
if ($cat=="WITNESSES"){
$q = "SELECT * FROM catalogue where category='WITNESSES'";
$r= mysql_query($q);
$tot=mysql_num_rows($r);
echo $tot;

}

but doesn' t work.
I mean, i need to loop for each category and output something like this:

{advertising}

&adv=number of records;

&title1=
&period1=

then

&title2=
&period2=

till the end for that category.
This needs to be done for each category.

Manuela

tomhath

$query = "SELECT category, count(*) as numrecords FROM catalogue group by category";
$result= mysql_query($query) or die(mysql_error());

while($cnt=mysql_fetch_array($result)){
$cat =$cnt['category'];
$numrecords = $cat['numrecords'];
...

manuela

If i run your query and echo
$numrecords = $cat['numrecords'];
it does not return the total records for each category;

i think should be:
$numrecords = $cnt['numrecords'];

Besides,
if I use GROUP BY , and get the total records for each category, how can i loop and echo all the records that belongs to that specific category?

Thanks a lot for your help.

With R.

Manuela

bogu

Try something like this:

$category = array();
$query = "SELECT * FROM catalogue";
$result= mysql_query($query); $i = 0;
while($cnt=mysql_fetch_array($result)){
    $category[$cnt['category']][$i]['id'] = $cnt['id'];
    $category[$cnt['category']][$i]['period'] = $cnt['period'];
    $category[$cnt['category']][$i]['title'] = $cnt['title'];
    $i++;
}

Examples of working with this array:

/* nr of elements in each category */
foreach ($category as $key=>$value) {
    echo "Category [{$key}] has ".count($value)." elements.<br />";
}

/* echo all the elements from category $categ_name */
$categ_name = "bla_bla";
echo "Category [{$categ_name}] have  this elements:";
foreach ($category[$categ_name] as $key=>$value) {
    echo "id = {$value['id']} | period = {$value['period']}  | title = {$value['title']} ";
}

Example of the output u want:

foreach ($category as $key => $value) {
	$tmp = "&adv_name={$key}&adv_nr=".count($value); $i = 0;
	foreach ($value as $item) {
		++$i;
		$tmp .= "&title{$i}={$item['title']}&period{$i}={$item['period']}";
	}
	echo "{$tmp}<br />";
	unset ($tmp);
}

manuela

I Bogu!
thanks a lot it works!!
Just one more think,

if i want to output the name and the total records of the category in this way:

[adv]
&name1=
&total1=
..........

[print]
&name2=
&total2=
..................

wath should i do?

Thanks ,
and sorry for bother you again with this issue.

Manuela