请教一个问题,就是我想实现用PHP实现按条件查询 数据库内容并输出的功能,如何实现?
举例数据库表 123姓名 型号 工位 数量 时间
张三 A8-300 A 200 2013/6/5 8:46
李四 A8-300 B 121 2013/6/5 8:48
王五 A1-200 C 45 2013/6/5 7:48
张三 A8-300 A 420 2013/6/5 10:46
王五 A8-300 C 500 2013/6/5 13:46如何实现在一个页面上自定义查询条件 比如按: 姓名,时间,工位,数量,时间,的自定义组合查询,查询的结果在页面里显示出来并在底部自动做一个数量的汇总统计,谁可以帮我写写全代码,谢谢了!由于刚接触PHP,还不是很熟悉。PHPSQL数据库
举例数据库表 123姓名 型号 工位 数量 时间
张三 A8-300 A 200 2013/6/5 8:46
李四 A8-300 B 121 2013/6/5 8:48
王五 A1-200 C 45 2013/6/5 7:48
张三 A8-300 A 420 2013/6/5 10:46
王五 A8-300 C 500 2013/6/5 13:46如何实现在一个页面上自定义查询条件 比如按: 姓名,时间,工位,数量,时间,的自定义组合查询,查询的结果在页面里显示出来并在底部自动做一个数量的汇总统计,谁可以帮我写写全代码,谢谢了!由于刚接触PHP,还不是很熟悉。PHPSQL数据库
:
数据库:
username pinfan ddtime shuliang
user 24120-u100 2013-06-04 14:21:00 20
user 24110-y350 2013-06-05 14:21:00 150
user 24110-y350 2013-06-04 14:20:00 1211
123 24136-t300 2013-06-04 19:33:00 1000
123 24156-p20 2013-06-04 19:34:00 212
search.php
<html>
<body>
<h3>查询</h3>
<form action="search_result.php" method="POST">
型号:<input type="text" size=25 name="pinfan1" value=""> <br><br>
姓名: <input type="text" size=25 name="username1" value=""> <br><br>
时间: <input type="text" size=25 name="ddtime1" value=""> <br><br>
时间: <input type="text" size=25 name="ddtime2" value=""> <br><br>
<input type="submit" name="提交" value="提交">
</form>
</body>
</html>
search_result.php<?php
$link=mysql_connect("localhost","admin","admin");
if(!$link) echo "没有连接成功!";
else echo "连接成功!";
mysql_select_db("phpcms", $link); ?>
<?php //注释1-----------------------------
$pinfan=$_POST["pinfan1"];
$username=$_POST["username1"];
$ddtime=$_POST["ddtime1"];
$ddtime2=$_POST["ddtime2"];
//注释2------------------------
if($pinfan != null){
$a = " and pinfan like '%$pinfan%'";} if($username != null){
$b = " and username like '%$username%'";} if($ddtime != null){
$c = " and ddtime BETWEEN '%$ddtime%' and '%$ddtime2%'";} //注释3------------------------
$q = "SELECT * FROM v9_form_sctj where (1=1)";
$q .=$a;
$q .=$b;
$q .=$c; //注释4------------------------------------------
mysql_query("SET NAMES GB2312");
$rs = mysql_query($q, $link);
echo "<table border=1>";
echo "<tr><td>型号</td><td>姓名</td><td>时间</td><td>数量</td></tr>";
while($row = mysql_fetch_object($rs)) echo "<tr><td>$row->pinfan</td><td>$row->username</td><td>$row->ddtime</td><td>$row->shuliang</td></tr>";
echo "</table>"; mysql_close($link); ?>
$a = " and pinfan like '%$pinfan%'";}
这句上面加上:
$a=$b=$c='';
这只是个警告,没什么影响。 你这么赋值试试应该就好了
$a = " and pinfan like '%$pinfan%'";}
elseif($username != null){
$a = " and username like '%$username%'";}
elseif($ddtime != null){
$a = " and ddtime like '%$ddtime%'";} //注释3------------------------
$q = "SELECT * FROM v9_form_sctj where (1=1)";
$q .=$a;
这个错误提示的我用这个解决了,
但是一个时间范围内判断的查询怎么实现比如说查询 2013-06-04 14:21:00 到 2013-06-04 15:21:00 这个时间段 用户user的所有数据
if($pinfan != null){
$a = " and pinfan like '%$pinfan%'";}
if($username != null){
$b = " and username like '%$username%'";}
if($ddtime != null){
$c = " and ddtime like '%$ddtime%'";} //注释3------------------------
$q = "SELECT * FROM v9_form_sctj where (1=1)";
$q .=$a.=$b.=$c;
我把其中的那段代码这样修改后已经可以实现我刚才说的三个选项的互相组合查询,但是统计结果和一个时间范围内判断的查询怎么实现比如说查询 2013-06-04 14:21:00 到 2013-06-04 15:21:00 这个时间段 用户user的所有数据 还是没实现
$c = " and date_format(ddtime,"%Y-%m-%d %H:%i:%s") BETWEEN '$ddtime' and '$ddtime2'";}
改成单引号后不提示语法错误了,差寻出来时空的
date_format(ddtime,"%Y-%m-%d %H:%i:%s")
2013-06-04 14:21:00
2013-06-05 14:21:00
2013-06-04 14:20:00
2013-06-04 19:33:00
2013-06-04 19:34:00
2013-06-06 14:18:00
2013-06-03 14:18:00
2013-05-15 14:19:00
2013-05-15 14:19:00
SELECT *
FROM v9_form_sctj
WHERE ( 1 =1 )
AND date_format( ddtime, '%Y-%m-%d %H:%i:%s' )
BETWEEN '%2013%'
AND '%2013%'
LIMIT 0 , 30
$c = " and date_format(ddtime,'%Y-%m-%d %H:%i:%s') BETWEEN '$ddtime' and '$ddtime2'";}
这样就行了,但是这个必须是完整的日期时间格式才行,只写年份是查不到结果的
这个基本解决了,谢谢啦 ,呵呵还有就是查询出来的结果如何做一个汇总统计在显示的表格下方?
by username,pinfan with rollup";
我如何把这句加到上边的查询后面让其输出出来,或者这个语句汇总跟上边的查询整合到一起,实现查询的同时显示汇总结果
select username,count(*) as cnt group by username
$q2="select username,count(*) as cnt where 1=1 ".$a.$b.$c." group by username ";
$r2=mysql_query($q2);
while($row2=mysql_fetch_assoc($r2)){
$arr[$row2['username']]=$row2['cnt'];
}
$arr就是汇总的数组
下面是我的代码,后面是显示结果<?php
$link=mysql_connect("localhost","admin","admin");
if(!$link) echo "没有连接成功!";
else echo "连接成功!";
mysql_select_db("phpcms", $link); ?>
<?php //注释1-----------------------------
$pinfan=$_POST["pinfan1"];
$username=$_POST["username1"];
$ddtime=$_POST["ddtime1"];
$ddtime2=$_POST["ddtime2"];
//注释2------------------------
$a=$b=$c='';
if($pinfan != null){
$a = " and pinfan like '%$pinfan%'";} if($username != null){
$b = " and username like '%$username%'";} if($ddtime != null){
$c = " and date_format(ddtime,'%Y-%m-%d %H:%i:%s') BETWEEN '$ddtime' and '$ddtime2'";} //注释3------------------------
$q = "SELECT * FROM v9_form_sctj where (1=1)";
$q .=$a.=$b.=$c;
//$q .=$b;
//$q .=$c; //注释4------------------------------------------
mysql_query("SET NAMES GB2312");
$rs = mysql_query($q, $link);
echo "<table border=1>";
echo "<tr><td>型号</td><td>姓名</td><td>时间</td><td>数量</td></tr>";
while($row = mysql_fetch_object($rs)) echo "<tr><td>$row->pinfan</td><td>$row->username</td><td>$row->ddtime</td><td>$row->shuliang</td></tr>";
echo "</table>";
//echo $q;
$q2="select username,count(*) as cnt where 1=1 ".$a.$b.$c." group by username ";
$r2=mysql_query($q2);
while($row2=mysql_fetch_assoc($r2)){
$arr[$row2['username']]=$row2['cnt'];
}
mysql_close($link); ?>显示结果连接成功!
型号 姓名 时间 数量
24120-u100 user 2013-06-04 14:21:00 20
24010-y3000 user 2013-06-05 14:21:00 150
24010-y3000 user 2013-06-04 14:20:00 1211
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in D:\PC_webserver\wwwroot\search_result.php on line 42
$r2=mysql_query($q2) or die(mysql_error()); //报什么错
型号 姓名 时间 数量
24136-t300 123 2013-06-04 19:33:00 1000
24156-p20 123 2013-06-04 19:34:00 212
24010-y3000 123 2013-06-06 14:18:00 200
24156-p20 123 2013-06-03 14:18:00 1211
24120-u100 123 2013-05-15 14:19:00 1000
24120-u100 123 2013-05-15 14:19:00 1000
select username,count(*) as cnt where 1=1 and username like '%123%' and username like '%123%' group by username You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where 1=1 and username like '%123%' and username like '%123%' group by usernam' at line 1
你的 from 表名 在哪里?
24136-t300 123 2013-06-04 19:33:00 1000
24156-p20 123 2013-06-04 19:34:00 212
24010-y3000 123 2013-06-06 14:18:00 200
24156-p20 123 2013-06-03 14:18:00 1211
24120-u100 123 2013-05-15 14:19:00 1000
24120-u100 123 2013-05-15 14:19:00 1000 统计 123 3623我想要实现的就是这样的输出结果,统计可以跟据前面的查询选择做组合统计汇总就行,比如按姓名,或者型号,或者时间等。现在就是这个统计部知道如何实现
$link=mysql_connect("localhost","admin","admin");
if(!$link) echo "没有连接成功!";
else echo "连接成功!";
mysql_select_db("phpcms", $link); ?>
<?php //注释1-----------------------------
$pinfan=$_POST["pinfan1"];
$username=$_POST["username1"];
$ddtime=$_POST["ddtime1"];
$ddtime2=$_POST["ddtime2"];
//注释2------------------------
$a=$b=$c='';
if($pinfan != null){
$a = " and pinfan like '%$pinfan%'";} if($username != null){
$b = " and username like '%$username%'";} if($ddtime != null){
$c = " and date_format(ddtime,'%Y-%m-%d %H:%i:%s') BETWEEN '$ddtime' and '$ddtime2'";} //注释3------------------------
$q = "SELECT * FROM v9_form_sctj where (1=1)";
$q .=$a.=$b.=$c;
//$q .=$b;
//$q .=$c; //注释4------------------------------------------
mysql_query("SET NAMES GB2312");
$rs = mysql_query($q, $link);
echo "<table border=1>";
echo "<tr><td>型号</td><td>姓名</td><td>时间</td><td>数量</td></tr>";
while($row = mysql_fetch_object($rs)) echo "<tr><td>$row->pinfan</td><td>$row->username</td><td>$row->ddtime</td><td>$row->shuliang</td></tr>";
echo "</table>";
echo $q;
$q2="select username as '姓名',sum(shuliang) as '合计' from v9_form_sctj where 1=1 ".$a.$b.$c." ";
//echo $q2; //贴出结果
$r2=mysql_query($q2,$link) ; //报什么错
while($row2=mysql_fetch_assoc($r2)){
$arr[$row2['username']]=$row2['sum'];
}
mysql_close($link); ?>为什么我这样写了还是错误
select username as '姓名',sum(shuliang) as '合计' from v9_form_sctj where 1=1 and username like '%123%' and username like '%123%'
Notice: Undefined index: username in D:\PC_webserver\wwwroot\search_result.php on line 45Notice: Undefined index: sum in D:\PC_webserver\wwwroot\search_result.php on line 45
首先,不是要你再去查一查数据库。
其次,你的第二条sql语句。。,看来你对SQL语句的了解也不是很多。
最后,写流程控制类型的语法时,最好还是用{}括起来,方便阅读,不容易出错。我说的方法的意思是,你在while循环里面去构建这个统计数组,如:$arr[$row->username] += $row->shuliang;
$pinfan=$_POST["pinfan1"];
$username=$_POST["username1"];
$ddtime=$_POST["ddtime1"];
$ddtime2=$_POST["ddtime2"];
//注释2------------------------
$a=$b=$c='';
if($pinfan != null){
$a = " and pinfan like '%$pinfan%'";} if($username != null){
$b = " and username like '%$username%'";} if($ddtime != null){
$c = " and date_format(ddtime,'%Y-%m-%d %H:%i:%s') BETWEEN '$ddtime' and '$ddtime2'";}
这里post过来的应该是是空字符串或有内容的字符串,不会有null吧。
$q2="select username ,sum(shuliang) as sum from v9_form_sctj where 1=1 ".$a.$b.$c." ";
执行完后是没错误提示了,但是统计结果没有输出$r2=mysql_query($q2,$link) ;
while($row2=mysql_fetch_assoc($r2)){
$arr[$row2['username']]=$row2['sum'];
}
是不是这个输出的语句有问题?
foreach($arr as $k=>$v){
echo $k . "----" . $v ."<br>";
}
//注释4------------------------------------------
mysql_query("SET NAMES GB2312");
$rs = mysql_query($q, $link);
echo "<table border=1>";
echo "<tr><td>型号</td><td>姓名</td><td>时间</td><td>数量</td></tr>";
while($row = mysql_fetch_object($rs)) echo "<tr><td>$row->pinfan</td><td>$row->username</td><td>$row->ddtime</td><td>$row->shuliang</td></tr>";
echo "</table>";
//echo $q;
$q2="select username ,sum(shuliang) as sum from v9_form_sctj where 1=1 ".$a.$b.$c."group by username ";
//echo $q2; //贴出结果
$r2=mysql_query($q2,$link) ; //报什么错
while($row2=mysql_fetch_assoc($r2)){
$arr[$row2['username']]=$row2['sum'];
foreach($arr as $k=>$v){
echo "<br>";
echo " 统计--------". $k . "-----------------合计---" . $v ."<br>";
}
}
mysql_close($link);
$arr[$row2['username']]=$row2['sum'];
} //////
foreach($arr as $k=>$v){
echo "<br>";
echo " 统计--------". $k . "-----------------合计---" . $v ."<br>";