Unable to update multiple records into mysql

Question

Unable to update the multiple records into mysql by using foreach in php with the following error;

foreach( $_POST['edit'] AS $edit ) {
    $fromdate=mysql_real_escape_string($_POST['fromdate'][$edit]);
    $todate=mysql_real_escape_string($_POST['todate'][$edit]);
    $total=mysql_real_escape_string($_POST['total'][$edit]);
    $ampm=mysql_real_escape_string($_POST['ampm'][$edit]);
    $Update_leavedetails .= "UPDATE leaverequestdetails SET fromdate='$fromdate', " . "todate='$todate',total='$total',ampm='$ampm' " . "WHERE tranid = '$edit';";
}

if(!mysql_query($Update_leavedetails)){
    die('Invaild query: ' . mysql_error());
} else {
    .....

Invaild query: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE leaverequestdetails SET fromdate='2019-08-26', todate='2019-08-26',total=' at line 1

Nick · Accepted Answer · 2019-08-14T02:00:11.973

You are trying to run multiple queries at once, which mysql_query doesn't support. You need to bring the execution of the query into the loop i.e.

foreach( $_POST['edit'] AS $edit ) {
    $fromdate=mysql_real_escape_string($_POST['fromdate'][$edit]);
    $todate=mysql_real_escape_string($_POST['todate'][$edit]);
    $total=mysql_real_escape_string($_POST['total'][$edit]);
    $ampm=mysql_real_escape_string($_POST['ampm'][$edit]);
    $Update_leavedetails = "UPDATE leaverequestdetails SET fromdate='$fromdate', ".
    "todate='$todate',total='$total',ampm='$ampm' ".
    "WHERE tranid = '$edit';";
    if(!mysql_query($Update_leavedetails)){
        die('Invalid query: ' . mysql_error());
    }
}

You really need to stop using the mysql_* interface, it has been deprecated since PHP5.5 and removed as of PHP7. Upgrade to mysqli (this question can help) or better yet PDO (this question can help). While you are in the process, change to prepared statements (MySQLi or PDO), they will protect from injection far better than mysql_real_escape_string.

Using MySQLi prepared statements, you would write

$stmt = $link->prepare("UPDATE leaverequestdetails SET fromdate=?, todate=?,total=?,ampm=? WHERE tranid = ?");
foreach( $_POST['edit'] AS $edit ) {
    $stmt->bind_param("ssssi", $_POST['fromdate'][$edit], $_POST['todate'][$edit], $_POST['total'][$edit], $_POST['ampm'][$edit], $edit);
    if (!$stmt->execute()) die($stmt->error);
}

Please keep in mind that your application should not die with the error message. This could be a huge security issue. — Dharman, Aug 14 '19 at 05:51
@Dharman that's a fair comment, and I'm assuming (possibly without justification) that OP will deal with that before going into production. But OPs original code had it in there and I was trying to make as few changes as possible. — Nick, Aug 14 '19 at 06:00

Unable to update multiple records into mysql

1 Answers1