0

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?

1 Answer 1

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

1
  • 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
    Commented Jan 25, 2021 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.