mpsn Posted June 29, 2011 Share Posted June 29, 2011 Hi, I need help to do two queries, the first is common outputting each record and the other one is to output the total count (quantity) of a duplicate field. See, I am building a simple online buying site and of course there are duplicate of the same products. Please read the code carefully below to see what I need to do to do, thanks. <?php include_once('config.php'); require 'bellConnect.inc.php'; //phpinfo(); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Bell Mobility by Andy 2: Public</title> </head> <body> <form method="POST" action="buyTester.php"><?php $result=mysql_query("SELECT ID, Image, Name, Manufacturer, Price, Description, SimSupport FROM bellProducts GROUP BY Name, Manufacturer;");//NB: GROUP BY will NOT include records w/ duplicate field values $result1=mysql_query("SELECT COUNT(DISTINCT Name, Manufacturer) FROM bellProducts;"); //NB: print table headings if(mysql_num_rows($result))//if there is at least one entry in bellProducts, make a table { //$counter+=1; print "<table border='border'>"; print "<tr> <th>Select products to buy</th> <th>Available Quantity</th> <th>Enter quantity</th> <th>Image</th> <th>Name</th> <th>Manufacturer</th> <th>Price</th> <th>Description</th> <th>Sim Support</th> </tr>"; //NB: now output each row of records while($row=mysql_fetch_assoc($result))//NB: while the current db table's row still has fields to extract { //extract($row); print "<tr align='center'> <td><input type='checkbox' name='modifyArray[]' value='$row[iD]' id=\"modifyArray[]\" /></td> <td style='text-align:center'>"; print "d"; print "</td> <td style='text-align:center'><input type='text' name='updateQuantity' size='2' value=1 /></td> <td>"; if(!empty($row['Image'])) { $curImage=$row['Image']; } else if(empty($row['Image'])) { $curImage='fileUploadIcon.jpg'; } print "<img src=$curImage width='70px' height='90px' title='please upload product image' /> </td> <td> $row[Name] </td> <td> $row[Manufacturer] </td> <td> $$row[Price]</td> <td align='left'>$row[Description]</td> <td>$row[simSupport]</td> </tr>"; //}//END INNER IF }//END WHILE }//END BIG IF ?> </table> <input type='submit' value='Proceed to checkout' name='checkout' /> </form> </body> </html> **I don't know how to incorporate the $result1... Quote Link to comment https://forums.phpfreaks.com/topic/240751-how-to-use-two-mysql_query/ Share on other sites More sharing options...
requinix Posted June 29, 2011 Share Posted June 29, 2011 You can combine the two into one query. Thankfully, MySQL is lenient with grouping... SELECT ID, Image, Name, Manufacturer, Price, Description, SimSupport, COUNT(1) AS Count FROM bellProducts GROUP BY Name, Manufacturer And you shouldn't be putting semicolons in the queries to mysql_query(). Quote Link to comment https://forums.phpfreaks.com/topic/240751-how-to-use-two-mysql_query/#findComment-1236604 Share on other sites More sharing options...
mpsn Posted June 29, 2011 Author Share Posted June 29, 2011 Hi, can you explain what COUNT(1) AS COUNT means? Quote Link to comment https://forums.phpfreaks.com/topic/240751-how-to-use-two-mysql_query/#findComment-1236605 Share on other sites More sharing options...
requinix Posted June 29, 2011 Share Posted June 29, 2011 - COUNT(X) counts the number of non-NULL Xs. Since 1 is never null, COUNT(1) is always 1. Imagine that it happens before the GROUP BY (so every row has a 1 there) and what the grouping does is sum up all the values per group. - "X AS Y" essentially renames X as Y when retrieving the values. If you had some long and complicated formula for a column then you'd have to retype that formula to get the value, but with an alias you just use the alias. Quote Link to comment https://forums.phpfreaks.com/topic/240751-how-to-use-two-mysql_query/#findComment-1236607 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.