如何在sql查询中替换表名？

I have this code

我有这个代码

$Models = [
'users' => 'm_users',
'products' => 'm_products',
'customers' => 'm_customers'];

$string = "SELECT users as Name,`users`.`family` AS Family, products.id AS PID, `customers`.`id` AD CID FROM users INNER JOIN customers INNER JOIN products WHERE customers.customers = 1 and users.users = 2 and `products`.`id` = 1 and products.name = 'me' ORDER BY customers.id DESC";
$string_1 = "SELECT users FROM users";
foreach ($Models as $alias => $table) {
$string = preg_replace("/(\s|,)`?{$alias}`?/", "$1`{$table}`", $string);
}

And i want to replace the table name, but it's not working for any sql query string Like $string and $string_1

我想替换表名,但它不适用于任何sql查询字符串,如$ string和$ string_1

1 个解决方案

#1

The following will replace only the table selecting FROM, but not table references within the SELECT i.e. users.some_field.

以下内容将仅替换选择FROM的表,而不替换SELECT中的表引用,即users.some_field。

$str = 'SELECT users, users.some_field FROM users';
preg_replace("/(?<=FROM\s)(users)/", "m_users", $str);
// SELECT users, users.some_field FROM m_users

EDIT: This one should replace table references within the SELECT.

编辑:这个应该替换SELECT中的表引用。

$str = 'SELECT users, users.some_field FROM users';
preg_replace("/(?<=FROM\s)(users)|(users(?=\.))/", "m_users", $str);
// SELECT users, m_users.some_field FROM m_users

To put this into your loop:

把它放到你的循环中:

$Models = [
    'users' => 'm_users',
    'products' => 'm_products',
    'customers' => 'm_customers'
];
$string = "SELECT users as Name,`users`.`family` AS Family, products.id AS PID, `customers`.`id` AD CID FROM users INNER JOIN customers INNER JOIN products WHERE customers.customers = 1 and users.users = 2 and `products`.`id` = 1 and products.name = 'me' ORDER BY customers.id DESC";
foreach ($Models as $alias => $table) {
    $string = preg_replace("/(?<=FROM\s)({$alias})|({$alias}(?=\.))/", $table, $string);
}

#1