PHP JSON Array not returning all rows from my database

Question

$retrieve = mysql_query("SELECT * FROM `users`") or die(mysql_error());

$object -> userDetails = array();

while($retrieveArray = mysql_fetch_array($retrieve))
{
    $item -> userId =  $retrieveArray['id'];
    $item -> userEmail = $retrieveArray['email'];
    $item -> userLocation = $retrieveArray['location'];
    $item -> userFirstName = $retrieveArray['firstname'];
    $item -> userLastName = $retrieveArray['lastname'];
    $object ->userDetails[] = $item;
}   
$json = json_encode($object);
echo $json;

Is there something wrong with this code? My output only displays the first row of my database.

{"userDetails":[{"userId":"1","userEmail":"[email protected]","userLocation":"HomeAddress","userFirstName":"Allan","userLastName":"Knocks"}]}

@JugalThakkar - They aren't. My problem is that my database contains three rows but my output returns only one. The output is at the lower part of my question. — JetPro
– JetPro, Commented Aug 4, 2012 at 8:04
Stop using the old mysql_* functions, they are deprecated. Use PDO or mysqli instead. If you var_dump($item) inside the while loop, how many times does it print? Since you are not creating a new $item object in the loop, the later iterations just rewrite the fields of the existing object - you should do $item = new stdClass() or similar inside the loop to avoid this. — user1233508
– user1233508, Commented Aug 4, 2012 at 8:07
try array_push($object ->userDetails, $item); instead of the last line inside your for loop — jmishra
– jmishra, Commented Aug 4, 2012 at 8:07
mysql_fetch_assoc() did a different thing. It displayed 3 rows but with the same values. — JetPro
– JetPro, Commented Aug 4, 2012 at 8:09
array_push($object ->userDetails, $item); did the exact thing with what mysql_fetch_assoc() did. Displayed 3 rows with the same values from the first row. — JetPro
– JetPro, Commented Aug 4, 2012 at 8:11

xdazz · Accepted Answer · 2012-08-04 08:06:24Z

1

Try this:

$retrieve = mysql_query("SELECT id, email, location, firstname, lastname FROM `users`") or die(mysql_error());
$userDetails= array();
while($row = mysql_fetch_assoc($retrieve)) {
  $userDetails[] = $row;
}   
$json = json_encode(array('userDetails' => $userDetails));
echo $json;

answered Aug 4, 2012 at 8:06

xdazz

161k38 gold badges255 silver badges278 bronze badges

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

2 Comments

JetPro Over a year ago

This is great. Short but perfect. Thanks!

Jugal Thakkar Over a year ago

still curious, @xdazz, could you throw some light on the major downside of the previous approach? Why it failed?

Samson · Accepted Answer · 2012-08-04 10:23:08Z

1

Beware! Doing this in a loop will modify the same instance many times and you will end up with the same object added many times to your array:

while($retrieveArray = mysql_fetch_array($retrieve))
{
    $item -> userId =  $retrieveArray['id'];
    $item -> userEmail = $retrieveArray['email'];
    $item -> userLocation = $retrieveArray['location'];
    $item -> userFirstName = $retrieveArray['firstname'];
    $item -> userLastName = $retrieveArray['lastname'];
    //you are adding here the same object with changed properties.
    $object ->userDetails[] = $item;
}

In the end $object->userDetails will contain n references to the same object with the last set properties.

Instead you should create a new instance inside the loop :

while($retrieveArray = mysql_fetch_array($retrieve))
{
    //new item (if there is a class Item)
    $item = new Item();
    // if $item is just a stdObject created on the fly then use 
    $item = new stdClass();
    $item -> userId =  $retrieveArray['id'];
    $item -> userEmail = $retrieveArray['email'];
    $item -> userLocation = $retrieveArray['location'];
    $item -> userFirstName = $retrieveArray['firstname'];
    $item -> userLastName = $retrieveArray['lastname'];
    //you are adding here another object
    $object ->userDetails[] = $item;
}

edited Aug 4, 2012 at 10:23

answered Aug 4, 2012 at 8:45

Samson

2,8317 gold badges40 silver badges55 bronze badges

2 Comments

Samson Over a year ago

Lol..that was an example... You didn't post the entire code so I guessed your class name. What's it called?

Samson Over a year ago

No.. I'm sorry if you misunderstood.

Enamul Haque · Accepted Answer · 2017-08-08 18:17:50Z

0

You can try

 $sql = "SELECT * FROM users";
 $result = $con->query($sql);
 if ($result->num_rows > 0) {   

  $userDetails = array();
  while ($row = $result->fetch_assoc()) {     
       $userData[] = array(
        "id" => $row["id"],
        "name" => $row["name"],
        "email" => $row["email"],
     );
   }
} else {
   echo "0 results";
}

 $result1['regular'] = $userData;
 $json = json_encode($result1);
  echo $json;

Your json will look like bellow

{"regular":[{"id":"2","name":"enamul Haque","email":"[email protected]"},{"id":"55","name":"Rafiq","email":"[email protected]"}]}

answered Aug 8, 2017 at 18:17

Enamul Haque

5,0952 gold badges44 silver badges66 bronze badges

Collectives™ on Stack Overflow

PHP JSON Array not returning all rows from my database

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related