将sql语句转换为php查询 - Convert an sql statement into a php query

Convert an sql statement into a php query

本文关键字：php 查询转换 sql 语句 | 更新日期: 2023-09-27

嗨，我正在尝试转换此代码

  INSERT INTO destTable
SELECT Field1,Field2,Field3,... 
FROM srcTable
WHERE NOT EXISTS(SELECT * 
                 FROM destTable 
                 WHERE (srcTable.ID=destTable.ID)
                 )

转换为PHP查询。这是我目前所拥有的，但它是错误的。

mysql_query('INSERT INTO ' . $targetDB . 'SELECT * FROM' . $sourceDB . 'WHERE NOT EXISTS(SELECT * FROM' . $targetDB . 'WHERE ('. $targetDB . '`.` ID ='. $sourceDB . '`.` ID)'

你能调整一下使它正确吗？

您可以使用PDO和白名单数组来验证您的表变量：

define('DB_HOST', 'localhost');
define('DB_NAME', 'your_database');
define('DB_USER', 'your_username');
define('DB_PASSWORD', '');
try {
    //Make your connection handler to your database
    $conn = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USER, DB_PASSWORD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
    $tables = array('table1', 'table2', 'table3');
    if (in_array($targetDB, $tables) && in_array($sourceDB, $tables)) {
        $sql = "INSERT INTO $targetDB (SELECT * FROM $sourceDB WHERE NOT EXISTS (SELECT * FROM $targetDB WHERE $targetDB.ID = $sourceDB.ID))";
        $stmt = $conn->prepare($sql);
        $stmt->execute();
    } else {
        echo 'Wrong tables';
    }
} catch(PDOException $e) {
    echo $e->getMessage();
    die();
}

您只是缺少了一些空间并添加了不必要的引号

'INSERT INTO ' . $targetDB . ' SELECT * FROM ' . $sourceDB . ' WHERE 
NOT EXISTS(SELECT * FROM ' . $targetDB . ' WHERE ('. $targetDB . '.ID ='. $sourceDB . '.ID)'