0
453
ViewsCreate JSON from PDO query - PHP
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();
}
}
?>
·
Santiago Trujillo
2 answers
Answer question0
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 to 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); }
·
Santiago Trujillo
Report
0
I got the solution to my problem, the problem was that in my database I have special characters that the json_encode function doesn't understand, to solve it add 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); }
·
Santiago Trujillo
Report
Answer question