我有一個大數據庫，存儲了很多關于客戶和賬單等的信息。我想做的是，使用客戶的ID，搜索他們所有的賬單，SUM（）每個包含的價格，并存儲它們以打印一個唯一的賬單。所以我沙吞聲地想著這個：try { $conn = new PDO('mysql:host=myhost;dbname=accounting','user','pass'); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);}catch(PDOException $e){ echo "ERROR: " . $e->getMessage();}$stmt = $conn->prepare('SELECT GROUP_CONCAT(DISTINCT source_external_subscriber_id) AS ids FROM cdr');$stmt->execute();foreach ($stmt as $row) { $string = $row['ids'];}$array = explode(',', $string);array_pop($array);array_shift($array);$destinationId = 0;foreach ($array as $id) { $stmt2 = $conn->prepare('SELECT id, call_type, source_customer_cost FROM cdr WHERE source_external_subscriber_id = :id AND destination_account_id = :destinationId'); $stmt2->execute(array('id' => $id, 'destinationId' => $destinationId)); foreach ($stmt2 as $row2) { $datos[] = $row2; }}我不相信結果，所以我決定自己檢查，首先執行我的代碼廣告，然后尋找客戶將賬單數量與數組的長度進行比較（因此還缺少SUM（）然后，當我正在尋找一種更有說服力的方式時，我偶然發現了這個非常有用的帖子。所以我把我的代碼改成了這個。但在這里，他們只解釋如何編寫查詢，而不是如何將其存儲在數組中。try { $conn = new PDO('mysql:host=host;dbname=accounting','user','pass'); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); var_dump($conn);}catch(PDOException $e){ echo "ERROR: " . $e->getMessage();}$destinationId = 0;$stmt2 = $conn->prepare('SELECT source_external_subscriber_id, source_customer_cost FROM cdr WHERE destination_account_id = :destinationId GROUP BY source_external_subscriber_id');$stmt2->execute(array('destinationId' => $destinationId));foreach ($stmt as $row) { $datos[] = $row2;}謝謝大家的幫助，希望大家有美好的一天！
2 回答
慕哥9229398
TA貢獻1877條經驗 獲得超6個贊
因此，如果我正確理解您，則您正在搜索類似以下內容的內容：
SELECT source_external_subscriber_id id, (SELECT SUM(source_customer_cost)) total FROM cdr GROUP BY source_external_subscriber_id;
牛魔王的故事
TA貢獻1830條經驗 獲得超3個贊
因此，這個問題的答案和簡單一樣棘手：查詢返回的數組有重復的行，也許我錯了for-each語句，因為我最近開始使用它們。無論如何，以下是查詢和我的存儲解決方案：
$destinationId = 0;
$stmt2 = $conn->prepare('SELECT source_external_subscriber_id, SUM(source_customer_cost) FROM cdr WHERE destination_account_id = :destinationId GROUP BY source_external_subscriber_id');
$stmt2->execute(array('destinationId' => $destinationId));
foreach ($stmt2 as $row2) {
$data[] = $row2;
}
for ($i=0; $i < sizeof($data); $i++) {
$sanitizedData[$i] = array(
0 => $data[$i][0],
1 => $data[$i][1]
);
}
- 2 回答
- 0 關注
- 145 瀏覽
添加回答
舉報
0/150
提交
取消