Valid SQL query return empty

Question

I see the following string when I print my prepared query:

"SELECT p.ID, COALESCE(w.value,Display_name) value, p.post_title, p.post_date, p.post_content,p.post_author,user_login FROM wp_posts p INNER JOIN wp_users a ON a.ID = p.post_author LEFT JOIN wp_bp_xprofile_data w ON w.user_id = a.ID WHERE post_type = 'reply' and post_parent = 53592568 and p.ID > 53592614 AND TIMEDIFF( '2017-12-04 06:14:58', post_date ) < '00:00:15' AND post_content is not null ORDER BY post_date ASC LIMIT 0 , 5"

It seems to return fine when I run it through the SQL client:

But it's not returning when I try to run with the wpdb->get_results or wpdb->query function in my wordpress template.

Here's my code:

global $wpdb;

$reply_id = $_REQUEST["id"];
$postId   = $_REQUEST["post_id"];

$data = date("Y-m-d H:i:sa");
$dataStr = str_replace("am","",str_replace("pm","",$data));

$mysqli_query = "SELECT p.ID, COALESCE(w.value,Display_name) value, p.post_title, p.post_date, p.post_content,p.post_author,user_login ";
$mysqli_query .= " FROM  wp_posts p ";
$mysqli_query .= " INNER JOIN wp_users a ON a.ID = p.post_author ";
$mysqli_query .= " LEFT JOIN wp_bp_xprofile_data w ON w.user_id = a.ID ";
$mysqli_query .= " WHERE post_type = 'reply' ";
$mysqli_query .= " and post_parent = %d ";
$mysqli_query .= " and p.ID > %d ";
$mysqli_query .= " AND TIMEDIFF(  '%s', post_date ) <  '00:00:15' ";
$mysqli_query .= " AND post_content is not null ";
$mysqli_query .= " ORDER BY post_date ASC  ";
$mysqli_query .= " LIMIT 0 , 5";

$reply_query = $wpdb->prepare($mysqli_query, $postId, $reply_id, $dataStr);

$results = $wpdb->get_results( $reply_query );
var_dump($results);

Here's what var_dump displays for me:

array(0) { }

Can anybody help me to figure out whats wrong with this code?

Thank you

Answer 1

There are view issues with the query:

• First what the @janh wrote.
• Second why you removing the am/pm when you could easily use the date("Y-m-d H:i:s");
• Third, there is this little helper for the database table prefixes $wpdb->prefix
• Users table the column name is display_name
• Also when you make aliases is a good rule to stick with them through the query even if there are not multiple columns with the same name.

For example without counting the bp_xprofile_data table that I mocked:

$reply_id = $_REQUEST["id"];
$postId   = $_REQUEST["post_id"];
$data = date("Y-m-d H:i:s");
$mysqli_query = "SELECT p.ID, COALESCE(w.value,a.display_name) value, p.post_title, p.post_date, p.post_content, p.post_author, a.user_login ";
$mysqli_query .= " FROM {$wpdb->prefix}posts p ";
$mysqli_query .= " INNER JOIN {$wpdb->prefix}users a ON a.ID = p.post_author ";
$mysqli_query .= " LEFT JOIN {$wpdb->prefix}bp_xprofile_data w ON w.user_id = a.ID ";
$mysqli_query .= " WHERE p.post_type = 'reply' ";
$mysqli_query .= " AND p.post_parent = %d ";
$mysqli_query .= " AND p.ID > %d ";
$mysqli_query .= " AND TIMEDIFF(  %s, p.post_date ) <  '00:00:15' ";
$mysqli_query .= " AND p.post_content IS NOT NULL ";
$mysqli_query .= " ORDER BY p.post_date ASC  ";
$mysqli_query .= " LIMIT 0 , 5";

$reply_query = $wpdb->prepare($mysqli_query, $postId, $reply_id, $data);

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

Answer 2

Don't put single quotes around placeholders, they are added automatically. Turn '%s' into just %s.