COUNT and show number of records

[unistake]

Hi all,

 

I am trying to use the Mysql COUNT function to count the number of records in a database where the manufacturer is the same and then generate a list of all manufacturers along with a number of associated records next to their name.

 

I have got the script below to show the manufacturer in a fetch array but I can not get the number next to the manufacturer to work.

 

A push in the right direction would be appreciated thanks.

 

<?php 
include("cxn.php");

$query = "SELECT manufacturer FROM sales"; 
$result = mysqli_query($cxn,$query) or die(mysqli_error());

?>
<select name="manufacturer" id="manufacturer">
<option value="" selected="selected">Select a Manufacturer..</option>";
<?php 
while($row = mysqli_fetch_array($result)){
$query1 = "SELECT manufacturer COUNT(*) FROM sales WHERE manufacturer='$row[manufacturer]'";  // TRYING TO COUNT THE ROWS HERE
$result1 = mysqli_query($cxn,$query1) or die (mysqli_error());

while($row1 = mysqli_fetch_array($result1)){
	echo "<option value=\"202\">".$row['manufacturer'] ."[". $row1['COUNT(manufacturer)']."]</option>"; // SHOW NUMBER HERE
}
}
?>

 

[kristijan.jurkovic]

first-> your mysqli functions are wrong (i am assuming that u r trying to use PHP MySQLi class)

 

 

//connection:
$mysqli = new mysqli($host, $user, $psswd, $db);

//query:
$query = $mysqli->query($sql);

 

here is the whole manual: http://www.php.net/manual/en/class.mysqli.php

 

 

and for sql try query with something like this:

 

SELECT manufacturer, COUNT(manufacturer) AS ShowNumber
FROM sales 
WHERE manufacturer = '{$row[manufacturer]}'
GROUP BY manufacturer
HAVING ( COUNT(manufacturer) > 1 )

 

 

[Andy-H]

<?php 
include("cxn.php");

$query = "SELECT manufacturer, COUNT(*) FROM sales GROUP BY manufacturer"; 
$result = mysqli_query($cxn,$query) or die(mysqli_error());

?>
<select name="manufacturer" id="manufacturer">
   <option value="" selected="selected">Select a Manufacturer..</option>";
<?php 
while($row = mysqli_fetch_row($result)){
      echo "<option value=\"202\">".$row[0] ."[". $row[1]."]</option>"; // SHOW NUMBER HERE
   }
?>

 

That should work.

[Pikachu2000]

first-> your mysqli functions are wrong (i am assuming that u r trying to use PHP MySQLi class)

 

No, they aren't wrong. Just because they're written procedural rather than OO doesn't make them wrong.

[kristijan.jurkovic]

tnx for info.. didnt know that... i m not rly using procedural code so i assumed that was wrong.

[unistake]

Fantastic work, thanks Andy,

 

Pikachu I am glad you said that!