how to execute multiple update queries in PHP using MySQL?

Question

I have to execute multiple update queries in single query using mysql_query() funciton of PHP. I append multiple update statements to form single query to pass parameter to mysql_query() function. So at the end I am getting one combined query but it is found to be wrong. When I run that query at that time during execution I am getting error as "Invalid query". I do not understand what is wrong happening , so please help me in this quetion . I am suffering from it so badly. I tried my best. I am giving my code for reference.

Here I am getting values of varibles through querystring

my querystirng : localhost/ticker/publish-ticker.php?i=1,2,3&j=3,4

if(isset($_GET['i']))
{
    $selected_tickers = $_GET['i']; 
}

if(isset($_GET['j']))
{
    $selected_twitters = $_GET['j'];    
}

Here I am forming the query

$sql2 = "";
if(count($sel_ticker_array) > 0 )
{
foreach($sel_ticker_array as $value2)
{
    $tid1 = preg_replace('/[^0-9]/','', $value2);                               
    $sql2 .= "update tbl_ticker2 set ticker_flag=0 where id!=$tid1;";

}



foreach($sel_ticker_array as $value)
{
    $tid3 = preg_replace('/[^0-9]/','', $value);                                
    $sql2 .= "update tbl_ticker2 set ticker_flag=1,last_used='$todaysdate' where id=$tid3;";

}

}


if(count($sel_twitter_array) > 0)
{
foreach($sel_twitter_array as $value)
{
    $tid = preg_replace('/[^0-9]/','', $value);                             
    $sql2 .= "update tbl_ticker2 set twitter_flag=0 where id!=$tid;";

}



foreach($sel_twitter_array as $value)
{
    $tid = preg_replace('/[^0-9]/','', $value);                             
    $sql2 .= "update tbl_ticker2 set twitter_flag=1,last_used='$todaysdate' where id=$tid;";

}
}

Here I am executing it but I am getting output as "Invalid query"

$query3 = mysql_query($sql2);

    if(!$query3)
    {
        die("Invalid Delivery");
    }
    else
    {
        echo '<script>';
        echo 'alert("Ticker Updated Successfully");';
        echo 'location.href="add_ticker.php"';
        echo '</script>';         

    }

Please help me.

let me get the "you should be using pdo or mysqli" out of the way — Dave, Jun 16 '14 at 18:00
You can't run multiple queries in a single call using `mysql_query`. If you want to do that, you need to use `mysqli_multi_query()`. — andrewsi, Jun 16 '14 at 18:00
you may want to look at making use of SQL "IN" statements such as `update tbl_ticker2 set twitter_flag=1,last_used='$todaysdate' where id in ($tid1,$tid2,$tid3);` then take a look at having your foreach statements build the list of conditions matching the statement — Dave, Jun 16 '14 at 18:02
okay @andrewsi ....I will use mysqli_multi_query()....but for that...how should I combine multiple statements into single one ? — Yogesh k, Jun 16 '14 at 18:08
@andrewsi I am getting wrong combined query.So please check that also. — Yogesh k, Jun 16 '14 at 18:09
@Yogeshk - you can't just use that function, without changing your code to use `mysqli_*` throughout. Once you've done that, you can pass a list of semi-colon separated SQL statements into the function. — andrewsi, Jun 16 '14 at 18:14
@andrewsi Thats okay...but main problem is also in appending multiple queries into one. Actully problem is when my query starts appending at that time, when $sql2 executes two time then it will produce following result update tbl_ticker2 set ticker_flag=0 where id!=1; update tbl_ticker2 set ticker_flag=0 where id!=1;update tbl_ticker2 set ticker_flag=0 where id!=2; update tbl_ticker2 set ticker_flag=0 where id!=1;update tbl_ticker2 set ticker_flag=0 where id!=2;update tbl_ticker2 set ticker_flag=0 where id!=3; which is invalid — Yogesh k, Jun 16 '14 at 18:20
@andrewsi actully result of $sql2 after executing two times is only like this : update tbl_ticker2 set ticker_flag=0 where id!=1;update tbl_ticker2 set ticker_flag=0 where id!=2;update tbl_ticker2 set ticker_flag=0 where id!=3; — Yogesh k, Jun 16 '14 at 18:22

Alireza Fallah · Answer 1 · 2014-06-16T18:10:58.650

1

You can't do that using the regular mysql-api in PHP.

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

it can be done using mysqli and the mysqli_multi_query() function.

how to execute multiple update queries in PHP using MySQL?

2 Answers2