CODEWITHSUNDEEP
phpmysqlprepared-statement
owwyess
owwyess

Reputation: 175

Loop through mysql query with PHP Prepared Statement and order by int

I have this piece of code:

$item_number = 0;
$rowsize = 12;

    for ($i = 0; $i < $rowsize; $i++) {

        $stmt = $mysqli->stmt_init();
        $stmt->prepare('SELECT z, name FROM house_room1 INNER JOIN objects ON house_room1.object_id=objects.object_id WHERE house_room1.ref_id = ? ORDER BY z'); 
        $stmt->bind_param('i', $i
        );
        if ($stmt->execute()) {
            $stmt->bind_result($z, $name);
                while($stmt->fetch()) {
                   echo '<li id="item-' . $item_number . '" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span>' . $name . '</li>';
                        }
                }
                else {
                    echo 'Something went terribly wrong' . $mysqli->error;
                        }
                $stmt->close();
                //$n++;
                $item_number++;
            }

which loops through a query 12 times (currently 12, will be higher) and select a name and a z value from a specific ref_id. Since this loop goes through 12 times, I get the z value from all the 12 ref_id's. The problem is that I can't order the query I get out because of the fact that it only takes out one query at a time. Uhm I need it to get the z value of all 12 (and soon more) and still be able to order the output by the z value. Preferably by only looping through the query once. Any ideas? Thanks in advance.

UPDATE:

$item_number = 0;

    $i = 12;

    $stmt = $mysqli->stmt_init();
    $stmt->prepare('SELECT z, name
    FROM house_room1
    INNER JOIN objects ON house_room1.object_id=objects.object_id
    WHERE house_room1.ref_id < ?
    ORDER BY z'); 
    $stmt->bind_param('i', $i
    );
        if ($stmt->execute()) {
        $stmt->bind_result($z, $name);
        while($stmt->fetch()) {
            echo '<li id="item-' . $item_number . '" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span>' . $name . '</li>';
        }
    }
    else {
        echo 'Something went terribly wrong' . $mysqli->error;
    }
    $stmt->close();
    $item_number++;

Upvotes: 0

Views: 2008

Answers (2)

luv2code
luv2code

Reputation: 1296

Try placing the results in an array and then ordering the array:

$item_number = 0;
$rowsize = 12;

$itemArray = array();
$finalArray = array();
$results = 0;

    for ($i = 0; $i < $rowsize; $i++) {

        $stmt = $mysqli->stmt_init();
        $stmt->prepare('SELECT z, name FROM house_room1 INNER JOIN objects ON house_room1.object_id=objects.object_id WHERE house_room1.ref_id = ? ORDER BY z'); 
        $stmt->bind_param('i', $i
        );
        if ($stmt->execute()) {
            $stmt->bind_result($z, $name);
                while($stmt->fetch()) {


               $results = 1;
               $itemArray['number'] = $item_number;
               $itemArray['name'] = $name;
               $itemArray['ref_id'] = $z;
               array_push($finalArray, $itemArray);

                        }
                }
                else {
                    echo 'Something went terribly wrong' . $mysqli->error;
                        }
                $stmt->close();
                //$n++;
                $item_number++;
            }

if($results == 1){
aasort($finalArray,"ref_id");

foreach($finalArray as $arr){
echo '<li id="item-' . $arr['number'] . '" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span>' . $arr['name'] . '</li>';
}

}

//create a function for sorting
function aasort (&$array, $key) {
    $sorter=array();
    $ret=array();
    reset($array);
    foreach ($array as $ii => $va) {
        $sorter[$ii]=$va[$key];
    }
    asort($sorter);
    foreach ($sorter as $ii => $va) {
        $ret[$ii]=$array[$ii];
    }
    $array=$ret;
}

So basically what I added to your code, is the two array variables:
itemArray = is the array you we are creating with the results
finalArray = is the array we push all the itemArrays onto. This was you are creating a multidimensional array which we can later sort.

Then we set the variable $results, this is going to make sure that the results were found otherwise we will not run the code.

function aasort does what you want, sorts the information accorrding to the array tag (in this case ref_id)

Then you simply loop through the now ordered results and output. Very simple and should be fast. I do recommend not looping through sql queries though. You should try to have as few queries as you can to the database. Hope this helps! Let me know if you have questions.

Upvotes: 0

Kryten
Kryten

Reputation: 15760

Wouldn't it be easier to write one query and let the server sort it? Something like this...

SELECT z, name
FROM house_room1
    INNER JOIN objects ON house_room1.object_id=objects.object_id
WHERE house_room1.ref_id < ?
ORDER BY z

This has the benefit of being one query, rather than 12 (and therefore more efficient) and it allows you to order the WHOLE result set any way you please.

Upvotes: 1

Related Questions