如何从具有TABLE_NAMES的LIKE子句的语句中获取mysql记录

Question

我想从两个表中选择记录。这两张表的前缀是"shop_"。

如何在sql语句中为两个商店选择记录？

我目前的声明：

// Select
$stmt = $mysqli->prepare("SELECT name, html_id, price FROM database_name WHERE TABLE_NAME LIKE 'shop%'");
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc())
{
$arr[] = $row;
}
$name = [];
foreach($arr as $arrs)
{
$name[] = array(0 => $arrs['name'], 1 => $arrs['html_id'], 2 => $arrs['price']); //here
}
$stmt->close();
print_r($name);

mysql.php当前的错误是：

Fatal error: Uncaught Error: Call to a member function execute() on boolean in C:wamp642wwwwebcartsearch.php on line 17 and line 17 is: $stmt->execute();

我可以用以下命令来"显示"表格：

$stmt = $mysqli->prepare("show tables like '%shop%'");

但它没有得到记录，我认为只是一个物体。

"显示类似于"%shop%"的表"的输出将打印2个数组，但这些数组为空，没有数据/记录。

我认为是sql语句需要工作。谢谢

编辑：

我也试过：

$stmt = $mysqli->prepare("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='feeds' AND TABLE_NAME LIKE 'shop%'");

EDIT:search.php的内容

<?php 
include 'define.php';
$mysqli = new mysqli($host, $db_user, $db_password, $db_database);
if($mysqli->connect_error)
{
?><script>var profiles_delete_modal_1 = ' Error 3: Problem deleteing profile. Make sure database login credentials are correct.';</script>
<script>$(".modal").css({"opacity":"1", "z-index":"100"});$(".modal_mid").html("<pre>" + profiles_delete_modal_1 + "</pre>");setTimeout(function(){$(".modal").css({"opacity":"0", "z-index":"-100"});},5000);</script><?php
exit;
}
$shop = 'shop';
// Select
//$stmt = $mysqli->prepare("show tables like '%shop%'");
//$stmt = $mysqli->prepare("SELECT * FROM feeds WHERE TABLE_NAME LIKE 'shop%'");
$stmt = $mysqli->prepare("SELECT name, html_id, price FROM database_name WHERE TABLE_NAME LIKE 'shop%'");
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc())
{
$arr[] = $row;
}
$n=0;
$name = [];
foreach($arr as $arrs)
{
$name[] = array(0 => $arrs['name'], 1 => $arrs['html_id'], 2 => $arrs['price']); //here
$n++;
}
$stmt->close();
print_r($name);

和define.php:的内容

$www_dir = 'webcart';
$url_root = 'http://localhost/' . $www_dir . '';
$www_dir_slash = $_SERVER['DOCUMENT_ROOT'] . '' . $www_dir . '/';
$host = 'localhost';
$db_user = 'webcart_admin';
$db_password = 'asd123';
$db_database = 'shop';
$_SESSION['host'] = $host;
$_SESSION['db_user'] = $db_user;
$_SESSION['db_password'] = $db_password;
$_SESSION['db_database'] = $db_database;

编辑

根据下面的答案，我已经能够创建这样的字符串：

SELECT name, html_id, price FROM shop_a UNION shop_b

但是它不会正确执行。

这是我的代码：

$stmt = $mysqli->prepare("SELECT name, html_id, price FROM shop_a UNION shop_b"); 
$result = $stmt->execute();

它给出以下错误：

Fatal error: Uncaught Error: Call to a member function execute() on boolean in C:wamp642wwwwebcartsearch.php on line 43

编辑明白了。

我很快就会公布答案。声明如下：

"SELECT name, html_id, price FROM shop_a UNION SELECT name, html_id, price from shop_b"

Answer 1

我几乎从musafar得到了答案，尽管我需要找到数组中的对象。所以我用了一些前臂环来做这件事，因为我不知道其他的方法。如果有其他方法可以获取mysqli_object数据，请告诉我。

$stmt = $mysqli->prepare("SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'shop' AND table_name LIKE 'shop%'"); 
//table_schema is the database name and 'shop%' is the search string
$stmt->execute();
$tables = $stmt->get_result();
$stmt->close();
$arr = [];
foreach($tables as $tabless)
{
$arr[] = $tabless;
}
foreach($arr as $arrs)
{
$toby[] = implode(',',$arrs);   
}       
$tobyy = implode(' UNION SELECT name, html_id, price from ',$toby);
//$tobyy = "shop_a UNION SELECT name, html_id, price from shop_b"
$arr = [];  
$stmt = $mysqli->prepare("SELECT name, html_id, price FROM " . $tobyy); 
$result = $stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc())
{
$arr[] = $row;
}
$n=0;
$name = [];
foreach($arr as $arrs)
{
$name[$n] = array(0 => $arrs['name'], 1 => $arrs['html_id'], 2 => $arrs['price']); //here
$n++;
}
$stmt->close();
print_r($name);
//$name = "Array ( [0] => Array ( [0] => Chromecast [1] => chromecast [2] => 59 ) [1] => Array ( [0] => EZCast [1] => ezcast [2] => 49 ) )"

Answer 2

SELECT查询用于从DB表中获取数据，而不是从DB本身中获取数据。因此，您需要在查询的FROM部分中提供一个表名。

考虑到您正试图从类似的表(相同的字段(中提取数据。。。

$stmt = $mysqli->prepare("SELECT table_name FROM information_schema.tables WHERE table_schema = 'wp_105424' AND table_name LIKE 'shop%'");
$stmt->execute();
$tables = $stmt->get_result();
$dataStmt = $mysqli->prepare("SELECT name, html_id, price FROM " . implode(',', $tables)); // name, html_id, price should be in all tables that starts with *shop*
$dataStmt->execute();
$data = $dataStmt->get_result();

您可能需要添加条件来处理所有场景。