MYSQL Database PHP Search Engine

[alexsaidani]

I am developing a MYSQL database movie search engine using php right now, i have managed to get it working where if i type the name of the film it will show the name, category, rating and description. But what i want to do is be able type the name of a film in and for it to display a random film name from a category that is called say, action, or be able to choose category, say, action from a drop down list and for it to show other films in that category and finally or be able to select a film rating from a drop down list and for it to be able to show films that have the same rating. I understand the basics of PHP pretty much and MYSQL.

But im not the skilled with it and i was just wondering if anyone could provide me with some assistance or code snippets to assist me.
Thanks in advance.

[IQAndreas]

Hm... I recently created a search engine for a PHP site, but the code is still quite messy and needs some cleaning up...

Anyway, I'll post the queries here as well as the HTML form for inputting the search results, and you can make do with it. Just ask if you want me to elaborate further.

Search form:

<form action="<?php echo FSearch::makeQueryURL(); ?>" method="post">
<table class="companies">
<tr>
<td colspan="2"><input type="text" name="fs_query" maxlength="250" width="1000" value="<?php echo $_POST('fs_query'); ?>" /></td>
<td><?php FSearch::showCategoryDropdown(); ?></td>
</tr>
<tr>
<td width="40%"><?php FSearch::showRegionDropdown(); ?></td>
<td width="40%"><?php FSearch::showLocationDropdown(); ?></td>
<td width="20%"><input type="hidden" name="fs" value="query" /><input type="submit" name="" value="Sök" class="ri" /></td>
</tr>
</table>

</form>

Database side for the query (simplified)

public static function query_baralan($searchstr, $category, $lan)
{
$searchkey = FSearchDatabase::parseQuery("foretag.fulltext", "foretag.metakey", "foretag.introtext", "foretag.title", $searchstr);

if ($searchkey)
{
$db =& JFactory::getDBO();

$query = "SELECT companies.id, companies.title, ";
$query .= " companies.introtext as foretag_text ";

$query .= "FROM `#__k2_items` AS companies ";

$query .= "WHERE ((companies.published ='1') AND (companies.trash = '0')) ";
$query .= "AND (companies_cat.parent='".$category."') ";
$query .= "AND (companies_cat.lan='".$lan."') ";
$query .= "AND ".$searchkey;

$query .= " ORDER BY 2; "; //Order by company name

$db->setQuery($query);
$r = $db->loadObjectList();
echo $db->getErrorMsg();
return $r;
}
else
{
return null;
}
}


public static function parseQuery($fulltext, $metakey, $introtext, $title, $query)
{
//If query is blank, return 1
if (!$query)
{ return "(1)"; }

$newQuery = Cleaner::parseString($query);

$itemArray = explode(" ", $newQuery);
$queryArray = array();

foreach ($itemArray as $value)
{
//Removed minimum length
//if(strlen($value) > 2){
$likeval = "LIKE '%".strtoupper($value)."%'";
$queryArray[] = "(upper(".$fulltext.") ".$likeval.") OR (upper(".$metakey.") ".$likeval.") OR (upper(".$introtext.") ".$likeval.") OR (upper(".$title.") ".$likeval.")";
//}
}

if (count($queryArray))
{
return "(".implode(" OR ", $queryArray).")";
}
else
{
return null;
}
}

Hm... It is quite messy. Well, here are some links which helped me (or at least that I visited when searching):
http://www.roscripts.com/PHP_search_engine-119.html
http://php.about.com/od/phpwithmysql/ss/php_search_3.htm
http://www.devarticles.com/c/a/MySQL/Developing-A-Site-Search-Engine-With-PHP-And-MySQL/

[geestring]

http://www.roscripts.com/PHP_search_engine-119.html


is this tutorial any good? In the comments it seems that it doesnt actually work?

[IQAndreas]

Geestring asked me via PM about the "Cleaner" class. I forgot which tutorial I got it from, so it is copyright of someone else, but here it is.

class Cleaner {

//static var $stopwords = array(" hitta ", " about ", " me ", " ever ", " each ")//you need to extend this big time.

public static $symbols = array('/','\\','\'','"',',','.','<','>','?',';',':','[',']','{','}','|','=','+','-','_',')','(','*','\&','^','%','$','#','@','!','~','`' );//this will remove punctuation

public static function parseString($string) {
$string = ' '.mysql_real_escape_string($string).' ';
//$string = $this->removeStopwords($string);
$string = Cleaner::removeSymbols($string);
return $string;
}

/*
public static function removeStopwords($string) {
for ($i = 0; $i < sizeof($this->stopwords); $i++) {
$string = str_replace($this->stopwords[$i],' ',$string);
}

//$string = str_replace(' ',' ',$string);
return trim($string);
}
*/

public static function removeSymbols($string) {
for ($i = 0; $i < sizeof(Cleaner::$symbols); $i++) {
$string = str_replace(Cleaner::$symbols[$i],' ',$string);
}

return trim($string);
}
}

I would be REALLY thankful if anyone has any views on the system I have set up if there are more secure methods of searching etc.