iT邦幫忙

0

PHP MYSQL運算問題

php mysql
scjh8214 2019-02-05 14:35:25 ‧ 2344 瀏覽

我想做一個統計排行
欄位有
|月份|餐點名稱|銷售量|銷售額|銷售額變化|
前面四個我已經成功顯示出來
但銷售額變化的部分
不清楚如何抓取上個月的銷售額與本月的銷售額做運算
求救高手們給點提點!

目前程式碼如下

<?
echo '
		<table border="1" bgcolor="#ccffcc" align="center">
			<tr>
			<td width="120" align="center"><font size="5">月份</font></td>
			<td width="120" align="center"><font size="5">餐點名稱</font></td>
			<td width="120" align="center"><font size="5">銷售量</font></td>
			<td width="120" align="center"><font size="5">銷售額</font></td>
			<td width="110" align="center"><font size="5">銷售額變化</font></td>
			</tr>';  
  $sql = "SELECT DISTINCTROW DATE_FORMAT(`tmealday`, '%c月份') AS mth,MealNumber, Sum(nReservedata.amount), Sum(nReservedata.Price) FROM nReservedata 
GROUP BY mth,MealNumber
ORDER BY mth ASC,MealNumber ASC";
  $result = mysql_query($sql) or die('MySQL query error');
  while($row = mysql_fetch_array($result))
  {
   	$Odnumber = $row[0];
	$UAccount = $row[1];
	$tmealday = $row[2];
	$tmealtime = $row[3];

		echo "<tr>";
		echo "<td align='center'><font size='4'>$Odnumber</font></td>";
		echo "<td align='center'><font size='4'>$UAccount</font></td>";
		echo "<td align='center'><font size='4'>$tmealday</font></td>";
		echo "<td align='center'><font size='4'>$tmealtime</font></td>";

  }
  		echo   '</tr></table><br>';
?>
scjh8214 iT邦新手 5 級 ‧ 2019-02-05 14:35:34 檢舉
1
weiclin iT邦高手 4 級 ‧ 2019-02-05 15:18:49 檢舉
先學習怎樣貼程式碼吧:
https://ithelp.ithome.com.tw/markdown
淺水員 iT邦大師 6 級 ‧ 2019-02-05 16:14:30 檢舉
如果sql直接取所有結果太複雜，也可以考慮其中一部分用php處理
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

0
淺水員
iT邦大師 6 級 ‧ 2019-02-05 23:41:17

如果要直接用SQL語法處理
可以用：window function 的 LAG函式
其中還搭配子查詢

/*建立測試表格*/
CREATE TABLE test(
	`mth` int,
	`prod` varchar(2),
	`price` int
);

/*測試資料*/
INSERT INTO `test`(`mth`,`prod`,`price`) VALUES 
(1,'A',1),
(1,'B',2),
(1,'B',3),
(1,'C',9),
(1,'A',5),
(1,'A',6),
(2,'A',1),
(2,'B',2),
(2,'C',3),
(2,'A',4),
(2,'B',5),
(2,'C',6);

/*取值*/
SELECT
	mth,
	prod,
	total,
	LAG(total,1) OVER (PARTITION BY prod ORDER BY mth,prod) - total AS diff
FROM (SELECT 
		mth,
		prod,
		sum(price) AS total
	FROM test
	GROUP BY mth,prod
	ORDER BY mth,prod) AS tmp;

如果未來希望從事相關工作
建議訓練自己能夠盡可能找尋解決方式
在提出問題之後
最好能稍微描述一下自己嘗試過哪些方式解決

看更多先前的回應...收起先前的回應...
scjh8214 iT邦新手 5 級 ‧ 2019-02-06 17:54:49 檢舉

好的!!謝謝
如果要用PHP直接處理該用甚麼方式處理呢??

