PHP array wont fill with mysql data

Question

Possible Duplicate:
How to go through mysql result twice?

I have a PHP script with a loop within a loop. The outer loop walks down an array for each $unit in the $unitsarray and query's a MySQL db to see if there is an entry with the same matching string. If $result1 does indeed return an entry(s), I create another array called $devicetokens array for the row "devicetoken" and then enter my second loop. For each device token, I create an Apple Push Notification to send to an iOS device. I have two problems, first the mysql_query returns nothing. If I replace $unit with a value I know will return na entry, then it works. Second, if I have replace the $unit and get a result back, the $devicetokens array wont populate with any data even though I have a result back from the mysql query. Here is my code:

foreach ($unitsarray as $unit) {

    echo "Unit = $unit </br>";

    // Create array of devices that match the unit
    $result1 = mysql_query("SELECT * FROM `department devices` WHERE unit LIKE '%$unit%'") or die(mysql_error());

    //Print results
    while ($row = mysql_fetch_assoc($result1)) {
        echo "&nbsp;&nbsp;&nbsp;&nbsp;";
        echo $row["device_id"];
        echo " , ";
        echo $row["devicetoken"];
        echo " , ";
        echo $row["unit"];
    }
    echo "</br>";

    $devicetokenarray = array();
    while ($row = mysql_fetch_assoc($result1)) {
        array_push($devicetokenarray, $row["devicetoken"]);
    }

    // Print array
    print_r($devicetokenarray);
    echo "</br>";

    // Loop APNS for each device token in $devicetoken array
    foreach ($devicetokenarray as $devicetoken)
    {

    // Build the binary notification
    $msg = chr(0).pack('n', 32).pack('H*', $devicetoken).pack('n', strlen($payload)).$payload;

    // Send it to the server
    $result = fwrite($fp, $msg, strlen($msg));

    // Create APNS operation output
        if (!$result)
            echo 'Failed message'.PHP_EOL;
        else
            echo "<b>Successful message sent:</b>&nbsp;&nbsp; $call - $location - $station - $units to device(s):&nbsp;&nbsp;'$devicetoken </br>".PHP_EOL;
    }
}

Here's what my db looks like:

device_id   devicetoken                                         unit

T05 ipad   773f5436825a7115417d3d1e036da20e806efeef547b7c3fe4   121
E05 ipad   773f5436825a7115417d3d1e036da20e806efeef547b7c3fe4   121

Any help would be greatly appreciated!

Answer 1

You are doing a query and storing a result resource in $result1, then fetching all the rows in a loop that you echo out, then immediately trying to fetch it again. Once you fetch all the results, you can't fetch them again. Well you can, using mysql_data_seek, but it's really inefficient and wasteful to do so in most cases. Store the results the first time in an array.

$rows = array();

while ($row = mysql_fetch_assoc($result1)) {
     $rows[] = $row;
}

Then you can foreach through this array.

foreach ($rows as $row) {
    // Build the binary notification
    $msg = chr(0).pack('n', 32).pack('H*', $row['devicetoken']) . pack('n', strlen($payload)) . $payload;
    //... etc

}

Answer 2

If you are using the deprecated mysql_* API and you want to have all result rows in a single array, you should first of all create yourself a new helper function because the old library doesn't have this predefined.

The following function fetches all result-rows in form of an array.

function mysql_fetch_all($result, $result_type = MYSQL_BOTH) {
    $rows = array();
    while ($row = mysql_fetch_assoc($result1)) {
        $rows[] = $row;
    }
    return $rows;
}

Usage:

$rows = mysql_fetch_all($result1, MYSQL_ASSOC);

Then you've got the problem that you fly blind and you do not know what the input is and which sql query is generated. You need to be more verbose then:

$sql     = "SELECT * FROM `department devices` WHERE unit LIKE '%$unit%'";
printf("DEBUG: <pre>%s</pre>\n", htmlspecialchars($sql));
$result1 = mysql_query($sql) or die(mysql_error());

You then can see which query you execute. Preferable is a step-debugger like xdebug.