Help with Distinct causing error!

PHPAnx

I have this select:

$sql="SELECT DISTINCT toursArtist,toursDate,toursCity,toursState,toursVenue FROM tours WHERE $show LIKE ('%".addslashes($search)."%') ORDER BY toursDate LIMIT $offset, $sqllimit";

The distinct is in place because there are often people entering the same info twice by mistake, so this eliminates the duplicates from my results.

Below I use this code to echo my fields:

 $id = $row["toursID"];
 $artist = $row["toursArtist"]; 
 $date = $row["toursDate"]; 
 $city = $row["toursCity"]; 
 $state = $row["toursState"]; 
 $venue = $row["toursVenue"];

echo "blah blah blah $id $artist and so on, you see?";

Problem is, by using the Distinct code, my $id field will not work. I cannot add it to the distinct as then of course the function will no work as the toursID field is an auto field to identify each row uniquely.

So how do I get around this??? I must be able to call the $id in the echo!

Thanks

Sxooter

You didn't mention the database you're using, so I'll assume Postgresql... :-)

If not, then you'll have to look elsewhere for the answer.

For pgsql, you can use distinct ON like so:

SELECT DISTINCT ON (toursArtist,toursDate,toursCity,toursState,toursVe
nue) id toursArtist,toursDate,toursCity,toursState,toursVe
nue FROM tours WHERE $show LIKE ('%".addslashes($search)."%') ORDER BY toursDate LIMIT $offset, $sqllimit

PHPAnx

It's actually a MySQL DB.

Lars_Berg

Why don't you solve the real problem, i.e. that you allow duplicate data in the table.

PHPAnx

Originally posted by Lars Berg
Why don't you solve the real problem, i.e. that you allow duplicate data in the table.

How would I not allow this?? When literally hundreds are submitting data, how is it possible to keep everyone from entering duplicate data sometimes. This is not a static database. This database changes daily with thousands of field additions.

Lars_Berg

You can define a unique constraint on the columns.

PHPAnx

Originally posted by Lars Berg
You can define a unique constraint on the columns.

I don't understand what you are referring to at all??

The data for the DB is sent via record labels, tour managers, venues and bands. So often duplicate data can be sent from a whole number of different places. There is no way I can go through thousands of emails or direct form submissions to look for dupes. Unless you know??

Lars_Berg

You can read about constraints here

http://www.mysql.com/doc/en/ALTER_TABLE.html

Any attempt to insert duplicate data will be rejected by Mysql if you have a unique constraint.