Set MySQL variables in WPDB

Question

I have an SQL query that works beautifully as a Raw SQL query, however when I put it into WPDB it throws an error:

global $wpdb;

    $sql = "SET @product_group = (SELECT product_group FROM insert_temporary LIMIT 1);

SELECT * FROM insert_temporary 
WHERE product_group in (SELECT product_group FROM insert_temporary
                WHERE product_group = @product_group
              GROUP BY product_group
              HAVING COUNT(product_group) > 1)";

    $data = $wpdb->get_results($sql);

This is the error:

WordPress database 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 'SELECT * FROM insert_temporary WHERE product_group in (SELECT product_group FRO' at line 3]
SET @product_group = (SELECT product_group FROM insert_temporary LIMIT 1); SELECT * FROM insert_temporary WHERE product_group in (SELECT product_group FROM insert_temporary WHERE product_group = @product_group GROUP BY product_group HAVING COUNT(product_group) > 1)

How can I set MySQL variables in the WPDB so that I can use the two together please?

Steven Hardy · Accepted Answer · 2021-01-25 06:46:38Z

1

I am so silly. With this in the end, I set a PHP variable that I needed instead of the "@SET". My final code was this:

$group_sql = "SELECT product_group FROM insert_temporary LIMIT 1";
    $group = $wpdb->get_var($group_sql);

    $sql = "SELECT * FROM insert_temporary 
WHERE product_group in (SELECT product_group FROM insert_temporary WHERE product_group = '". $group ."' GROUP BY product_group HAVING COUNT(product_group) > 1)";

    $results = $wpdb->get_results($sql);

This may help someone :)

answered Jan 25, 2021 at 6:46

Steven Hardy

112 bronze badges

So the outcome was you can't do both statements in a single SQL query? Did you find out where the restriction is here that's stopping that, in WordPress or in the PHP MySQL connector, or something else?

Rup
– Rup

2021-01-25 11:08:01 +00:00
Commented Jan 25, 2021 at 11:08

Add a comment |

Stack Exchange Network

Set MySQL variables in WPDB

1 Answer 1

Your Answer

Hot Network Questions

Set MySQL variables in WPDB

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions