00stuff Posted June 3, 2011 Share Posted June 3, 2011 Hi, I have a database that contains about 1000 records and one of the fielsd is called "product". There is a total of about 7 different products but the records are all mixed on the database. What I want to do is to show on the page a count of how many records of each product there are. Database Example: id - location - product - color 1 - Houston - chair - red 2 - Dallas - shoe - black 3 - Mission - chair - white 4 - Austin - tv - black This is how I want the script to display the count of products on the web page. chair - 2 shoe - 1 tv - 1 Does anyone have any idea how to check the count of each product without me having to hard code the product name on the script? The reason for that is that the data being inputed in the database will change all the time, including the product data. There might be as much as a couple of hundred different products. Thanks, Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/ Share on other sites More sharing options...
Pikachu2000 Posted June 3, 2011 Share Posted June 3, 2011 That can be done easily with a db query. SELECT product, COUNT(product) AS number FROM table GROUP BY product Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1224907 Share on other sites More sharing options...
00stuff Posted June 3, 2011 Author Share Posted June 3, 2011 This is what I did. The field containing the different products is called description. <?php $getcount = mysql_query ("SELECT description, COUNT(description) AS number FROM inventory_file GROUP BY description"); $productcount = mysql_num_rows($getcount); echo $productcount; ?> Then when I run the script I get a crazy long number 141414141414141........... I"m not sure how to get the counts with that. Can anyone elaborate please. Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1224915 Share on other sites More sharing options...
Pikachu2000 Posted June 3, 2011 Share Posted June 3, 2011 Close, but since the query returns the values you want, you dont need to use mysql_num_rows(). Give this a try instead; you can tweak the display markup after you get it returning the right results . . . <?php $getcount = mysql_query ("SELECT description, COUNT(description) AS number FROM inventory_file GROUP BY description"); while( $array = mysql_fetch_assoc($getcount) ) { echo "Description: {$array['description']} count: {$array['number']}<br>"; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1224919 Share on other sites More sharing options...
QuickOldCar Posted June 3, 2011 Share Posted June 3, 2011 I thought you showed your db and it shows stuff like chair,shoe,tv as product. Well you can also do something like this. <?php $product = "chair"; $getcount = mysql_query("SELECT product FROM inventory_file WHERE product='$product'"); $productcount = mysql_num_rows($getcount); echo $productcount; ?> Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1224920 Share on other sites More sharing options...
00stuff Posted June 6, 2011 Author Share Posted June 6, 2011 I tried doing something like this: <?php $product_count = 0; $getcount = mysql_query ("SELECT description, COUNT(description) AS number FROM inventory_file GROUP BY description"); while( $array = mysql_fetch_assoc($getcount) and $product_count < 3 ) { echo "Description: {$array['description']} count: {$array['number']}<br>"; $product_count = $product_count + 1; } ?> The output is this: Description: ball count: 1 Description: shirts count: 2 Description: shoes count: 1 Description: ball count: 1 Description: shirts count: 2 Description: shoes count: 1 Description: ball count: 1 Description: shirts count: 2 Description: shoes count: 1 Description: ball count: 1 Description: shirts count: 2 Description: shoes count: 1 I just want it to display it once not once for every product tha database. Does anyone know how I can fix this? Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1226055 Share on other sites More sharing options...
mikosiko Posted June 6, 2011 Share Posted June 6, 2011 post your inventory_file table exact definition Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1226070 Share on other sites More sharing options...
00stuff Posted June 6, 2011 Author Share Posted June 6, 2011 this is the inventory file, but what do you mean exact definition? [attachment deleted by admin] Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1226074 Share on other sites More sharing options...
mikosiko Posted June 6, 2011 Share Posted June 6, 2011 that is a csv file not your table description with column names, type, size ,etc..etc have your tried your code without that "and $product_count < 3" ?? Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1226090 Share on other sites More sharing options...
00stuff Posted June 6, 2011 Author Share Posted June 6, 2011 Table Name: inventory_file Name - Type(size) id - int(11) Auto Increment location - text description - text tag_number - int(30) tag_number_2 - text letter - varchar(5) lot_number - text bin - varchar(30) time_stamp - text number_1 - int(10) number_2 - int(10) number_3 - int(10) inventory_description - text inventory_description_2 - text date_2 - text highlight - int(2) Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1226100 Share on other sites More sharing options...
mikosiko Posted June 6, 2011 Share Posted June 6, 2011 I did edit my previous answer... check it again Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1226106 Share on other sites More sharing options...
00stuff Posted June 6, 2011 Author Share Posted June 6, 2011 Yes, I tried this: <?php $getcount = mysql_query ("SELECT description, COUNT(description) AS number FROM inventory_file GROUP BY description"); while( $array = mysql_fetch_assoc($getcount) ) { echo "Description: {$array['description']} count: {$array['number']}<br>"; } ?> I got this. Results: Description: ball count: 1 Description: shirts count: 2 Description: shoes count: 1 Description: ball count: 1 Description: shirts count: 2 Description: shoes count: 1 Description: ball count: 1 Description: shirts count: 2 Description: shoes count: 1 Description: ball count: 1 Description: shirts count: 2 Description: shoes count: 1 Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1226109 Share on other sites More sharing options...
wildteen88 Posted June 6, 2011 Share Posted June 6, 2011 Is there more code to this? Is that code part of another loop which is why your getting duplicated results. Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1226110 Share on other sites More sharing options...
00stuff Posted June 6, 2011 Author Share Posted June 6, 2011 This is the entire code but I don't see anything wrong. <?php $getinfo = mysql_query("SELECT * FROM inventory_file"); $number = mysql_num_rows($getinfo); while ($row = mysql_fetch_assoc($getinfo)) { $item_location = $row['location']; $item_description = $row['description']; $item_tag_number = $row['tag_number']; $item_highlight = $row['highlight']; if ($item_highlight == 1) { echo "<table border='1' bordercolor='black'><tr><td bgcolor='yellow'>" . $item_tag_number . "</td><td bgcolor='yellow'>" . $item_description . "</td></tr><tr>"; } else if ($item_highlight == 2) { echo "<table border='1' bordercolor='black'><tr><td bgcolor='red'>" . $item_tag_number . "</td><td bgcolor='red'>" . $item_description . "</td></tr><tr>"; } /* $getcount = mysql_query ("SELECT description, COUNT(description) AS number FROM inventory_file GROUP BY description"); while( $array = mysql_fetch_assoc($getcount) ) { echo "Description: {$array['description']} count: {$array['number']}<br>"; } */ $getcount = mysql_query ("SELECT description, COUNT(description) AS number FROM inventory_file GROUP BY description"); while( $array = mysql_fetch_assoc($getcount) ) { echo "Description: {$array['description']} count: {$array['number']}<br>"; } /* $getcount = mysql_query ("SELECT description, COUNT(description) AS number FROM inventory_file GROUP BY description"); $productcount = mysql_num_rows($getcount); echo $productcount; */ } echo "</tr></table>"; ?> Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1226113 Share on other sites More sharing options...
mikosiko Posted June 6, 2011 Share Posted June 6, 2011 double loop... no wonder Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1226115 Share on other sites More sharing options...
00stuff Posted June 6, 2011 Author Share Posted June 6, 2011 I took it out and it worked. Lol, sorry. Thanks for the help. Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1226117 Share on other sites More sharing options...
ryanfilard Posted June 6, 2011 Share Posted June 6, 2011 I use this: <?php $link = mysql_connect("localhost", "myuser", "mypass"); mysql_select_db("login_system", $link); $result = mysql_query("SELECT * FROM users", $link); $num_rows = mysql_num_rows($result); $totalone = $num_rows ; echo "$totalone"; ?> Quote Link to comment https://forums.phpfreaks.com/topic/238352-getting-count-of-how-many-different-data-there-is-on-a-mysql-database/#findComment-1226153 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.