Create JSON from PDO query - PHP

Question

I have an stored procedure in MySQL Database that execute a query like "SELECT * FROM table" and return this rows.

I want to create a JSON with this information, how can i do that?

This is my code:

Main

<?php

 include "config.php";
 include "utils.php";

 $dbConn =  connect($db);

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

   $sth = $dbConn->prepare("CALL consulta_administrador()");
   $sth->execute();

   $result = $sth->fetchAll();
   var_dump($result);
   echo json_encode($result);
}

?>

config.php

<?php

  $db = [
  'host' => 'myDBHost',
  'username' => 'myUsername',
  'password' => 'myPassword',
  'db' => 'myDB'
   ];

?>

utils.php

<?php
 function connect($db)
{
  try {
      $conn = new PDO("mysql:host={$db['host']};dbname={$db['db']}", $db['username'], $db['password']);
      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      
      return $conn;
  } catch (PDOException $exception) {
      echo "Error:" , $exception->getMessage(), '<br>';
      die();
  }
}
?>

Please don't catch PDO exceptions if you do not know what to do with them. This is a really bad practice. — Dharman, Jun 23 '20 at 18:11
I am not sure why you are using stored procedures but that is only going to cause you problems. — Dharman, Jun 23 '20 at 18:14
Ok, I will remove the exceptions. The procedure has many joins that i don't want to write in the php code. — davids182009, Jun 23 '20 at 18:20
thanks for the suggestion...but something happen when i try to encode data to json , code updated — davids182009, Jun 23 '20 at 18:36
Appears the result of var_dump function i get something like this **array(17) { [0]=> array(32) { ["id_funcion"]=> string(1) "1......."** but with the json_encode function nothing happens — davids182009, Jun 23 '20 at 18:45

Hassaan Ali · Answer 1 · 2020-06-23T18:49:02.623

3

You can do something like this:

<?php
$pdo = new PDO("mysql:dbname=database;host=127.0.0.1", "user", "password");
$statement = $pdo->prepare("SELECT * FROM table");
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);

Edit: Assuming you're connected with the database:

if ($_SERVER['REQUEST_METHOD'] == 'GET'){
  $pdo = new PDO("mysql:host={$db['host']};dbname={$db['db']}", $db['username'], $db['password']);
  $sql = $pdo->prepare("CALL consulta administratdor()";
  $sql->execute();
  $results = $statement->fetchALL(PDO::FETCH_ASSOC);
  $json = json_encode($results);
}

edited Jun 23 '20 at 18:49

answered Jun 23 '20 at 18:00

Hassaan Ali

1,038
7
16

Testing this and adding `var_dump($json);` to my code the variable `$json` have this `bool(false)` – davids182009 Jun 23 '20 at 18:13
Are you sure you're connected with your database? – Hassaan Ali Jun 23 '20 at 18:20
With the code that i posted i have results. The stored procedure have many joins that result in a set of records (image in the post). – davids182009 Jun 23 '20 at 18:24
edited my answer. Check it. – Hassaan Ali Jun 23 '20 at 18:31
i tried but get this error Fatal error: Uncaught Error: Non-static method PDO::query() cannot be called statically in /storage/ssd5/029/13611029/public_html/cinelandia/consultaInformacionAdministrador.php:18 in this line $q = $dbConn::query ($sql); – davids182009 Jun 23 '20 at 18:41
$pdo->query instead\ – Dharman Jun 23 '20 at 18:45

score 0 · Accepted Answer · edited Jun 23 '20 at 22:03

0

I got the solution to my problem, the thing was that in my DB i have special characters that json_encode function doesn't understand, to solve it aggregate this line to my code $dbConn->query("SET NAMES 'UTF8'");:

$dbConn =  connect($db);
$dbConn->query("SET NAMES 'UTF8'");

if ($_SERVER['REQUEST_METHOD'] == 'GET'){
    $sql = "CALL consulta_administrador()";
    $q = $dbConn->query($sql);
    $data = $q->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($data, JSON_UNESCAPED_UNICODE);
}

edited Jun 23 '20 at 22:03

Dharman

30,962
25
85
135

answered Jun 23 '20 at 20:32

davids182009

441
3
7
26

1

You should not be using `SET NAMES`. See this post. https://stackoverflow.com/questions/279170/utf-8-all-the-way-through – Dharman Jun 23 '20 at 22:03

Create JSON from PDO query - PHP

2 Answers2