running multiple queries through a single php mysqli_query function [duplicate]

Question

I have a script that generates a SQL insert or update script depending on several factors. Below is the string value of the script it's generating:

INSERT INTO AAB_EVENT_SHIFTS ( EVENT_ID ,SHIFT ,START_TIME ,END_TIME 
    ,CREATE_USER ,CREATE_DATE ,MODIFY_USER ,MODIFY_DATE ) VALUES ( 6 ,1 
    ,STR_TO_DATE('04/01/2016 10:00 am', '%m/%d/%Y %I:%i %p' ) 
    ,STR_TO_DATE('04/01/2016 11:00 am', '%m/%d/%Y %I:%i %p' ) ,14 ,now() ,14 ,now() 
    ); INSERT INTO AAB_EVENT_SHIFTS ( EVENT_ID ,SHIFT ,START_TIME ,END_TIME ,CREATE_USER ,CREATE_DATE ,MODIFY_USER ,MODIFY_DATE ) VALUES ( 6 ,2 ,STR_TO_DATE('04/01/2016 11:00 am', '%m/%d/%Y %I:%i %p' ) ,STR_TO_DATE('04/01/2016 12:00 pm', '%m/%d/%Y %I:%i %p' ) ,14 ,now() ,14 ,now() ); 
INSERT INTO AAB_EVENT_SHIFTS ( EVENT_ID ,SHIFT ,START_TIME ,END_TIME ,CREATE_USER ,CREATE_DATE ,MODIFY_USER ,MODIFY_DATE ) VALUES ( 6 ,3 
,STR_TO_DATE('04/01/2016 12:00 pm', '%m/%d/%Y %I:%i %p' ) 
,STR_TO_DATE('04/01/2016 1:00 pm', '%m/%d/%Y %I:%i %p' ) ,14 ,now() ,14 ,now() );

If I run that in a sql editor connected to the database it runs perfectly fine and inserts all rows expected. However when calling that query thusly:

$result = mysqli_query($link,$query);

echo mysqli_error($link);

returns this:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO AAB_EVENT_SHIFTS ( EVENT_ID ,SHIFT ,' at line 24

I changed the way the query generates from creating a concatenated string of multiple queries to creating an array of queries and running them one at a time - this seems to have corrected the issue.

        for($i=1;$i<=$event->total_shifts($event_id);$i++) {
//        echo $event->shift_exists($event_id,$i).'-'.$i.'::<P>';
        if($event->shift_exists($event_id,$i)) {
            $query[$i] =
            'UPDATE AAB_EVENT_SHIFTS '
                    . 'SET START_TIME   = STR_TO_DATE(\''.$event->event_date($event_id).' '.$_POST['start_shift-'.$i].'\', '.'\'%m/%d/%Y %I:%i %p\' )'
                    . ', END_TIME       = STR_TO_DATE(\''.$event->event_date($event_id).' '.$_POST['end_shift-'.$i].'\', '.'\'%m/%d/%Y %I:%i %p\' )'
                    . ',MODIFY_USER     = '. get_session_user_id(session_id())
                    . ',MODIFY_DATE     = now()'
                    . ' WHERE EVENT_ID = '.$event_id.' AND SHIFT = '.$i.'; ';

        } else { $query[$i] =
                '
        INSERT INTO AAB_EVENT_SHIFTS
        (
        EVENT_ID
        ,SHIFT
        ,START_TIME
        ,END_TIME
        ,CREATE_USER
        ,CREATE_DATE
        ,MODIFY_USER
        ,MODIFY_DATE
        )
        VALUES
        (
        '.$event_id.'
        ,'.$i.'
        ,STR_TO_DATE(\''.$event->event_date($event_id).' '.$_POST['start_shift-'.$i].'\', '.'\'%m/%d/%Y %I:%i %p\' )
        ,STR_TO_DATE(\''.$event->event_date($event_id).' '.$_POST['end_shift-'.$i].'\', '.'\'%m/%d/%Y %I:%i %p\' )            
        ,'. get_session_user_id(session_id()) .'
        ,now()
        ,'. get_session_user_id(session_id()) .'
        ,now()
        ); 
        ';

        }
        }

        $i = 1;
        while($query[$i]) {
        echo '<P>'.$query[$i];
            $result = mysqli_query($link, $query[$i]);
            $i++;
        echo '<P>'.mysqli_error($link);
        }

Show how you create your $query variable!!

Saty
– Saty

2016-04-06 11:01:18 +00:00
Commented Apr 6, 2016 at 11:01 — Saty
– Saty, Commented Apr 6, 2016 at 11:01
Add your $query or print the query & run the same in mysql.

Dipanwita Kundu
– Dipanwita Kundu

2016-04-06 11:04:54 +00:00
Commented Apr 6, 2016 at 11:04 — Dipanwita Kundu
– Dipanwita Kundu, Commented Apr 6, 2016 at 11:04

Your Common Sense · Accepted Answer · 2016-04-06 11:08:08Z

1

You cannot run multiple queries through a single php mysqli_query function.
Just make your script generate an array of queries and then run them one by one in a loop.

answered Apr 6, 2016 at 11:08

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

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

3 Comments

Jeff Over a year ago

This is exactly what I did and it's working now. Thanks mate.

Jester Over a year ago

you can with mysqli_multi_query though?

Your Common Sense Over a year ago

@Jester using multi_query is not that easy

Jester · Accepted Answer · 2016-04-06 11:12:12Z

0

you can either loop through the queries or you have to use the mysqli_multi_query function if you want to execute multiple with one function: mysqli_multi_query

answered Apr 6, 2016 at 11:12

Jester

1,4081 gold badge9 silver badges21 bronze badges

4 Comments

Your Common Sense Over a year ago

As almost no one understands what mysqli_multi_query is and how to use it properly, it is strongly recommended not to use it for a silly case like this. An outcome may be surprising.

Jester Over a year ago

@YourCommonSense I guess so, must say haven't used it myself before but I thought it might be worth mentioning. In which case would you say it would be better to use it instead of executing them one by one, or just never?

Your Common Sense Over a year ago

When you need asynchronous query execution I'd say. In any other case it's better to do separate query calls.

Jeff Over a year ago

I could use this elsewhere although it wasn't necessary for this purpose, thanks mate.

Collectives™ on Stack Overflow

running multiple queries through a single php mysqli_query function [duplicate]

2 Answers 2

3 Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Linked

Related