淺水員 iT邦大師 6 級 ‧ 2019-02-06 20:41:06 檢舉 
<?php
//假設用 PDOStatement::fetchAll(PDO::FETCH_ASSOC)
//或 mysqli_fetch_all 取出的資料如下：
$testData=array(
    array('mth'=>1,'prodId'=>1,'income'=>100),
    array('mth'=>1,'prodId'=>2,'income'=>75),
    array('mth'=>1,'prodId'=>3,'income'=>125),
    array('mth'=>2,'prodId'=>1,'income'=>110),
    array('mth'=>2,'prodId'=>2,'income'=>130),
    array('mth'=>2,'prodId'=>4,'income'=>135),
    array('mth'=>3,'prodId'=>1,'income'=>115),
    array('mth'=>3,'prodId'=>2,'income'=>140),
    array('mth'=>3,'prodId'=>3,'income'=>122),
    array('mth'=>3,'prodId'=>4,'income'=>132),
);

//函式 addDiff 可以幫每一列加上 diff 欄位
//其中 arr 是如上面排序好的資料
//empty 是如果沒有上個月資料的預設值
function addDiff(&$arr,$empty=null)
{
    $n=count($arr);
    if($n===0){
        return 0;
    }
    $curMth=$arr[0]['mth'];
    for($i=0;$i<$n;++$i){
        if($arr[$i]['mth']!==$curMth){
            $curMth=$arr[$i]['mth'];
            break;
        } else {
            $arr[$i]['diff']=$empty;
        }
    }
    $k=0;
    for(;$i<$n;++$i){
        $curMth=$arr[$i]['mth'];
        $curProdId=$arr[$i]['prodId'];
        while($k<$n){
            $cmp=compareLastMth($arr[$k],$arr[$i]);
            if($cmp>=0){
                break;
            }
            ++$k;
        }
        if($k<$i && $cmp==0){
            $arr[$i]['diff']=$arr[$i]['income']-$arr[$k]['income'];
        } else {
            $arr[$i]['diff']=$empty;
        }
    }
}

//兩列的比較函數，addDiff裡面有用到
//注意：月份用 row1 的月份 跟 row2 的前一月份 做比較
//因此，row1 為較舊的資料，row2 為較新的資料
function compareLastMth($row1,$row2)
{
    if($row1['mth']!==$row2['mth']-1){
        return $row1['mth']-$row2['mth']+1;
    } else {
        return $row1['prodId']-$row2['prodId'];
    }
}

//執行 addDiff ，並設定空值填 'empty'
addDiff($testData,'empty');
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>DEMO</title>
</head>
<body>
    <table>
        <tr>
            <th>月份</th>
            <th>產品Id</th>
            <th>收入</th>
            <th>上月比較</th>
        </tr>
        <?php foreach($testData as $row):?>
        <tr>
            <td><?php echo $row['mth'];?></td>
            <td><?php echo $row['prodId'];?></td>
            <td><?php echo $row['income'];?></td>
            <td><?php echo $row['diff'];?></td>
        </tr>
        <?php endforeach;?>
    </teble>
    <style>
        table{
            border-collapse: collapse;
        }
        table,tr,td,th{
            border:1px solid black;
        }
    </style>
</body>
</html>
scjh8214 iT邦新手 5 級 ‧ 2019-02-07 21:10:01 檢舉

這個方式是將資料庫資料丟進陣列裡面再做處理嗎??

scjh8214 iT邦新手 5 級 ‧ 2019-02-07 22:21:33 檢舉

我成功丟進陣列裡了
但ID列顯示出來都是????????
試了很多種方法也無法顯示

<?php
//假設用 PDOStatement::fetchAll(PDO::FETCH_ASSOC)
//或 mysqli_fetch_assoc 取出的資料如下：

$con=mysqli_connect("localhost","xxxx","xxxxxx","xxx"); 
mysqli_query('set names `utf8`');
 
$sql="SELECT DISTINCTROW DATE_FORMAT(`tmealday`, '%c月份') AS mth,MealNumber, Sum(amount) AS amount, Sum(Price) AS income FROM nReservedata 
 GROUP BY mth,MealNumber	ORDER BY mth ASC,income DESC";
$result=mysqli_query($con,$sql);
$testData = array();
if(mysqli_num_rows($result) > 0 ){
	while($row=mysqli_fetch_assoc($result)){
		$testData[]=$row;
	}
}

//函式 addDiff 可以幫每一列加上 diff 欄位
//其中 arr 是如上面排序好的資料
//empty 是如果沒有上個月資料的預設值
function addDiff(&$arr,$empty=null)
{
    $n=count($arr);
    if($n===0){
        return 0;
    }
    $curMth=$arr[0]['mth'];
    for($i=0;$i<$n;++$i){
        if($arr[$i]['mth']!==$curMth){
            $curMth=$arr[$i]['mth'];
            break;
        } else {
            $arr[$i]['diff']=$empty;
        }
    }
    $k=0;
    for(;$i<$n;++$i){
        $curMth=$arr[$i]['mth'];
        $curProdId=$arr[$i]['MealNumber'];
        while($k<$n){
            $cmp=compareLastMth($arr[$k],$arr[$i]);
            if($cmp>=0){
                break;
            }
            ++$k;
        }
        if($k<$i && $cmp==0){
            $arr[$i]['diff']=$arr[$i]['income']-$arr[$k]['income'];
        } else {
            $arr[$i]['diff']=$empty;
        }
    }
}

//兩列的比較函數，addDiff裡面有用到
//注意：月份用 row1 的月份 跟 row2 的前一月份 做比較
//因此，row1 為較舊的資料，row2 為較新的資料
function compareLastMth($row1,$row2)
{
    if($row1['mth']!==$row2['mth']-1){
        return $row1['mth']-$row2['mth']+1;
    } else {
        return $row1['MealNumber']-$row2['MealNumber'];
    }
}

//執行 addDiff ，並設定空值填 'empty'
addDiff($testData,'no');
?>
<!DOCTYPE html>
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>DEMO</title>
</head>
<body>
    <table>
        <tr>
            <th>月份</th>
            <th>產品Id</th>
            <th>收入</th>
            <th>上月比較</th>
        </tr>
        <?php foreach($testData as $row):?>
        <tr>
            <td><?php echo $row['mth'];?></td>
            <td><?php echo $row['MealNumber'];?></td>
            <td><?php echo $row['income'];?></td>
            <td><?php echo $row['diff'];?></td>
        </tr>
        <?php endforeach;?>
    </teble>
    <style>
        table{
            border-collapse: collapse;
        }
        table,tr,td,th{
            border:1px solid black;
        }
    </style>
</body>
</html>
scjh8214 iT邦新手 5 級 ‧ 2019-02-08 00:31:52 檢舉

如果SQL語法是顯示原本的資料表的餐點代碼可以正常顯示
但SQL語法裡增加關聯讓代碼轉換成中文就會出現??????無法正常顯示
我有設定UTF-8了還是一樣無法正常顯示0.0

淺水員 iT邦大師 6 級 ‧ 2019-02-08 18:20:16 檢舉

抱歉我很少用 mysqli 所以上面打錯函式名稱
mysqli_fetch_assoc 改成 mysqli_fetch_all 吧
mysqli_fetch_all 的回傳值直接就能用了，不用自己寫到陣列中
然後可以印出來檢查一下結果

$result=mysqli_query($con,$sql);
$arr=mysqli_fetch_all($result,MYSQLI_ASSOC);
echo '<pre>';
var_dump($arr);
echo '</pre>';

另外我給的主要是作為範例，所以

  1. 根據欄位的資料型態不同， compareLastMth 也要做修改 (數字的比較才能直接加減，字串要用 strcmp 等函式)
  2. testData 的排序是 mth, prodId，而 addDiff 是基於這樣的排序去寫的，所以排序不一樣的話，很可能要重寫 addDiff 函式
