时间戳

首页 > 解决方案 > 来自 MySQL PDO 的回显数组

php - 来自 MySQL PDO 的回显数组

问题描述

我有一些我编写的 MySQL 代码可以完美运行：

<?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;
?>

然而，有人告诉我使用 PDO 更安全，所以我将代码转换为 PDO，我做到了这一点：

<?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;
?>

问题的一部分是肯定的：

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

我试图让输出看起来像这样（与第一个相同）：

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

有人可以告诉我如何完成这个吗？谢谢！

标签： phpmysqlarrayspdo

解决方案

无论你找到什么教程，你都需要放弃它。您正在编写的代码仍然容易受到 SQL 注入的影响，而且您并没有太大的改进。您只是对本教程感到困惑。请查看https://phpdelusions.net/pdo以获得良好的 PDO 教程。

至于您编写的代码，您应该考虑一些更改：

  1. 设置正确的字符集，最好禁用模拟语句（默认情况下启用，但您不需要它们）。

  2. 使用 PHP 的DateTime类而不是strtotime()& date()。它更健壮，更可靠。

  3. 不要使用TableRows类，无论它应该是什么。你不需要它！请阅读这篇文章：致命错误：找不到类“TableRows”

  4. 不要捕获异常只是为了显示它们。它们将由 PHP 为您显示。只有当你知道如何处理它们时，你才应该抓住它们。

<?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());

推荐阅读