Good day,

I am climbing a wall trying to figure out why I can not retrieve the results of a mysql query to a php array.

My query polls the database and it is retrieved but all the data is encrypted. I need to take the results and put them through a decryption algorithm. Hence the need for an array because I can implement a foreach..

If I do the following:

//$record is the result of a mysql_query
list($id,$pwd,$firstname,$approved,$user_level,$salt) = mysql_fetch_row($record);

I get the variables stored in memory.

What is the simplest method to take the same result of the query and output it to an array? I want something like

$encrypted['id']
$encrypted['firstname']
etc.

I've tried

while ( $row = mysql_fetch_assoc($record)) {
   $encrypted['id'] = $row['id'];
   $encrypted['firstname'] = $row['firstname'];
   etc.
}

but when I run it and print_r the array, there's no data. Same goes for mysql_fetch_array. If I do a dump on the individual variables stated in the list() function, they output as they should.

If I step through the code with a debugger, $row comes up as FALSE.

Any idea would be most welcome.

Member Avatar for diafol
while ( $row = mysql_fetch_assoc($record)) {
   $encrypted['id'] = $row['id'];
   $encrypted['firstname'] = $row['firstname'];
   etc.
}

that's the general way to do it. However, do you need to create $encrypted? the array already exists as $row. Using a function on each member to decrypt the data is simple with array_map() or array_walk().

while ( $row = mysql_fetch_assoc($record)) {
  array_walk($row,'function_name');
//do something with it
}

I intend to do the decryption as a function once I can create the array. What I am banging my head against the wall on is that when the while is evaluated $row comes up as FALSE. So it doesn't matter right now what syntax I have as a second line in the while statement for the file pointer never gets there.

Here is an expanded view of my code. Maybe I'm missing something that a fresh pair of eyes will see.

/*$user_cond is a string variable that can inject different query parameters
* It is not having a negative impact on the query for the query string dumps okay on
* debug and there's no indication of a 0 result once executed.  I am getting a result 
* from the database
*/
    $record = mysql_query("SELECT `id`, `pwd`, `firstname`, `approved`, `user_level`, `salt`
                          FROM users
                          where $user_cond AND `banned` = '0'") or die(mysql_error());
    $num = mysql_num_rows($record);
    if($num <= 0){
        $msg[] = "The data you entered does not match any of our users.";
    }
    if($num > 1){
        $msg[] = "Your log-in can not be processed at this time. Contact the administrator.";
    }
    if($num = 1){
        list($id,$pwd,$firstname,$approved,$user_level,$salt) = mysql_fetch_row($record);
        //this I know works for the variables in list will echo on a debug dump
        if (!$approved) {
            if ($user_registration = 0) {
                /*message for manual registration approval process*/
                $err[] = 'Your registration has not been approved.  Contact the administrator.';
            } else {
                /*message for automatic registration approval process*/
                $err[] = 'Your registration has not been approved. Check your e-mail for activation code.';
            }
        }
        $encrypted = array();
        while ( $row = mysql_fetch_assoc($record)) {
            //challenge is here.  $row comes up as FALSE
        }
        print_r($encrypted);die();
    }
Member Avatar for diafol

does mysql_fetch_row move the internal pointer along? If so mysql_fetch_assoc has nothing to access. Try commenting out all the bits relevant (lines 17 to 27).

Hey ardav,

For some reason, removing those lines of code did the trick. Now I can access the data.

Thanks for the help.

Member Avatar for diafol

For some reason, removing those lines of code did the trick. Now I can access the data.

The 'some reason' is that like many mysql functions, mysql_fetch_row moves the internal pointer to the next record. If you have only one record, then the next record doesn't exist - so no data.

Glad it's solved. :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.