Forum Moderators: coopster
I'm fairly new to php-programming and MySQL. I've built a simple search engine that searches through different columns in my database, with the help of a like-query. The database consists of one table, ca 13000 posts, with just text or numbers, with the name of organisations, when they started, which city, category etc. The query looks like this:
$query = "select * from the_table where column1 like
\"%$trimmed%\" OR column2 like \"%$trimmed%\" OR column3 like
\"%$trimmed%\" order by column1; For the search I have a form with a standard search field, like this:
<form name="form" action="search.php" method="get">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />I also have two (so far) drop down-menus, like this:
<select name="city">
<option value="">All</option>
<option value="umea">Umea</option>
<option value="storuman">Storuman</option>
<option value="skelleftea">Skelleftea</option>
</select><select name="category">
<option value="%">All</option>
<option value="ekonomiska foreningar">Ekonomiska foreningar</option>
<option value="idrottsforbund">Idrottsforbund</option>
<option value="nykterhetsforbund">Nykterhetsforbund</option>
</select>
</form>
What I want to do, is to precise the search, if necessary, so that you can write a keyword, for example "women", and choose from the drop down menu called 'city', a city and get all the hits that include both the keyword, and narrows it down with the particular city chosen (cities are in one column). If that's understandable...:)
But it should also be possible just to search in the search-field, without choosing anything from the drop down-menues. Or vice versa: choosing just something from the different choices in either of the drop down-menues, and get all the results from these choices. Also, be able to combine one choice from the 'city' drop down, and another from 'category', and make it search for these criterias.
I've tried different solutions, but they either don't match up with my needs, or they do not work at all. Could someone possibly help me?
Many thanks,
/victor
The query would need to be constructed so that any of the optional parts would be ANDed together. One way to do that would be to set each of the desired criteria into an array, then use the implode() [php.net] function to combine them:
$phrases = array();
if($trimmed!= '')
$phrases[] = "(column1 like \"%$trimmed%\" OR column2 like \"%$trimmed%\" . . .)";
if($_POST['city']!= '')
$phrases[] = "(column2 = '" . mysql_real_escape_string($_POST['city']) . "')";
if($_POST['category']!= '%')
$phrases[] = "(column3 = '" . mysql_real_escape_string($_POST['category']) . "')";
$query = "select * from the_table where (" . implode(' AND ',$phrases) . ")";
mysql_real_escape_string() [php.net] makes sure the data hasn't been monkeyed with.
I've sorted out the whole thing now, and the serach function works as I want it to. My solution is probably not the ultimate one, but I really learnt a great deal and built up some confidence in writing php-stuff.
But I'm sure I will be back with more questions. :)
Thanks,
/victor
