Databases
StackOverflow2013
Postsdbo
POFetch data from mysql via PHP, then group rows with similar column values and count
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFetch data from mysql via PHP, then group rows with similar column values and count
    text
    copied!<p>I basically have this program which fetches orders from a database. My problem is I need to make a report in such a way that the script will get rows with the same column value then count them and display them.</p> <p>Say Table 'orders'</p> <pre> salesorder family product 1111111 pi_gx af000 1111111 pi_gx af000 1111112 sfng af111 1111113 pi_gx af000 </pre> <p>will display in my php page</p> <pre> sales order family qty product 1111111 pi_gx 2 af000 1111112 sfng 1 af111 1111113 pi_gx 1 af000 </pre> <p>It counts the quantity of row of the said sales order and displays the quantity, at the same time displays only a single copy of that sales order in my page.</p> <p>Here's the code:</p> <pre><code>&lt;body class="printable"&gt;&lt;h1 align="center"&gt;New Orders Dropped for Product Integration 1X&lt;/h1&gt; &lt;table align="center" width="100%"&gt; &lt;tr&gt; &lt;td class="labels"&gt;Prepared: &lt;/td&gt; &lt;td class="boxed"&gt;&lt;?php date_default_timezone_set("Asia/Singapore");$today = date("d/m/y H:i");echo $today; ?&gt;&lt;/td&gt; &lt;td class="divider"&gt;&amp;nbsp;&lt;/td&gt; &lt;td class="labels"&gt;Time Coverage: &lt;/td&gt; &lt;td class="boxed"&gt;12:00 to 2:00&lt;/td&gt; &lt;td class="divider"&gt;&amp;nbsp;&lt;/td&gt; &lt;td class="labels"&gt;BirthStamp: &lt;/td&gt; &lt;td class="boxed"&gt;5/21/2012&lt;/td&gt; &lt;td class="divider"&gt;&amp;nbsp;&lt;/td&gt; &lt;td class="labels"&gt;Saved: &lt;/td&gt; &lt;td class="boxed"&gt;&lt;?php echo $today; ?&gt;&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td class="labels"&gt;Prepared by (Production): &lt;/td&gt; &lt;td&gt;&lt;input type="text" name="preparer" id="preparer" class="boxedPrepared" /&gt;&lt;/td&gt; &lt;td class="divider"&gt;&lt;/td&gt; &lt;td class="labels"&gt;Recorded by (Store): &lt;/td&gt; &lt;td&gt;&lt;input type="text" name="recorder" id="recorded" class="boxedPrepared" /&gt;&lt;/td&gt; &lt;td class="divider"&gt;&lt;/td&gt; &lt;td class="labels"&gt;Recorded: &lt;/td&gt; &lt;td class="boxed" colspan="3"&gt;&lt;?php echo $today; ?&gt;&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;br /&gt; &lt;?php $conn = mysql_connect("localhost", "root", "123456") or die(mysql_error()); mysql_select_db("store") or die(mysql_error()); $sql = mysql_query("SELECT * FROM report ORDER BY salesorder AND masterproduct ASC") or die(mysql_error()); if(mysql_num_rows($sql) == 0) { echo "&lt;center&gt;&lt;b&gt;No ORDER/S in Queue&lt;/b&gt;&lt;/center&gt;"; } else { echo " &lt;table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\" class=\"data\"&gt; &lt;tr&gt; &lt;td class=\"dataHeader\"&gt;Sales Order&lt;/td&gt; &lt;td class=\"dataHeader\"&gt;Sales Order Code&lt;/td&gt; &lt;td class=\"dataHeader\"&gt;Family&lt;/td&gt; &lt;td class=\"dataHeader\"&gt;Product Code&lt;/td&gt; &lt;td class=\"dataHeader\"&gt;Quantity&lt;/td&gt; &lt;td class=\"dataHeader\"&gt;Birth Stamp&lt;/td&gt; &lt;td class=\"dataHeader\"&gt;Due Date&lt;/td&gt; &lt;/tr&gt; "; while($result = mysql_fetch_array($sql)) { echo " &lt;tr&gt; &lt;td class=\"data\"&gt;".$result['salesorder']."&lt;/td&gt; &lt;td class=\"data\"&gt;&lt;span class=\"title\"&gt;*".$result['salesorder']."*&lt;/span&gt;&lt;br /&gt;".$result['salesorder']."&lt;/td&gt; &lt;td class=\"data\"&gt;".$result['family']."&lt;/td&gt; &lt;td class=\"data\"&gt;&lt;span class=\"title\"&gt;*".$result['masterproduct']."*&lt;/span&gt;&lt;br /&gt;".$result['masterproduct']."&lt;/td&gt; &lt;td class=\"data\"&gt;"; //need to echo the value here echo "&lt;/td&gt; &lt;td class=\"data\"&gt;".$result['birthstamp']."&lt;/td&gt; &lt;td class=\"data\"&gt;&lt;span class=\"title\"&gt;*".$result['duedate']."*&lt;/span&gt;&lt;br /&gt;".$result['duedate']."&lt;/td&gt; &lt;/tr&gt; "; } echo "&lt;/table&gt;"; } ?&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload