unistake Posted October 18, 2010 Share Posted October 18, 2010 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 } } ?> Quote Link to comment Share on other sites More sharing options...
kristijan.jurkovic Posted October 18, 2010 Share Posted October 18, 2010 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 ) Quote Link to comment Share on other sites More sharing options...
Andy-H Posted October 18, 2010 Share Posted October 18, 2010 <?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. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted October 18, 2010 Share Posted October 18, 2010 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. Quote Link to comment Share on other sites More sharing options...
kristijan.jurkovic Posted October 18, 2010 Share Posted October 18, 2010 tnx for info.. didnt know that... i m not rly using procedural code so i assumed that was wrong. Quote Link to comment Share on other sites More sharing options...
unistake Posted October 18, 2010 Author Share Posted October 18, 2010 Fantastic work, thanks Andy, Pikachu I am glad you said that! Quote Link to comment 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.