mysqli_multi_query(); numerous submissions

Question

I have a PHP script that prepares 3 different SQL submissions. The SQL is valid for all of them. If I swap the order of the IF statements below, they all work. If the $ratesSQL is first then both 1) and 2) work. If either of the other two are first, then only 1) works.

The SQL:

$sqlRates = "
LOAD XML LOCAL INFILE '/xx/yy/zz/example.xml'
REPLACE
INTO TABLE rates
ROWS IDENTIFIED BY '<row>'";

Both $vixSQL and $aaaSQL are a lot of REPLACE INTO statements. Example:

REPLACE INTO aaa (date,value) VALUES ('2016-01-29','4.05'); REPLACE INTO aaa (date,value) VALUES ('2016-01-28','4.07');

The IF statements:

1) Successful submission with the below. $ratesSQL is approximately 300 rows of data.

if (mysqli_multi_query($dbc, $ratesSQL)) {
    echo "<h3>Rates Load Successful</h3><br>";
} else {
    echo "<h3>Rates Load Error</h3><br>";
}

2) Successful submission. ~8000 rows of data.

if (mysqli_multi_query($dbc, $vixSQL)) {
    echo "<h3>VIX Load Successful</h3><br>";
} else {
    echo "<h3>VIX Load Error</h3><br>";
}

3) Failed Submission, ~8000 rows of data

if (mysqli_multi_query($dbc, $aaaSQL)) {
    echo "<h3>AAA Load Successful</h3><br>";
} else {
    echo "<h3>AAA Load Error</h3><br>";
}

The $ratesSQL submission is only about 300 rows of data whilst the other two are about 8000. Is it this data quantity that is causing me issues? Any suggestions for getting round it?

COMBINING QUERIES PER ANSWER BELOW ALLOWS ME TO GET AROUND THIS. STILL PUZZLING AS TO WHY SEPARATE QUERIES FAILED. ANY FURTHER INFO WOULD BE APPRECIATED

If the SQL contains `CREATE TABLE` statements with foreign keys, then the order might be important. Referenced tables must exist. What kind of SQL statements are done? Is there any SQL executed before or after, such as `DROP TABLE`? — Kenney, Feb 03 '16 at 01:17
Please add the query string for `$ratesSQL`, `$vixSQL` and `$aaaSQL` — jameshwart lopez, Feb 03 '16 at 01:17

jameshwart lopez · Accepted Answer · 2016-02-03T01:59:42.717

0

Combine all your query and use mysqli_multi_query once

Note:multiple queries should be concatenated by a semicolon

$finalSqlQuery = $sqlRates .';'.$vixSQL.$aaaSQL;

if(mysqli_multi_query($dbc,$finalSqlQuery)){
   //Then you can use mysqli_store_result()
   do{
      if($result = mysqli_store_result($dbc)){
         //Note that mysqli_store_result will return a buffer object and false on query error.

          print_r($result);/*display and check what is the results of the query if are you sure that the query doesn't have an error just to check the result of the query*/
          mysqli_free_result($result);
      }

        /* print divider */
        if (mysqli_more_results($dbc)) {
            printf("-----------------\n");
        }


   }while(mysqli_next_result($dbc));
   $result = mysqli_store_result($dbc)
}

edited Feb 03 '16 at 01:59

answered Feb 03 '16 at 01:53

jameshwart lopez

2,993
6
35
65

Combining / concatenating works. I can move past this issue, thanks. Still puzzling as to why the separate queries were not working – DVCITIS Feb 03 '16 at 02:35
its logical that php will expect one whole query string and one multi_query in one php file ,anyway you can try to use mysqli_errno to see what's your error in using multi_query multiple times in one page. – jameshwart lopez Feb 03 '16 at 03:25

mysqli_multi_query(); numerous submissions

1 Answers1