Jump to content

Need some idea on how can I do this..


bugzy

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/266015-need-some-idea-on-how-can-i-do-this/
Share on other sites

 

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 />";
}
?>

 

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?

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>";
}

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 />";
}
?>

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?

$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.

 

 

$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  :D

 

Thanks Barand !!!

 

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

 

Thanks again!

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?

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.