is there any way i could minimize this mysql query?

Question

Hi,

i am developing an application which have eight advertisement boxes, the advertisement data with position is stored into the database.

the advertisement module works like first it will check if the particular position exist in the database (i.e 1 to 8) if it finds or not find it will return a Boolean accordingly.

for that reason i am using a user defined function like this.

 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;
             }

as i have to make the eight queries i am doing it somewhat like this.

$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'");

and then i fetch the data like this.

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

although it works fine, it is messed up and a huge queries is piled up. i want to clean and minimize it if possible.

what is your take?

Answer 1

This should do what you want:

$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;
}

You can access the data as $adbox[1]['id']/$adbox[1]['pic_brief'] and so forth.

Edit: I've updated the code to avoid leaving empty adbox ids.

Answer 2

Round trip to the database is less efficient than a loop in PHP. So my suggestion would be to minimize it to one query (assuming pos is an integer), something like:

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

Then loop over like so to build out an array:

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

Do a boolean test by looking for pos as an array key:

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

You can break it out into a function if you like on your own. But that's my take on minimizing. An added benefit is that you have access to the ad data in $adbox