[RESOLVED] count

slimjim

I have two tables such as:
CATEGORY

cat_id
cat_name

and other table

PRODUCTS

catid

I could do this for each category count

<font color="#000000" size="2">catename :</font> (
<?
$array = array(1, 2, 3); 
$in = implode(',', $array);

$result = mysql_query("SELECT COUNT(*) FROM products where catid='1'");
$result2 = mysql_fetch_row($result);
$num = $result2[0];
echo"<font color=\"#000000\" size=\"2\">$num</font>";

?> )

I would rather get all cat_id and cat_name from category table
Then from products table sort all products by catid and show them with cat_name and count

ANy idea on how to do this ?

Piranha

As I understand it you only need to change your query:

SELECT c.id, c.name, count(p.cid) as amount
FROM categories c
LEFT JOIN products p ON c.id = p.cid
ORDER BY c.id
GROUP BY c.id

slimjim

Can you explain this query.

Piranha

What part of it do you need explained? Have you googled the keywords to get an understanding of it?

slimjim

Well the table colums do not match mine.
I am using cat_id and cat_name for the category table and in the products table I have the catid colum.
How does that query get the count of the products table for each category and where does the name of the category come into play.
Thne how do I even show them..

And yes I been at this all day and googled my brains out...

slimjim

Maybe I need to reword it...

slimjim

First table
Name = category

cat_id
cat_name

Second table
Name = products

catid ( which gets the cat_id from category table )

I want to show the count of catid in prodtucts table and get the cat_name from category table

So in Products table there may be 50 catid with value of 1 and 32 of catid with value of 2 and so on

Now need to show that count plus the cat_name

SO it may look like this on a page :

Music cd's = ( 32 )
Computers = ( 50 )

I hope this makes sence.....

Piranha

The thing is that I help out. I won't do your work. If I think it's easier to use other simular column names in the query I do that, and I expect you to check and change where needed. GROUP BY groups it together so you can get the count. Using LEFT JOIN makes sure that even if the count is 0 it will still show.

About showing it you can use something like this, which is a thread of your own where you handles many results and many variables in each result.

I could do all this for you, but then you would learn nothing from it. It is better for you that you really have to understand what happens to solve everything, and that I only show you in what direction to go. Then you will remember it until next time, I'm sure of it.

slimjim

Thank You
I really do not want you to do my work for me.

Here is what I have so far, but have error

$query = "SELECT c.cat_id, c.cat_name, count(p.catid) as amount
FROM cats_m c
LEFT JOIN products p ON c.cat_id = p.catid
ORDER BY c.cat_id
GROUP BY c.cat_id";

mysql_query($query) or die (error); 
$result=mysql_query($query);

while($row=mysql_fetch_array($result)){ 
		$catid=$myrow["catid"];
		$catname=$myrow["cat_name"];
echo $catid;
echo $catname;
echo $amount;
}

Please can you tell me what I am doing wrong..

Weedpacket

You forgot to explain what the error is. I can fix two lines there, though.

mysql_query($query) or die (error);
$result=mysql_query($query);

Would be better as

$result=mysql_query($query) or die (mysql_error());

That way you only query the database once instead of twice, and if there is an error at the database you get told what it is.

Oh, and when you are developing, it is a good idea to do with with error_reporting set to E_ALL; that will cause shonky coding practices to be flagged and stop you getting into the habit of using them.

slimjim

all it says is " error "

slimjim

Ok made the change you said and now get this error

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY c.cat_id' at line 5

Weedpacket

And you've done what the error message suggests?

slimjim

Also all I want to show is the category names and the the count of products in each category.

This seems easy enough and I can not figure it out.

slimjim

This whole type of query is not making since to me. and the Group by looks normal.

Piranha

Damnit, that is one of those things that I haven't managed to learn when it comes to basic SQL syntax. You can solve the problem here, a tip is to check in what order things is.

Blulagoon

while($row=mysql_fetch_array($result)){ 
		$catid=$myrow["catid"];
		$catname=$myrow["cat_name"];
echo $catid;
echo $catname;
echo $amount;
}

Surely this should be:
$catid=$row['catid'];
$catname=$row['cat_name'];

HTH - Blu

slimjim

Thank You, not matter what I did, no matter how I changed things regardless of the order, nothing was working and had the error.

All becausee of myrow and should have been row.

But to say the least, going thru the site mysql.com I learned alot of tips and tricks

So thank you all.