Jump to content
WesleyA

find identical input with php or mysql

Recommended Posts

In what way is it identical? Do you mean to find two rows that have all the columns the same or just some of the columns?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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.

Share this post


Link to post
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?

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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?

Share this post


Link to post
Share on other sites

You can use this if you want to find out how many times each value appears:

 

SELECT name, COUNT(*) FROM table GROUP BY name
That will print each name and how many times it appears.

Share this post


Link to post
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?

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...