sql_insert = "INSERT INTO pics (filename) VALUES ('$fileName')";
$results_insert = $conn->query($sql_insert);
$max = "SELECT pic_id FROM pics WHERE pic_id = (SELECT MAX(pic_id) FROM pics)";
$sql_update = "UPDATE users
SET pic_id = '$max'
WHERE username = '$username'" ;
我收到的错误是:
错误:"字段列表"中的未知列"pic_id">
我不确定如何将带有SQL命令的PHP字符串嵌套到另一个SQL
插入语句中。我已经仔细检查了pics
表以及pic_id
列是否存在。
编辑:我现在正在尝试使用PDO来完成此操作。
$t = $pdo->beginTransaction();
$sth = $pdo->prepare('SELECT MAX(pic_id) FROM pics');
$sth -> execute();
$pic_id = $sth->fetch(PDO::FETCH_ASSOC);
$sth = $pdo->prepare('UPDATE users SET username = :username, password = :password, email = :email, name = :name, country_id = :country_id, pic_id = :pic_id WHERE username = :username');
$sth->bindParam(':pic_id', $pic_id, PDO::PARAM_INT);
$sth->bindParam(':username', $username, PDO::PARAM_STR);
$sth->execute();
$sth = $pdo->prepare('INSERT INTO pics (pic_id, filename, filepath) VALUES (:pic_id, :fileName, :resized_file)');
$sth->bindParam(':fileName', $fileName, PDO::PARAM_STR);
$sth->execute();
$pdo->commit();
我之所以将插入移动到users
表下方的pics
表中,是因为 pics
中的pic_id
引用了 users
中的pic_id
。但是,我仍然遇到相同的错误:
致命错误:未捕获的异常"PDOException",消息为"SQLSTATE[42S22]":找不到列:1054 "字段列表"中未知列"pic_id">
许多程序(MySQLI,PDO(都有返回最后插入项ID的方法,但我不确定您使用什么程序与数据库进行交互。
泛型方法
SELECT pic_id FROM pics ORDER BY pic_id DESC LIMIT 1;
MySQLI 方法
$max = $mysqli->insert_id;
PDO方法
$max = $pdo->lastInsertId();
我会使用
PDO 和绑定参数,而不是使用 PHP 字符串值。另外,我想您应该在一个事务中执行所有这些操作。所以我会重写代码如下:
$t = $pdo->beginTransaction();
$sth = $pdo->prepare('INSERT INTO pics (filename) VALUES (:fileName)');
$sth->bindParam(':fileName', $fileName, PDO::PARAM_STR);
$sth->execute();
$id = $pdo->lastInsertId();
$sth = $pdo->prepare('UPDATE users SET pic_id = :id WHERE username = :username');
$sth->bindParam(':id', $id, PDO::PARAM_INT);
$sth->bindParam(':username', $username, PDO::PARAM_STR);
$sth->execute();
$pdo->commit();