Wordpress Prepare SQL

[jarvis]

Hi All,

Apologies if this is in the wrong place.

I'm using Wordpress to run a SQL query as per the below:

$results = $wpdb->get_results($wpdb->prepare("SELECT * FROM $table_name WHERE 'start_date' = %s AND 'email_sent' = %d", $test, $email_sent), ARRAY_A);

This outputs the following:

SELECT * FROM mfs_training_course_email_notifications WHERE 'start_date' = '2023-06-17' AND 'email_sent' = 0

Which returns 0 results. 

However, if I know there is a result. If I go to PHPMyAdmin and run the following:

SELECT * FROM `mfs_training_course_email_notifications` WHERE `start_date` = '2023-06-17' AND `email_sent` = 0

It works and returns a result.

If I alter the code:

$results = $wpdb->get_results($wpdb->prepare("SELECT * FROM $table_name WHERE 'email_sent' = %d", $email_sent), ARRAY_A);

Therefore:

SELECT * FROM mfs_training_course_email_notifications WHERE 'email_sent' = 0

It returns results, so seems the issue is the start_date part. However, I'm perplexed as to why? The start_date column is a date field - in case that makes any odds!? Am trying to prevent doing the last search, then adding an IF statement to check the date. Would rather try to include both in the initial query to make things quicker/streamlined.

Hope that makes sense!?

[Barand]

The query you ran in PHPMyAdmin appears to be the only one above that has correct syntax.

[jarvis]

Hi @Barand

Is that due to the `mfs_training_course_email_notifications` as opposed to SELECT * FROM mfs_training_course_email_notifications (no backticks)

It's the only difference I can see? 

You're right!

$results = $wpdb->get_results($wpdb->prepare("SELECT * FROM `$table_name` WHERE `start_date` = %s AND `email_sent` = %d", $test, $email_sent), 

Worked!

Thanks

Edited September 23, 2022 by jarvis
Being a numpty!

[Barand]

Your problem is  was where to use backticks and where to use single quotes.