$sql="SELECT products.name_pro as name,products.id_pro,sum(sub_products.sub_out) as quan FROM products INNER JOIN sub_products ON products.id_pro=sub_products.id_pro GROUP BY sub_products.sub_out,sub_products.id_pro ORDER BY sub_products.sub_out DESC";
ส่วนอันนี้โค้ดทั้งหมดครับ Code (PHP)
<?php
include "../config.inc.php";
$con = mysql_connect($host,$username,$password);
if (!$con)
{ die('Could not connect: ' . mysql_error()); }
mysql_select_db($database, $con);
mysql_query("SET NAMES UTF8");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script type="text/javascript">
window.onload = function () {
var chart = new CanvasJS.Chart("chartContainer",
{
animationEnabled: true,
title:{
text: "Chart with Labels on X Axis"
},
data: [
{
type: "column", //change type to bar, line, area, pie, etc
dataPoints: [
<?
//$sql="SELECT products.name_pro as name,sub_products.sub_out as quan FROM products INNER JOIN sub_products ON products.id_pro=sub_products.id_pro GROUP BY products.name_pro,sub_products.sub_out ORDER BY sub_products.sub_out DESC";
$sql="SELECT products.name_pro as name,products.id_pro as id_pro1,sum(sub_products.sub_out) as quan FROM products INNER JOIN sub_products ON products.id_pro=sub_products.id_pro GROUP BY sub_products.sub_out,sub_products.id_pro ORDER BY sub_products.sub_out DESC";
$result = mysql_query($sql) or die(mysql_error());
//while($row = mysql_fetch_array($result)){
for ($i=0;$i<5;$i++) //นำกราฟมาแสดง 3 รายการ
{
$row = mysql_fetch_array($result);
echo "{ label:\"".$row['name']."\", y:".$row['quan']." },";
// echo "{ label:\"".$row['name']."\", y:".$row['quan']." },";
// echo "$row['product'];
// echo $row['quan'];
}
?>
/* { label: "banana", y: 18 },
{ label: "orange", y: 29 },
{ label: "apple", y: 40 },
{ label: "mango", y: 34 },
{ label: "grape", y: 24 } */
]
}
]
});
chart.render();
}
</script>
<script type="text/javascript" src="canvasjs.min.js"></script>
</head>
<body>
<div id="chartContainer" style="height: 300px; width: 100%;"></div>
</body>
</html>
SELECT
pd.name_pro as name,
pd.id_pro as id_pro1,
sum( coalesce( spd.sub_out, 0) ) as quan
FROM products pd
LEFT JOIN sub_products spd
ON pd.id_pro=spd.id_pro
GROUP BY pd.id_pro
ORDER BY sum( spd.sub_out) DESC
Load balance : Server 02
