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?

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?

• wpdb

1 Answer 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 :)