judejitsu Posted May 24, 2012 Share Posted May 24, 2012 Hi guys, I am learning PHP on my own. I have this small office project where my script fetches data from a mysql database. I am having problems though with the query. I need to merge rows of the same column data and count its occurrence. Say, table report: Col1___Col2___Col3 AAA____BBB___CCC AAA____BBB___CCC XXX____MMM___NNN Basically, I need to do this as output to PHP: Col1___Col2___Col3___qty AAA____BBB___CCC___2 XXX___MMM__NNN___1 Here's part my script : $conn = mysql_connect("localhost", "root", "123456") or die(mysql_error()); mysql_select_db("store") or die(mysql_error()); // $masterproduct = $_POST['masterproduct']; $family = $_POST['family']; $startDT = $_POST['birthstamp']." ".$_POST['hour'].":00:00"; $endDT = $_POST['birthstamp']." ".$_POST['hour2'].":00:00"; $sql = mysql_query("SELECT * FROM report WHERE family='$family' AND birthstamp BETWEEN '$startDT' AND '$endDT'") or die(mysql_error()); if(mysql_num_rows($sql) == 0) { echo "<center><h3 style=\"color=red\">Sorry, no Orders with those parameters are saved in the database. Please import a New CSV with that range.</h3></center>"; } else { echo " <table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\" class=\"data\"> <tr> <td class=\"dataHeader\">Sales Order</td> <td class=\"dataHeader\">Sales Order Code</td> <td class=\"dataHeader\">Family</td> <td class=\"dataHeader\">Product Code</td> <td class=\"dataHeader\">Quantity</td> <td class=\"dataHeader\">Birth Stamp</td> <td class=\"dataHeader\">Due Date</td> </tr> "; while($result = mysql_fetch_array($sql)) { echo " <tr> <td class=\"data\">".$result['salesorder']."</td> <td class=\"data\"><span class=\"title\">*".$result['salesorder']."*</span><br />".$result['salesorder']."</td> <td class=\"data\">".$result['family']."</td> <td class=\"data\"><span class=\"title\">*".$result['masterproduct']."*</span><br />".$result['masterproduct']."</td> <td class=\"data\">"; //need to echo the Quantity value here echo "</td> <td class=\"data\">".$result['birthstamp']."</td> <td class=\"data\"><span class=\"title\">*".$result['duedate']."*</span><br />".$result['duedate']."</td> </tr> "; } echo "</table>"; } ?> I will be very thankful if someone can help me what I need to do with my script. Thank you! Quote Link to comment https://forums.phpfreaks.com/topic/263089-group-rows-of-the-same-column-data-and-count/ Share on other sites More sharing options...
Barand Posted May 25, 2012 Share Posted May 25, 2012 SELECT col1, col2, col3, COUNT(*) as total FROM tablename GROUP BY col1, col2, col3 Quote Link to comment https://forums.phpfreaks.com/topic/263089-group-rows-of-the-same-column-data-and-count/#findComment-1348463 Share on other sites More sharing options...
judejitsu Posted May 25, 2012 Author Share Posted May 25, 2012 SELECT col1, col2, col3, COUNT(*) as total FROM tablename GROUP BY col1, col2, col3 Thank you for the fast reply, will try this out Quote Link to comment https://forums.phpfreaks.com/topic/263089-group-rows-of-the-same-column-data-and-count/#findComment-1348471 Share on other sites More sharing options...
judejitsu Posted May 25, 2012 Author Share Posted May 25, 2012 I cannot make it to work. I changed the $sql from my code above to this: $sql = mysql_query("SELECT salesorder, masterproduct, family, COUNT(*) as total FROM report GROUP BY salesorder, masterproduct, family, WHERE family='$family' AND birthstamp BETWEEN '$startDT' AND '$endDT'") or die(mysql_error()); Am I doing something wrong? Because my program output becomes : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE family='PPS' AND birthstamp BETWEEN '2012-05-24 14:00:00' AND '2012-05-24 ' at line 1 Quote Link to comment https://forums.phpfreaks.com/topic/263089-group-rows-of-the-same-column-data-and-count/#findComment-1348474 Share on other sites More sharing options...
Barand Posted May 25, 2012 Share Posted May 25, 2012 $sql = mysql_query("SELECT salesorder, masterproduct, family, COUNT(*) as total FROM report WHERE family='$family' AND birthstamp BETWEEN '$startDT' AND '$endDT' GROUP BY salesorder, masterproduct, family") or die(mysql_error()); your WHEREand GROUP BY clauses were in wrong sequence Quote Link to comment https://forums.phpfreaks.com/topic/263089-group-rows-of-the-same-column-data-and-count/#findComment-1348476 Share on other sites More sharing options...
judejitsu Posted May 25, 2012 Author Share Posted May 25, 2012 $sql = mysql_query("SELECT salesorder, masterproduct, family, COUNT(*) as total FROM report WHERE family='$family' AND birthstamp BETWEEN '$startDT' AND '$endDT' GROUP BY salesorder, masterproduct, family") or die(mysql_error()); your WHEREand GROUP BY clauses were in wrong sequence Thank you for that! It's working, however it returns zero rows instead of returning all rows which qualifies from the sql query... Here's the part of the code : $family = $_POST['family']; $startDT = $_POST['birthstamp']." ".$_POST['hour'].":00:00"; $endDT = $_POST['birthstamp']." ".$_POST['hour2'].":00:00"; //$sql = mysql_query("SELECT * FROM report WHERE family='$family' AND birthstamp BETWEEN '$startDT' AND '$endDT'") or die(mysql_error()); $sql = mysql_query("SELECT salesorder, masterproduct, family, COUNT(*) as total FROM report WHERE family='$family' AND birthstamp BETWEEN '$startDT' AND '$endDT' GROUP BY salesorder, masterproduct, family") or die(mysql_error()); if(mysql_num_rows($sql) == 0) { echo "<center><h3 style=\"color=red\">Sorry, no Orders with those parameters are saved in the database. Please import a New CSV with that range.</h3></center>"; } else { echo " <table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\" class=\"data\"> <tr> <td class=\"dataHeader\">Sales Order</td> <td class=\"dataHeader\">Sales Order Code</td> <td class=\"dataHeader\">Family</td> <td class=\"dataHeader\">Product Code</td> <td class=\"dataHeader\">Quantity</td> <td class=\"dataHeader\">Birth Stamp</td> <td class=\"dataHeader\">Due Date</td> </tr> "; while($result = mysql_fetch_array($sql)) { echo " <tr> <td class=\"data\">".$result['salesorder']."</td> <td class=\"data\"><span class=\"title\">*".$result['salesorder']."*</span><br />".$result['salesorder']."</td> <td class=\"data\">".$result['family']."</td> <td class=\"data\"><span class=\"title\">*".$result['masterproduct']."*</span><br />".$result['masterproduct']."</td> <td class=\"data\">"; //need to echo the value here echo "</td> <td class=\"data\">".$result['birthstamp']."</td> <td class=\"data\"><span class=\"title\">*".$result['duedate']."*</span><br />".$result['duedate']."</td> </tr> "; } echo "</table>"; } Thank you so much for your help... Quote Link to comment https://forums.phpfreaks.com/topic/263089-group-rows-of-the-same-column-data-and-count/#findComment-1348478 Share on other sites More sharing options...
Barand Posted May 25, 2012 Share Posted May 25, 2012 $query = "SELECT salesorder, masterproduct, family, COUNT(*) as total FROM report WHERE family='$family' AND birthstamp BETWEEN '$startDT' AND '$endDT' GROUP BY salesorder, masterproduct, family"; $sql = mysql_query($query) or die (mysql_error()) echo "<pre>$query</pre>"; // so you can check the actual submitted query ... Quote Link to comment https://forums.phpfreaks.com/topic/263089-group-rows-of-the-same-column-data-and-count/#findComment-1348481 Share on other sites More sharing options...
judejitsu Posted May 25, 2012 Author Share Posted May 25, 2012 $query = "SELECT salesorder, masterproduct, family, COUNT(*) as total FROM report WHERE family='$family' AND birthstamp BETWEEN '$startDT' AND '$endDT' GROUP BY salesorder, masterproduct, family"; $sql = mysql_query($query) or die (mysql_error()) echo "<pre>$query</pre>"; // so you can check the actual submitted query ... Thanks. It's still the same. Here's the preformatted output: SELECT salesorder, masterproduct, family, COUNT(*) as total FROM report WHERE family='F201_PWS_PF' AND birthstamp BETWEEN '2012-05-24 12:00:00' AND '2012-05-24 16:00:00' GROUP BY salesorder, masterproduct, family However, when I change it to : $sql = mysql_query("SELECT salesorder, masterproduct, family, birthstamp, duedate, COUNT( * ) AS total FROM report WHERE family = '$family' GROUP BY salesorder, masterproduct, family, birthstamp, duedate "); It actually works fine, but the problem is it stops working when I add the BETWEEN clause: $sql = mysql_query("SELECT salesorder, masterproduct, family, birthstamp, duedate, COUNT( * ) AS total FROM report WHERE family = '$family' AND birthstamp BETWEEN '$startDT' AND '$endDT' GROUP BY salesorder, masterproduct, family, birthstamp, duedate "); please note that the $startDT and $endDT variables are fetched from a post and then I just concatenated some text to fill the remaining date time. $startDT = $_POST['birthstamp']." ".$_POST['hour'].":00:00"; $endDT = $_POST['birthstamp']." ".$_POST['hour2'].":00:00"; Thank you for your help ... Really appreciate it... Quote Link to comment https://forums.phpfreaks.com/topic/263089-group-rows-of-the-same-column-data-and-count/#findComment-1348482 Share on other sites More sharing options...
Barand Posted May 25, 2012 Share Posted May 25, 2012 do you have dates in your data BETWEEN '2012-05-24 12:00:00' AND '2012-05-24 16:00:00' ? Quote Link to comment https://forums.phpfreaks.com/topic/263089-group-rows-of-the-same-column-data-and-count/#findComment-1348486 Share on other sites More sharing options...
judejitsu Posted May 25, 2012 Author Share Posted May 25, 2012 do you have dates in your data BETWEEN '2012-05-24 12:00:00' AND '2012-05-24 16:00:00' ? Thank you for the reply... Yes, I have. Here's a sample output if I exclude the BETWEEN clause. Quote Link to comment https://forums.phpfreaks.com/topic/263089-group-rows-of-the-same-column-data-and-count/#findComment-1348490 Share on other sites More sharing options...
judejitsu Posted May 25, 2012 Author Share Posted May 25, 2012 Thanks for all the replies. I finally figured out the mistake. The date was 23 not 24. Quote Link to comment https://forums.phpfreaks.com/topic/263089-group-rows-of-the-same-column-data-and-count/#findComment-1348495 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.