PHP multi word search

I was building a search for a site I was working on and needed the search to work on the words a user might type in separately not as one string. For instance, if they typed: “wiffle ball in Louisville”, it would search: wiffle, ball, and Louisville.

Building my SELECT statement was a little tricky at first.

First I had to take my string that’s being passed and store each word in an array. The below is a very simple example, I highly recommend that you clean up the string first!

$searchString = $_POST['who-search'];
$searchArray = explode(" ", $searchString);

This takes our string and uses the explode function to break up our string into an array whenever whitespace is found. Again, this should be refined a bit more in a production level product.

Now that we have an array of the words that were entered, a way to work this array into the SQL statement had to be figured out. Using a couple of loops to go through the array and create OR’s did the job.

$sql = "SELECT DISTINCT * FROM table WHERE ("; 
while (list($key,$val) = each($searchArray)) {
	$val = addslashes($val);
	if ($val<>" " and strlen($val) > 0) {
		$sql .= "column1 LIKE '%$val%' OR column2 LIKE '%$val%'  OR column3 LIKE '%$val%') OR";
$sql = substr($sql,0,(strLen($sql)-3));//this will eat the last OR
$sql .= ") ORDER BY column1 DESC";

The while loop goes through our array the addslashes function is called to the current value. This escapes apostrophes from throwing off the SQL statement.

If the current value isn’t empty then it’s inserted in the SQL and it’s value compared to the columns you want to compare it against in your SQL. In my example above I’ve used LIKE against 3 columns.

Finally you have to remove the final OR from the SQL statement. A simple substr function is used to remove it from the end.


YQL nest query

I wanted to use YQL to build a query to grab photos on an account with certain tags that I could then render on a page. The just gets you the photo’s id and not all the other goodness that is required to make the image display.

So I built a nested query in YQL to get the job done.

select farm, id, secret, server, title
where photo_id in (select id from where user_id='&lt;your_user_id&gt;' and tags='soccer,men')

So in one call it’s gets the id of the photos that meet your initial query:

select id from where user_id='&lt;your_user_id&gt;' and tags='soccer,men'

It then takes the id’s from that query and grabs the that you need, all in one call! Very cool stuff.

Go to the YQL Console and play around.


Yahoo! Term Extraction Web Service

Just finished up using one of my favorite and what seems to be little well known web service, the Term Extraction from the folks a Yahoo! You feed it some content and it returns a list of significant words from the content. This simple service can be incredibly useful.

For instance I recently used this service to scan an article on a site and using the words that it returns, as tags to pull relevant photos from Flickr to go along with the article. So in this case I used it to pull tags from a body of text, very cool. You can also use YQL to make calls to it as well.