Running multiple MySQL queries with user defined variable through PHP

Question

I'm trying to run a combination of queries from PHP. Something like this:

SELECT @rownum:=0; INSERT INTO tbl1 (`myId`, `rank`) (SELECT myId, @rownum:=@rownum+1 FROM `tbl2` WHERE 1 ORDER BY rank DESC)

Because of the user-defined var (@rownum) I have to run these queries "together". I've tried doing it either with mysqli or PDO, but both seemed to block this.

The mysqli usage I tried was:

    public function multiQuery($query) {
        if (@parent::multi_query($query)) {
            $i = 0; 
            do { 
                $i++; 
            } while (@parent::next_result()); 
        }
        if (!$result) {
            printf("MySQLi error:<br><b>%s</b><br>%s <br>", $this->error, $query);
        }

        return $result;
    }

And in PDO I just expected it to work with the query() func.

How can I overcome this?

Have you checked: [How to execute mysql script with variables using PHP::PDO?](http://stackoverflow.com/questions/4683110/how-to-execute-mysql-script-with-variables-using-phppdo)? (this could be consider a duplicate) — Gerardo Charles Rojas Vega, Dec 04 '14 at 14:14
@CharlesRojas That question is specific to PDO (mine actually discusses mainly MySQLi) and shows a specific query that might lead to a totally different solution (performing the same operation without a user-defined var). — Noam, Dec 04 '14 at 14:57
As your question is: to get both queries running (for that check: http://stackoverflow.com/a/24853726/2737474), so maybe edit your question to explain what really is the goal.. in that way (what I've understood): Re-rank my ids from one table to another one — Gerardo Charles Rojas Vega, Dec 04 '14 at 15:14
And for that case (as far as I can go) what you have is the best possible solution — Gerardo Charles Rojas Vega, Dec 04 '14 at 15:21

score 0 · Answer 1 · answered Dec 04 '14 at 20:05

This might work better:

(set tbl1.rank to autoincrement)

TRUNCATE tbl1;

INSERT INTO tbl1 (myId) SELECT myId FROM tbl2 ORDER BY rank DESC;

It's two separate commands but you won't have to worry about the variable issue. It doesn't answer the question "how to run two different commands in one string" or "how to use variables in a query with PHP" but if you're just trying to re-rank things, that should work.

Truncate will empty tbl1 and reset the autoincrement value back to 1

Running multiple MySQL queries with user defined variable through PHP

1 Answers1