CODEWITHSUNDEEP
phpmysqlsqlcategoriesfetch
Henk de B
Henk de B

Reputation: 55

MySQL / PHP: Fetch multiple values from 1 column in Database

I'm trying to select multiple values from 1 column in my MySQL database. I have a Table 'products' with a column 'category'. Categories: Home, Garden, Cars, Bicycle etc. I want to fetch the number of products with these categories for statistics. It sounds simple but I can only get it done with allot of code. I want all of these categories to be variables so I only have to put my variables in my statistics engine to do the calculation. Right now this is my code to fetch the number of products with category 'Garden':

$query = "SELECT * FROM products WHERE category='Garden'";
$result= mysql_query($query);
$row   = mysql_fetch_array($result);

echo "$row[category]";

Repeating this for every category does 't seem right to me.. Does anyone understand my question and have a solution?

Upvotes: 0

Views: 3434

Answers (3)

echo_Me
echo_Me

Reputation: 37233

try this

   $query = "SELECT *, count(*) as counts FROM products group by category";
   $result= mysql_query($query);
   while($row   = mysql_fetch_array($result))
   {
   echo $row['category']." ". $row['counts'];

   }

you have to use while to fetch multiple categories.

you need to group by category to get distinct categories.

Upvotes: 0

Rafal
Rafal

Reputation: 2576

I think this is what you want

$query = "SELECT `category`, COUNT(`category`) FROM `products` GROUP BY `category`;";

Upvotes: 3

user3771071
user3771071

Reputation: 16

Just use a while as a loop to get the data.

What is While Loop?

The do while construct consists of a process symbol and a condition. First, the code within the block is executed, and then the condition is evaluated. If the condition is true the code within the block is executed again. This repeats until the condition becomes false. Because do while loops check the condition after the block is executed, the control structure is often also known as a post-test loop. Contrast with the while loop, which tests the condition before the code within the block is executed.The do-while loop is an exit-condition loop. This means that the code must always be executed first and then the expression or test condition is evaluated. If it is true, the code executes the body of the loop again. This process is repeated as long as the expression evaluates to true. If the expression is false, the loop terminates and control transfers to the statement following the do-while loop.

Just use the code below to fetch multiple values.

$query = "SELECT * FROM products WHERE category='Garden'";
$result= mysql_query($query);
while($row=mysql_fetch_array($result))
{
echo "$row[category]<br>";
}

Upvotes: -1

Related Questions