Getting count of how many different data there is on a mysql database.

[00stuff]

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,

[Pikachu2000]

That can be done easily with a db query.

 

SELECT product, COUNT(product) AS number FROM table GROUP BY product

[00stuff]

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.

[Pikachu2000]

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

[QuickOldCar]

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;
?>

[00stuff]

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?

[mikosiko]

post your inventory_file table exact definition

[00stuff]

this is the inventory file, but what do you mean exact definition?

 

[attachment deleted by admin]

[mikosiko]

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" ??

[00stuff]

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)

[mikosiko]

I did edit my previous answer... check it again

[00stuff]

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

[wildteen88]

Is there more code to this? Is that code part of another loop which is why your getting duplicated results.

[00stuff]

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

[mikosiko]

double loop... no wonder

[00stuff]

I took it out and it worked. Lol, sorry. Thanks for the help.

[ryanfilard]

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

?>