Custom Query - Based on user input

Question

I'm working on displaying suggestions to an input, assisting the user.

I'm using the included jQuery suggest. That's loading - and through ajax I'm using a custom SQL query and can see it's attempting to pull it in.

I'm having trouble writing the SQL query.

Here's what I have so far.

global $wpdb;

// group name key
$meta_key = '_create_new_group';
// user input
$search = $_REQUEST['q'];

$groupnames = $wpdb->get_col($wpdb->prepare(
    "
    SELECT  meta_value 
    FROM    $wpdb->postmeta
    WHERE   meta_key = %s
    LIKE    %s // Works up to here
    ",
    $meta_key,
    $search
));

To get to where I am now I've used examples from the wpdb class reference. http://codex.wordpress.org/Class_Reference/wpdb

When I type into the input, the query overloads the server. And a var_dump as far as it'll load starts to list meta values outside of what I'm querying (as far as I can see).

Can anyone see where I must be clearly going wrong...

Answer 1

While this is primarily a SQL question, there are WordPress components and Core has no efficient mechanism to pull this data that I am aware of. This has long been a problem in Core in my opinion. So:

global $wpdb;

// group name key
$meta_key = '_create_new_group';
if (!empty($_REQUEST['q'])) {
  // user input
  $search = $_REQUEST['q'].'%';

  $sql = "SELECT meta_value 
  FROM {$wpdb->postmeta} 
  WHERE meta_key = '{$meta_key}' 
  AND meta_value LIKE %s";
  $sql = $wpdb->prepare($sql,$search);
  // var_dump($sql);
  $groupnames = $wpdb->get_col($sql);
}

Notes:

1. Don't do anything if $_REQUEST['q'] isn't set. That is the if (!empty($_REQUEST['q'])) { conditional
2. You do not need to escape hard coded values, only the user supplied ones. In your case, that is $search and only $search.