Search Custom WP Table WordPress

• SOLVED

Hello,
I have created a database table called "wp_cities" and have a field called City in which I have a whole bunch of cities listed. The idea is, using the Autocompleter plugin, I can auto-complete user searches when they look for cities. The only problem is, by default, the plugin won't look at my wp_cities database table.

Below I am providing a piece of code taken from plugin. It's looking for tags based on a few parameters. I'd like to have this modified to just look at everything in the wp_cities table.



$words = $wpdb->get_results("SELECT concat( name, '|', sum( count ) ) name, sum( count ) cnt FROM ".$wpdb->prefix."terms t, ".$wpdb->prefix."term_taxonomy tt WHERE t.term_id = tt.term_id AND tt.taxonomy='post_tag' AND name LIKE '$search%' GROUP BY t.term_id ORDER BY cnt DESC");



Thanks!
Mike

Answers (3)

2010-10-18

nathanbriggs answers:

Now I've seen the whole thing...
$wpdb->get_results("SELECT City as name FROM ".$wpdb->prefix."cities AND City LIKE '$search%'");

You could also use the built in taxonomy system with a custom taxonomy.
http://net.tutsplus.com/tutorials/wordpress/introducing-wordpress-3-custom-taxonomies/
http://justintadlock.com/archives/2009/06/04/using-custom-taxonomies-to-create-a-movie-database

---

Mike McAlister comments:

I'll definitely check that out, as that may be better in the long run.

I should have mentioned in the question above, inside the wp_cities table, there is a field called City that the cities are sitting in. I'm guessing that's crucial information, since I tried your code above with no luck ;).

---

Mike McAlister comments:

Hmm, that one doesn't seem to be connecting either.

---

Mike McAlister comments:

Still no luck. Maybe it will help to see the full code http://snipt.org/kpmY/. You can see I placed the code where it says //Only tags. I didn't put it there for any particular reason. I just figured it could go anywhere, so long as I had that option selected in the plugin settings (which I did).

2010-10-18

Denzel Chia answers:

Hi,

Seen your code snippet, I think your query should use WHERE Instead of LIKE
Construct your statement like this,
SELECT col_name FROM tbl_name WHERE col_name = $search

Thanks.

---

Mike McAlister comments:

Yeah that code is from the original plugin author. I'm just trying to modify parts of the plugin a bit.

2010-10-20

Nick Parsons answers:

I think what Denzel was saying is that Nathan's answer is really close, it's just needs a WHERE in place of the AND, like this:

$wpdb->get_results("SELECT City AS name FROM ".$wpdb->prefix."cities WHERE City LIKE '$search%'");

Does that work? :)