0

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...

  • Why are you using LIKE? I something appended or prepended to _create_new_group? – s_ha_dum Dec 16 '15 at 19:09
  • I'm not experienced in SQL. I see the query as; select meta values from post meta where the meta key is _create_new_group and is like the input. Have I got that wrong? – JohnMcCarthy Dec 16 '15 at 19:11
  • Just a tip: You do not need and shouldn't do this with "naked" SQL. Use get_posts() – kraftner Dec 16 '15 at 19:16
  • Thanks kraftner, I wrote the query a while ago and was sure that I had to pull the values in this way. So if I use get_posts to query for posts with my desired key, then get the ID from each post and use that to call get_post_meta? Would that be the right way? – JohnMcCarthy Dec 16 '15 at 19:43
  • @kraftner : using get_posts() is an extremely wasteful way to get this information. That will trigger numerous queries where only one is necessary. Sorry, I usually defend using Core functions, but only when it makes sense. Core really needs a way to pull data of this nature but I am not aware of one. – s_ha_dum Dec 16 '15 at 19:55
2

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.
  • Thank you. I understand 1 & 2, with 3 I do want to use LIKE. I'm attempting to use the user input to list groups that are already in the database that they can then join. – JohnMcCarthy Dec 16 '15 at 20:20
  • Do you understand the difference between LIKE and =? – s_ha_dum Dec 16 '15 at 21:28
  • My understanding is that LIKE will allow me to use wild cards and therefore the user can enter the beginning of the group name and the query will run showing results helping them pick an existing name (which is the reason I'm trying to implement this. If I used = the user would have to enter the exact name for it to appear as a result. So, answer to your question; I think so. – JohnMcCarthy Dec 16 '15 at 22:32
  • @JohnMcCarthy I edited to use a LIKE query matching only on the trailing end-- the first characters are an exact match. – s_ha_dum Dec 16 '15 at 22:50
  • Thanks very much @s_ha_dum, that's cleared up some syntax understanding for me and all working as desired. – JohnMcCarthy Dec 16 '15 at 23:13

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.