zach zach - 1 year ago 82
SQL Question

$_POST Loop as Update mysql statment

I am attempting to create a form that, when submitted, will update the values that were inserted into the form and leave the unanswered values unchanged.

My idea was to create a loop that would set all the $_POST keys and values into an update statement. The names of each key correspond with each column in my table and so it should work out.

This is what I came up with:

$query = "UPDATE accounts SET ";

foreach($_POST as $field => $value) {
if ($field != null && $value != 'Update Information!'){
$query .= "{$field} = {$value}";
$query .= ", ";
$query .= "WHERE id = {$current_user["id"]}";

The issue I am running into is the last line of the loop. The loop inserts a comma at the end of each loop which is fine until the last value where it messes up the UPDATE statement.

Is there anyway to exclude the comma on the last loop? Thanks!

Answer Source

Just delete the , it after the loop using $query = substr($query, 0, -2).

However your approach is dangerous in the first place. NEVER EVER use user input direclty in an SQL query. Escape it properly using mysql_real_escape_string() or use prepared statements (recommended). Imagine

$_POST['something'] = "foo; DROP DATABASE; UPDATE accounts SET id = 5";

Also note that the mysql_* interface is outdated - you should switch to mysqli_* or PDO.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download