How to exclude MySQL query from my loop (limit unnecessary queries)

Question

I'm asking MySQL for data, but it slows down the whole script. Yet I have no idea how to get this out of a loop. I tried converting it to PHP array but honestly after day of tries I failed.

<?php

$id = '1';

include_once 'include_once/connect.php';

for ($x = 1; $x <= 5; $x++) {
for ($y = 1; $y <= 5; $y++) {

    $xy = $x."x".$y;

    $pullMapInfo = "SELECT value FROM mapinfo WHERE id='".$id."' AND xy='".$xy."'";
    $pullMapInfo2 = mysql_query($pullMapInfo) or die('error here');

    if ($pullMapInfo3 = mysql_fetch_array($pullMapInfo2)) {
        #some code
    } else {
        #some code
    }
}
}

?>

How to get MySQL query $pullMapInfo2 out of loop to shorten loading it by asking once?

If you want to fire script on your localhost you can c&p whole thing :-)

First, mysql_* functions have been deprecated and will be removed from PHP. Second, don't use die() for error handling, use throw new Exception("Meaningfull error message here.") (this will help you find the file and line of code where the error occurred, when debugging - die() will not offer your the same information, and you will have to search everywhere for it). — oxygen
– oxygen, Commented Sep 12, 2012 at 12:58

FilmJ · Accepted Answer · 2012-09-09 11:02:00Z

1

I'm not sure what you have in your table, but considering you are basically looping through virtually everything in it, I'd say do a single query for the given Id and then sort out what you need from the larger dataset.

Especially if you are always pulling back essentially the complete dataset for each id, there's no reason to even bother with the IN query, just pull it all back into a single PHP array, and then iterate through that as needed.

answered Sep 9, 2012 at 11:02

FilmJ

2,0413 gold badges19 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alain · Accepted Answer · 2012-09-09 11:23:52Z

0

Use a MySQL IN clause

<?php

$id = '1';

include_once 'include_once/connect.php';

// first we create an array with all xy
$array = array();
for ($x = 1; $x <= 5; $x++) {
    for ($y = 1; $y <= 5; $y++) {
        $xy = $x."x".$y;
        $array[] = $xy;
    }
}

$in = "'" . implode("', '", $array) . "'";
$pullMapInfo = "SELECT xy, value FROM mapinfo WHERE id='".$id."' AND xy IN ({$in})";
$pullMapInfo2 = mysql_query($pullMapInfo) or die('error here');

// we create an associative array xy => value
$result = array();
while (($pullMapInfo3 = mysql_fetch_assoc($pullMapInfo2)) !== false) {
    $result[ $pullMapInfo3['xy'] ] = $pullMapInfo3['value'];
}


// we make a loop to display expected output
foreach ($array as $xy)
{
    if (array_key_exists($xy, $result)) {
        echo '<div class="castle_array" style="background-image: url(tiles/'.$result[$xy].'.BMP)" id="'.$xy.'">'. $result[$xy] .'</div>';
    } else {
        echo '<div class="castle_array" id="'.$xy.'"></div>';
    }
    echo '<div class="clear_both"></div>';
}

?>

edited Sep 9, 2012 at 11:23

answered Sep 9, 2012 at 10:57

Alain

37.1k18 gold badges128 silver badges160 bronze badges

4 Comments

Szymon Toda Over a year ago

$in = "'" . implode("', '", $array) . "'"; $pullMapInfo = "SELECT xy, value FROM mapinfo WHERE id='".$id."' AND xy IN {$in}"; $pullMapInfo2 = mysql_query($pullMapInfo) or die('error here');

returns "error here"

Alain Over a year ago

Excuse me, IN ({$in}), forgot parenthesis.

Szymon Toda Over a year ago

Ninsuo whats the difference between with and without this IN statement?

Alain Over a year ago

With the IN statement you only get elements that are defined inside the $array variable. So if in your database you have 1x1, 1x2, ... 5x5, 5x6, 6x1, ..., then you are sure to get only from 1x1 to 5x5. If you know there is only data from 1x1 to 5x5 in your DB, then IN is useless.

Collectives™ on Stack Overflow

How to exclude MySQL query from my loop (limit unnecessary queries)

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related