Using DISTINCT causes problems.

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
wmcneely
Forum Newbie
Posts: 2
Joined: Sun Jul 06, 2003 5:30 pm

Using DISTINCT causes problems.

Post by wmcneely »

Hello everyone,

I am attempting to populate a drop down field with info from a database. All works perfectly until I add distinct in. The data is coming from a database that has 333 records, with 2 distinct years. When I remove the DISTINCT paramer I get all records. When I include the DISTINCT parameters I get one record. Havent been able to figure this on out yet. Any help would be appreciated, as I am sure that it is something simple that I have overlooked.

Here is the code in question.

<SELECT ID="Forms Combo Box1" NAME="cmbYear"> <?php

mysql_connect("*********", *********, *********)
or die("Could not connect to database");
mysql_select_db("nfl")
or die("Could not select database");
if(!isset($query) || empty($query))
{$query = "SELECT DISTINCT game_season FROM game_info ORDER BY game_season";}
$result = mysql_query($query) or die(mysql_error());

while($year=mysql_fetch_row($result)){
print "<option value='$year[0]'>$year[0]<br>";}
?>
</SELECT>
:?:
User avatar
trollll
Forum Contributor
Posts: 181
Joined: Tue Jun 10, 2003 11:56 pm
Location: Round Rock, TX
Contact:

Post by trollll »

Hmm... You could try using something like "SELECT game_season FROM game_info ORDER BY game_season GROUP BY game_season" (I think I have the correct order of the last two options). Definitely not as efficient, but it should work if DISTINCT gives you a hard time.
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post by Jean-Yves »

ORDER BY comes after GROUP BY, though I'm not sure whether all rdbm's enforce this. But trolll is correct in that using GROUP BY should eliminate duplicates (as long as you don't also group by a unique value field, such as the primary key)
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

Can you check the mysql_num_rows($result);
If that count is 1, the problem is the mysql syntax, else it's the program itself.
wmcneely
Forum Newbie
Posts: 2
Joined: Sun Jul 06, 2003 5:30 pm

Thanks for all of your assistance.

Post by wmcneely »

Unfortuneately it did not work. When using GROUP BY and ORDER BY I received the same information as I did with DISTINCT.

When doing a record count on the return I got the expected 2 records.

When I ran the same code outside of the drop down loading routine I got what I expected, so I changed my row calls to array calls - did not help. I even went as far as changing the code to SELECT g_s as A, SELECT g_s as B and referenced the drop down load accordingly to no avail.

So after all of this I added a dummy record into the database with a year earlier than 2002 (I know that this is absolutely not the thing to do...but) and saw what I was originally looking for, but not what would be expected considering...

Fortunately, this is not a really serious database, in fact it is just so that my dispersed family can stay in touch picking football games, so the dummy year won't have a major effect at this point in putting this program together.

One day when I have hours of extra time I will look into this problem a little further.

Thanks again for all of your help and for reading my novella of an answer.

Regards! :lol:
User avatar
Johnm
Forum Contributor
Posts: 344
Joined: Mon May 13, 2002 12:05 pm
Location: Michigan, USA
Contact:

Post by Johnm »

Did you try using UNIQUE instead of DISTINCT,
Just a thought. They do similar things but they do them differently.


John M
Post Reply