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?

If I understand your question, you want trip_id = $tripID to be before the where, part of the set clause. Or maybe you mean to use == rather than =? — Justin Smith, Jan 18 '10 at 07:08

score 3 · Answer 1 · answered Jan 18 '10 at 07:09

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.

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

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.

edited May 23 '17 at 12:03

Community

1
1

answered Jan 18 '10 at 07:14

Alix Axel

151,645
95
393
500

How to update sql like this？

2 Answers2