How to execute mulitple statement SQL queries using $wpdb->query?

Question

I call $wpdb->query() on a query like

INSERT INTO wp_ctt_timetables (name, homework, tod, class, dow, lesson) VALUES ('', '', '', 3, 0, 0);
INSERT INTO wp_ctt_timetables (name, homework, tod, class, dow, lesson) VALUES ('', '', '', 3, 0, 1);
INSERT INTO wp_ctt_timetables (name, homework, tod, class, dow, lesson) VALUES ('', '', '', 3, 0, 2);
INSERT INTO wp_ctt_timetables (name, homework, tod, class, dow, lesson) VALUES ('', '', '', 3, 0, 3);
-- and so on

But it doesn't work and says this to the error log:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO wp_ctt_timetables (name, homework, tod, class, dow, lesson) VALUES (' at line 2

Answer 1

TL;DR

This is not possible with wpdb. Use dbDelta(). Even better, use something else to run raw queries.

Explanation

wpdb->query() uses the mysql_query() function. From PHP manual:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

In order for that to work, wpdb would have to use mysqli_multi_query(). But you are dealing with WordPress here: it still runs with the deprecated mysql extension.

Solution 1

I imagined that WP would have something to aid with migrations, and apparently it does: dbDelta(). This is a 438-line-long monster, so I will save you some time:

It doesn't give you a list of separated queries. It returns a list of updates that are performed by the queries - if you were to run them with the second parameter being true. Inside, it splits the query by semicolons, and then does such magix that it's impossible to tell what happens. If used incorrectly, this function may open a portal into Oblivion. Looks like it actually skips certain queries, like those that create global tables. It will normalize whitespace, quote column names. If the query is creating WP tables that already exist, but something in the schema is different, such as indices or column types, it will try to alter the tables without re-creating them, so that they can match what your query would create. I do not know why it is able to successfully run queries where the semicolon doesn't terminate a query, such as part of the string.

If you thought that you can use IoC and pass the wpdb object to your cool standardized interface implementation, then you're out of luck.

Solution 2

Use another DB adapter or extension. In most cases, if you need to run migrations, such as create tables and insert many rows, it's not a problem if you don't use wpdb (and thus have to create another DB connection), because this is only going to happen very rarely. Also, due to the crazy transformations done by dbDelta(), it's probably more reliable to find a way to run raw queries.

• mysqli_multi_query() is a function of the mysqli (not outdated) extension that can easily run multiple statements at once.
• PDO can run multiple queries in emulation mode.

Answer 2

Declare your table name in a variable.

$tbl_name = $wpdb->prefix.'your_table_name';

Declare your field names in an array.

$fields = array(
    'your_field1',
    'your_field2',
    'your_field3',                      
);

Store form data in some variables.

$data1 = $_POST['your_field1'];
$data2 = $_POST['your_field2'];
$data3 = $_POST['your_field3'];

Store form data in a variable as array

$mydata = array( 
    'your_field1' => $data1,
    'your_field2' => $data2,
    'your_field3' => $data3,        
) ;

And finally call the insert query.

$wpdb->insert( $tbl_name, $mydata )

You can run the codes in foreach loop for multiple data rows.

Answer 3

In your case you are trying to insert multiple rows in your table, so instead of doing 1 insert query for each row, you can insert all your rows using a single statement,

$sql = "INSERT INTO wp_ctt_timetables (name, homework, tod, class, dow, lesson) VALUES ('', '', '', 3, 0, 0), ('', '', '', 3, 0, 1), ('', '', '', 3, 0, 2), ('', '', '', 3, 0, 3), ('', '', '', 3, 0, 4)"; //... and so on
$results = $wpdb->query($sql);

However, to answer your original question, if each of your queries are different, you can also execute the queries one after the other,

$sql = "query 1...";
$results = $wpdb->query($sql);
$sql = "query 2...";
$results = $wpdb->query($sql);
$sql = "query 3...";
$results = $wpdb->query($sql);

this even works if you create a temporary table and use that table in a subsequent query.