SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Strange problem using WHERE clause

    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.


    Code PHP:
    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. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code PHP:
    $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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wasn't that, that was a mistake when I was copying the code over.

    Did find the problem however, it was because I had

    Code PHP:
    $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. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the confusion can be blamed on the ill-advised convention of including the table name as a prefix in the column name
    I do something similar to avoid reserved names. Why's it bad?
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    it's just noise -- extra letters, longer names, more forest, trees harder to see
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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.
    The only code I hate more than my own is everyone else's.

  8. #8
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    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.
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •