Looping through columns and conditionally updating their values with MySQL and PHP

Question

I'm working with PHP and MySQL.

Before we start:
I'm aware of SQL-Injection: Please do not suggest alternatives, I already know.

I'm not too hot with SQL queries, I am currently having a PHP loop through an array of field values obtained from a POST form. These values are then checked against values already in the database, if they are different (and not NULL), the columns will be updated. All of this is done in PHP. I was wondering if there was a way to 'loop' with pure SQL query? Here is the general idea of my code:

foreach($myInfo as $key=>$value){
    if($value["mand"]){
        $frmErr = $_POST[$value["post"]] == "" ? 1 : $frmErr ;
        $frmErrStr .= $_POST[$value["post"]] != "" ? "" : "- Your ".$value["response"]."<br />" ;
    }
}
if(!$frmErr){
    $updUser = mysql_query("
        UPDATE table_name SET
        homeTelephone='".$_POST[$myInfo["homeTelephone"]["post"]]."'
        WHERE samaccountname='".$samaccountname.
        "'");
    header("Location: confUpdate.php?s=yes");
}

So to summarize, what I'm looking for here is a pure-MySQL query that will loop through elements within an array, check the posted values ($_POST[$arrayVal]) against the corresponding database columns and then, if they are different, update the column with the POSTed value.

I don't think you can loop using a array in pure MySQL, the most similar solution would be a `NOT IN ('value1', 'value2', 'value3')` — Naryl, Dec 11 '12 at 12:06
You don't "loop" with SQL. But the query you're talking about sounds like an `INSERT ... ON DUPLICATE KEY UPDATE` http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html — dnagirl, Dec 11 '12 at 12:16

score 1 · Accepted Answer · answered Dec 11 '12 at 12:29

if samaccountname is a unique key, you can insert a new record or update an existing one like this:

INSERT INTO mytable(samaccountname, hometelephone)
VALUES ($samaccountname1, $hometel1),  ($samaccountname2, $hometel2), ...
ON DUPLICATE KEY UPDATE hometelephone = VALUES(hometelephone)

score 0 · Answer 2 · answered Dec 11 '12 at 13:12

You cannot loop through a PHP array using MySQL. you can create a very sophisticated query, via looping thourgh arrays in PHP, and then execute that query, or you can get the whole db table to an array, then loop through that array, make necessary changes to it and then loop it again and update the changed parts of the table.

And also take a look at SQL Stored Procedures. ;)

Looping through columns and conditionally updating their values with MySQL and PHP

2 Answers2