Strange problem using WHERE clause

Databases
1

Hello, I’m having a strange problem using an SQL SELECT statement that I’ve not experienced in all the times I’ve had to perform one before. Mabye I’m missing something because I haven’t done it in a few weeks so hopefully you will see what the problem is.

Essentially I just want to do a simple SELECT WHERE post_type is equal to ‘page’ to create a list of URL links.

function listPages() {
		
		$result = mysql_query("SELECT * FROM posts WHERE page_type='page'");
		$row = mysql_fetch_array($result);
		echo "<ul>";
		while($row = mysql_fetch_array($result)) {
				$pageID = $row['id'];
				$pageTitle = $row['title'];
				
				echo "<li><a href='" . BASE_URL . "/index.php?id=$pageID'>$pageTitle</a></li>";
			}
		
		echo "</ul>";
}

The problem is it isn’t returning a post. But the PHP itself I think is sound because if I replace “page_type” with “post_author” which requests a number, it returns rows just fine.

So I thought maybe it just wasn’t able to deal with how I’ve stored “page_type” (Varchar) but if I do “Post_id” which is itself a number, it won’t return anything. So it is boggling my mind, this kind of setup worked fine in the past but it won’t find a number like “post_id”?

I can only assume I’m missing something obvious because I’ve used this type of code fine before and it will return rows based on some items and not others, its just the first time I’ve had this problem and nothing obvious I can think of is working so I’m wondering if something is wrong with how I’ve set up the DB.

This is the “posts” DB structure.

And this is the overview. Its just a testing DB so it’s not properly populated. In this specific case I’m trying to get post 80 to show up as it is the only “page”

As an aside, I have tried selecting specific columns rather than * but it doesn’t make a difference.

2 
$pageID = $row['id'];
$pageTitle = $row['title'];

Your fields appear to be called post_id and post_title rather than the id and title referred to in the code.

3

well spotted, philip

the confusion can be blamed on the ill-advised convention of including the table name as a prefix in the column name

notice that it was not applied consistently to all the columns in the table

and that’s even more confusing, wouldn’t you say?

:cool:

4

Wasn’t that, that was a mistake when I was copying the code over.

Did find the problem however, it was because I had

$row = mysql_fetch_array($result);
        echo "<ul>";
        while($row = mysql_fetch_array($result)) {

I removed the first $row= and it started working. Not sure what the reason is behind that mind you but it is working now. I can only assume it worked when calling on posts by author because there were more than one post while calling it by post id or page type, there was only one of each.

@r937, what is the convention? I admit it gets confusing but seemed the most logical way to remember.

5

I do something similar to avoid reserved names. Why’s it bad?

6

it’s just noise – extra letters, longer names, more forest, trees harder to see

7

I removed the first $row= and it started working. Not sure what the reason is behind that mind you but it is working now. I can only assume it worked when calling on posts by author because there were more than one post while calling it by post id or page type, there was only one of each.

It was only functioning because there was more than row in the result set. However, what you had before always excluded the first row in the result set from being displayed. being there was a single result nothing was shown because the result cursor was moved to the 2nd row before the while the loop. Any place else that same code is being used is skipping the first row.

8

That makes a lot of sense. But since the line is logical in the context I didn’t notice there was two. Such a silly mistake.