Php search.. Only show results where all search terms match in a database.

05-12-2013 4:32pm #1

So I've got data added to my database..

ID File Keywords1

Keywords2 etc...... Maybe 5 columns in total.
1 a.txt apple banana

mango
2 b.txt apple

mango
3 c.txt apple

orange banana

I have a very basic search built that will search for one word from one column and present the results.

Basically, I need a search for "apple" that will give all 3 results but "apple banana" will give 1 and 3.
Any pointers on what functions to use? I will try and learn it myself if I'm shown in the right direction.. My website idea goes out the window if I can't do it.

mad turnip · 05-12-2013 4:41pm

SELECT * FROM table WHERE (Keywords1 LIKE '%apple%' OR Keywords2 LIKE '%apple%') AND (Keywords1 LIKE '%banana%' OR Keywords2 LIKE '%banana%')

you can use the function "split" to divide the search query into parts seperated with spaces. Then loop through each "word" and append

AND (Keywords1 LIKE '%banana%' OR Keywords2 LIKE '%banana%')

to the query everytime a word is encountered. You can also put in some kind of a filter to remove words like:

the, if, of etc

Also I'm not sure where your trying to go with the keywords two column, but it does not seem like the right way to go about it.

05-12-2013 4:53pm

This will still only search for one term.. If I enter two terms, there are no results.

$searchterms = explode(" ", $searchq);

$query = mysql_query("Select * from info where
data LIKE '%searchterms[0]%' and
data LIKE '%searchterms[1]%''") or die("nope..");

I took out the preg-replace line that only allows characters and numbers but that didn't help..

05-12-2013 4:59pm

mad turnip wrote: »

SELECT * FROM table WHERE (Keywords1 LIKE '%apple%' OR Keywords2 LIKE '%apple%') AND (Keywords1 LIKE '%banana%' OR Keywords2 LIKE '%banana%')

you can use the function "split" to divide the search query into parts seperated with spaces. Then loop through each "word" and append

to the query everytime a word is encountered. You can also put in some kind of a filter to remove words like:

the, if, of etc

Also I'm not sure where your trying to go with the keywords two column, but it does not seem like the right way to go about it.

Ok, I'll look up the split function.. Hmm. The first column would be for initial data and the second column would be for further user or employee revisions..

mad turnip · 05-12-2013 4:59pm

Deleted User wrote: »

This will still only search for one term.. If I enter two terms, there are no results.

$searchterms = explode(" ", $searchq);

$query = mysql_query("Select * from info where
data LIKE '%searchterms[0]%' and
data LIKE '%searchterms[1]%''") or die("nope..");

I took out the preg-replace line that only allows characters and numbers but that didn't help..

does your column data contain both the keywords your searching for?

Have you tried echo'ing your query to what exactly is being sent. I've always found formatting errors in php most common.

I'm not sure what language I'm thinking of but does php's array index start at 1?

05-12-2013 5:21pm

mad turnip wrote: »

does your column data contain both the keywords your searching for?

Have you tried echo'ing your query to what exactly is being sent. I've always found formatting errors in php most common.

I'm not sure what language I'm thinking of but does php's array index start at 1?

Yep, both words are there and are separately searchable.. I'm gonna be away from it now for a while, I'll try again later.

oscarBravo · 05-12-2013 5:31pm

Deleted User wrote: »

$searchterms = explode(" ", $searchq);

$query = mysql_query("Select * from info where
data LIKE '%searchterms[0]%' and
data LIKE '%searchterms[1]%''") or die("nope..");

Don't you need $searchterms[0] etc in the query string to do variable substitution? In fact, with the array index, you might need {$searchterms[0]}.

Try to echo $query after the assignment to see what it contains.

The Corinthian · 05-12-2013 5:41pm

You could end up with a horribly long SQL query for your search. Do you need to have Keywords1, Keywords2,... Keywords5 fields?

Anyhow, this function should construct the WHERE clause of your query, as a string, given a space delineated list of keywords as the input:
[PHP]function db_setSqlWhereClause($keywords) {
$numberOfCols = 5; // Number of 'KeywordsN' fields, where N is an int.
$a_keyword = explode(" ", $keywords); // Your keywords as an array.
$sql = "WHERE "; // Initialize output SQL

foreach ($a_keyword as $kw) {
for ($i = 0; $i < $numberOfCols; $i++) {
$sql .= "Keywords".$i." LIKE %'".$kw."%' OR "
}
}
$sql = substr($sql, 0, -4); // Trim off the OR at the end

return $sql;
}[/PHP]
Warning: I've not bothered testing, checking or even reading over this. On top of which, I've not touched PHP in a year.

oscarBravo · 06-12-2013 1:14am

Of course, you shouldn't be concatenating variables into a SQL statement at all; instead you should be using prepared statements for security reasons.

06-12-2013 4:33am

The Corinthian wrote: »

You could end up with a horribly long SQL query for your search. Do you need to have Keywords1, Keywords2,... Keywords5 fields?

Anyhow, this function should construct the WHERE clause of your query, as a string, given a space delineated list of keywords as the input:
[PHP]function db_setSqlWhereClause($keywords) {
$numberOfCols = 5; // Number of 'KeywordsN' fields, where N is an int.
$a_keyword = explode(" ", $keywords); // Your keywords as an array.
$sql = "WHERE "; // Initialize output SQL

foreach ($a_keyword as $kw) {
for ($i = 0; $i < $numberOfCols; $i++) {
$sql .= "Keywords".$i." LIKE %'".$kw."%' OR "
}
}
$sql = substr($sql, 0, -4); // Trim off the OR at the end

return $sql;
}[/PHP]
Warning: I've not bothered testing, checking or even reading over this. On top of which, I've not touched PHP in a year.

I used the guts of this with some google searching and got it finally

oscarBravo wrote: »

Of course, you shouldn't be concatenating variables into a SQL statement at all; instead you should be using prepared statements for security reasons.

I'm not sure what you mean unfortunately, I've only got a limited knowledge of these things..

Dave! · 06-12-2013 10:15am

Next time you're asking for help, a "please" and "thank you" couldn't hurt

myIdea · 06-12-2013 10:38am

Deleted User wrote: »

I'm not sure what you mean unfortunately, I've only got a limited knowledge of these things..

Basically... if your just learning, its suggested that you learn Today's technique's.

Either of the top two here...
http://www.php.net/manual/en/mysqlinfo.api.choosing.php

Reason:
Over the coming months/years (depends on your hosting company) the standard mysql extension will eventually get dropped from servers around the world as they upgrade to PHP 5.5.0 which doesn't support mysql.

Some however may still offer support for mysql, but I wouldn't bank on that and besides, it would be better to learn something new now.

On a personal level, I went with mysqli as I can relate to that easier having used mysql in the past, but that's just me

For now though, prepared statements are for mysqli and POD.

http://php.net/manual/en/mysqli.prepare.php
http://it1.php.net/manual/en/pdo.prepare.php

__________

Final thought... create your sites functionality under mysql if you find that easier for the moment and when your happy... convert your sql query's one-by-one to whichever new extension you choose to learn.

You could/can run both database connect scripts while your working on a change over/upgrade.

Hope that makes sense...

The Corinthian · 06-12-2013 11:29am

oscarBravo wrote: »

Of course, you shouldn't be concatenating variables into a SQL statement at all; instead you should be using prepared statements for security reasons.

In a perfect World, PHP support for MySQL stored procedures wouldn't be so ropey, then we wouldn't have to worry about any SQL in the PHP code.

myIdea wrote: »

Some however may still offer support for mysql, but I wouldn't bank on that and besides, it would be better to learn something new now.

I wouldn't hold my breath on backward comparability for mysql vanishing too quickly. The need for 90% of all bespoke PHP sites to have large chunks rewritten, would prove a PR nightmare for the language.

myIdea · 06-12-2013 11:39am

The Corinthian wrote: »

I wouldn't hold my breath on backward comparability for mysql vanishing too quickly. The need for 90% of all bespoke PHP sites to have large chunks rewritten, would prove a PR nightmare for the language.

Yeah I know they'll most likely offer php 5.4.xx for some time to come, with an optional upgrade select or new accounts = new servers running 5.5. I just thought it would be best to encourage @Ads By Google

but I guess you have just put his mind at ease

mad turnip · 06-12-2013 11:50am

Don't forget about sql injection!!!!!!!!

mysql_escape_string

is the function I believe in php!

Giblet · 06-12-2013 3:40pm

If your database has lots of data, the above queries are possible the worst you can use. "Like" searches that match anywhere in the string do not use indexes, and using multiple ones is even worse. If you have > 500k rows, or large columns, I would suggest using a dedicated index, either FullText, or Lucene.

Of course, if you're just playing around, disregard the above.

06-12-2013 5:28pm

Lots of stuff to thin about, thanks guys.

Talisman · 13-12-2013 4:08pm

You should also consider Sphinx.

Full-Text Search with Sphinx and PHP [pdf]

Php search.. Only show results where all search terms match in a database.

Comments