switch statement queries

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

switch statement queries

Post 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
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post 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
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post by bimo »

will do, thanks,
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

Additionally I think it is a good idea to escape or remove % and _ as well (with addcslashes after mysql_real_escape).
Post Reply