Extract a mysql resource to php array?

Question

My goal is to display the profile of a user. I have this function:

function get_profile($un) {
            if($registerquery = $this->conn->query("SELECT * FROM table WHERE usr = '".$un."' ")){
               return $profile = mysql_fetch_array($registerquery); 
           }
        }

Then the display snippet:

<?php $profile = $mysql->get_profile($un); 

foreach($profile as $key => $value){

     echo "<span>".$key.': '.$value."</span><br />"; 

  }

?>

But I get: "Warning: Invalid argument supplied for foreach() in..."

Help pls???

Answer 1

Check the result of get_profile, as it will return null if the query failed. You can't loop over null.

Answer 2

I Agree with Anthony Forloney. The following code is just returning TRUE or FALSE depending on wether loading the $profile variable worked:

return $profile = mysql_fetch_array($registerquery); 

You don't need $profile. You can eliminate it as such:

return mysql_fetch_array($registerquery); 

The function will return the array and then when you call the function later you can load it's return value into $profile as you do with the following:

$profile = $mysql->get_profile($un); 

Answer 3

Be very very careful here. You are passing a raw string into the query function without escaping it and without using a parameterized query. Use mysql_escape_string around $un in your query. Your code flaw is called a sql injection attack.

Someone could pass their username as this

myusername'; update users set password = ''; 

And blank all passwords, thereby allowing themselves to access any account. Other similar shady attacks are equally likely.. you can basically do anything to a database with sql injection attacks.

Answer 4

You need to see if the result was a success or not

if (gettype($result) == "boolean") {
    $output = array('success' => ($result ? 1 : 0));
}

And you need to cycle through it if it's a resource type...

if (gettype($result) == "resource") {
    if (mysql_num_rows($result) != 0 ) {
        while ($row = mysql_fetch_assoc($result)) {
            $output[] =$row;
        }
    }
}

I chopped up some real code that does basically everything pretty awful for you because I can't release it, sorry.

Answer 5

Try this:

function get_profile($un) {
    if($result = $this->conn->query("SELECT * FROM table WHERE usr = '".$un."' ")){
        return $result->fetchArray(MYSQLI_ASSOC); 
    }
    return array();
}

You're mixing MySQLi and MySQL functions and you can't do that. And, the last line of this code will return an empty array if the query does not work, rather than return null.

Answer 6

I have found that the easiest way to loop through mysql results is to use a while loop:

$select = "SELECT * FROM MyTable";
$result = mysql_query($select);
while ($profile = mysql_fetch_array($result)) {
    $name = $profile['name'];
    ...
}

Answer 7

It is probably empty ($profile). Print the value of "count($profile)"