How to update sql like this？

Question

Hey,i've got a problem, i want to execute this sql update, but it doesn't work. Could anyone help me? Thanks!

The code:

$temp = $_GET['ul'];

foreach ($temp as $key=> $value)
{
    $str=array();
    $arr=explode(',',$value); 
    $str =array($key=>$arr);

    for($i=0;$i<count($str[$key]);$i++)
    {
        $tripID='2';                    
        $sql = "UPDATE places_trips 
                SET orderNo='$i', ColumnNo='$key' 
                WHERE place_id=" . $str[$key][$i] . "
                AND trip_id=" . $tripID;
        mysql_query($sql);
        }
    } 
}

I want to set the $tripID = 2, but actually, $tripID=2222. So how to make the $tripID=2 all the time?

Answer 1

Your query doesn't change trip_id. It tries to change orderNo and ColumnNo for rows where trip_id is 2. If I understand you correctly, you should put it in the first part of your query:

"UPDATE places_trips SET orderNo = '$i', ColumnNo = '$key', trip_id = $tripID WHERE place_id = ".$str[$key][$i];

That being said, read about SQL injections. You need it because your current code is terribly dangerous.

Answer 2

I hope this is what you're after:

UPDATE places_trips SET
    orderNo = $i,
    ColumnNo = $key
WHERE place_id = $str[$key][$i]
AND trip_id RLIKE '^2+$';

Should update all rows where trip_id contains only 2s.

Also search on StackOverflow for SQL Injections, your code is vulnerable to them.

Answer 3

There is a mistake in your original query:

WHERE place_id=" . $str[$key][$i] . "and 

The and follows immediately on the place_id which leads to an error. It should be " AND instead.