homepage Welcome to WebmasterWorld Guest from 54.196.63.93
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PHP MySQL - simple edit for search code
jeffshead



 
Msg#: 4373680 posted 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?

 

jeffshead



 
Msg#: 4373680 posted 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'])."%' ";
}

jeffshead



 
Msg#: 4373680 posted 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?

Habtom

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4373680 posted 6:12 am on Oct 13, 2011 (gmt 0)

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 ."%' ";
}
}

Habtom

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4373680 posted 6:12 am on Oct 13, 2011 (gmt 0)

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 ."%' ";
}
}

rocknbil

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



 
Msg#: 4373680 posted 7:13 pm on Oct 13, 2011 (gmt 0)

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%'

jeffshead



 
Msg#: 4373680 posted 9:28 pm on Oct 16, 2011 (gmt 0)

Thanks, everyone!

I will give the suggestions a try.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved