dououde4065 2012-05-24 07:06
浏览 14
已采纳

通过PHP从mysql中获取数据，然后将具有相似列值和计数的行分组

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.

Say Table 'orders'

salesorder family product
1111111    pi_gx  af000
1111111    pi_gx  af000
1111112    sfng   af111
1111113    pi_gx  af000

will display in my php page

sales order   family  qty   product
1111111       pi_gx   2     af000
1111112       sfng    1     af111
1111113       pi_gx   1     af000

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.

Here's the code:

<body class="printable"><h1 align="center">New Orders Dropped for Product Integration 1X</h1>
<table align="center" width="100%">
<tr>
    <td class="labels">Prepared: </td>
<td class="boxed"><?php date_default_timezone_set("Asia/Singapore");$today = date("d/m/y H:i");echo $today; ?></td>
    <td class="divider">&nbsp;</td>
    <td class="labels">Time Coverage:  </td>
    <td class="boxed">12:00 to 2:00</td>
    <td class="divider">&nbsp;</td>
    <td class="labels">BirthStamp: </td>
    <td class="boxed">5/21/2012</td>
    <td class="divider">&nbsp;</td>
    <td class="labels">Saved: </td>
    <td class="boxed"><?php echo $today; ?></td>
</tr>
<tr>
    <td class="labels">Prepared by (Production): </td>
    <td><input type="text" name="preparer" id="preparer" class="boxedPrepared" /></td>
    <td class="divider"></td>
    <td class="labels">Recorded by (Store): </td>
    <td><input type="text" name="recorder" id="recorded" class="boxedPrepared" /></td>
    <td class="divider"></td>
    <td class="labels">Recorded: </td>
    <td class="boxed" colspan="3"><?php echo $today; ?></td>
</tr>
</table>
<br />
<?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 "<center><b>No ORDER/S in Queue</b></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>";
}

?>
  • 写回答

3条回答 默认 最新

  • dongtan1845 2012-05-24 07:08
    关注
    SELECT salesorder, family, product, COUNT() AS qty FROM orders 
       GROUP BY salesorder;

    EDIT: OK, try this on for size:

    SELECT r.*, t.qty FROM report r LEFT JOIN
      (SELECT salesorder, COUNT() AS qty FROM orders 
         GROUP BY salesorder) t
      ON t.salesorder=r.salesorder
    ORDER BY r.salesorder AND r.masterproduct ASC
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题？

悬赏问题

  • ¥60 版本过低apk如何修改可以兼容新的安卓系统
  • ¥25 由IPR导致的DRIVER_POWER_STATE_FAILURE蓝屏
  • ¥50 有数据，怎么建立模型求影响全要素生产率的因素
  • ¥50 有数据，怎么用matlab求全要素生产率
  • ¥15 TI的insta-spin例程
  • ¥15 完成下列问题完成下列问题
  • ¥15 C#算法问题, 不知道怎么处理这个数据的转换
  • ¥15 YoloV5 第三方库的版本对照问题
  • ¥15 请完成下列相关问题！
  • ¥15 drone 推送镜像时候 purge: true 推送完毕后没有删除对应的镜像,手动拷贝到服务器执行结果正确在样才能让指令自动执行成功删除对应镜像，如何解决？