Using Prepared Statements for Update Queries
When executing update queries with PHP and MySQL, it is recommended to use prepared statements to ensure data integrity and prevent SQL injection. In a recent discussion on MySQL's UPDATE statement, a user sought guidance on how to utilize prepared statements.
Solution
In MySQL, prepared statements follow a similar format to INSERT or SELECT statements. To use a prepared statement for an UPDATE query, simply replace all variable values with placeholders represented by the question mark (?):
<code class="php">$sql = "UPDATE Applicant SET phone_number=?, street_name=?, city=?, county=?, zip_code=?, day_date=?, month_date=?, year_date=? WHERE account_id=?";</code>
Once the SQL statement is prepared, create a prepared statement object:
<code class="php">$stmt = $db_usag->prepare($sql);</code>
Next, bind parameters to the prepared statement. The following example assumes that the date and account_id parameters are integers (d), while the remaining parameters are strings (s):
<code class="php">$stmt->bind_param('sssssdddd', $phone_number, $street_name, $city, $county, $zip_code, $day_date, $month_date, $year_date, $account_id);</code>
Execute the prepared statement:
<code class="php">$stmt->execute();</code>
Handle any errors if they occur:
<code class="php">if ($stmt->error) { echo "FAILURE!!! " . $stmt->error; } else echo "Updated {$stmt->affected_rows} rows";</code>
Finally, close the prepared statement:
<code class="php">$stmt->close();</code>
The above is the detailed content of How to Update Data Securely in MySQL using Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!