How to get all items from database in json format php?

Question

I am new to php development.

How to display multiple json objects fetched from database? So far I am getting single data. I need to display all inserted data in database in json format.

When I insert first data, I get a response like this. If I insert second data, it should display first and second data and so on in upload_details object but it displaying last inserted data only.

{"code":200,
 "message":"The file FileUpload1444329638_li.jpg has been uploaded.",
 "upload_details": {"desc":"hi",
 "file_name":"abc.com\/FileUpload1444329637_li.jpg"}}

When I insert second data:

{"code":200,
"message":"The file FileUpload1444329638_li.jpg has been uploaded.",
"upload_details": {"desc":"h2",
 "file_name":"abc.com\/FileUpload1444329638_li.jpg"}}

Here is my code:

 <?php 
include 'db_config.php'; //echo "hi";exit;
if($_POST['api_name']=="upload_file"){
if(!empty($_FILES["profile_pic"]["name"])){
 $fileName = time().'_'.$_FILES["profile_pic"]["name"]; 
 if (move_uploaded_file($_FILES["profile_pic"]["tmp_name"], "uploads/".$fileName)) { 
 $sql = "Insert into file_upload(`desc`,`file_name`) values ('".$_POST['desc']."','".$fileName."');";
 if($conn->query($sql)){ 
 $response= array('code'=>200,'message'=>"The file ". basename( $_FILES["profile_pic"]["name"]). " has been uploaded.",'upload_details'=>array("desc"=>$_POST['desc'],"file_name"=>$_SERVER['SERVER_NAME'].dirname($_SERVER['SCRIPT_NAME']).$fileName));
//print_r($response);exit;
 }else{
 $response= array('code'=>500,'message'=>"Error in uploading file");
 }
 } else {
 $response= array('code'=>500,'message'=>"Error in uploading file");
 }
}else{
 $response= array('code'=>500,'message'=>"Error in uploading file");
}
}elseif ($_POST['api_name']=="get_files"){
$response['code']=200;
$response['file_lists'] = array();
$res = $conn->query("select * from file_upload");
while($row = $res->fetch_object()){
 array_push($response['file_lists'],array('desc'=>$row- >desc,'file_path'=>$_SERVER['SERVER_NAME'].dirname($_SERVER['SCRIPT_NAME']).$row ->file_name));
}
}
echo json_encode($response);exit; 

I expect to get response as data from all which is inserted in table, like:

{"code":200,
 "message":"The file has been uploaded.",
 "upload_details": {"desc":"hi",
 " file_name":"abc.com\/FileUpload1444329637_li.jpg"},
{"desc":"hi2",
 "file_name":"abc.com\/FileUpload1444329638_li.jpg"}
}

• wallyk: When you say you need to "display all inserted data in database", do you mean to exclude updated data?
• GolezTrol: Your code says file_lists, but your output says upload_details. Are you sure this code is actually the code executed? It seems to be okay, but your output is from another (maybe older) version of the code, so maybe you just modified the wrong file, or the upload of the PHP file to the server failed.
• user3557113: i need to include updated data too. for eg: if i insert first and second data means i need to display all two datas. everything which is included in table. @wallyk
• mpen: @user3557113 You're taking the wrong approach. You should either APPEND to the new file to the DOM instead of flushing what's already there, or create a 2nd API endpoint to fetch the entire file list.

Answer 1

First, your script currently only has access to the data being inserted for that POST. Your code does nothing at all to query all records that currently exist in file_upload table. Without this, I don't know how you expect these other records to magically be returned in the response.

Second, it would be VERY atypical to tie a single insert operation to a full listing of all records in the table into which the insert was made. This seems like a problematic approach in that you slow down the process of returning a successful insert message to the caller while you try to query the full table. Normally, if one wanted a full listing of records, one might expect to make a GET request against an API specifically designed to do this rather than make a POST (insert) to get this information. Additionally, it seems very odd to intentionally design an API that will get slower and slower over time as you add more records and have a larger payload to return to the caller. The problem is compounded by the fact you are trying to json_encode the data structure. This means that your script will actually continue to take more and more memory to execute with each call to the insert API (as you have to hold the entire data structure for all records in memory to encode it). This means that, at a server level, calls to the API will continue to take a greater percentage of system resources with each passing request, perhaps causing you to need to scale hardware just to meet this use case. You should REALLY, REALLY, REALLY (is that enough REALLY's?) reconsider this requirement.

Third, You have significant SQL injection vulnerability. You should look into using prepared statements and/or sanitizing/validating the user input before making the insert.

Finally, the response format you propose is not valid JSON. I would think that, if you decided you REALLY want to return all records for each successful insert, you would want a format like:

{
 "code":200,
 "message":"The file has been uploaded.",
 "upload_details": [
 {
 "desc":"hi",
 "file_name":"abc.com\/FileUpload1444329637_li.jpg"
 },
 {
 "desc":"hi2",
 "file_name":"abc.com\/FileUpload1444329638_li.jpg"}
 }
 ]
}

Note the array wrapper around the two returned records.