Wpdb query with dynamic table name

Question

I have a separate custom table in my database for each language I'm providing for my website. I need to select the correct table dynamically from a variable that is sent from a POST request.

First I was trying to to use $wpdb->prepare but then I read that it can't really handle the name of the table since it will force it into a string.

The other solutions that I saw looked something like this:

$foods = $wpdb->get_results("SELECT * FROM $sanitized_search_language WHERE dbID = $sanitized_search_text", ARRAY_A);

That will give me the following error:

"<div id=\"error\"><p class=\"wpdberror\"><strong>WordPress database error:</strong> [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near &#039;WHERE dbID = 719&#039; at line 1]<br /><code>SELECT * FROM  WHERE dbID = 719</code></p></div>[]"

I also saw the variables wrapped in curly brackets in some solutions like this:

$foods = $wpdb->get_results("SELECT * FROM {$sanitized_search_language} WHERE dbID = {$sanitized_search_text}", ARRAY_A);

But that is also giving me an error.

How can I select the custom table I want to query based on a dynamic variable?

Answer 1

I will start with the question.

From the sql error output we see this SELECT * FROM WHERE dbID = 719

This tells us that $sanitized_search_language has no value, so check it first.

Now for the BIG problem with this query!

You used get_results with a sql query that has variables as is, get_results doesn't escape the variables.

You must use prepare when passing variables into an sql query in order to prevent sql injections.

see wpdb::get_results, the first user contribution shows a great example on how to implement this