Duplicate results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Perfidus
Forum Contributor
Posts: 114
Joined: Sun Nov 02, 2003 9:54 pm

Duplicate results

Post by Perfidus »

I'm queryng mysql database:

$result= mysql_query("SELECT * FROM $table" ,$link);
while ($row = mysql_fetch_array($result)){

And then I try to echo results:

echo "<br>";
echo $row['whatever'];
echo $row['whatever2'];
echo "<br>";
}

But I get the results repeted as much times as lines in my table!!
How can avoid that?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

that's exactly what you asked for... you may want to look into [mysql_man]LIMIT[/mysql_man]
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Normally for a SELECT statement you would also have a WHERE clause.

Code: Select all

SELECT * FROM TABLE WHERE column<10;
Another useful command if duplicate data exists is DISTINCT

Code: Select all

SELECT DISTINCT * FROM table;
The question you have to ask yourself however is why does the table contain duplicates ?
Perfidus
Forum Contributor
Posts: 114
Joined: Sun Nov 02, 2003 9:54 pm

Post by Perfidus »

I'm sorry I didn't explain myself pretty good.
Lets say I have this table:

num | weight | size | age |
----------------------------------
1 | 45 | 1,60 | 17 |
----------------------------------
2 | 56 | 1,78 | 19 |
----------------------------------
3 | 47 | 1,67 | 18 |
-----------------------------------

I try to get size and age from all the table, and I get them but like this:

1,60 - 17
1,60 - 17
1,60 - 17
1,78 - 19
1,78 - 19
1,78 - 19
1,67 - 18
1,67 - 18
1,67 - 18

Instead this:

1,60 - 17
1,78 - 19
1,67 - 18
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

DISTINCT should solve your problem. If all the columns are the same, only one row is returned rather than the duplicates. If only one column is different then DISTINCT doesn't work. There is syntax for DISTINCT ON but you would need to look that up. An alternative is to limit the returned rows to contain only the information required.

Code: Select all

SELECT DISTINCT weight,size,age FROM mytable;
This has the added advantage that the return size is smaller. (For complex selects it is noticable how much quicker returning only those values you need can be.)

Have you tried your SELECT yourself using SQL ? Ensure the SQL is correct first, then handle it with PHP.
Post Reply