[SOLVED] pulling my hair out over a mysql query

[evanct]

so i have this pretty simple procedure, getting some mysql rows and formatting them as html:

 

<?php
$sql="SELECT * FROM posts WHERE parent=0 AND 'status'!='deleted' ORDER BY votes DESC LIMIT $page, $limit";
	$result=mysql_query($sql);
	while ($row=mysql_fetch_assoc($result)) {
		$posts[$row['post_id']]=$row;
	}

	$this->content.="<div id='content'><ul>";
	foreach ($posts as $post) {
		$this->makePost($post,1);
	}
	$this->content.="</ul>";
?>

 

but for no apparent reason it's ignoring the WHERE AND clause of the query. the rest of it works fine - so it's getting every row regardless of the parent and status values, ordering them by votes and limiting them by the given variables. anyone know what the problem might be? here is the whole table:

 

CREATE TABLE `posts` (
`post_id` int(11) NOT NULL AUTO_INCREMENT,
`post_text` text NOT NULL,
`file` varchar(80) DEFAULT NULL,
`votes` int(11) DEFAULT NULL,
`parent` int(11) DEFAULT '0',
`children` int(11) DEFAULT '0',
`auth_ip` varchar(40) NOT NULL,
`auth_name` varchar(40) DEFAULT 'Anonymous',
`status` varchar(20) DEFAULT NULL,
`thread` int(11) DEFAULT '0',
PRIMARY KEY (`post_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1

[roopurt18]

In MySQL strings are enclosed in single quotes.

 

Thus you have as part of your WHERE clause 'status'!='deleted', which translates to: where the STRING 'status' is not equal to the STRING 'deleted'.

 

Since they are never equal, it's as if you said WHERE TRUE, which is true for every row.

 

You enclose fields in backticks (`, or the unshifted tilde).

[evanct]

oh. I had tried changing the single quotes, to status='deleted' and status=deleted, but they just invalidated the entire query. hadn't considered backticks.

[evanct]

..okay, now it isn't getting any rows.

 

"SELECT * FROM posts WHERE `parent`=0 AND `status`!='deleted' ORDER BY votes DESC LIMIT $page, $limit"

 

not getting any mysql errors. and there are several rows where parent is 0 and status is not 'deleted'.

[roopurt18]

Assuming that $page and $limit are valid, the query looks ok to me.  Are you sure you're not getting any errors?  What is your error handling code?

 

What is your script doing?  Is it working at all?  Blank page?

[evanct]

for error handing i just have an "or die(mysql_error());". the rest of the script is working fine. i'm not even getting any Notices.

 

echoing $sql gives me "SELECT * FROM posts WHERE `parent`=0 AND `status`!='deleted' ORDER BY votes DESC LIMIT 0, 10", so there's nothing wrong there.

[roopurt18]

Have you run that in phpMyAdmin or a similar tool to ensure it's working as expected?

 

Did you remember to connect to the database?

 

Still need to see more code.

[evanct]

I ran it in phpmyadmin and... nothing. zero rows.

 

I'm certain it's connected to the database, no question there

 

i'm pretty much given you all the relevant code. here's the entire function:

 

<?php
function makeIndex() {
	global $posts;

                //----------GET NUMBER OF PAGES-------------//

	if (isset($_GET['page'])) {
		$page=$_GET['page'];
	} else {
		$page=0;
	}
	$limit=PER_PAGE;
	$sql="
	SELECT 
		* 
	FROM 
		posts 
	WHERE 
		`status`!='deleted' 
	ORDER BY 
		votes 
	DESC";
	$result=mysql_query($sql) or die(mysql_error());
	$numrows=mysql_num_rows($result);
	$pages=intval($numrows/$limit);
	if ($numrows%$limit) {
		$pages++;
	}
	$current=($page/$limit)+1;
	if ($pages<1) {
		$total=1;
	} else {
		$total=$pages;
	}

	//-----------MAKE POSTS---------//

	$sql="SELECT * FROM posts WHERE `parent`=0 AND `status`!='deleted' ORDER BY votes DESC LIMIT $page, $limit";
	echo $sql;
	$result=mysql_query($sql) or die(mysql_error());
	while ($row=mysql_fetch_assoc($result)) {
		$posts[$row['post_id']]=$row;
	}

	$this->content.="<div id='content'><ul>";
	foreach ($posts as $post) {
		$this->makePost($post,1);
	}
	$this->content.="</ul>";

	//--------------PAGE LINKS-----------//

	for ($i=1; $i <= $pages; $i++) {
		$ppage = $limit*($i - 1);
		if ($ppage == $page) {
			$this->content.="<b>[".$i."]</b> "; 
		} else {
			$this->content.="<a href=".$_SERVER['PHP_SELF'].
			"?page=$ppage&limit=$limit>[".$i."]</a>";
		}
	}
	$this->content.="</div>";
	return $this->content;
}
?>

 

 

[roopurt18]

If you ran it in phpMyAdmin and got zero rows and zero errors, then there's only two conclusions I can draw:

1) Your query is syntactically correct

2) There are no rows that match your WHERE criteria

[evanct]

see for yourself:

 

[roopurt18]

You have misunderstood, or not been told, about how MySQL treats NULL.

 

In most SQL database system, NULL is not equal to anything, not even to itself.  Further, performing operations against NULL values such as math, concatenation, date operations, or, in your case, comparisons, will always result in NULL.

 

So your for those records where `status` is null, your WHERE clause is becoming:

WHERE `parent`=0 AND `status`=NULL -- leads to

WHERE `parent`=0 AND NULL -- leads to

WHERE NULL

 

Since NULL is not equal to anything and it is certainly not equal to TRUE, the query does not return them.

 

Try this:

SELECT * FROM posts WHERE `parent`=0 AND (`status`!='deleted' OR `status` IS NULL) ORDER BY votes DESC LIMIT 0, 10

[roopurt18]

Alternatively you could give `status` a default value, such as 'good'

[evanct]

well sheeyit, there goes a few hours of my life, lost to a simple misunderstanding. again. thanks, it works now.

[roopurt18]

No problem.

 

Just remember this simple step for future reference:

If you ran it in phpMyAdmin and got zero rows and zero errors, then there's only two conclusions I can draw:

1) Your query is syntactically correct

2) There are no rows that match your WHERE criteria

 

You always have tools to help you locate problems.  These tools are typically front-ends to the technologies that we write specialized applications for.  In most cases we can be reasonably confident that the tools (php, pgAdmin, phpMyAdmin, jslint, validators, etc) are solid and work correctly.

 

In other words, it's much more likely there is a problem with YOUR | OUR code than there is MySQL, PHP, etc.

 

Whenever you have a problem, try and break it down into solvable steps.  In your case you had a query that appeared to not work.

 

Whenever I'm faced with a similar situation (a non-working query), I immediately run it directly in the database.  By doing so I accomplish the following:

1) I remove all of my PHP code, logic, and settings from the problem.

2) I immediately see if the query has errors (from the database front-end)

3) If the query runs without errors, I immediately see the results I should be getting

4) If the results are not what I expected, then I know my query is wrong, although it is syntactically correct.

 

So in your case the query worked (no errors) in phpMyAdmin and returned an empty result set.  At that point you don't even need to look for errors in your PHP code because the query itself is broken.

 

Once the query is fixed in phpMyAdmin, you can put it back in your code.

 

And then you are likely to encounter more errors that may be PHP-related, at which point you need to use more tools to find the problems.