代码之家 › 专栏 › 技术社区 › Ibrahim Azhar Armar

有什么方法可以最小化这个mysql查询吗?

mysql php

Ibrahim Azhar Armar · 技术社区 · 16 年前

我正在开发一个有八个广告框的应用程序,其中包含位置的广告数据存储在数据库中。

广告模块的工作方式与第一个类似,它将检查数据库中是否存在特定位置(即1到8),如果找到或没有找到,它将相应地返回一个布尔值。

因此,我使用这样一个用户定义的函数。

 function dbgetvar($query) {
             $res = mysql_query($query);
         if( !$res) {
             trigger_error("dbget: ". mysql_error(). " in " .$query);
             return false;
             }
             if( mysql_num_rows($res) == '0' ) {
             return false;
             }
             $row = mysql_fetch_array($res);
             if(!$row) return "";
             return $row;
             }

因为我必须做八个查询,所以我做的有点像这样。

$adbox1 = dbgetvar("SELECT advertisements.id as 1_id, advertisements.pic_brief as 1_brief  FROM advertisements WHERE pos = '1'");
$adbox2 = dbgetvar("SELECT advertisements.id as 2_id, advertisements.pic_brief as 2_brief  FROM advertisements WHERE pos = '2'");
$adbox3 = dbgetvar("SELECT advertisements.id as 3_id, advertisements.pic_brief as 3_brief  FROM advertisements WHERE pos = '3'");
$adbox4 = dbgetvar("SELECT advertisements.id as 4_id, advertisements.pic_brief as 4_brief  FROM advertisements WHERE pos = '4'");
$adbox5 = dbgetvar("SELECT advertisements.id as 5_id, advertisements.pic_brief as 5_brief  FROM advertisements WHERE pos = '5'");
$adbox6 = dbgetvar("SELECT advertisements.id as 6_id, advertisements.pic_brief as 6_brief  FROM advertisements WHERE pos = '6'");
$adbox7 = dbgetvar("SELECT advertisements.id as 7_id, advertisements.pic_brief as 7_brief  FROM advertisements WHERE pos = '7'");
$adbox8 = dbgetvar("SELECT advertisements.id as 8_id, advertisements.pic_brief as 8_brief  FROM advertisements WHERE pos = '8'");

然后我就这样取数据。

if($adbox1){
    echo "I am found";
}
if(!$adbox1){
    echo "I am not found";
}

虽然它工作得很好,但是它被搞得一团糟,并且大量的查询被堆积起来。如果可能的话,我想把它清理干净并最小化。

你拿什么?

2 回复 | 直到 16 年前

MichaÅ Tatarynowicz 16 年前

这应该符合您的要求:

$query = "SELECT id, pic_brief FROM advertisements WHERE pos BETWEEN 1 AND 8";
$res = mysql_query($query);
$adbox = array_fill(1, 8, null);
while ($row = mysql_fetch_array($res)) {
    $adbox[$row['id'] = $row;
}

您可以通过以下方式访问数据 $adbox[1]['id'] / $adbox[1]['pic_brief'] 等等。

编辑:我已经更新了代码以避免留下空的adbox id。

Jason McCreary 16 年前

到数据库的往返比PHP中的循环效率低。因此,我的建议是将其最小化为一个查询(假设pos是一个整数),如下所示:

SELECT id, pic_brief, pos FROM advertisements WHERE pos BETWEEN 1 AND 8;

然后像这样循环以构建一个数组:

$adbox = array();
while ($row = mysql_fetch_array($res)) {
  $adbox[$row['pos']] = $row
}

通过将pos作为数组键进行布尔测试:

if ($adbox[1]) {
  // has ad 1
}
else {
  // no ad 1
}

如果你自己喜欢,你可以把它分解成一个函数。但这是我对最小化的看法。另一个好处是您可以访问 $adbox