我有一个使用for循环的多个插入(4条记录)。然后,我想对插入的记录运行更新查询,其中pathway_allowed = 'y'(4个记录中只有一个具有此值)。我猜last_insert_id()在这里会很有用,但不确定如何使用它来更新插入以下内容的记录:
$pathway_allowed = intval($_POST['allowed']);
$action = mysql_real_escape_string($_POST['actions']);
if(isset($_POST['submit'])){
$pathway_comment = array();
foreach($_POST['comment'] as $comment) {
$pathway_comment[]= mysql_real_escape_string($comment);
}
for($i=0, $count = count($pathway_comment);$i<$count;$i++) {
$comment = $pathway_comment[$i];
$query = sprintf(
"INSERT INTO pathway (
pathway_pk,
case_fk,
level,
pathway_action_fk,
pathway_allowed,
comment
) VALUES (
'',
'$case_pk',
'1',
'$action',
'%s',
'$comment')", $pathway_allowed === $i ? 'y' : 'n');
$result = mysql_query($query, $connection) or die(mysql_error());
}
if($result){
- SELECT the 4 records here...
}
}
在每个循环迭代中,将mysql_insert_id()
存储到一个数组中,稍后可以使用该数组来选择新记录。注意,我在这里将增量for
循环替换为更整齐的foreach
:
// Initialize arrays for later
// one for the new ids
$inserted_ids = array();
// one to keep comments from failed queries
$failed_comments = array();
// and one for the MySQL errors of failed queries
$errors = array();
foreach ($pathway_comment as $comment) {
$query = sprintf(
"INSERT INTO pathway (
pathway_pk,
case_fk,
level,
pathway_action_fk,
pathway_allowed,
comment
) VALUES (
'',
'$case_pk',
'1',
'$action',
'%s',
'$comment')", $pathway_allowed === $i ? 'y' : 'n');
$result = mysql_query($query, $connection);
// If the iteration was successful, save the insert id onto an array
// but only if $pathway_allowed == 'y'
if ($result) {
if ($pathway_allowed == 'y') {
$inserted_ids[] = mysql_insert_id();
}
}
// Otherwise, keep an array of comments that failed
// Maybe useful later if you want to print those that failed
else {
$failed_comments[] = $comment;
// And keep the mysql_error() as well.
$errors[] = mysql_error();
}
}
// Loop is done, now you can implode() the inserted ids array:
// These are all ints from an auto_increment PK, so no additional escaping needed
// Execute the query than do whatever you need with the newly inserted rows.
$query = "SELECT * FROM pathway WHERE pathway_pk IN (" . implode(",", $inserted_ids) . ")";
你以前可能听说过这个,但从长远来看,考虑切换到支持准备语句的API,如MySQLi或PDO。这里已经完成了所有必要的转义和整数强制转换,但是如果在循环中编译和执行预处理语句,除了不需要转义之外,还可以更快。
mysql_connect扩展在PHP 5.5.0中已弃用,并将在PHP 5.5.0中删除未来。相反,应该使用MySQLi或PDO_MySQL扩展名。请参见MySQL:选择API指南和相关FAQ了解更多信息信息。此函数的替代方法包括:PDO和mysqli_connect()
如果你正在使用PDO,你可以使用这个。
$dbh = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
在for循环中你可以这样做:
$stmt = $dbh->prepare("INSERT INTO test (name, email) VALUES(?,?)");
$tmt->execute( array('user', 'user@example.com'));
$inserted_ids[] = $dbh->lastInsertId();
循环后,你将得到一个包含4个插入id的数组
编辑:你不需要重新选择你已经输入的值,你可以使用这个IN FOR LOOP
{
$stmt = $dbh->prepare($query = sprintf("INSERT INTO pathway (pathway_pk,case_fk,level,pathway_action_fk,pathway_allowed, comment)
VALUES (
'',
'$case_pk',
'1',
'$action',
'%s',
'$comment')", $pathway_allowed === $i ? 'y' : 'n'););
// change the values to bindParam
$stmt->execute( array('user', 'user@example.com'));
$data[$i]['pathway_pk'] = $dbh->lastInsertId();
$data[$i]['case_fk'] = $case_pk;
$data[$i]['level'] = 1;
$data[$i]['pathway_action_fk'] = $action;
$data[$i]['pathway_allowed'] = %s;
$data[$i]['comment'] = $comment;
}
然后foreach($data as $d){
echo $d['pathway_pk'];
echo $d['case_fk'];
}