Welcome to WebmasterWorld Guest from 54.145.173.36

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

PHP MySQL - simple edit for search code

   
4:43 pm on Oct 12, 2011 (gmt 0)



I use a PHP script that searches a database and returns the results. The HTML form contains a drop down list with two options (title or description) so the user can pick which column to search.

I want to add a third option so the user can search both the title and description columns, together.

Here is a code a code snippet that determines which column should be searched based on the selection made by the user:


if($_REQUEST['searchType'] == 'title')
$aWhere[] = " `ag`.`title` LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' ";
else
$aWhere[] = " `ag`.`description` LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' ";


I'm not very familiar with PHP or MYSQL so I do not know how to edit the code to add the third option.

I guess what I need is kinda like below but I don't think it's correct:


if($_REQUEST['searchType'] == 'title')
$aWhere[] = " `ag`.`title` LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' ";
elseif
$aWhere[] = " `ag`.`description` LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' ";
else
$aWhere[] = " `ag`.`description`,`title`LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' ";


Can someone please tell me what code I should be using?
7:35 pm on Oct 12, 2011 (gmt 0)



I tried the following but it does not work:


if($bSearch)
{
if($_REQUEST['searchType'] == 'title')
$aWhere[] = " `ag`.`title` LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' ";
elseif($_REQUEST['searchType'] == 'description')
$aWhere[] = " `ag`.`description` LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' ";
else
$aWhere[] = " `ag`.`title` LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' AND `ag`.`description` LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' ";
}
8:06 pm on Oct 12, 2011 (gmt 0)



This seems to work:


if($bSearch)
{
if($_REQUEST['searchType'] == 'title')
$aWhere[] = " `ag`.`title` LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' ";
elseif($_REQUEST['searchType'] == 'desc')
$aWhere[] = " `ag`.`description` LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' ";
else
$aWhere[] = " `ag`.`title` LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' OR `description` LIKE '%".mysql_real_escape_string($_REQUEST['searchWords'])."%' ";
}


Can someone tell me if the code looks OK or is there a better way?
6:12 am on Oct 13, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



yea, but you need to clean up your code. Build a function to clean up your inputs ... this one is just not enough.

$searchInput = mysql_real_escape_string($_REQUEST['searchWords']);
$searchType = $_REQUEST['searchType'];

if($bSearch)
{
if($searchType == 'title') {
$aWhere[] = " `ag`.`title` LIKE '%". $searchInput ."%' ";
} elseif($searchType == 'desc') {
$aWhere[] = " `ag`.`description` LIKE '%".$searchInput."%' ";
} else {
$aWhere[] = " `ag`.`title` LIKE '%". $searchInput ."%' OR `description` LIKE '%". $searchInput ."%' ";
}
}
6:12 am on Oct 13, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



yea, but you need to clean up your code. Build a function to clean up your inputs ... this one is just not enough.

$searchInput = mysql_real_escape_string($_REQUEST['searchWords']);
$searchType = $_REQUEST['searchType'];

if($bSearch)
{
if($searchType == 'title') {
$aWhere[] = " `ag`.`title` LIKE '%". $searchInput ."%' ";
} elseif($searchType == 'desc') {
$aWhere[] = " `ag`.`description` LIKE '%".$searchInput."%' ";
} else {
$aWhere[] = " `ag`.`title` LIKE '%". $searchInput ."%' OR `description` LIKE '%". $searchInput ."%' ";
}
}
7:13 pm on Oct 13, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Is there a reason you use arrays?

If it works for you, it works. A select statement is a string. So I like to concatenate. The way it could work is if some fields are present, build the where based on it (you have that already.) If nothing, it just selects all records. So

third option so the user can search both the title and description columns, together.


You'll note I SPECIFICALLY make my form names and DB fields different; don't reveal table or field names publicly.

<select name="searchType" id="SearchType">
<option value="">All fields</option>
<option value="1">Title</option>
<option value="2">Description</option>
</select>

$where = null;

$fields = array ('','title','description');

if ($empty($searchType)) {
$where .= " table.title like '%$term%' or table.description like '%$term%'";
}
else if ($searchType >0) {
$where .= $table.$fields[$searchType] " like '%term%'";
}

$query = "select * from table";
if ($where) { $query .= " where $where"; }

A caveat, you can extend this to contain radio buttons:

<input type="radio" name="wild" id="wild-exact" value="1" checked> Exact
<input type="radio" name="wild" id="wild-start" value="2"> Starts with
<input type="radio" name="wild" id="wild-exact" value="3"> Ends with
<input type="radio" name="wild" id="wild-exact" value="4"> Anywhere

Then

$where = null;

$fields = array ('','title','description');
$compstarts = array ('', "='", " like '", " like '%", " like '%");
$compends = array ('', "'", "%'", "%'", "%'");

if ($empty($searchType)) {
$where .= " table.title " . $compstarts[$wild] . $term . $compends[$wild] . " or table.description " . $compends[$wild] . $term . $compends[$wild];
}
else if ($searchType >0) {
$where .= $table.$fields[$searchType] . " " . $compstarts[$wild] . $term . $compends[$wild];
}

$query = "select * from table";
if ($where) { $query .= " where $where"; }

So you'd have, for example,

select * from table ='$term'
select * from table like '$term%'
select * from table like '%$term'
select * from table like '%$term%'
9:28 pm on Oct 16, 2011 (gmt 0)



Thanks, everyone!

I will give the suggestions a try.