Queries not running when & is involved

I don't exactly know what I have to do, but when I search for a value with an ampersand, it returns nothing. For other values, it returns all matching rows correctly. I set up this function that basically gets all values from the database, then tries to find the match for the input variable. Here is the code:

function check_department($v)
{	
	$query = "SELECT * FROM academicprograms";
	$result=mysql_query($query);
	if (!$result) 
	{
    	echo("Could not successfully run database query");
    	return;
	}
	$row = mysql_fetch_assoc($result);
	while ($row = mysql_fetch_assoc($result)) 
	{
		if (in_array($v, $row))
		{
			return $v;
		}
	}
	echo "Can't find the value: ".$v;
	return false;
}

PHP:

If $v is a string like "Digital Forums", it matches and returns. If $v is something like "Digital & Forums", it prints that it cannot find the value.

 

Is it stored as & or & ?

Also, you'll always skip the first result using this:

$row = mysql_fetch_assoc($result);
    while ($row = mysql_fetch_assoc($result))

PHP:

Take out $row = mysql_fetch_assoc($result); to make sure you get all your results back.

 

SELECT
	myColumn1,
	myColumn2
FROM
	myTable
WHERE
	myColumn1
REGEXP
	'^Digital (&|and|&) Forums$'

Code (markup):

 

Except he's comparing a string to every field in the table, so that'd be

SELECT
	*
FROM
	myTable
WHERE
	myColumn1
REGEXP
	'^Digital (&|and|&) Forums$'
OR 	myColumn2
REGEXP
	'^Digital (&|and|&) Forums$'
OR 	myColumn3
REGEXP
	'^Digital (&|and|&) Forums$'
OR 	myColumn4
REGEXP
	'^Digital (&|and|&) Forums$'
OR 	myColumn5
REGEXP
	'^Digital (&|and|&) Forums$'

Code (markup):

etc. and instantly breaks when a new field is added. I assume that's why he's using in_array() anyway.

 

Is he really? That sounds somewhat redundant. My example was a simplified version of what you need to do. You also have to take into consideration that people may also type and instead of & as well. The MySQL I wrote will consider that possibility.

 

	$aItems = array('Digital & Forums');

	foreach($aItems as $aItem)
	{
		if(preg_match('/^Digital (&|and|&) Forums$/i', $aItem))
		{
			return true;
		}
		else
		{
			return false;
		}
	}

PHP:

 

I'm not sure if that's definitely his intention, but it makes sense to me - if it's some sort of CMS then he could search title and content in one go, and if he then redesigned the structure he could have a description field, and it'd automatically search that without modifying the check_department() function.

It's not the way I'd do it - it'll be damn slow when there are lots of records, but I quite like the lateral thinking

 

Thanks, I've played around so much with the code, I didn't even notice this.

So regular expressions is the way to go here? Basically, the reason I'm doing this is for some sort of data check. I'm using GET variables on the page, and don't want to be susceptible to SQL injection attacks. So, this function looks at all the data currently in the db, sees if the input matches, and if not, throws it out. If it does, then I do data lookups and basically continue the script. The server is running on PHP 4.1 so some of the newer more secure functions aren't available to me. This table will always stay fairly small, it's a listing of degrees offered at a college. So, I'm not really worried about performance as much as I am security.