Count and display rows

[Unknown98]

I have a mysql table that holds data for cars - each car has it's own ID, name, and year. Some cars are repeated.

 

Example: (ID - year - car name)

0001 - 2009 - Honda Civic

0002 - 2008 - Toyota Prius

0003 - 2009 - Honda Civic

0004 - 2008 - Toyota Prius

0005 - 2008 - Toyota Prius

0006 - 2007 - Honda Civic

 

How would I count how many of the same type of cars I have in that table and then display the quantity next to the car name? Like for the above example it would be:

 

2009 Honda Civic: 2

2007 Honda Civic: 1

2008 Toyota Prius: 3

 

Thanks in advance for any help 

 

[codeprada]

A query that selects the car's year and name and also the result of COUNT(*) while grouping the data by the car's year and name should do the job.

SELECT `year`, `car_name`, COUNT(*) AS `total` FROM `table` GROUP BY `car_name`, `year` ORDER BY `total` DESC

 

[Unknown98]

Thanks! That works perfect

[Unknown98]

What am I doing wrong here with the WHERE clause? The following works fine without the where clause, it shows all cars in the table and the total next to them. But then when I add the where clause it says there are no cars selected, when I know for a fact there are cars in the database with the owner name and warehouse id that I specify.

 

<?php
$sql = 'SELECT `car_year`, `car_name`, COUNT(*) as `total` FROM `company_inventory` WHERE owner_name = '.$userdata['user_name'].' AND warehouse_id = '.$_GET['id'].' GROUP BY `car_name`, `car_year` ORDER BY `car_name` ASC';
$result = mysql_query($sql) or trigger_error($sql . ' has an error<br />' . mysql_error());
if(mysql_num_rows($result) > 0) {
  while($row = mysql_fetch_assoc($result)) {
     echo $row['car_name'] . ' | ' . $row['total'] . '<br />';
  }
}
else {
    echo 'There are no cars selected!';
}


?>

[harristweed]

I think that you can't use a GET in the query, Try

$id=$_GET['id'];
$sql = 'SELECT `car_year`, `car_name`, COUNT(*) as `total` FROM `company_inventory` WHERE owner_name = '.$userdata['user_name'].' AND warehouse_id = '.$id.' GROUP BY `car_name`, `car_year` ORDER BY `car_name` ASC';
$result = mysql_query($sql) or trigger_error($sql . ' has an error<br />' . mysql_error());

[Unknown98]

Hmm,

 

Well that didn't work. It just does the same thing, saying there are no cars selected when there are eligible cars in the DB table.

[codeprada]

Don't practice throwing data from the user directly into your queries. It makes your database vulnerable to SQL injections. I'd suggest using Prepared Statements offered by MySQLi and PDO. They're immune to SQL injections and will save you a lot of headache.

 

I'm assuming owner_name is a string so therefore it must have single quotes around the value. This is basically you're query. Let's use my username for example

SELECT `car_year`, `car_name`, COUNT(*) as `total` FROM `company_inventory` WHERE owner_name = codeprada AND warehouse_id = 444 GROUP BY `car_name`, `car_year` ORDER BY `car_name` ASC

If the warehouse_id is an integer type then it's ok but owner_name fill cause the query to fail. Prepared statements also place the quotes around your values automatically if necessary.

 

[dodgeitorelse]

I think it may be your quote marks. If sql is in single quotes wouldn't .$user_data['username]. be in double quotes? I am just guessing here. And I have a file that does use Get in my query and it works fine. 

[Unknown98]

I've heard of PDO before, just never looked into it too much yet. Once I learn it I will probably re-write all my code to PDO. For now I'm just trying to get a working concept.

 

I tried manually entering the owner_name and warehouse_id in the query, instead of using $userdata['user_name'] and $id, but that still did not work.

 

It's not the quotes either, when I replace the single quotes with double quotes I get: Parse error: syntax error, unexpected T_STRING on line 2

[jcbones]

Try this:

<?php
$sql = 'SELECT `car_year`, `car_name`, COUNT(*) as `total` FROM `company_inventory` WHERE owner_name = \''.$userdata['user_name'].'\' AND warehouse_id = \''.$_GET['id'].'\' GROUP BY `car_name`, `car_year` ORDER BY `car_name` ASC';
$result = mysql_query($sql) or trigger_error($sql . ' has an error<br />' . mysql_error());
if(mysql_num_rows($result) > 0) {
  while($row = mysql_fetch_assoc($result)) {
     echo $row['car_name'] . ' | ' . $row['total'] . '<br />';
  }
}
else {
    echo 'There are no cars selected!';
}


?>

 

AS has already been stated, you need some santitation, and validation on any data coming from the client (browser).