PHP Update Set If else

Question

If sinkQty field in the given record is not equal to the existing value, then want current date NOW() otherwise keep existing date. The update fails. Any words of wisdom would sure help on this project!

$tbl_name="sink";

$count=mysql_num_rows($result);

$sinkid    = $_POST['sinkID'];
$qty       = $_POST['sinkQty'];
$qtyonorder= $_POST['sinkQtyOnOrder'];
$desiredqty= $_POST['sinkDesiredQty']; 
$posted    = $_POST['sinkPosted'];

for($i=0;$i<$count;$i++)
{
    $sql1=("UPDATE $tbl_name 
            SET sinkPosted = IF ( sinkQty != $qty[$i] ,NOW(), $posted[$i]),
                sinkQty=$qty[$i],
                sinkDesiredQty=$desiredqty[$i],
                sinkQtyOnOrder=$qtyonorder[$i]                         
            WHERE sinkID=$sinkid[$i]");

    $result1=mysql_query($sql1);
}

Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — John Conde, Dec 04 '17 at 02:14
FYI, [you shouldn't use `mysql_*` functions in new code](http://stackoverflow.com/questions/12859942/). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://php.net/manual/en/function.mysql-connect.php)? Learn about [*prepared statements*](https://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which one is best for you. — John Conde, Dec 04 '17 at 02:14
You don't know what's wrong because you don't check for errors in your code. Never assume the code is always going to work flawlessly. Use [`mysql_error()`](http://php.net/manual/en/mysql.error.php) to get a detailed error message from the database. — John Conde, Dec 04 '17 at 02:16
@TerrySchroeder might you set up a small sqlfiddle for us? This will speed up our response time and afford us greater confidence in our support. (I don't see any single quotes around the values in your query are they all integers?...I'm getting not. ...listen to John, use prepared statements.) — mickmackusa, Dec 04 '17 at 02:59

score 0 · Answer 1 · answered Dec 04 '17 at 11:38

 $sql1=("UPDATE $tbl_name 
        SET sinkPosted = IF ( sinkQty != $qty[$i] ,NOW(), $posted[$i]),
            sinkQty=$qty[$i],
            sinkDesiredQty=$desiredqty[$i],
            sinkQtyOnOrder=$qtyonorder[$i]                         
        WHERE sinkID=$sinkid[$i]");
// Try
$query = "UPDATE $tbl_name 
        SET sinkPosted = IF ( sinkQty != $qty[$i] ,NOW(), $posted[$i]),
            sinkQty=$qty[$i],
            sinkDesiredQty=$desiredqty[$i],
            sinkQtyOnOrder=$qtyonorder[$i]                         
        WHERE sinkID=$sinkid[$i]";
echo "<pre>$query</pre>";
$result1 = mysql_query($query) or die(mysql_error());
// And please, remove SQL Injection from $query.

PHP Update Set If else

1 Answers1