PHP |数据库 |使用 information_schema 列的多个查询

Question

我有一个通过 phpMyAdmin 运行的查询:

SET @sql = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.columns where table_name='staff' and table_schema='tag'), ' FROM tag.staff WHERE staff_id=1;'); 
PREPARE stmt1 FROM @sql; 
EXECUTE stmt1;

但是当我尝试从 php 文件运行它时，我没有返回任何行。

我通过谷歌搜索了解到，从 php 运行多个查询会出现问题，我看到的一个提示建议使用存储过程。我也试过这个但是它抛出了一个错误:

$this->_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$stmt = $this->_db->prepare($part1);
$stmt->execute();

但在大多数情况下，我不理解我找到的几个答案(我的 PHP 或 SQL 都不高级)。

有人做过吗？

谢谢艾玛

Answer 1

这段代码对我有用!

<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

$sql = "SELECT COUNT(*) AS _num FROM test; ";
$sql.= "INSERT INTO test(id) VALUES (1); ";
$sql.= "SELECT COUNT(*) AS _num FROM test; ";

if (!$mysqli->multi_query($sql)) {
    echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

do {
    if ($res = $mysqli->store_result()) {
        var_dump($res->fetch_all(MYSQLI_ASSOC));
        $res->free();
    }
} while ($mysqli->more_results() && $mysqli->next_result());
?>