一对多关系的数据,比如订单和订单商品,要一起显示订单和其商品信息,像淘宝那样,每个订单(order表)对应多个商品(order_item表)时,查询订单信息必须把该订单的商品主要信息也显示出来。
$sql="select *,sum(`pro_price`*`order_account`) as all_money from cms_order_detail d , cms_order o where o.order_num=d.order_num and o.member_id='{$_SESSION['member_id']}' group by d.pro_id order by order_time desc"; 这样的语句只能查询出一条商品信息,,不能查询全部,是什么原因!
$sql="select *,sum(`pro_price`*`order_account`) as all_money from cms_order_detail d , cms_order o where o.order_num=d.order_num and o.member_id='{$_SESSION['member_id']}' group by d.pro_id order by order_time desc"; 这样的语句只能查询出一条商品信息,,不能查询全部,是什么原因!
解决方案 »
- 关于安装jpgraph的问题,望各位解答一下
- session
- mysql_fetch_row(): supplied argument is not a valid MySQL result resource,何人能解?
- php4如何使用schemavalidate来验证xml的合法性
- 很基础的问题,望解答
- 大家都是使用什么工具开发PHP程序啊?
- csdn的php版块好奇怪啊
- 求rewrite正则
- 请各位大大帮忙,php中session数组合并的问题!
- PHP发送表单内容到指定邮箱,能发送多个邮箱吗?
- PHP水印图片按比例缩放后,并写入到原图到某个xy怎么写
- php如何获取当前页面已选中的checked?
from cms_order o left join cms_order_detail d on o.order_num=d.order_num and o.member_id='{$_SESSION['member_id']}' group by d.pro_id order by order_time desc
$sql="select *,(`pro_price`*`order_account`) as all_money from cms_order_detail d , cms_order o where o.order_num=d.order_num and o.member_id='{$_SESSION['member_id']}' order by o.order_num desc, order_time desc";
我现在做的这个查询还没有设计到商品表,,,其实我做这个整个查询里面包含了4个表,,会员表,订单表,订单详情表,商品表,现在我只是对中间两个表进行查询!
订单表(cms_order):order_id , member_id(会员id) , order_num(订单号) , order_sum(订单总金额)
订单详情表(cms_order_detail):detail_id , order_num(订单号) , pro_id(产品id) , pro_price(商品单价) , order_account(商品数量)
这两个表是由order_num关联起来。 想实现的效果是:
订单号:xxxxxxxxxx 时间:xxxxxx 订单总额:xxxx
商品1:xx 单价: xx 数量:xx 小计:xx
商品2:xx 单价: xx 数量:xx 小计:xx
商品3:xx 单价: xx 数量:xx 小计:xx
$sql="select *,sum(`pro_price`*`order_account`) as all_money from cms_order_detail d , cms_order o where o.order_num=d.order_num and o.member_id='{$_SESSION['member_id']}' group by d.order_num order by order_time desc";我现在用上面这条语句可以实现这样的效果
订单号:xxxxxxxxxx 时间:xxxxxx 订单总额:xxxx
商品1:xx 单价: xx 数量:xx 小计:xx只能显示一条商品,,但是总额那些都可以查到,,就是不知道怎么显示其他的商品
何况不还有联合(union)吗?
union 在我这个查询里面怎么用啊,,不懂啊
SELECT o.order_num,o.order_sum,o.order_time,d.pro_id,d.pro_price,d.order_account,d.pro_price*d.order_account as total from cms_order o left join cms_order_detail d on (o.order_num=d.order_num) where o.member_id='{$_SESSION['member_id']}' order by order_time desc再将这查询出来的数据循环存到一个多维数组中,第一层放订单信息,key为order_num,第二层放商品详细 key为pro_id$data[$rs["order_num"]]["order_num"]=$rs["order_num"];
$data[$rs["order_num"]]["order_time"]=$rs["order_time"];
$data[$rs["order_num"]]["order_sum"]=$rs["order_sum"];
$data[$rs["order_num"]]["proData"][$rs["pro_id"]]["pro_id"]=$rs["pro_id"];
$data[$rs["order_num"]]["proData"][$rs["pro_id"]]["pro_price"]=$rs["pro_price"];
$data[$rs["order_num"]]["proData"][$rs["pro_id"]]["order_account"]=$rs["order_account"];
$data[$rs["order_num"]]["proData"][$rs["pro_id"]]["total"]=$rs["total"];然后在页面循环按格式输出对应的数据
foreach($data as $k=>$v){
echo "订单号:".$v["order_num"]." "."时间:".$v["order_time"]." "."订单总额:".$v["order_sum"];
$n=1;
foreach($v["proData"] as $pk=>$pv){
echo "商品".$n.":".$pv["pro_id"]." "."单价:".$pv["pro_price"]." "."数量:".$pv["order_account"]." "."小计:".$pv["total"];
$n++;
}
}
输出格式与美化,你自己修正