wpdb->query and running 2 sql statements with one go

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

wpdb->query and running 2 sql statements with one go

Haluk Karamete
I'd like to be able to run this SQL using one wpdb->query call.

$sql = 'TRUNCATE TABLE `my_new_table` ;ALTER TABLE `my_new_table`
auto_increment = 1';

$query_result = $wpdb->query($sql);


As of 3.9.1, we do have the mysqli support. So, I'm not sure why this would
not fly.

Is that one of my brain-freeze moments again?


but I'm getting the *familiar error: *

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 'ALTER TABLE
`my_new_table` auto_increment = 1)' at line 1]
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: wpdb->query and running 2 sql statements with one go

J.D. Grimes
wpdb uses mysqli_query() - https://core.trac.wordpress.org/browser/tags/3.9.1/src/wp-includes/wp-db.php#L1616

mysqli_multi_query() must be used to run multiple statements - http://php.net/manual/en/mysqli.quickstart.multiple-statement.php

-J.D.
On Jul 15, 2014, at 2:14 PM, Haluk Karamete <[hidden email]> wrote:

> I'd like to be able to run this SQL using one wpdb->query call.
>
> $sql = 'TRUNCATE TABLE `my_new_table` ;ALTER TABLE `my_new_table`
> auto_increment = 1';
>
> $query_result = $wpdb->query($sql);
>
>
> As of 3.9.1, we do have the mysqli support. So, I'm not sure why this would
> not fly.
>
> Is that one of my brain-freeze moments again?
>
>
> but I'm getting the *familiar error: *
>
> 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 'ALTER TABLE
> `my_new_table` auto_increment = 1)' at line 1]
> _______________________________________________
> wp-hackers mailing list
> [hidden email]
> http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: wpdb->query and running 2 sql statements with one go

Haluk Karamete
thank you J.D,
I figured that what I wanted to do was not possible __within the wpdb__
environment, (which is the environment I'd like to stay in whenever I touch
the wordpress database)
I figured so because my guess is that Wordpress doesn't/and/would not
opt-in for the multiquery support you've mentioned about due to SQL
injection concerns...

So, I took a different route;

instead of going

$sql=statement1;statement2",

I array'ed them.

sql[]='statement1';
sql[]='statement2';

the rest was smooth...

        if (is_array($sql)):
            #this is an exception
            foreach ($sql as $cur_sql):
                $query_result = $wpdb->query($cur_sql); # $results could be
FALSE if in errors!
                if ( FALSE === $query_result  ):
                    #early exit time, one of the baby sql's failed us.
                    return FALSE;
                endif;
                $wpdb->flush();
            endforeach;
        else:
            #this is when $sql is a string
            $query_result = $wpdb->query($sql);
        endif;





On Tue, Jul 15, 2014 at 11:31 AM, J.D. Grimes <[hidden email]> wrote:

> wpdb uses mysqli_query() -
> https://core.trac.wordpress.org/browser/tags/3.9.1/src/wp-includes/wp-db.php#L1616
>
> mysqli_multi_query() must be used to run multiple statements -
> http://php.net/manual/en/mysqli.quickstart.multiple-statement.php
>
> -J.D.
> On Jul 15, 2014, at 2:14 PM, Haluk Karamete <[hidden email]>
> wrote:
>
> > I'd like to be able to run this SQL using one wpdb->query call.
> >
> > $sql = 'TRUNCATE TABLE `my_new_table` ;ALTER TABLE `my_new_table`
> > auto_increment = 1';
> >
> > $query_result = $wpdb->query($sql);
> >
> >
> > As of 3.9.1, we do have the mysqli support. So, I'm not sure why this
> would
> > not fly.
> >
> > Is that one of my brain-freeze moments again?
> >
> >
> > but I'm getting the *familiar error: *
> >
> > 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 'ALTER TABLE
> > `my_new_table` auto_increment = 1)' at line 1]
> > _______________________________________________
> > wp-hackers mailing list
> > [hidden email]
> > http://lists.automattic.com/mailman/listinfo/wp-hackers
>
> _______________________________________________
> wp-hackers mailing list
> [hidden email]
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers