รบกวนช่วยหน่อยครับผมต้องการจะดึงข้อมูลจาก ดาต้าเบจมาบวกกันครับ พอกดค้นหาประเภทอะไหล่เสร็จ มันจะโชว์ข้อมูลแต่แต่ละรายการในประเภทนั้นๆ ตอนนี้ผลบวกมันบวกรวมกันทั้งฟิวก์ คือตอนนี้ผมติดปัญหาสองอย่างครับ
1.หลังจากที่กดค้นหาแล้ว ผมอยากจะให้โชว์ผล sum ข้อมูลของ WRENCH_NAME แยกตาม TYPE อะไหล่
2.sum ยอด ISSUED ว่าแต่ละวันเบิกไปเท่าไหร่แยกตาม TYPE อะไหล่เอามาโชว์ที่ช่อง ยอดเบิก/ต่อวัน ครับ
ดาต้าเบจครับ
Code
CREATE TABLE `wrench` (
`ID` int(11) NOT NULL auto_increment,
`WRENCH_NAME` varchar(100) NOT NULL,
`CUSTOMER_NAME` varchar(50) NOT NULL,
`EMPLOYEES_NAME` varchar(50) NOT NULL,
`RECEIRE` int(11) NOT NULL,
`ISSUED` int(11) NOT NULL,
`TOTAL` int(11) NOT NULL,
`TYPE` varchar(50) NOT NULL,
`DATE` date NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=35 ;
--
-- dump ตาราง `wrench`
--
INSERT INTO `wrench` VALUES (1, 'wrench-L V8', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (2, 'wrench-L V9', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (3, 'wrench-L V10', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (4, 'wrench-L V11', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (5, 'wrench-L V12', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (6, 'wrench-L V13', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (7, 'wrench-L V14', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (8, 'wrench-L V15', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (9, 'wrench-L V16', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (10, 'wrench-L V17', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (11, 'wrench-L V18', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (12, 'wrench-L V19', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (13, 'wrench-L V20', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (14, 'wrench-L V21', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (15, 'wrench-L V22', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (16, 'wrench-L V23', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (17, 'wrench-L V24', 'surasak khompatee', 'weena pantakit', 100, 12, 88, 'wrench-L', '2017-10-01');
INSERT INTO `wrench` VALUES (18, 'wrench-Ring V10', 'surasak khompatee', 'weena pantakit', 400, 50, 350, 'wrench-Ring', '2017-10-01');
INSERT INTO `wrench` VALUES (19, 'wrench-Ring V12', 'surasak khompatee', 'weena pantakit', 400, 50, 350, 'wrench-Ring', '2017-10-01');
INSERT INTO `wrench` VALUES (20, 'wrench-Ring V14', 'surasak khompatee', 'weena pantakit', 400, 50, 350, 'wrench-Ring', '2017-10-01');
INSERT INTO `wrench` VALUES (21, 'wrench-Ring V16', 'surasak khompatee', 'weena pantakit', 400, 50, 350, 'wrench-Ring', '2017-10-01');
INSERT INTO `wrench` VALUES (22, 'wrench-Ring V18', 'surasak khompatee', 'weena pantakit', 400, 50, 350, 'wrench-Ring', '2017-10-01');
INSERT INTO `wrench` VALUES (23, 'wrench-Ring V20', 'surasak khompatee', 'weena pantakit', 400, 50, 350, 'wrench-Ring', '2017-10-01');
INSERT INTO `wrench` VALUES (24, 'wrench-Ring V22', 'surasak khompatee', 'weena pantakit', 400, 50, 350, 'wrench-Ring', '2017-10-01');
INSERT INTO `wrench` VALUES (25, 'wrench-Ring V24', 'surasak khompatee', 'weena pantakit', 400, 50, 350, 'wrench-Ring', '2017-10-01');
INSERT INTO `wrench` VALUES (26, 'wrench-C V8', 'surasak khompatee', 'weena pantakit', 200, 25, 175, 'wrench-C', '2017-10-01');
INSERT INTO `wrench` VALUES (27, 'wrench-C V10', 'surasak khompatee', 'weena pantakit', 200, 25, 175, 'wrench-C', '2017-10-01');
INSERT INTO `wrench` VALUES (28, 'wrench-C V12', 'surasak khompatee', 'weena pantakit', 200, 25, 175, 'wrench-C', '2017-10-01');
INSERT INTO `wrench` VALUES (29, 'wrench-C V14', 'surasak khompatee', 'weena pantakit', 200, 25, 175, 'wrench-C', '2017-10-01');
INSERT INTO `wrench` VALUES (30, 'wrench-C V16', 'surasak khompatee', 'weena pantakit', 200, 25, 175, 'wrench-C', '2017-10-01');
INSERT INTO `wrench` VALUES (31, 'wrench-C V18', 'surasak khompatee', 'weena pantakit', 200, 25, 0, 'wrench-C', '2017-10-01');
INSERT INTO `wrench` VALUES (32, 'wrench-C V20', 'surasak khompatee', 'weena pantakit', 200, 25, 0, 'wrench-C', '2017-10-01');
INSERT INTO `wrench` VALUES (33, 'wrench-C V22', 'surasak khompatee', 'weena pantakit', 200, 25, 0, 'wrench-C', '2017-10-01');
INSERT INTO `wrench` VALUES (34, 'wrench-C V24', 'surasak khompatee', 'weena pantakit', 200, 25, 0, 'wrench-C', '2017-10-01');
check_wrench.php
Code
<?php require_once('Connections/Connect.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
$colname_WRE = "-1";
if (isset($_POST['TYPE'])) {
$colname_WRE = $_POST['TYPE'];
}
mysql_select_db($database_Connect, $Connect);
$query_WRE = sprintf("SELECT * FROM wrench WHERE TYPE LIKE %s", GetSQLValueString("%" . $colname_WRE . "%", "text"));
$WRE = mysql_query($query_WRE, $Connect) or die(mysql_error());
$row_WRE = mysql_fetch_assoc($WRE);
$totalRows_WRE = mysql_num_rows($WRE);
?>
<!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>
<link href="css/bootstrap.min.css" rel="stylesheet" />
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="">
<meta name="author" content="">
<style type="text/css">
@-moz-document url-prefix() {
fieldset { display: table-cell; }
}
div {
text-align: left;
}
</style>
<title>CHECK WRENCH</title>
</head>
<title>CHECK WRENCH</title>
</head>
<body>
<div class="row">
<div class="col-lg-12">
<h3 class="page-header alert btn-info"><i class="fa fa-search fa-fw"></i> ตรวจสอบยอดคงเหลืออะไหล่</h3>
</div></div>
<!-- /.col-lg-12 -->
<div class="row">
<div class="col-lg-12">
<div class="row">
<div class="col-sm-6">
<form id="form1" name="form1" method="post" action="" class="form-horizontal">
<div class="form-group">
<div class="col-sm-6" align="left">
<input name="TYPE" type="text" required="required" class="form-control" id="TYPE" placeholder="กรุณาระบุชื่ออะไหล่"/>
</div> </div>
<input type="submit" name="button" id="button" value="ค้นหา" class="btn btn-primary" />
</form>
</div></div>
<div class="row">
<div class="col-lg-12">
<div class="dataTable_wrapper">
<table width="741" class="table table-striped table-bordered table-hover" id="dataTables-example">
<tr class="info">
<td width="40%" height="24" >ประเภทอะไหล่</td>
<td width="15%" >จำนวนรับ</td>
<td width="15%" >จำนวนเบิก</td>
<td width="15%" >คงเหลือ</td>
<td width="15%" >ยอดเบิก/ต่อวัน<br /></td>
</tr>
<tr class="info">
<td height="24"><?php echo $row_WRE['WRENCH_NAME']; ?></td>
<td ><?php echo $row_WRE['RECEIRE']; ?></td>
<td ><?php echo $row_WRE['ISSUED']; ?></td>
<td ><?php echo $row_WRE['TOTAL']; ?></td>
<td ><?php $sql="select sum(ISSUED) as punch from (wrench) ";
$result=mysql_query($sql);
while($rs=mysql_fetch_array($result)){
echo $rs['punch'];
}
?>
</tr>
</table>
</div></div></div></div>
</body>
</html>
<?php
mysql_free_result($WRE);
?>
Tag : PHP, MySQL
Load balance : Server 01
