0

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

0

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 );
1

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

  • same empty output :( – Bruno Sousa Dec 4 '17 at 8:09
  • Use $wpdb->last_query to access the SQL that was actually executed and compare that to the SQL that's working when you run it manually. – janh Dec 4 '17 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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