Group Rows of the Same Column Data and Count

judejitsu · 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

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! :confused:

Barand · May 25, 2012

SELECT col1, col2, col3, COUNT(*) as total

FROM tablename

GROUP BY col1, col2, col3

judejitsu · 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

judejitsu · 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

Barand · 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

judejitsu · 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...

Barand · 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

...

judejitsu · 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...

Barand · May 25, 2012

do you have dates in your data BETWEEN '2012-05-24 12:00:00' AND '2012-05-24 16:00:00' ?

judejitsu · 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. :confused:

judejitsu · May 25, 2012

Thanks for all the replies. I finally figured out the mistake. The date was 23 not 24.

Sign In

Group Rows of the Same Column Data and Count

Recommended Posts

judejitsu

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

judejitsu

Link to comment

Share on other sites

judejitsu

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

judejitsu

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

judejitsu

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

judejitsu

Link to comment

Share on other sites

judejitsu

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information