I need to run the below in a function, it updates my database if certain conditions are met. I can't get it to work and I can only assume its because of the "SET @newnum" at the beginning.

I've echo'd my query and tested it via PHPMyAdmin and it works fine there.

Does anyone know how to correctly execute this?

global $wpdb;
$sql = "SET @newnum = 0;
    Update wp_esp_ticket tix
    SELECT et.TKT_ID FROM wp_esp_ticket et 
    INNER JOIN wp_esp_datetime_ticket edtt on edtt.TKT_ID = et.TKT_ID
    INNER JOIN wp_esp_datetime edt on edtt.DTT_ID = edt.DTT_ID
    WHERE edt.EVT_ID = ".$courseBookID."
    ORDER BY edt.DTT_EVT_start ASC
    ) b ON tix.TKT_ID = b.TKT_ID
    SET tix.TKT_order = @newnum:=@newnum + 1";

  • wpdb::query() falls back on either mysql_query() or mysqli_query() which are both not meant to be used with more than one query per call. So first of all, you should split the statement to two separate queries. – David Aug 19 '15 at 15:56

As David pointed out, wpdb::query() does not allow multiple calls. So yes, it is because of having the "SET @newnum = 0;" before your actual query. This kind of query is simply not possible as the backend prevents that in order to protect against SQL injection attacks.

Besides of that, the technique I described in my blog serves a different use case which seems unrelated to what you want to achieve here.

May be you should be more exact on what you want you want your "SET" clause to do?

[...] SET tix.TKT_order = @newnum:=@newnum + 1";

What should be the expected outcome on the query?

  • This statement gets a set of records in the database in date order it then updates a column called TKT_order placing a number in increments of 1 in each row returned. so row 1 would have an order of "1", row 2 would have "2", row 3 "3" etc etc. using a @variable is the only efficient way I know of doing it without lopping through all the records in php and running an mysql update for each one... – Zoe Epix Aug 21 '15 at 12:56
  • Can anyone help with this? I can't find anyone reporting similar issues/solutions... – Zoe Epix Aug 24 '15 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.