Echoing Array from MySQL PDO

Question

I have some MySQL code that I wrote that functions flawlessly:

<?php
include 'dbconfig.php';

$startdate = date("Y-m-d", strtotime($_GET['startdate']));
$class = ($_GET['class']);
$city = ($_GET['city']);
$coverage = ($_GET['coverage']);

$sql="SELECT day, week, month FROM pricing 
  WHERE '" . $startdate . "' between start_date AND end_date and class='" . $class . "' and city='" . $city . "' and coverage='" . $coverage . "'";
$result = mysqli_query($conn,$sql);


while($row = mysqli_fetch_array($result)) {

    $date_ranges = array(
        'day'   => $row['day'],
        'week'     => $row['week'],
        'month'     => $row['month']
    );

    mysqli_close($conn);

} 

$json = json_encode($date_ranges);
echo $json;
?>

However I was told to use PDO to be more secure, so I am converting the code to PDO and I got this far:

<?php
include 'dbconfig.php';

$startdate = date("Y-m-d", strtotime($_GET['startdate']));
$class = ($_GET['class']);
$city = ($_GET['city']);
$coverage = ($_GET['coverage']);

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("SELECT day, week, month FROM pricing 
  WHERE :startdate between start_date AND end_date and class=:class and city=:city and coverage=:coverage"); 

    $stmt->bindParam(':startdate', $startdate);
    $stmt->bindParam(':class', $class);
    $stmt->bindParam(':city', $city);
    $stmt->bindParam(':coverage', $coverage);

    $stmt->execute();

    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
    foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { 
        echo $v;

    }
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;
?>

The part that's the issue is for sure this:

$result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
    foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { 
        echo $v;

I'm trying to get the output to look like this (same as the first):

{"day":"35","week":"150","month":"650"}

Could someone show me how to complete this? Thanks!

Answer 1

Whatever tutorial you have found, you need to abandon it. The code you are writing is still vulnerable to SQL injection, and you have not improved much. You are only confusing yourself with this tutorial. Please check out https://phpdelusions.net/pdo for a good PDO tutorial.

As for the code you have written, you should consider few changes:

1. Set proper charset, and preferably disable emulated statements (on by default, but you do not need them).

2. Use PHP's DateTime class instead of strtotime() & date(). It is more robust and more reliable.

3. Do not use TableRows class, whatever it is supposed to be. You do not need it! Please read this post: Fatal error: Class 'TableRows' not found in

4. Don't catch the exceptions just to display them. They will be displayed for you by PHP. You should only catch them if you know how to handle them.

<?php

include 'dbconfig.php';

$options = [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES => false,
];
$conn = new \PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password, $options);

$stmt = $conn->prepare("SELECT day, week, month FROM pricing 
    WHERE :startdate BETWEEN start_date AND end_date 
        AND class=:class 
        AND city=:city 
        AND coverage=:coverage");

// bind in execute with an array. 
$values = [
    'startdate' => (new \DateTime($_GET['startdate']))->format('Y-m-d'),
    'class' => $_GET['class'],
    'city' => $_GET['city'],
    'coverage' => $_GET['coverage']
];
$stmt->execute($values);

// echo all results in JSON format
echo json_encode($stmt->fetchAll());

Answer 2

If you're just trying to output the query results in JSON format,

echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));

should work just fine. No need for the extra classes, or the loop.

---

By the way, the way you're doing it with mysqli might actually have a problem, even though it appears to be working properly. It looks like the query could potentially return more than one row, but $date_ranges is being overwritten each time the loop runs, so you'll only end up outputting the last one.

---

If there really should only be one result, you should use fetch() instead of fetchAll() so you'll just get one object in your JSON output rather than an array of objects.

echo json_encode($stmt->fetch(PDO::FETCH_ASSOC));