Custom Post Search With SQL Causing Database Connection To Drop

[jarvis]

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

[Barand]

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?

[jarvis]

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?

[jarvis]

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?

[Barand]

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

[jarvis]

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?

[Barand]

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?

[jarvis]

Hi Barand,

 

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

 

I'll check the error logs

 

Thanks again for your help