WesleyA Posted November 30, 2015 Share Posted November 30, 2015 Hi I'm looking for php/mysql code to find identical input in a mysql database column. Link to comment Share on other sites More sharing options...
Ingolme Posted December 1, 2015 Share Posted December 1, 2015 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? Link to comment Share on other sites More sharing options...
WesleyA Posted December 2, 2015 Author Share Posted December 2, 2015 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 More sharing options...
dsonesuk Posted December 2, 2015 Share Posted December 2, 2015 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 More sharing options...
Ingolme Posted December 3, 2015 Share Posted December 3, 2015 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 More sharing options...
WesleyA Posted December 3, 2015 Author Share Posted December 3, 2015 (edited) 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: It's not I want to create a phonebook with only appearing single occurences of 1 family name Edited December 3, 2015 by WesleyA Link to comment Share on other sites More sharing options...
dsonesuk Posted December 3, 2015 Share Posted December 3, 2015 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 More sharing options...
WesleyA Posted December 3, 2015 Author Share Posted December 3, 2015 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 More sharing options...
justsomeguy Posted December 3, 2015 Share Posted December 3, 2015 You can use this if you want to find out how many times each value appears: SELECT name, COUNT(*) FROM table GROUP BY nameThat will print each name and how many times it appears. Link to comment Share on other sites More sharing options...
WesleyA Posted December 4, 2015 Author Share Posted December 4, 2015 @ 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 More sharing options...
dsonesuk Posted December 4, 2015 Share Posted December 4, 2015 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now