Building API: How do I Do SQL Query w/in the API function

1

Hello,

I’m fairly new with the concept of API and also using Slim PHP framework.

I did some tutorials on how to create API using Slim PHP framework.

How do I do this:

  • I would like to call a SQL query within an API function for validation purpose.
2

Welcome to the Slim forum @cws101

Please add more technical details about your current project, database package and your previous attempts.

1 Like
3

I am using MariaDB for database.

Here is a sample code

$app->post('/customers-data/add', function (Request $request, Response $response, array $args) {
  $data = $request->getParsedBody();
  $name = $data["name"];
  $email = $data["email"];
  $phone = $data["phone"];
 
  $sql = "INSERT INTO customers (name, email, phone) VALUES (:name, :email, :phone)";
 
  try {
    $db = new Db();
    $conn = $db->connect();
   
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':email', $email);
    $stmt->bindParam(':phone', $phone);
 
    $result = $stmt->execute();
 
    $db = null;
    $response->getBody()->write(json_encode($result));
    return $response
      ->withHeader('content-type', 'application/json')
      ->withStatus(200);
  } catch (PDOException $e) {
    $error = array(
      "message" => $e->getMessage()
    );
 
    $response->getBody()->write(json_encode($error));
    return $response
      ->withHeader('content-type', 'application/json')
      ->withStatus(500);
  }
 });

Let us for example say: before I execute the INSERT statement here. I would like to do some validation – say if the record is related to another record (like brother, sister, father, mother etc). I need to access the database to get any relation.

4

Thank you for not replying. I haven’t exhausted tutorials from the net.

I think this is one of your tutorial Slim 4 - Tutorial | Daniel Opitz - Blog – I looking forward to learning more from this link.

Add some more link for my consumption:

5

For input validation, you can use a validation library. Based on this Slim tutorial, you can find more articles for CakePHP Validation and Symfony Validator in my Slim 4 eBook. This video from Gio shows how to use Valitron with Slim 4.

In your specific case, you may also want to check some values in the database. Most of the packages also provide a “callable” validator that can be used for additional database checks. However, the database validation itself must be integrated and performed with your own logic.

1 Like
6

Thanks for the input.

From a beginner’s point of view. This is a tad overwhelming. I was expecting I’ll just manage with just slimphp framework and not deal with other frameworks (cakephp symfony). I’m thinking these are sets of library to make easy development with validation.

Right now, I’m good with creating the simple API (GET, POST, PUT, DELETE). And then I’d like to know more about managing/organizing my files. And then yes, I guess, that’s another one I’d like to get into - validation. I’d like to keep things simple so it’s not hard to understand especially when I have to ‘go back’ and update/maintain my works. Sometimes, there’s just a lot of extras I don’t need or I just don’t know the ‘boundery’ of learning just enough to get the right amount of learning to get things going.

Really appreciate the inputs.

7

And then I’d like to know more about managing/organizing my files.

Then I would recommend taking a look at the Single Action Controller section because putting all the logic into a single route callback (function) is not maintainable in the long run.

I would also recommend installing a DI container (PHP-DI) to manage dependencies, such as the database connection.