Get an array of JSON data from php database

Question

I am kinda new to SQL and I am having a problem. I am following the tutorial from Here to try to get data from my database into my android app.

I have it working, but it only gets one line. I know it should be possible to iterate through the database and echo out every line that matches the ID to JSON, but I do not know how. How would I do this?

My current code:

<?php 

 if($_SERVER['REQUEST_METHOD']=='GET'){

 $id  = $_GET['id'];

 require_once('dbConnect.php');

 $sql = "SELECT * FROM LATLON WHERE DeviceID='".$id."'";

 $r = mysqli_query($con,$sql);

 $res = mysqli_fetch_array($r);

 $result = array();

 array_push($result,array(
 "INDEX"=>$res['INDEX'],
 "DeviceID"=>$res['DeviceID'],
 "LAT"=>$res['LAT'],
 "LON"=>$res['LON']
 )
 );

 echo json_encode(array("result"=>$result));

 mysqli_close($con);

 }
?>

EDIT I edited my code to this, but I am getting nothing. Still not quite understanding what is happening. Thanks for all the help!

<?php 

 if($_SERVER['REQUEST_METHOD']=='GET'){

 $id  = $_GET['id'];

 require_once('dbConnect.php');

 $sql = "SELECT * FROM LATLON WHERE DeviceID='".$id."'";

 $r = mysqli_query($con,$sql);

while($row = $result->mysqli_fetch_array($r, MYSQLI_ASSOC)){
    $array[] = $row;
}

 echo json_encode($array);

 mysqli_close($con);

 }
?>

Your code is wide open to SQL injection attacks. Never trust user input, including $id in your example. Instead of building SQL queries by sticking strings together, read about prepared statements. Alternatively, with PDO. — Chris
– Chris, Commented Nov 9, 2016 at 21:36
OH! Thanks for the info. This is currently just a sample, but I definately need to clean the input! — Reid
– Reid, Commented Nov 9, 2016 at 21:37
Aren't you getting an error message? It should be $result->fetch_array, not $result->mysqli_fetch_array. — Barmar
– Barmar, Commented Nov 9, 2016 at 21:52
Don't edit the question to remove the problem. Solutions go in answers, not the question. — Barmar
– Barmar, Commented Nov 9, 2016 at 21:53

Daniel · Accepted Answer · 2016-11-09 21:45:13Z

3

You can iterate through mysqli_fetch_array();

while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
        $array[] = $row;
}
echo json_encode($array);

edited Nov 9, 2016 at 21:45

answered Nov 9, 2016 at 21:40

Daniel

1,26315 silver badges26 bronze badges

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

2 Comments

Reid Over a year ago

Still not quite sure what I am doing wrong. Am I implementing your code correctly?

Daniel Over a year ago

Sorry, check the updated code. I used OO (object oriented) style and you were using procedural.

Barmar · Accepted Answer · 2016-11-09 21:55:52Z

2

When you use the OO interface, the method names don't begin with mysqli_, that's only used for procedural calls. You also have no variable named $result, the result is in $r.

So it should be:

while ($row = $r->fetch_array(MYSQLI_ASSOC)) {

or:

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

answered Nov 9, 2016 at 21:55

Barmar

789k57 gold badges554 silver badges669 bronze badges

Collectives™ on Stack Overflow

Get an array of JSON data from php database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related