Jump to content

find identical input with php or mysql


WesleyA

Recommended Posts

like this:

 

unique number ------- column 1----------column 2 -----------

1 John Doe

2 Mark Holmes

3 Aly Holmes

 

Holmes is then a double input in one column.

 

I would like to use mysql to find these doubles and the delete the entire record.

Link to comment
Share on other sites

But, but! They are not known as a duplicate record, one is Mark Holmes, Aly Holmes, you could have multiple peoples with surname Holmes, many with Smith, multiple wiith same forename and surname, the duplicates to look for would be email, mobile phone compared with forename, surname.

Link to comment
Share on other sites

So you want to delete any records that have the same last name as another record. Of the two records, Mark Holmes and Aly Holmes, which one do you want to delete and why?

Link to comment
Share on other sites

I found different techniques online.

 

One is with SELECT DISTINCT. The result is an output of all unique fields from a specified column . The technique makes you create a new table with only these unique column fields.

 

I would like to use these commands but then without having to make a new table. Maybe I decide to do make it with a new table and then change the new name into the old name.

 

But if there is an easier way I would like to do that.

 

@dsonesuk : I realize now it is not a duplicate record but a duplicate field. I explained that wrong: :facepalm:

 

It's not I want to create a phonebook with only appearing single occurences of 1 family name :lol:

Edited by WesleyA
Link to comment
Share on other sites

But SELECT DISTINCT usually using GROUP BY only gathers a single distinct value, from a group of identical values from specific table/tables, it does not create a new table. You would still to compare with another field containing maybe phone, moble numbers, email, or if avaiable, customer or company id.

Link to comment
Share on other sites

I use for instance this in the sql console

     CREATE TABLE temp SELECT DISTINCT col1, col2, col3, col4, col5 FROM my_table GROUPED BY col1;

After that I drop my_table

      DROP TABLE my_table; 

then I use ALTER TABLE and RENAME like this

     ALTER TABLE temp RENAME TO my_table

But I need to make a php script of it.

 

so my_table is in my case $var

 

How should the query look like then?

Link to comment
Share on other sites

@ justsomeguy

 

What I want is different.

 

I noticed an important difference between MYSQL punctuation and PHP. Am I right that MYSQL in this way differs from PHP?

 

Why is this? And how should I write the code above in the former posting; in PHP?

Link to comment
Share on other sites

Why are you creating dropping renaming tables? All the data you require is stored in one table and striped down to distinct grouped data, required in the SQL of that one table.

 

You then loop through that striped down data to produce required content.

 

I don't see why you would risk losing possibly important data that you could lose using your method.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...