PHP, mysql search script

Bananna man

Hey,

I need to write a php script to search my database. Does anyone know how to go about this or a good website tutorial? Ive found a couple of tutorials on the web but they warent that good.

Thanks

is_that_so

Here are a couple . You can't beat Google. Search for "php and mysql tutorials" There are lots out there.


http://www.freewebmasterhelp.com/tutorials/phpmysql
http://webmonkey.wired.com/webmonkey/programming/php/tutorials/tutorial4.html

Also go to PHP Home

Bananna man

cheers for that, but their not really what im after. Im looking to write a search script for my database similar to what google does on the web (obviously not as safisticated). I thought first that i could just do a "LIKE" query but that wouldnt work very well.

is_that_so

Can you be more specific and post an exact example of what you expect to retrieve? Are you talking about string matching or using regular expressions ?

Rollo Tamasi

you can always put a google search on your site and save yourself a lot of bother.

Bananna man

I need to search my database for strings and regular expressions and then just sort the results by date.

When someone come onto the site they will enter a search term/s and then any posts left that match the search terms they input will be retrieved.

Webmonkey

Well its not that difficult really. I take it you want to search in boolean mode then?

Well you could do this. Have a form called 'searchword' and submit it to a script someting like this.
[PHP]
$searchword = $_POST;

if (!isset($searchword))
{
print "You forgot the enter a search word!";
}
else {

//maybe you might want to close it with quotes. best in boolean mode
//$searchword = "\"".$searchword."\"";

$searchquery = "SELECT * FROM `yourtable` WHERE MATCH (column1, column2, column3) AGAINST ('$searchword' IN BOOLEAN MODE) ORDER BY `date` DESC";
$result = mysql_query($result);
if (!$result)
{
die ("Error With DATABSE!".mysql_error());
}
else {
while ($row = mysql_fetch_assoc($result))
{
print "Found match in <strong>$row</strong><br>";
}
}
}
[/PHP]

Thats what i'd do, thats written from head and not tested so maybe expect it not to work at first


EDIT: Regular expressions, havn't done them before with searches.
Look at this:
http://docs.mandragor.org/files/Databases/Mysql/Mysql_manual_en/manual_Regexp.html

Bananna man

Webmonkey wrote:

Well its not that difficult really. I take it you want to search in boolean mode then?

Well you could do this. Have a form called 'searchword' and submit it to a script someting like this.
[PHP]
$searchword = $_POST;
//maybe you might want to close it with quotes. best in boolean mode
//$searchword = "\"".$searchword."\"";
if (!isset($searchword))
{
print "You forgot the enter a search word!";
} else

{
$searchquery = "SELECT * FROM `yourtable` WHERE MATCH (column1, column2, column3) AGAINST ('$searchword' IN BOOLEAN MODE) ORDER BY `date` DESC";
$result = mysql_query($result);
if (!$result)
{
die ("Error With DATABSE!".mysql_error());
} else
{
while ($row = mysql_fetch_assoc($result))
{
print "Found match in <strong>$row</strong><br>";
}
}
}
[/PHP]

Thats what i'd do, thats written from head and not tested so maybe expect it not to work at first

Written from head!! And here's me scratching my head as to where to even begin . Will this work if someone search with a few terms at the same time i.e. will it bring back results if one of the terms they used was found or will it have to match exactly what they wrote?

is_that_so

So it's the SQL you are after.

Try something like this from your search

$input_str is the search string

"select field1, field 2 from mytable where lower(data) like lower('%".$input_str. "%') ORDER BY date desc;"

Webmonkey

Bananna man wrote:

Written from head!! And here's me scratching my head as to where to even begin . Will this work if someone search with a few terms at the same time i.e. will it bring back results if one of the terms they used was found or will it have to match exactly what they wrote?

If they enter two keywords, it should search both separetly (sp? im bad) and if you uncomment the
[php]
//$searchword = "\"".$searchword."\"";
[/php]
you should get an exact match. You could put a tick box on the forum to allow the user to enable that or not but if you don't bother uncommenting that and user enters "hello test", with the quotes it will find the exact match of 'hello test' (I think!, try it out sure)

Bananna man

Cheers, i'll give it a lash today.

Webmonkey

Bananna man wrote:

Cheers, i'll give it a lash today.

Good luck with it. Any probs let us know

Bananna man

Hey,

I've been trying it out but i keep getting a mysql syntax error:

[PHP]Error With DATABSE!You have an error in your SQL syntax near 'BOOLEAN MODE) ORDER BY review_date DESC ' at line 4[/PHP]

The code i am using at the moment is this: (i have changed the table names and column again)

[PHP] $searchquery = "SELECT *
FROM my_table
WHERE MATCH (job_category_from_user)
AGAINST (".$searchword." IN BOOLEAN MODE)
ORDER BY review_date DESC
";
$result = mysql_query($searchquery);
if (!$result)
{
die ("Error With DATABSE!".mysql_error());
} else
{
while ($row = mysql_fetch_assoc($result))
{
print "Found match in <strong>".$row."</strong><br>";
}
}
} [/PHP]

Anyone know where the error is?

Thanks

Ph3n0m

*doh


going back to sleep, go away

Serbian

Ph3n0m wrote:

BEGIN__VBULLETIN__CODE__SNIPPET

The vBulletin forum is putting that there, that's not from his code. You get that if you post PHP code using the [ php ] tags, and don't have the <?php at the beginning of your code.

Bananna man

Ph3n0m wrote:

*doh


going back to sleep, go away

Thanks for the input mate!!

Ph3n0m

chances are your table hasnt been set up for fulltext indexes


and my previous input was wrong thats why I re-edited it with my "going back to sleep...."

Bananna man

Ph3n0m wrote:

chances are your table hasnt been set up for fulltext indexes


and my previous input was wrong thats why I re-edited it with my "going back to sleep...."

Lol, i thought you were telling me to go back to sleep and go away. Apologies

Serbian

What version of MySQL are you using?

Bananna man

Serbian wrote:

What version of MySQL are you using?

Its MySQL 3.23.58, i think it may have to do with setting up a FULLTEXT index. Ive been trying to set it up but no success so far. Maybe it has to be on MySQL 4 and above?

Webmonkey

I think boolean searching hasn't come into it till later version. Better off upgrading to version 5.

As well change this line:
[php]
AGAINST (".$searchword." IN BOOLEAN MODE) [/php]


to [php] AGAINST ('".$searchword."' IN BOOLEAN MODE) [/php]

As well as that you are selecting for 'my_table' - is this the actual name of your table.
When printing too you won't see anything as your printing $row.
print $row;