scjh8214 iT邦新手 5 級 ‧ 2019-02-08 20:10:30 檢舉

我成功讓它顯示中文了但有一個很奇怪的問題
比較那欄的運算值有些是對的有些是錯的

<?php
$servername = '';
$username = '';
$password = '';
$mysqldb = '';

$mysqli = new mysqli($servername, $username, $password, $mysqldb);

if ($mysqli->connect_error) {
  die(" Error: " . $mysqli->connect_error);
}

$mysqli->query("SET NAMES utf8");
$mysqli->set_charset("utf8mb4");

date_default_timezone_set('Asia/Taipei');

$data =$mysqli->query(
    "SELECT DISTINCTROW DATE_FORMAT(`tmealday`, '%c月份') 
    AS mth,nReservedata.MealNumber AS MealNumber,Menudata.MealName 
    AS MealName, Sum(nReservedata.amount) 
    AS amount, 
    Sum(nReservedata.Price) AS income FROM nReservedata 
    INNER JOIN Menudata ON nReservedata.MealNumber=Menudata.MealNumber 
    GROUP BY mth,nReservedata.MealNumber	
    ORDER BY mth ASC,MealNumber ASC"
);

$testData = array();
if(mysqli_num_rows($data) > 0 ){
	while($row=mysqli_fetch_assoc($data)){
		$testData[]=$row;
	}
}
//函式 addDiff 可以幫每一列加上 diff 欄位
//其中 arr 是如上面排序好的資料
//empty 是如果沒有上個月資料的預設值
function addDiff(&$arr,$empty=null)
{
    $n=count($arr);
    if($n===0){
        return 0;
    }
    $curMth=$arr[0]['mth'];
    for($i=0;$i<$n;++$i){
        if($arr[$i]['mth']!==$curMth){
            $curMth=$arr[$i]['mth'];
            break;
        } else {
            $arr[$i]['diff']=$empty;
        }
    }
    $k=0;
    for(;$i<$n;++$i){
        $curMth=$arr[$i]['mth'];
        $curProdId=$arr[$i]['MealNumber'];
        while($k<$n){
            $cmp=compareLastMth($arr[$k],$arr[$i]);
            if($cmp>=0){
                break;
            }
            ++$k;
        }
        if($k<$i && $cmp==0){
            $arr[$i]['diff']=$arr[$i]['income']-$arr[$k]['income'];
        } else {
            $arr[$i]['diff']=$empty;
        }
    }
}

//兩列的比較函數，addDiff裡面有用到
//注意：月份用 row1 的月份 跟 row2 的前一月份 做比較
//因此，row1 為較舊的資料，row2 為較新的資料
function compareLastMth($row1,$row2)
{
    if($row1['mth']!==$row2['mth']-1){
        return $row1['mth']-$row2['mth']+1;
    } else {
        return $row1['MealNumber']-$row2['MealNumber'];
    }
}

//執行 addDiff ，並設定空值填 'empty'
addDiff($testData,'empty');
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>DEMO</title>
</head>
<body>
    <table>
        <tr>
            <th>月份</th>
            <th>產品Id</th>
            <th>產品</th>
            <th>收入</th>
            <th>上月比較</th>
        </tr>
        <?php foreach($testData as $row):?>
        <tr>
            <td><?php echo $row['mth'];?></td>
            <td><?php echo $row['MealNumber'];?></td>
            <td><?php echo $row['MealName'];?></td>
            <td><?php echo $row['income'];?></td>
            <td><?php echo $row['diff'];?></td>
        </tr>
        <?php endforeach;?>
    </teble>
    <style>
        table{
            border-collapse: collapse;
        }
        table,tr,td,th{
            border:1px solid black;
        }
    </style>
</body>
</html>
淺水員 iT邦大師 6 級 ‧ 2019-02-08 20:28:05 檢舉

前面的留言我有提過的那兩點可以看一下
其實我是寫範例給你參考
不是直接修改拿來套的

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