通过单个php mysqli_query函数运行多个查询

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:

我有一个脚本,根据几个因素生成SQL插入或更新脚本。下面是它生成的脚本的字符串值:

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:

如果我在连接到数据库的sql编辑器中运行它,它运行得很好,并插入所有预期的行。但是,当这样调用该查询时:

$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

您的SQL语法有错误;检查与您的MariaDB服务器版本对应的手册,以便在'INSERT INTO AAB_EVENT_SHIFTS(EVENT_ID,SHIFT,'第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);
        }

2 个解决方案

#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.

您无法通过单个php mysqli_query函数运行多个查询。只需让您的脚本生成一个查询数组,然后在循环中逐个运行它们。

#2

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

您可以循环查询,或者如果要使用一个函数执行多个,则必须使用mysqli_multi_query函数:mysqli_multi_query

#1