Page 1 of 1

switch statement queries

Posted: Thu Sep 21, 2006 10:56 pm
by bimo
Hi. I'm having a small problem and I'd appreciate it if anyone could help.

I have a form that searches a database for info (I'm pretty sure this isn't a database issue, though, because the returned query runs smoothly from MySQLQueryBrowser). The form has a select box for the type of info the user is looking for (artist, album, or label) and a textfield for the search string. It's an AJAX suggest form so there is no submit button.

My problem is that when 'artist' is selected the query runs perfectly well but when either of the other two search types are selected it doesn't. I have the php returning the built query with every keypress and I have run each independantly and they work fine.

I'm not sure what I'm missing and I hope that someone had sharper eyes than I.

Here's the relevant code:

Code: Select all

$st = $_GET['st'];
		switch($st) 
		{
		  case "artist":
			$query = "SELECT distinct 
						artist 
					FROM 
						recordings 
					WHERE 
						artist LIKE '".$searchString."%' 
					ORDER BY artist ASC
					";
			break;
		  case "title":
			$query = "SELECT distinct 
						title 
					FROM 
						recordings 
					WHERE 
						title LIKE '%".$searchString."%' 
					ORDER BY title ASC
					";
			break;
		  case "label_year":
			$query = "SELECT distinct 
						label_year
					FROM 
						recordings 
					WHERE 
						label_year LIKE '".$searchString."%' 
					ORDER BY label_year ASC
					";
			break;
		}
		print("query: ".$query);
		$sql = mysql_query($query);
So in the above, the first case works and the second two don't.

I'd really appreaciate any help.

Thanks,

b

Posted: Thu Sep 21, 2006 11:11 pm
by aaronhall
It looks like you missed some percent signs in the queries. Here is some simplified code that produces the exact same results, only about 15 lines shorter:

Code: Select all

<?
$st = $_GET['st'];
$searchEscaped = mysql_real_escape_string($searchString);

if(in_array($st, array('artist', 'title', 'label_year'))) {
	$query = "SELECT DISTINCT $st FROM recordings WHERE $st LIKE '%" . $searchEscaped . "%' ORDER BY $st ASC";
	echo 'query: ' . $query;
	$sql = mysql_query($query);
} else {
	echo 'invalid search field';
}
?>
ps: Always make sure that you are escaping user input when you print it out or use it in a mysql query

Posted: Thu Sep 21, 2006 11:17 pm
by bimo
Yeah, that makes the queries much neater. thanks.

I figured out why it wasn't returning anything, though. I had the wrong array key for the returned array.

Thanks

Posted: Thu Sep 21, 2006 11:24 pm
by aaronhall
No problem -- as long as we're on the topic, you might check out MySQL's full text searching capabilities to return more relevant results: http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html

Posted: Fri Sep 22, 2006 12:53 am
by bimo
will do, thanks,

Posted: Fri Sep 22, 2006 6:30 am
by Mordred
Additionally I think it is a good idea to escape or remove % and _ as well (with addcslashes after mysql_real_escape).