求查询优化方法-php教程-PHP中文网

求查询优化方法

php中文网

发布： 2016-06-23 14:01:12

原创

1094人浏览过

请问下面的代码有优化的方法吗

$catalog_1 = "select name from commodity where catalog = 1 ORDER BY rank desc LIMIT 10";$result_1 = mysql_query($catalog_1);while($row_1 = mysql_fetch_assoc($result_1)){     $response [] = $row_1;}$catalog_2 = "select name from commodity where catalog = 2 ORDER BY rank desc LIMIT 10";$result_2 = mysql_query($catalog_2);while($row_2 = mysql_fetch_assoc($result_2)){     $response [] = $row_2;}$catalog_3 = "select name from commodity where catalog = 3 ORDER BY rank desc LIMIT 10";$result_3 = mysql_query($catalog_3);while($row_3 = mysql_fetch_assoc($result_3)){     $response [] = $row_3;}$catalog_4 = "select name from commodity where catalog = 4 ORDER BY rank desc LIMIT 10";$result_4 = mysql_query($catalog_4);while($row_4 = mysql_fetch_assoc($result_4)){     $response [] = $row_4;}$catalog_5 = "select name from commodity where catalog = 5 ORDER BY rank desc LIMIT 10";$result_5 = mysql_query($catalog_5);while($row_5 = mysql_fetch_assoc($result_5)){     $response [] = $row_5;}$catalog_6 = "select name from commodity where catalog = 6 ORDER BY rank desc LIMIT 10";$result_6 = mysql_query($catalog_6);while($row_6 = mysql_fetch_assoc($result_6)){     $response [] = $row_6;}$catalog_7 = "select name from commodity where catalog = 7 ORDER BY rank desc LIMIT 10";$result_7 = mysql_query($catalog_7);while($row_7 = mysql_fetch_assoc($result_7)){     $response [] = $row_7;}$catalog_8 = "select name from commodity where catalog = 8 ORDER BY rank desc LIMIT 10";$result_8 = mysql_query($catalog_8);while($row_8 = mysql_fetch_assoc($result_8)){     $response [] = $row_8;}$catalog_11 = "select name from commodity where catalog = 11 ORDER BY rank desc LIMIT 10";$result_11 = mysql_query($catalog_11);while($row_11 = mysql_fetch_assoc($result_11)){     $response [] = $row_11;}$catalog_10 = "select name from commodity where catalog = 10 ORDER BY rank desc LIMIT 10";$result_10 = mysql_query($catalog_10);while($row_10 = mysql_fetch_assoc($result_10)){     $response [] = $row_10;}

登录后复制

网龙b2b仿阿里巴巴电子商务平台

本系统经过多次升级改造，系统内核经过多次优化组合，已经具备相对比较方便快捷的个性化定制的特性，用户部署完毕以后，按照自己的运营要求，可实现快速定制会费管理，支持在线缴费和退费功能财富中心，管理会员的诚信度数据单客户多用户登录管理全部信息支持审批和排名不同的会员级别有不同的信息发布权限企业站单独生成，企业自主决定更新企业站信息留言、询价、报价统一管理，分系统查看分类信息参数化管理，支持多样分类信息，

查看详情

回复讨论(解决方案)

function getCataLog($catalog){	$catalog = "select name from commodity where catalog = ".$catalog." ORDER BY rank desc LIMIT 10";	$result = mysql_query($catalog); 	while($row = mysql_fetch_assoc($result)){	     $response [] = $row;	}}

登录后复制

改写成方法

是这样调用吗 getCataLog(11);

但没数据出来

function getCataLog($catalog){    $catalog = "select name from commodity where catalog = ".$catalog." ORDER BY rank desc LIMIT 10";    $result = mysql_query($catalog);      while($row = mysql_fetch_assoc($result)){         $response [] = $row;    }    return $response;}

登录后复制

还有个神奇的问题

catalog = 11 数据库有3条记录
catalog = 12 数据库有11条记录

$responses = getCataLog(11) + getCataLog(12);

这样输出应该是有14条记录的，但实标只输出了10条。而且catalog = 12的数据是从第四条开始接的

不是这么用的 php里面的 + 相比于数组的合并 array_merge 相同键值的被覆盖掉了

select name,catalog,rank from commodity c where 10 > (select count(*) from commodity where catalog=c.catalog and id>c.id) and catalog in (1,2,3,4,5,6,7,8,10,11) order by c.catalog,c.rank desc

登录后复制

function getCataLog($catalog,&$response = array()){    $catalog = "select name from commodity where catalog = ".$catalog." ORDER BY rank desc LIMIT 10";    $result = mysql_query($catalog);      while($row = mysql_fetch_assoc($result)){         $response [] = $row;    }}$response = array();getCataLog(1,$response);var_dump($response);getCataLog(2,$response);var_dump($response);

登录后复制

select name,catalog,rank from commodity c where 10 > (select count(*) from commodity where catalog=c.catalog and id>c.id) and catalog in (1,2,3,4,5,6,7,8,10,11) order by c.catalog,c.rank desc

登录后复制

请问再加两个条件怎么加啊,我这样加了，但这样加了和数据库对不上，少了很多条数据。

$marketId = $_GET['marketId'];$cityId = $_GET['cityId'];"select name from commodity where catalog = 1 and cityId = $cityId and marketId = $marketId ORDER BY rank desc LIMIT 10""select name,catalog,rank from commodity c where 10 > (select count(*) from commodity where catalog=c.catalog and id>c.id) and catalog in (1,2,3,4,5,6,7,8,10,11) and cityId = $cityId and marketId = $marketId order by c.catalog,c.rank desc"

登录后复制