Well, seeing n rows or only one does not make you any happier, because you
can not issue a DELETE statement that will delete only (n-1) entries.
If your table is small enough, you may try to delete duplicates manually
with a graphical interface such as Access or DBTools. But test first, as it
may very well delete all n rows (in fact it SHOULD if it implements SQL
properly...)
Your PHP script should test first whether the proposed entry exists, with a
query like:
SELECT *
FROM my_table
WHERE unique_field = '{$_POST['unique_field']}'
(As a rule, you must validate thoroughly ALL entries before inserting into
your DB.)
you can test over if ( mysql_num_rows( $result) ) if there is already an
entry. In such case, redirect to your script with an appropriate error
message.
If you are reluctant to validate data, you can send the INSERT query and
interpret an error as an attempt to create a duplicate (provided you had
first created the appropriate UNIQUE index). Very dirty and not recommended.
HTH
Ignatius
____________________________________________
----- Original Message -----
From: "Marco Alting" <marco@premiummedia.nl>
To: <php-db@lists.php.net>
Sent: Wednesday, December 04, 2002 12:57 PM
Subject: Re: displaying duplicate records...
> What I ment was that when using the GROUP by, you will see one of the
> duplicates, and if you output the 'cnt' value you would see how many times
> it has a duplicate. But I want to see all duplicate records in a table, so
I
> can add a delete button to the row.
>
> If I have the email address as a PRIMARY i would very much like to know
how
> I would handle the input by the user when it is send to the database. I
> assume it will generate an error upon submission. How do I go about
letting
> the user know they have entered an already existing address?
>
>
> "Ignatius Reilly" <ignatius.reilly@free.fr> wrote in message
> 030801c29b8b$8bd88ee0$0100005a@server">news:030801c29b8b$8bd88ee0$0100005a@server...
> > I do not understand your first question.
> > Your query in effect shows duplicates.
> >
> > To prevent future duplicates, you can do it in two ways:
> >
> > 1. with MySQL:
> > create a unique index on what should be unique, most likely the email
(it
> > looks like your business logic implies one entry per email)
> > in fact you would not have this problem if you had defined email as
> PRIMARY
> > KEY
> >
> > But you will have to remove the duplicates first - an unpleasant job.
> > AFAIK, there is no easy way to do it with MySQL (or SQL in general). You
> > have to do it procedurally by calling the entire table row by row.
> >
> > 2. Procedurally:
> > validate user data before entry in the DB
> >
> > HTH
> > Ignatius
> > ____________________________________________
> > ----- Original Message -----
> > From: "Marco Alting" <marco@premiummedia.nl>
> > To: <php-db@lists.php.net>
> > Sent: Wednesday, December 04, 2002 11:47 AM
> > Subject: displaying duplicate records...
> >
> >
> > > I have a database which allows people to upload info and foto's.
There's
> a
> > > unique ID field, but some people tend to upload their info more than
> once
> > > (its a contest site). What I'm able to do is to see how may duplicates
> > there
> > > are using the following statement:
> > >
> > > $query="SELECT COUNT(*) as cnt, voornaam,achternaam,leeftijd,ID,email
> FROM
> > > modellen GROUP BY email HAVING cnt >1";
> > >
> > > But this only gives me numbers. Does anyone know how to display every
> > record
> > > that has multiple duplicate ?
> > >
> > > Or is there an elegant way to stop people from entering their info
more
> > than
> > > once?
> > >
> > >
> > >
> > > --
> > > PHP Database Mailing List (http://www.php.net/)
> > > To unsubscribe, visit: http://www.php.net/unsub.php
> > >
> > >
> >
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
