$wpdb->get_results(…) with $wpdb->prepare(…) returns empty array despite correct query

This string, meta_value LIKE '%user-input-value%'", contains percentage wilcards (%) that indicate that we’re looking for a row where meta_value column contains user-input-value as part of its value.

$wpdb->prepare converts them into hashes, and the MySQL server gets something like this: meta_value LIKE '{399038939300d2c307c53b29a166ee90101da7b2aba7978a898694010bf1bfe6}user-input-value{399038939300d2c307c53b29a166ee90101da7b2aba7978a898694010bf1bfe6}'. Naturally, I was getting no results!

The wpdb::prepare page reads, ‘Literal percentage signs (%) in the query string must be written as %%. Percentage wildcards (for example, to use in LIKE syntax) must be passed via a substitution argument containing the complete LIKE string, these cannot be inserted directly in the query string. Also see wpdb::esc_like().’

So, the LIKE part must be prepared like this (example from the wpdb::esc_like() page):

$wild = '%';
$find = 'only 43% of planets';
$like = $wild . $wpdb->esc_like( $find ) . $wild;
$sql  = $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_content LIKE %s", $like );

Where $wpdb->esc_like() should go before $wpdb->prepare().

And if one wants to replace percentage wildcard placeholders manually, they should use $wpdb->remove_placeholder_escape() after $wpdb->prepare().

I ended up with this code, which worked:

$key = 'user-input-key';
$value="user-input-value";
$value="%" . $wpdb->esc_like($value) . '%';
$query = "SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key=%s AND meta_value LIKE %s";
$prepared_query = $wpdb->prepare($query, array($key,$value));
$prepared_query = $wpdb->remove_placeholder_escape($prepared_query);
$query = $wpdb->get_results($prepared_query);

Related Posts:

  1. How to parse row results from $wpdb -> get_results
  2. Why $wpdb->show_errors() and print_error() is showing an output even if the query output is correct?
  3. $wpdb->insert not working in any way
  4. get_results on large datasets
  5. $wpdb->insert() and Values for Datetime Columns?
  6. Change post status based on meta value
  7. wpdb->insert not working
  8. Speed optimization of $wpdb->get_results
  9. How do I create a single.php for a specific category?
  10. $wpdb is get_results escaped
  11. Using $wpdb generates DB error
  12. How do you use prepare when asking for a list of id’s
  13. how to execute different sql query in non-sanitized $wpdb->get_results function
  14. How to get top 10 user is based on user meta value
  15. wpdb prepare: passing varible number of fields as second argument
  16. $wpdb insert array
  17. Wpdb query for comment meta for current post
  18. How to update records using $wpdb?
  19. How to Modify this $wpdb query to accept an array of post statuses
  20. show badge with count for pending items in custom post type
  21. WPDB Update using Conditional Arrays
  22. $wpdb->get_results returns empty but value exists
  23. WP Sql query multiple where clause
  24. Problem displaying inserted form
  25. Get published posts and pages?
  26. create drop down menu in theme customizer from custom db
  27. Are there risks associated with using wp_options table using wpdb to update theme options
  28. Syntax for $wpdb->prepare when searching in two columns
  29. $wpdb->insert is running multiple times on page load, but only called once
  30. Get an array of meta_values for a user meta_key
  31. Confused by $wpdb->prepare
  32. How to get a value-only flat array from $wpdb->get_results when selecting a single column, without foreach()?
  33. How to Instantiate wpdb Object in New File
  34. How do you build a wpdb query dynamically?
  35. Would this WPDB setup result in potential race conditions?
  36. How to update a row in a table in WordPress
  37. WordPress (on AWS ubuntu) database Fails “Connection refused in .. /wp-includes/wp-db.php on line 1531”
  38. Optimizing WordPress Queries – Removing Group By ID
  39. External DB Connection [closed]
  40. How to get entry by title by using wpdb
  41. Codex: Database Description: meaning of Cardinality
  42. $wpdb not being defined in function: Fatal error: Call to a member function query() on a non-object
  43. WordPress is trimming leading 0s
  44. WordPress db prepare
  45. how to get db values without using an loop with wpdb->get_results()
  46. How to left join meta in queries [closed]
  47. How to run wp_insert_post() & wpdb on the background?
  48. Trying to get variable from WP table and toggle its value
  49. Set MySQL variables in WPDB
  50. get_results query with accent
  51. Save data from a checkbox to a wpdb array
  52. How to prepare an array of values with $wpdb
  53. Is querying wpdb directly and skipping actions provided by WP’s core “wp_update_post” a good idea?
  54. WP-PostRatings: list current user’s rated posts
  55. $wpdb query outputs php code instead of executing it
  56. WPDB Join with custom table
  57. Getting values from wpdb
  58. Using “->” in a page to exceute $wpdb query gives error
  59. Why is this $wpdb query looping 5 times?
  60. wpdb query not returning results containing single quotes
  61. duplicate entries in database while using save_post or wp_insert_post
  62. Unknown column ‘siteurl’ in ‘where clause’ WPDB outside
  63. Inserting a MySQL record into a table with an array of field names and an array of values
  64. Incorrect Use of wpdb::prepare()
  65. Exclude specific terms from all queries using posts_where or something similar
  66. query a newly created table using $wpdb
  67. Get all sticky posts from one user through user ID
  68. wpdb insert formatting for date in array
  69. how to list all post that are in the custom taxonomy using $wpdb
  70. Access to numerically indexed array in wpdb Class and undefined offset
  71. $wpbd->insert() does not insert user data
  72. Why $wpdb->insert and $wpdb->update not working server but it is working in localhost?
  73. wpdb prepare placeholders for MySQL keywords
  74. Sql query triggered twice
  75. Unable to insert data into using custom plugin
  76. Escaping a WPDB Object in One Shot
  77. Create a stored procedure on plugin activation
  78. Get last element from wpdb as a string
  79. Creating an Angular factory from custom database table
  80. wpdb get_row database query inquiry
  81. Object Cache – Avoid db queries totally
  82. Inserting and updating rows with wpdb indreases integer fields by 1 point sometimes
  83. Custom database query to validate data
  84. Creates only one table and not the other
  85. Update all fields of table with ON DUPLICATE KEY UPDATE command
  86. WordPress – wpdb query does not list same result as sql query
  87. What is the best practice to initialise $wpdb by loading wp-load.php?
  88. $wpdb->get_results in not an array
  89. adding a log row log table, when the draft post publish
  90. How to make iteration on wpdb->update or query statement?
  91. looking for a way to allow users to backup the plugin db data(save as)
  92. Error inserting row into table
  93. Jeditable Plugin working as it should – scope issue?
  94. Out of Memory – Line 791 of WP-DB.php (mysql_real_escape_string)
  95. Site going down due to slow queries
  96. Call to a member function get_results() on a non-object in wordpress plugin
  97. How do I change the datetime format from ( ‘y-m-d’ ) to ( ‘d m y’ ) [closed]
  98. Get count of rows based if column exists in two different tables
  99. Table wont load into WPDB
  100. WPDB prepare – like % – placeholders?