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?? (this could be consider a duplicate) — Gerardo Charles Rojas Vega
– Gerardo Charles Rojas Vega, Commented Dec 4, 2014 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
– Noam, Commented Dec 4, 2014 at 14:57
As your question is: to get both queries running (for that check: 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
– Gerardo Charles Rojas Vega, Commented Dec 4, 2014 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
– Gerardo Charles Rojas Vega, Commented Dec 4, 2014 at 15:21

Trevor Gryffyn · Accepted Answer · 2014-12-04 20:05:28Z

0

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

answered Dec 4, 2014 at 20:05

Trevor Gryffyn

494 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Running multiple MySQL queries with user defined variable through PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related