mysqli->multi_query problem

[qazplm114477]

I'm trying to pass several create statements through mysqli->multi_query, I tested the multi_query statements with a couple of queries and it works and when I called it with my actual create statements it just wouldn't go through... there weren't any errors and it returned true. I tested the query itself in a script form using the query browser and it went through perfectly.

here's the mysqli statement I'm using

public static function multi_query($sql){
        //connects to the database
        if(!self::$instance)
                {   $db = self::start_connection(); }
        //instantiate the mysqli class
        $mysqli = self::$instance;
        $result = null;
        
        if ($mysqli->multi_query($sql)) {
            do {
                /* store first result set */
                if ($result = $mysqli->store_result()) {
                    
                    $result->free();
                }
                /* print divider */
                if ($mysqli->more_results()) {
                    //I just kept this since it seems useful
                    //try removing and see for yourself
                }
            } while ($mysqli->next_result());
            
         }else{
            return false;
         }
         return true;

    }

Is there a limit to how many queries I can run using the multi_query function/method? I also can't run this in a .sql file since I'm generating the database based on the user's input. I guess I could fwrite into a .sql file and run that script with a simple mysqli->query but I would rather understand why I can't use mysqli->multi_query for this.

thanks to anyone who can help

[qazplm114477]

another quick note. my query contains several create statements and a couple of alter statements that adds a foreign key. When I took out the alter statements from my query, the query went through perfectly.

Seems that "Alter table" is where the problem lies, I echo'd the alter query and used it in mysql, there isn't a problem with the syntax of the query itself.

[qazplm114477]

After some testing, I still couldn't figure out my this was happening, but I did find out something weird as well. When I used an Alter statement in mysqli_multi_query() it did not work, but when I used the Alter statement in mysqli_real_query / mysqli_query it worked fine.

fyi, I did test my connection and query/multi query, and they were both working fine.

[Stefano Mtangoo]

After some testing, I still couldn't figure out my this was happening, but I did find out something weird as well. When I used an Alter statement in mysqli_multi_query() it did not work, but when I used the Alter statement in mysqli_real_query / mysqli_query it worked fine.

fyi, I did test my connection and query/multi query, and they were both working fine.

IMHO, alter statement is taken with many (I included) as bad design, so it much of the time, not thought to be of frequent use. So I guess that is why it isn't supported: Afterall we run the query when we mess up with design don't we? :)
Just run it as separate Query. I will check to see if my guess is so.

[qazplm114477]

@evstevemd Thanks for clearing that up, I thought it was just me, but at least now I know that it isn't supported. Yeah mysqli_query or real_query works perfectly! thanks for that