SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jul 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question :::Very Simple Query Returning Infinite Duplicate Results

    What's up Everyone,

    So, I'm new to this forum and I'm probably not as savvy with SQL as I ought to be (I'm more of a front end developer) but I'm doing something wrong with a very simple query and I can't for the life of me figure out what exactly it is.

    Basically, all I want to accomplish here is to pull the last 5 results from a single table, and display them into a little php box. To do this, I'm using a simple query:

    Code SQL:
    $sql = "
    SELECT DISTINCT
    property_id, property_title, property_sqft, features_bath, features_bed
    FROM property
    ORDER BY
    property_id DESC
    LIMIT 0, 5
    ";

    And then this returns my results:

    Code PHP:
    //set the query
    $query = mysql_query($sql); 
    //start while loop for each row
    while ($row = mysql_fetch_array($query)) { 
    //define variables
    		$sub_property_id 				= $row['property_id'];
    		$sub_property_title	 		= $row['property_title'];
    		$sub_property_sqft			= $row['property_sqft'];
    		$sub_features_bed			= $row['features_bed'];
    		$sub_features_bath			= $row['features_bath'];
    		$community .= ' [php stuff here]
    ';
    //return results
    echo $community;

    I didn't think I was doing anything unusual but the results will return like this:

    Row 1

    Row 1
    Row 2

    Row 1
    Row 2
    Row 3

    ...and onward for as many 'actual' rows as I have. I've tried pretty much every variant of GroupBy or Distinct and I've searched this forum and many others for similar problems but I'm starting to think maybe it's in the way that I'm having the results returned via my while loop but I really am lost at this point. The table itself definitely does not have any duplicate results, and property_id is the unique primary key.

    My server is using MySQL client version 5.0.27. What do you guys think could be going wrong here?

    Also, the LIMIT function doesn't work at all, it just returns all the results, is that a SQL error or is it part of whatever it is that I'm doing wrong?

    Cheers,

    Catherine

    Edit: Right after I hit "post thread" I realized that my error is because I didn't clear the variable after each loop, which became quite obvious in hindsight ; )
    Last edited by BinaryGirl23; Jul 27, 2009 at 11:14. Reason: Author is silly

  2. #2
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think it's a PHP problem, not MySQL. The issue lies in your $community variable, but since you commented out the good stuff, I can't tell you what it is exactly.

    Try closing your while loop before you echo and see if that gives you what you wanted.
    MySQL v5.1.58
    PHP v5.3.6

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by BinaryGirl23 View Post
    So, I'm new to this forum and I'm probably not as savvy with SQL as I ought to be (I'm more of a front end developer) but I'm doing something wrong with a very simple query
    welcome to da wonderful world of SQL

    your query is fine, except DISTINCT is expensive and quite unnecessary here

    so it's a php problem

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

  4. #4
    SitePoint Member
    Join Date
    Jul 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys, I do appreciate it. I think actually writing out my dilemma helped me isolate the problem (which of course was that the while loop kept appending new results to $community and thus creating a larger and larger variable each time - after I simply cleared the results after each loop, the result worked fine.)

    The "LIMIT 0,5" still doesn't work but I'm just breaking the operation after my "$counter" variable reaches 5 loops.

    Thanks for your help guys : )


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
  •