MySQL Forums
Forum List  »  PHP

Re: TEXT SEARCH
Posted by: Roland Bouman
Date: July 31, 2005 03:02AM

Hi! I can't seem to view you page (404 page not found). Maybe a typo in your link? Anyway, you can do it all in one query. You set up a query string that selects all the rows you want, without any extra criteria.

$query = 'SELECT * FROM sometable';

Then, you check if any of your criteria fields are set, and use that information to generate a WHERE clause. You can do this anyway you like

if (isset($_REQUEST['name'])
|| isset($_REQUEST['gene'])
|| isset($_REQUEST['organism'])
){
$count = 0;
$where = '';

//do the name field
$fieldname = 'name';
if (isset($_REQUEST[$fieldname])){
$where .= '\n'.($count==0?'WHERE':'AND').' '
. $fieldname." = "
."'".
.$_REQUEST[$fieldname]
."'"
;
$count++;
}
//do the gene field
$fieldname = 'gene';
if (isset($_REQUEST[$fieldname]) && $_REQUEST[$fieldname]!=''){
$where .= '\n'.($count==0?'WHERE':'AND').' '
. $fieldname." = "
."'".
.mysql_real_Escap_string($_REQUEST[$fieldname])
."'"
;
$count++;
}
//do the x field
...
$query .= '\n'.$where;
}

Then, you process your query as usual. Of course, now you are repeating the bit of code that adds the criterion for a single field. You could put that in a separate function. Or you could put your fieldnames in an array, and loop that array to avoid this repetition. Thats up to you.

One final note, youll notice that we simply put the literal value of the request right into the query string. This makes you vulnerable to sql injection (google it!). Also, you must be aware that if your user inserts some nasty ' (single quoute_ character) in the search value, your query string would become invalid sql, nut we take care of that by using the mysql_real_escape_string call.


Good luck!

Options: ReplyQuote


Subject
Written By
Posted
July 29, 2005 11:41AM
Re: TEXT SEARCH
July 31, 2005 03:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.