Issue with execution of MySQL query from PHP

Question

I'm trying to execute this query from PHP but it's not working:

$myQuery = 'SET @t1= 5;

                    INSERT INTO mvt (id_piece, code_mvt, origine_mvt, type_mvt, article_id_article, code_art, des_art, qte_old, qte_mvt, qte_new, link_piece)

                    SELECT

                    p.id_bsr,

                    CONCAT_WS(\'\',\'MVT/15/12/\',LPAD(@t1 := @t1+1, 3, \'0\')) AS code_mvt,

                    p.code_bsr,

             \'Bon Annulation\',

             l.article_id_article,

               l.code_art,

               l.des_art,

               qte_art,

               qte,

               (qte_art + (qte*1)),

               \'index.php?p=DetailsBonSortie&idBsr=167\'

                    FROM

                    bon_sortie p, ligne_sortie l, article a

                           WHERE p.id_bsr = l.bon_sortie_id_sortie

                   AND a.id_article = l.article_id_article

                   AND id_bsr = 167;
                           ';

As you can see it's an INSERT query from a SELECT query and a variable @t1.
When I execute this query directly in MySQL DB it works fine, but when I try to execute it from php like this:

$conn = new mysqli('host', 'user', 'password', 'database');
$conn->query($myQuery);

it doesn't work!
What am I missing ?

"it doesn't work!" - Not much to go on here. Check for errors php.net/manual/en/mysqli.error.php --- php.net/manual/en/function.error-reporting.php — Funk Forty Niner
– Funk Forty Niner, Commented Dec 7, 2015 at 13:57
Hard to tell without the actual error - try echo $myQuery before running, might be an obvious syntax issue. — dwjv
– dwjv, Commented Dec 7, 2015 at 14:01

Ronny Coolen · Accepted Answer · 2015-12-07 14:10:54Z

4

You can't execute 2 queries in the same statement with the php-mysql extension

Try this:

$myQuery1 = 'SET @t1=5';
$myQuery2 = '
    INSERT INTO mvt (id_piece, code_mvt, origine_mvt, type_mvt, article_id_article, code_art, des_art, qte_old, qte_mvt, qte_new, link_piece)
    SELECT
        p.id_bsr,
        CONCAT_WS(\'\',\'MVT/15/12/\',LPAD(@t1 := @t1+1, 3, \'0\')) AS code_mvt,
        p.code_bsr,
        \'Bon Annulation\',
        l.article_id_article,
        l.code_art,
        l.des_art,
        qte_art,
        qte,
        (qte_art + (qte*1)),
        \'index.php?p=DetailsBonSortie&idBsr=167\'
    FROM
        bon_sortie p, ligne_sortie l, article a
    WHERE p.id_bsr = l.bon_sortie_id_sortie
        AND a.id_article = l.article_id_article
        AND id_bsr = 167;
';

$conn = new mysqli('host', 'user', 'password', 'database');
$conn->query($myQuery1);
$conn->query($myQuery2);

answered Dec 7, 2015 at 14:10

Ronny Coolen

1705 bronze badges

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

3 Comments

I wrestled a bear once. Over a year ago

+1, I usually do something like $queries = explode(";", $sql); foreach($queries as $q); $conn->query($q);

Ronny Coolen Over a year ago

That's a good solution when you have a lot of queries (and without ';' in them). However, I believe it is safer to write the queries one by one (possibly in an array, which you can loop over with foreach), than to explode them from a big string. You might run into problems. (Delimiters can be changed, there can be ';' characters in strings in your query, ...)

SlimenTN Over a year ago

@RonnyCoolen thnx for the explanation, this make sence :)

Collectives™ on Stack Overflow

Issue with execution of MySQL query from PHP

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related