时间戳

首页 > 解决方案 > 当我尝试绘制查询表时，我的 php 代码出错

php - 当我尝试绘制查询表时，我的 php 代码出错

问题描述

由于我如何在表格中获得信息，我需要建立一个联合来显示交叉引用。

Tablebrand1 是表的名称。2 和 3

Tablebrand1

CODE    CROSSREFERENCE
A15     SAMSUNG A10
A16     SAMSUNG A20
A63     SAMSUNG A30

Tablebrand2

CODE    CROSSREFERENCE
X63     SAMSUNG A10
X64     SAMSUNG A20
X65     SAMSUNG A30

Tablebrand3

CODE    CROSSREFERENCE
TOP99   SAMSUNG A10
TOP98   SAMSUNG A20
TOP97   SAMSUNG A30

SQL查询

(SELECT "Tablebrand1" AS `brand`, CROSSREFERENCE as `code`
    FROM Tablebrand1 WHERE CROSSREFERENCE = 'SAMSUNG A10')
UNION 
(SELECT "Tablebrand2" AS `brand`, CROSSREFERENCE as `code`
    FROM Tablebrand2 WHERE CROSSREFERENCE = 'SAMSUNG A10')
UNION
(SELECT "Tablebrand3" AS `brand`, CROSSREFERENCE as `code`
    FROM Tablebrand3 WHERE CROSSREFERENCE = 'SAMSUNG A10');

查询正确地提供了它，但是当尝试在 php 中显示它时，我收到一个来自查询的错误，因为如果我修改它以获得更简单的查询，它会正确地给我带来数据。

<?php
echo "<table style='border: solid 1px black;'>";
echo "<tr><th>brand</th><th>code</th></tr>";

class TableRows extends RecursiveIteratorIterator {
  function __construct($it) {
    parent::__construct($it, self::LEAVES_ONLY);
  }

  function current() {
    return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
  }

  function beginChildren() {
    echo "<tr>";
  }

  function endChildren() {
    echo "</tr>" . "\n";
  }
}

$servername = "localhost";
$username = "user";
$password = "pass";
$dbname = "dbname";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $stmt = $conn->prepare("((SELECT "Tablebrand1" AS `brand`, CROSSREFERENCE as `code`
    FROM Tablebrand1 WHERE CROSSREFERENCE = 'SAMSUNG A10')
UNION 
(SELECT "Tablebrand2" AS `brand`, CROSSREFERENCE as `code`
    FROM Tablebrand2 WHERE CROSSREFERENCE = 'SAMSUNG A10')
UNION
(SELECT "Tablebrand3" AS `brand`, CROSSREFERENCE as `code`
    FROM Tablebrand3 WHERE CROSSREFERENCE = 'SAMSUNG A10');");
  $stmt->execute();

  // set the resulting array to associative
  $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;
echo "</table>";
?>

我的代码如下，我对 php 和 union 的了解很低。

标签： php

解决方案

你不是说你的错误信息是什么，但一个明显的错误是你使用的引号没有被转义。此外，在查询开始时您有一个(太多（"((SELECT...）所以您需要像这样转义查询中的引号

$stmt = $conn->prepare("
  (SELECT \"Tablebrand1\" AS `brand`, CROSSREFERENCE as `code`
   FROM Tablebrand1 WHERE CROSSREFERENCE = 'SAMSUNG A10')
  UNION 
  (SELECT \"Tablebrand2\" AS `brand`, CROSSREFERENCE as `code`
   FROM Tablebrand2 WHERE CROSSREFERENCE = 'SAMSUNG A10')
  UNION
  (SELECT \"Tablebrand3\" AS `brand`, CROSSREFERENCE as `code`
   FROM Tablebrand3 WHERE CROSSREFERENCE = 'SAMSUNG A10');"
);

或者，您可以在查询中使用单引号而不是像这样的双引号

$stmt = $conn->prepare("
  (SELECT 'Tablebrand1' AS `brand`, CROSSREFERENCE as `code`
   FROM Tablebrand1 WHERE CROSSREFERENCE = 'SAMSUNG A10')
  UNION 
  (SELECT 'Tablebrand2' AS `brand`, CROSSREFERENCE as `code`
   FROM Tablebrand2 WHERE CROSSREFERENCE = 'SAMSUNG A10')
  UNION
  (SELECT 'Tablebrand3' AS `brand`, CROSSREFERENCE as `code`
   FROM Tablebrand3 WHERE CROSSREFERENCE = 'SAMSUNG A10');"
);

推荐阅读