PHP array wont fill with mysql data [duplicate]

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!

That's the complete opposite of what I want to do. He is resetting his query results. I am using the results once, to create an array, device tokens, the looping through that array to push notifications. — Jon Erickson
– Jon Erickson, Commented Dec 16, 2012 at 7:47
You first consume all results to echo them. Then the results are gone but you still want to copy them to an array (second while loop). Hence you need to rewind. You see that in your code? — hakre
– hakre, Commented Dec 16, 2012 at 7:49
mysql_* functions are deprecated and will be removed from PHP in the future. Please see Choosing an API guide in the PHP manual. It is highly discouraged to build new application code using the old ext/mysql interface to work with your mysql database in PHP. Consider using the newer MySQLi or PDO APIs in PHP instead to spare yourself future grief. — Sherif
– Sherif, Commented Dec 16, 2012 at 7:54
Yep I know. I plan to go to mysqli as soon as I get all the logic worked out n — Jon Erickson
– Jon Erickson, Commented Dec 16, 2012 at 7:55
@JonErickson: You can work out the logic better with Mysqli (and PHP 5.4), otherwise if you're using a PHP version < 5.4, use PDO instead. — hakre
– hakre, Commented Dec 16, 2012 at 8:01

gview · Accepted Answer · 2012-12-16 07:48:09Z

2

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

}

answered Dec 16, 2012 at 7:48

gview

15.5k3 gold badges53 silver badges56 bronze badges

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

9 Comments

Jon Erickson Over a year ago

So you are saying, if I just take out my print statement of the results, it should work? Cause then I would only be using the results once.

Mario Mueller Over a year ago

Yep +1, $result1 is a pointer on a mysql result set. If you request the whole resultset or close the connection the mysql server forgets everything about your query.

gview Over a year ago

You'd have to remove the entire while loop, but yes.

Jon Erickson Over a year ago

Ok, the other problem I have is my failing MySql query statement. Something with the LIKE isn't working. It's returning no entry's even the units array is populated and working right.

gview Over a year ago

You should probably start by checking the value of $result1 after the query... if (!$result1) { die( mysql_error()); }

|

hakre · Accepted Answer · 2012-12-16 22:21:55Z

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.

Collectives™ on Stack Overflow

PHP array wont fill with mysql data [duplicate]

2 Answers 2

9 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Linked

Related