Need some idea on how can I do this..

[bugzy]

I have many to many relationship tables.

 

tables are:

 

item

category

item_category(merge table)

 

 

item can have many categories and vise versa.

 

 

 

 

so on item_category table it would look like this

 

category | item_id

 

cat_id1      item_id1

cat_id2      item_id1

cat_id3      item_id1

 

cat_id2      item_id2

cat_id5      item_id2

cat_id6      item_id2

 

 

 

 

I just want to count all the items under each categories on table "item_category".

 

I have this idea of putting all the category_id in an array and then use a select statement and use mysql_num_rows for each categories. But don't have an idea how to loop this kind of issue. This is my first time to do this..

 

Anyone?

[redarrow]

 

i might be a million years out but worth a try

<?php


$query = "SELECT category, COUNT(cat_id1,cat_id2,cat_id3,cat_id4,cat_id5) FROM item_category GROUP BY category"; 

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

// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(category)'] ." ". $row['item'] ." items.";
echo "<br />";
}
?>

[bugzy]

 

i might be a million years out but worth a try

<?php


$query = "SELECT category, COUNT(cat_id1,cat_id2,cat_id3,cat_id4,cat_id5) FROM item_category GROUP BY category"; 

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

// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(category)'] ." ". $row['item'] ." items.";
echo "<br />";
}
?>

 

 

Hello thanks for your suggestion.. Problem I can see is.. categories cannot be fix as it can be deleted and a user can add more categories..

 

I will try this though and play around with it.

 

Any other suggestion guys?

[Barand]

try

 

$query = "SELECT category, COUNT(*)  FROM item_category GROUP BY category"; 

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

// Print out result
while(list($cat, $total)= mysql_fetch_row($result)){
echo "$cat : $total<br>";
}

[redarrow]

the above will work i done it like this lol well wrong.

 

<?php

$querya = "SELECT category FROM item_category";

$resulta = mysql_query($querya) or die(mysql_error());

while($Get_them=mysql_fetch_assoc($resulta){

$query = "SELECT category, COUNT($get_them['category']) FROM item_category GROUP BY category"; 
}	 
$result = mysql_query($query) or die(mysql_error());


while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(category)'] ." ". $row['item'] ." items.";
echo "<br />";
}
?>

[bugzy]

try

 

$query = "SELECT category, COUNT(*)  FROM item_category GROUP BY category"; 

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

// Print out result
while(list($cat, $total)= mysql_fetch_row($result)){
echo "$cat : $total<br>";
}

 

Thanks Barrand! it's working! I was on the process on coding a foreach under a foreach under a foreach and I didn't know that this is just so easy with sql statement.

 

Problem now... instead of using the category_id how will I use the category name instead which is on the category table?

[Barand]

$query = "SELECT c.category_name , COUNT(*)  
                FROM item_category ic 
                   INNER JOIN category c ON ic.category = c.category_id
                GROUP BY c.category_name";

 

I'm having to guess at the column names in the category table.

 

 

[bugzy]

$query = "SELECT c.category_name , COUNT(*)  
                FROM item_category ic 
                   INNER JOIN category c ON ic.category = c.category_id
                GROUP BY c.category_name";

 

I'm having to guess at the column names in the category table.

 

Works perfectly and brilliantly 

 

Thanks Barand !!!

 

Can't believe I've spent like an hour doing foreach and the solution is just on the sql statement...

 

Thanks again!

[bugzy]

Need to unresolved this because I found an issue..

 

Here's the sql code now

 

SELECT c.cat_id, c.cat_name , COUNT(*) FROM item_category ic INNER JOIN category c ON ic.category_id = c.cat_id where c.cat_id != 1 GROUP BY c.cat_name

 

 

Problem is, it'll only show those categories that are also in the "item_category" table.. It will not show those categories that are not yet on the item_category table..

 

What I want is to show all categories even if they are not yet on "item_category" table.

 

Is this possible basing on my code above?

[Barand]

change to

SELECT c.cat_id, c.cat_name , COUNT(ic.category_id) 
FROM category c 
  LEFT JOIN item_category ic ON ic.category_id = c.cat_id 
WHERE c.cat_id != 1 
GROUP BY c.cat_name

[bugzy]

Wow! Thanks Barrand. Works perfectly now! 

 

I will definitely study this.

 

Thanks again!

[Barand]

this may help

 

http://www.phpfreaks.com/tutorial/data-joins-unions