Jump to content
jarvis

Custom Post Search With SQL Causing Database Connection To Drop

Recommended Posts

Hi All,

 

I'm hoping someone can help out.

 

We have a search form that's made up of two fields (a wild card and a location)

 

The idea being that it searches a custom post (job)

 

Because we need to search the post title and some custom meta fields, I couldn't fathom out a way to search as I needed. So came up with the following code

	SELECT      DISTINCT(ID), post_title
	FROM        $wpdb->posts
	INNER JOIN 	$wpdb->postmeta m1 ON $wpdb->posts.ID = m1.post_id
	INNER JOIN 	$wpdb->postmeta m2 ON $wpdb->posts.ID = m2.post_id
	WHERE       post_title LIKE %s
	AND 		post_type = 'jobs'
	AND 		post_status = 'publish'
	OR 			( m1.meta_key = 'dynamic_filled_company' AND m1.meta_value LIKE %s )
	OR 			( m2.meta_key = 'dynamic_filled_careers' AND m2.meta_value LIKE %s )
	AND 		post_parent = 0
	",
	$s, $s, $s

This worked but suddenly caused the database connection to drop. I checked with our hosting company and they pinpointed the issue to this code.

 

I'm no expert but it seems ok to me, however, I gladly welcome any thoughts on how I could either improve it or approach it differently

 

Thanks in advanced

Share this post


Link to post
Share on other sites

I am surprised it ever worked, there is a distinct lack of single quotes around those %s items. (You really should be using a prepared query instead of placing values into the query string.)

 

Are you sure that the ANDs and ORs are as you expected (The final AND will be coupled with the final OR expression).

 

What is the value of $s when it fails?

Share this post


Link to post
Share on other sites

Hi @Barand,

 

Many thanks for the reply.

 

The query should search:

 

All published jobs where the title or company or career = X AND location = Y

 

However, I haven't included location yet as the above is causing issue

 

May I ask, how you would approach it?

Share this post


Link to post
Share on other sites

Hi Barand,

 

My bad, I didn't answer one of your points

 

I'm using a conditional to check the inputs, basically:

if ($s != '' && $location == ''):
elseif ($s == '' && $location != ''):
elseif ($s != '' && $location != ''):
else:
endif;

I then have the following code if it's searching both:

	#construct our query
	$s = '%'.$_GET['s'].'%';
	$t = '%'.$_GET['location'].'%';


	$search_titles = $wpdb->get_col( $wpdb->prepare( 
	"
	SELECT      DISTINCT(ID), post_title
	FROM        $wpdb->posts
	INNER JOIN 	$wpdb->postmeta m1 ON $wpdb->posts.ID = m1.post_id
	INNER JOIN 	$wpdb->postmeta m2 ON $wpdb->posts.ID = m2.post_id
	INNER JOIN 	$wpdb->postmeta m3 ON $wpdb->posts.ID = m3.post_id
	WHERE (
		( m1.meta_key = 'dynamic_filled_careers' AND m1.meta_value LIKE %s ) OR	
		( m2.meta_key = 'dynamic_filled_company' AND m2.meta_value LIKE %s ) OR
		( m3.meta_key = 'dynamic_filled_town' AND m3.meta_value LIKE %s )
	)
	AND post_title LIKE %s
		AND 		post_type = 'jobs'
		AND 		post_status = 'publish'
	",
	$s, $s, $t, $s
	)); 

Not sure if that helps?

Share this post


Link to post
Share on other sites

Even though your function is called "prepare" the syntax looks suspiciously like sprintf(), in which case my comment on the missing single quotes and putting data directly into the query still stands

Share this post


Link to post
Share on other sites

Hi Barand,

 

I will try adding single quotes around the %s 

 

Do I need to make any other amends? Would the single quotes be the reason if could cause the issues?

Share this post


Link to post
Share on other sites

You need to check if the query is failing and, if it is, look at the error message produced.

 

What does that "prepare()" method do? What's the code?

Share this post


Link to post
Share on other sites

Hi Barand,

 

It's Wordpress and therefore uses WP's own prepare statements.

 

I'll check the error logs

 

Thanks again for your help

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.