How to get all records from the database via an endpoint in PHP

Question

Here is the endpoint

http://localhost/mudb/unitedapi/

I need to get all the records in a json format from my table when i load the url in my browser. I can now only get a single record using the id from http://localhost/mudb/unitedapi/1. How can I get them all?

My .htaccess

RewriteEngine On    # Turn on the rewriting engine

RewriteRule ^unitedapi/([0-9a-zA-Z_-]*)$ api.php?player_id=$1 [NC,L]

My code

<?php

 header("Content-Type:application/json");
 if (isset($_GET['player_id']) && $_GET['player_id']!="") {
   include('db.php');
   $player_id = $_GET['player_id'];

   $result = mysqli_query($con, "SELECT * FROM `players` ");

   if(mysqli_num_rows($result)>0)
   {
     $row = mysqli_fetch_array($result);
     $fname = $row['fname'];
     $lname = $row['lname'];
     $shirtno = $row['shirtno'];
     $position = $row['position'];
     $response_code = $row['response_code'];
     $response_desc = $row['response_desc'];

     response($player_id, $fname,$lname,$shirtno,$position, 
 $response_code,$response_desc);
     mysqli_close($con);
    }
    else {
      response(NULL, NULL,NULL, NULL, NULL, 200,"No Record Found");
    }
 } 
 else {
   response(NULL, NULL,NULL, NULL, NULL,400, "Invalid Request");
 }

 function response($player_id, $fname,$lname,$shirtno,$position, 
 $response_code,$response_desc)
 {
   $response['player_id'] = $player_id;
   $response['fname'] = $fname;
   $response['lname'] = $lname;
   $response['shirtno'] = $shirtno;
   $response['position'] = $position;
   $response['response_code'] = $response_code;
   $response['response_desc'] = $response_desc;

   $json_response = json_encode($response);
   echo $json_response;
 }
 ?>

`SELECT * FROM \`players\``...this code does not even select by the ID anyway, it just selects them all, and then the PHP returns whatever the first row in the results happens to be. — ADyson, Aug 03 '22 at 09:45
To return all the rows, you need to loop until all the rows have been processed, add each row to an array, and then output that array at the end. There is no shortage of existing examples of this online already (because, unsurprisingly, returning multiple rows from a query is a very common requirement). — ADyson, Aug 03 '22 at 09:45
Ah sorry, my edit might have overwritten it. Can you edit again? — ADyson, Aug 03 '22 at 09:49
Sorry, i dont seem to get any of what you have said. Could you please share a link with a similar implementation — 54V4G3208, Aug 03 '22 at 09:50
I just shared one. There are several others available to you via a simple google search. Basically you can either loop through all the rows, or use fetch_all() — ADyson, Aug 03 '22 at 09:51
your response function just makes your code uglier, it serves no purpose, simply just build out a var, i.e `$response` then send it, if you add 1 short line `exit(json_encode($response));`, or a long response( with a bunch of NULLs is no diff but much easier to follow — Lawrence Cherone, Aug 03 '22 at 09:53
(For more info, every time you run `mysqli_fetch_array` (or mysqli_fetch_row`, or `mysqli_fetch_assoc`) it returns one row from the database. If there are no more rows, it returns null. So one technique is to loop until that returns null). — ADyson, Aug 03 '22 at 09:53
Lawrence is right, a lot of this code is redundant, just copying variables and using up memory when you could have used the $row data directly. And setting "response code" in a JSON object doesn't actually change your HTTP response code (which is what a RESTful API is supposed to do). — ADyson, Aug 03 '22 at 09:55

How to get all records from the database via an endpoint in PHP

0 Answers0