Mysql result as array

Question

I have a php function that interogates a table and gets all the fields in a column if a condition is fulfield. So the function returns a collection of elements. The problem is that i want this function to return an array that i can parse and display. The code below:

function get_approved_pictures(){
    $con = mysql_connect("localhost","valentinesapp","fBsKAd8RXrfQvBcn");
    if (!$con)
    { 
      echo 'eroare de conexiune';
      die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("mynameisbrand_valentineapp", $con);

    $all = (mysql_query("SELECT picture FROM users WHERE approved = 1")); 

    $row=mysql_fetch_assoc($all);
     //  mysql_close($con);

    return $row['picture']; 
  }

Where am I wrong?

Answer 1

You need to use the loop for traversing all the data fetched by the query:

$pictures=array();
while($row=mysql_fetch_assoc($all))
{
    $pictures[]=$row['picture']; 
}
return $pictures;

Answer 2

Do it like this

$all = mysql_query("SELECT picture FROM users WHERE approved = 1"); 
$arr = array(); // Array to hold the datas
while($row = mysql_fetch_array($all)) {
  $data = $row['picture'];
  array_push($arr,$data);
}
return $arr;

You can now insert it into a function and return the values.

Note : mysql_* functions are being depreciated. Try to avoid them.

Answer 3

For the sake of diversity and to give you some sense of how to use PDO instead of deprecated mysql_*, this is how your function might look like:

function get_approved_pictures(){
    $db = new PDO('mysql:host=localhost;dbname=mynameisbrand_valentineapp;charset=UTF-8',
                  'valentinesapp', 'password');
    $query = $db->prepare("SELECT picture FROM users WHERE approved = 1");
    $query->execute();
    $pictures = $query->fetchAll(PDO::FETCH_ASSOC);
    $db = null;
    return $pictures;
}

Disclaimer: all error handling intentionally omitted for brevity

Answer 4

For the sake of diversity and to give you some sense of how the things have to be instead of inconvenient and wordy PDO, this is how your function might look like:

function get_approved_pictures(){
    global $db;
    return $db->getCol("SELECT picture FROM users WHERE approved = 1");
}

Disclaimer: all error handling is up and running but intentionally encapsulated into private methods for invisibility.