[PHP] Why does this SQL not work?

Conrad11 · 10 Sep 2007 at 22:02

Hi there,

This sql statement seems to just return "category" despite the fact its a SMALLINT(3) field.

I have checked spelling and variables so its nothing to do with that.

Code:

$result = mysql_query("SELECT 'id','name','link','category' FROM software ORDER BY 'id' DESC LIMIT $offset,$rowsPerPage") or die('Error1, query failed');

if ( mysql_num_rows($result) > 0 ) {
	while($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		WriteRow($row['id'],$row['name'],$row['link'],$row['category'],$tabs);
	}

Code:

function WriteRow($id,$name,$link,$category,$tabs){
	echo $category;
}

Why is that? and how do I fix it?

Thanks.

Inquisitor · 10 Sep 2007 at 22:05

Single quotes are for quoting values; use backticks (`) for quoting identifiers. As it is you're just selecting (and ordering by) static strings.

Code:

SELECT `id`, `name`, `link`, `category` FROM `software` ORDER BY `id` DESC LIMIT '$offset', '$rowsPerPage'

Conrad11 · 10 Sep 2007 at 22:07

Inquisitor said:
Use backticks (`) to quote identifiers in SQL; as it is you're just selecting strings.

Code:

SELECT `id`, `name`, `link`, `category` FROM `software` ORDER BY `id` DESC LIMIT $offset, $rowsPerPage

LOL - knew i was rusty - didn't think i was that rusty.

Thanks mate.

Caged · 10 Sep 2007 at 22:07

Or just don't bother quoting it at all

Conrad11 · 10 Sep 2007 at 22:19

Whilst I am here, this is ok to do isn't it? (security):

Code:

if(intval($_GET['category'])){
	$getCat = "WHERE `category` = " . intval($_GET['category']) . " ";
}else{
	$getCat = "";
}

...and then inserting that into the middle of an SQL statement.

Inquisitor · 10 Sep 2007 at 22:31

Yup. It's safe as long as there's no chance of characters that could alter the structure of the statement finding their way in, which intval() prevents