Very simple query returns duplicate entries : (

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Very simple query returns duplicate entries : (

Author

Topic

BinaryGirl23
Starting Member

2 Posts

Posted - 2009-07-22 : 15:37:02

What's up everyone,

I will admit I don't know as much about SQL as I should (I'm more of a front-end developer), but I cannot for the life of me figure out why such an incredibly simple query returns duplicate results, every time. Basically all I want is to query my single table (not joined to anything else), pull the last 5 results, and put the results into a little window.

Basically the sql query looks like this:


SELECT DISTINCT
*
FROM property
ORDER BY
property_id DESC
LIMIT 0, 5

See, I told you guys, really high-end stuff here.

Now the While loop:


//start my loop
while ($row = mysql_fetch_array($query)) { 

//define my variables
$sub_property_id = $row['property_id'];
$sub_property_title= $row['property_title'];
$sub_property_sqft= $row['property_sqft'];

//define my array
$community .= '
 [HTML stuff here] '

//return results
echo $community;
}

I'm positive I'm doing something wrong here but for the life of me I can't figure it out. I've tried calling GROUP BY on my query, I've checked and double checked my table, primary key is set to "property_id" with 'unique' set (there are no duplicates in my table anyway).

I've searched through this forum and probably a dozen others over the last 4 days to no avail. So is this just a SQL bug that some people suggest or is this something I'm doing wrong.

To be more specific as how my output looks, it kind of looks like this:

Row-X

Row-X
Row-Y

Row-X
Row-Y
Row-Z

It's almost like the while loop takes the current row, then duplicates it infinite times and appends it to each loop iteration.

Server says: MySQL client version: 5.0.27

"A materialist would, in my way of thinking, be a person who loves material and therefore respects it, and enjoys it. We don't. We are a people who hate material and are devoting ourselves to the abolition of it's limitations." - Alan Watts

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-22 : 15:43:26

LIMIT?

Is this mySQL?

In any Case, SELECT * is just bad form

List the columns you want, then try it.

Anything in the list of * that is even slightly different will be DISTINCT

What's the Primary Key of the table? Those are the only guarenteed thing to be DISTINCT

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

BinaryGirl23
Starting Member

2 Posts

Posted - 2009-07-22 : 16:29:05

Thanks Brett, point taken. Unfortunately it still gives me the same results.

The primary key is "property_id" and the query looks like this now:


$sql = "
SELECT DISTINCT
property_id, property_title, property_sqft, features_bath, features_bed
FROM property
ORDER BY
property_id DESC
";

As far as I was aware, LIMIT is a valid mysql syntax:

[url]http://php.about.com/od/mysqlcommands/g/Limit_sql.htm[/url]
[url]http://www.1keydata.com/sql/sql-limit.html[/url]

...but it doesn't work anyway. Usually I have to set fixed parameters to break the While loop otherwise it'll return duplicate entries for as many actual rows I have in the table (which is bad).

"A materialist would, in my way of thinking, be a person who loves material and therefore respects it, and enjoys it. We don't. We are a people who hate material and are devoting ourselves to the abolition of it's limitations." - Alan Watts

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources