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

Question

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 ?

liding · Accepted Answer · 2015-05-25 23:01:22Z

1

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;
}

edited May 25, 2015 at 23:01

answered May 25, 2015 at 21:48

liding

1161 silver badge6 bronze badges

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

8 Comments

Bogdan Daniel Over a year ago

I get an error at this line "email" = $user["email"], --> syntax error, unexpected '=', expecting ')'

Brian Over a year ago

"email" = $user["email"], ...should be... "email" => $user["email"],

Bogdan Daniel Over a year ago

I'll modify it now. What does the => stand for ?

liding Over a year ago

@Brian Thanks for the correction, I missed this point when writing. As regards the operator '=>' read php.net/manual/en/language.types.array.php

Brian Over a year ago

=> is for assigning a value to a named key. 'email' is the named key and $user["email"] is the assigned value.

|

Brian · Accepted Answer · 2015-05-25 22:41:59Z

0

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."}}"

edited May 25, 2015 at 22:41

answered May 25, 2015 at 21:46

Brian

1,0357 silver badges14 bronze badges

2 Comments

Bogdan Daniel Over a year ago

Thank you. I will try this too. I went for the shorter one because I'm really new to php. What does this line mean while($rows = $user) ?

Brian Over a year ago

I should've used a foreach() loop which I will update in my answer. While

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest