Html Php Help

[simpso]

Hi everyone

 

Im pretty new to this so bear with me if i state the obvious or anything

 

Im currently adding a search function to my website using mysql and php.

 

First of all i actually have the search working using the like function and the % wildcards to bring back multiple results.

 

For searching i am looking at two coloums in my database title and key words. The keywords are broken up by spaces.

 

The issue im having is when i search if i search for example new york this will bring back all the entries with new your in them but if i type new york city i get none because none of my keywords say city.

 

Same happens if the words are types out of sequence from the way they are in the database.

 

Does anyone have any idea how i can build this so for example with the new york city one the database will still return any entries that has new york?

 

 

Hope this makes sense and i hope you can help.

 

Thanks.

[AyKay47]

Please post the query you are currently using as well as the relevant database table structure. Typically, you would have a row for each keyword instead of separating them with a space in fewer rows.

[simpso]

Here is a copy of the code i am using.

 

the database structure is simply

 

ID

Title

Keywords

 

<?php

$search = $_GET['search'];

$maxRows_Recordset1 = 10;

$pageNum_Recordset1 = 0;

if (isset($_GET['pageNum_Recordset1'])) {

$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];

}

$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

 

mysql_select_db($database_Laughing_seagull, $Laughing_seagull);

$query_Recordset1 = "SELECT products.Title FROM products WHERE products.keywords LIKE '%".$search."%' OR products.Title LIKE '%".$search."%'";

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

$Recordset1 = mysql_query($query_limit_Recordset1, $Laughing_seagull) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

 

if (isset($_GET['totalRows_Recordset1'])) {

$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];

} else {

$all_Recordset1 = mysql_query($query_Recordset1);

$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);

}

$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

?>

[mrMarcus]

Consider full-text search.

 

Depending on the size of your table now, or in the future, using wildcards can be terrible on performance. And while full-text searching is not the creme de la creme of table search, it does a better job, hands down.

Edited November 26, 2012 by mrMarcus

[krisw44]

First thing that comes to mind is to split the search term that the user types on the space character. Then, search the database for each of the terms separately. So instead of searching for "new york city" you instead search for "new", "york", and "city". From this you can eliminate duplicates and possibly rank them based on how many times each word shows up. Maybe require at least 50% of the words to match to cut out words like "in" or "the" from flooding the results.

 

Hopefully this is helpful, it actually gave me an idea for my own search feature, so thank you.

[simpso]

First thing that comes to mind is to split the search term that the user types on the space character. Then, search the database for each of the terms separately. So instead of searching for "new york city" you instead search for "new", "york", and "city". From this you can eliminate duplicates and possibly rank them based on how many times each word shows up. Maybe require at least 50% of the words to match to cut out words like "in" or "the" from flooding the results.

 

Hopefully this is helpful, it actually gave me an idea for my own search feature, so thank you.

 

Ah it was nothing:)

 

Is this the full text searching that mr marcus described or something else?

 

If so how would i go about splitting the search term up?

[mrMarcus]

Ah it was nothing:)

 

Is this the full text searching that mr marcus described or something else?

 

If so how would i go about splitting the search term up?

First thing that comes to mind is to split the search term that the user types on the space character. Then, search the database for each of the terms separately. So instead of searching for "new york city" you instead search for "new", "york", and "city". From this you can eliminate duplicates and possibly rank them based on how many times each word shows up. Maybe require at least 50% of the words to match to cut out words like "in" or "the" from flooding the results.

 

Hopefully this is helpful, it actually gave me an idea for my own search feature, so thank you.

 

I hope you're not referring to cycling through the table 3 separate times for every one search.

 

Full-text search has Stop Words to handle words like "in" and "the".

 

It also allows for Boolean search.

Edited November 26, 2012 by mrMarcus