我不能在一个语句中运行INSERT INTO和SELECT查询。
这个php代码有问题:
$db = connect_db_marketlist();
if($db != null) {
$sql = "INSERT INTO items (user_id, market_table_id, price, info )"
." VALUES ('$id', (SELECT table_id FROM markets WHERE city='$city' AND market='$market'), $price, '$info')";
echo $sql; // !!! DEBUG !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
try {
$db->query($sql);
echo "OKAY: ".$db->lastInsertId();
} catch (Exception $e) {
echo "ERROR: ".$e->getMessage();
}
}
And I got error:
SQLSTATE[23000]: Integrity constraint violation: 1048 Column'market_table_id'不能为空
错误说SELECT查询返回null,但当我直接在phpmyadmin中运行$sql语句时,它正在工作。
这是echo $sql output:
INSERT INTO items (user_id, market_table_id, price, info) VALUES('12345678', (SELECT table_id FROM markets WHERE city='ANKARA' AND market='MİGROS'), 22.33, 'TEST_INFO_MİGROS')
我怎么了?也许这是我的数据库连接:
function connect_db_marketlist() {
$servername = "localhost";
$username = "marketuserdb";
$password = "pass1234";
$conn = null;
try {
$conn = new PDO("mysql:host=$servername;dbname=marketlist", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
return $conn;
}
是否可以使用PDO运行"INSERT INTO…SELECT…"查询?如果是,如何,如果不是,为什么?
p。S:当我输入任何整数而不是(SELECT....)查询时,它正在工作。所以DB连接没有问题。
您应该在DSN中设置正确的连接字符集,例如
"mysql:host=$servername;dbname=marketlist;charset=utf8mb4"
(这是针对utf-8的,您应该为您的表编码设置它)
var应该在select
这边
"INSERT INTO items (user_id, market_table_id, price, info )"
." SELECT '$id', table_id , $price, '$info'
FROM markets WHERE city= '$city' AND market='$market' ;";