preserve MySQL comment in PHP script

Question

I have been working on a project where I've been developing the MySQL commands interactively. I now want to run the MySQL commands using a PHP script. I do this by simply pasting in the MySQL commands and making them into a PHP string. Like this...

$queryStg = "
    update table1 set col1 = 1;

    drop table table2; 
    ";

$sqlQuery = mysqli_multi_query($mysqliLink, $queryStg);

However I've always had to strip out the MySQL comments to get it to work. I'd prefer to retain these comments. Is there a way to do this?. I've tried adding '\n' at the end of each comment but I cant get this to work. E.g. if I run this it will come back with errors...

$queryStg = "
    -- a mySQL comment
    update table1 set col1 = 1;

    --another comment
    drop table table2; 
    ";

$sqlQuery = mysqli_multi_query($mysqliLink, $queryStg);

Full code incase its useful

$mysqliLink = new mysqli ($host, $username, $password, $dbName);

$queryStg = "
    -- a mySQL comment
    update table1 set col1 = 1;

    --another comment
    drop table table2; 
    ";

$sqlQuery = mysqli_multi_query($mysqliLink, $queryStg);

do {
        if ( mysqli_error($mysqliLink) ) {
        die("ERROR: " .
            htmlspecialchars(mysqli_error($mysqliLink), ENT_QUOTES) .
            "<br>\n");
    }

    echo mysqli_affected_rows($mysqliLink);
    mysqli_use_result($mysqliLink);
    $moreResults = mysqli_more_results($mysqliLink);
    @mysqli_next_result($mysqliLink);
} while($moreResults);

Thanks

Isn't the error that you have multiple queries in one? That shouldn't work with mysqli_query in the first place. Try mysqli_multi_query() (I don't know whether that one accepts comments though) — Pekka
– Pekka, Commented May 14, 2011 at 11:04

AndersTornkvist · Accepted Answer · 2011-05-14 13:25:15Z

7

Comments will be skipped EDIT if properly escaped ("-- " needs to be followed by space character, tab, newline etc.). Try this instead:

$queryStg = "
-- a mySQL comment
update table1 set col1 = 1;

-- another comment
drop table table2; 
";

$sqlQuery = $mysqliLink->multi_query($queryStg);

edited May 14, 2011 at 13:25

answered May 14, 2011 at 11:20

AndersTornkvist

2,62921 silver badges38 bronze badges

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

7 Comments

Evert Over a year ago

Raffael: That's not fair, because in the OP's question there was already mention of $mysqliLink, thus it is implied that the OP already knows how to create it.

AndersTornkvist Over a year ago

Thank you Evert. Just like you said, I found it irrelevant to create a new object with fictitious parameters, since $mysqliLink was used in the OP's question without being created there.

spiderplant0 Over a year ago

Thanks @richard86 This was a typo (when I was simplifying things out of my wrapper functions). Code above corrected. Still doesnt work (I presume the change you meant was for the 'multi' bit and not the alternative object based notation.)

AndersTornkvist Over a year ago

@spiderplant0 Comments will be skipped only if properly escaped. "-- " needs to be followed by space character, tab, newline etc. I didn't first see that the second comment "--another comment" had no space character after "--". Please tell me if the provided code works when changed to "-- another comment".

Bojangles Over a year ago

+1 to counterract @Raffael, and because it's a nice, simple fix :-)

|

Johan · Accepted Answer · 2011-05-14 13:08:20Z

2

"select * from
 -- comment
 atable"

Gets translated into "select * from -- comment atable" MySQL also allows this type of comments:

/*comment that ends*/

"select * from
 /*comment*/
 atable"

Gets translated into "select * from /*comment*/ atable"

And that does work :-).

See: http://dev.mysql.com/doc/refman/5.1/en/comments.html

answered May 14, 2011 at 13:08

Johan

77.4k28 gold badges204 silver badges346 bronze badges

4 Comments

spiderplant0 Over a year ago

That worked. Thanks very much. I'll use this comment style in future as its just like PHP comments.

AndersTornkvist Over a year ago

For everyone's info: As you could see in the linked documentation, "-- comment \n" style is allowed. The example query will NOT as stated in Johan's answer be translated into "select * from -- comment atable". mysqli_multi_query() will just ignore the comment.

Johan Over a year ago

@Richard86, yep, but /*bla*/ always works without issues, and -- comment /n can easily be mistyped and the /n can be eaten, so I wouldn't recommend it.

AndersTornkvist Over a year ago

Personally, I agree that /*bla*/ looks better. My comment aimed at give future readers correct info on how comments work with mysqli_multi_query().

Collectives™ on Stack Overflow

preserve MySQL comment in PHP script

2 Answers 2

7 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related