Page 1 of 1

Using DISTINCT causes problems.

Posted: Sun Jul 06, 2003 5:30 pm
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>
:?:

Posted: Sun Jul 06, 2003 8:24 pm
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.

Posted: Mon Jul 07, 2003 3:50 am
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)

Posted: Mon Jul 07, 2003 4:12 am
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.

Thanks for all of your assistance.

Posted: Wed Jul 09, 2003 8:49 pm
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:

Posted: Thu Jul 10, 2003 7:24 am
by Johnm
Did you try using UNIQUE instead of DISTINCT,
Just a thought. They do similar things but they do them differently.


John M