CODEWITHSUNDEEP
phpmysqljson
Bogdan Daniel
Bogdan Daniel

Reputation: 2749

How to return multiple/all rows from mysql db via php and make json?

I have this in my php file:

$response = array();
    $user = $db->test($email);
                if ($user) {
                    // user stored successfully
                    $response["success"] = 1;
                    $response["user"]["email"] = $user["email"];
                    $response["user"]["imagepath"] = $user["imagepath"];
                    $response["user"]["about"] = $user["about"];

                    echo json_encode($response);
                } else {
                    // user failed to store
                    $response["error"] = 1;
                    $response["error_msg"] = "JSON Error occured in db";
                    echo json_encode($response);
                }

the test function is 

public function test($email){
            $result = mysql_query("SELECT * FROM activities WHERE email = '$email'");
            // return user details
            return mysql_fetch_array($result);
    }

The test function is returning multiple rows, but the php sends the json only with the first row from the database that matches that email. How can I make a foreach() or something like that to encode everything that the test function returns ?

Upvotes: 0

Views: 278

Answers (2)

liding
liding

Reputation: 116

First of all you have to refuse to use the mysql_fetch_array() function (read this).

Next, you must understand that your variable 'user' contains a list of all found users (and, in fairness, it should be called 'users')

$response = array();
$users = $db->test($email);
foreach($users as $user){
    if ($user) {
        // user stored successfully
        $response["success"] = 1;   
        $response["users"][] = array(
            "email" => $user["email"],
            "imagepath" => $user["imagepath"],
            "about" => $user["about"]
        );
    } else {
        if( empty($response) ){
            // user failed to store
            $response["error"] = 1;
            $response["error_msg"] = "JSON Error occured in db";
        }
    }
}
echo json_encode($response);

And update test function:

public function test($email){
    $result = mysql_query("SELECT * FROM activities WHERE email = '$email'");
    // get all users details
    $users = array();
    while($user = mysql_fetch_array($result)){
        $users[] = $user;
    }
    return $users;
}

Upvotes: 1

Brian
Brian

Reputation: 1025

You need to loop through the user array. You also need to increment your array keys so the next entry doesn't overwrite the previous.

<?php
$response = array();
$user = $db->test($email);
if ($user) {
  // user stored successfully
  $response['success'] = 1;

  $key = 0; // unique key

  // Loop through user array
  foreach($user as $row) {

    $response[$key] = array(
      'email' => $row["email"],
      'imagepath' => $row["imagepath"],
      'about' => $row["about"];
    );

    $key++; // increment key

  }

} else {
  // user failed to store
  $response['error'] = 1;
  $response['error_msg'] = "JSON Error occurred in db";
}

var_dump($response); // debugging

$json = json_encode($response);
echo($json);
?>

The response array before json_encode():

array(3) {
  ["success"]=> string(1) "1"
  [0]=>
  array(3) {
    ["email"]=> string(21) "[email protected]"
    ["imagepath"]=> string(13) "path/to/image"
    ["about"]=> string(26) "about content etc etc etc."
  }
  [1]=>
  array(3) {
    ["email"]=> string(22) "[email protected]"
    ["imagepath"]=> string(14) "path/to/image2"
    ["about"]=> string(26) "about content etc etc etc."
  }
}

Response after json_encode():

string(227) "{"success":"1","0":{"email":"[email protected]","imagepath":"path\/to\/image","about":"about content etc etc etc."},"1":{"email":"[email protected]","imagepath":"path\/to\/image2","about":"about content etc etc etc."}}"

Upvotes: 0

Related Questions