Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
| Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 19th, 2004, 09:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default Detecting duplicate rows.

Here's a question for you SQL heads...

I have a fairly mucky database I've inherited, here, with lots of duplicate rows. I want not only to be able to remove the duplicates, but to provide a means for the client I'm working for to be able to search for, and remove these duplicates in the future. I'm doing a self join, thus:

SELECT t1.id, t2.id, t1.name FROM table AS t1 LEFT JOIN table AS t2 ON t1.name=t2.name AND t1.id<>t2.id AND t2.id IS NOT NULL;

This works, to an extent (it spots duplicates), but my 'IS NOT NULL' clause isn't working. It's probably stinking obvious, but I can't see it. Maybe I'm running short of calories and need food.

Any assistance greatly appreciated.

Dan
 
Old March 19th, 2004, 10:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, now. Here's wierd. Do this:

SELECT t1.id, t2.id, t1.name FROM table AS t1 LEFT JOIN table AS t2 ON t1.ame=t2.name AND t2.id IS NOT NULL;

...and my 'IS NOT NULL' works, removing null results> However, I can't add a third clause to the effect:
e1.organiser_id<>e2.organiser_id

...since all the NULL values come back in, despite the fact that I'm stipulating (effectively) "clause AND clause AND clause".

I don't get this. I'm either seriously misunderstanding something about self-joins, or something about NULLs...
 
Old March 19th, 2004, 10:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Right. I'm stupid. Try:

SELECT t1.id, t2.id, t1.name FROM table AS t1 LEFT JOIN table AS t2 ON t1.name=t2.name AND t1.id<>t2.id WHERE t2.id IS NOT NULL;

This works - and is, incidentally, a lot simpler than the "temporary table" approach you usually see. (The optimiser seems to like it, too).

(AS I said, I was running short of blood sugar at the time of my original post. I owe this result to a Medium Roast Beef and Rocket sandwich... Very nice ;)
 
Old May 19th, 2004, 07:02 PM
Authorized User
 
Join Date: May 2004
Location: Dhaka, , Bangladesh.
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to ruhin Send a message via AIM to ruhin Send a message via MSN to ruhin Send a message via Yahoo to ruhin
Default


Make the Column as UNIQUE.

By making this it would not accept any
redundant values.

It is very easy to use "phpMyAdmin" for creating
and structuring MySql database.I suggest to use this.
 
Old May 20th, 2004, 04:04 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, no, the fields I needed to match were things like 'organiser name', where the same organiser name _may_ have been entered twice for the same organiser, but _may_ also have been entered for a national chain of organisers whoe ach share the same name; where the only way of testing the legitimacy of the duplicate field value, is to see it in the context of other fields, such as the 'city' portion of the address, or similar identifying characteristic. There are many tens of thousands of results, and the client really needs to be able to seek out _possible_ duplicates, not erradicate them. In any case, the records already existed, so even if the aim was a totally unique column of values, trying to force that upon the existing table would have resulted in a constraint failure, at best. In cases like this, you need to use a LEFT JOIN and filter against NULL values - which is somewhat counterintuitive, because you're actually aiming to generate false matches and filter _in favour_ of those false matches. In any case, the SELECT in my third post sorted it: it was fairly trivial, when I figured out what I was doing wrong.

I always try to remind myself that when a program isn't doing what I want it to, it's because it's doing precisely what I'm _telling_ it to do - and so all I need to do, is work out what I'm _telling_ it to do, in order to solve the problem... As I said, on the day in question, I think I was just suffering a crisis of faith over this doctrine, brought on by a lack of blood sugar during the latter part of a long morning's work. The roast beef fixed it. I may put a small notice on the finished product, when I'm done: "Some cows were injured in the creation of this product". :)
 
Old June 21st, 2004, 12:29 AM
Authorized User
 
Join Date: Apr 2004
Location: malacca, malacca, Malaysia.
Posts: 69
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to junemo
Default

Hi Daniel,

I not really understand the function JOIN in SQL statement.

My question: What is the best way to check if a record already exists in a oracle-dbase from php or in the case of when the same form is submited twice beacuse of the "refresh"-button?

Please attach a sample code (oracle in php SQL script)for detect the duplicate record.

Thanks in advance.

 
Old June 21st, 2004, 02:39 AM
Friend of Wrox
Points: 1,288, Level: 14
Points: 1,288, Level: 14 Points: 1,288, Level: 14 Points: 1,288, Level: 14
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2004
Location: Stockholm, Sweden
Posts: 331
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to qazi_nomi
Default

You should have a unique primary key in the relations and when
you enter the record you check that weather the record is already there or not if not then insert else no insertion

Love 4 all




Similar Threads
Thread Thread Starter Forum Replies Last Post
How can you delete duplicate rows without using te subhasischakraborty SQL Server 2000 15 October 19th, 2007 06:51 AM
Detecting duplicate values Europom Excel VBA 2 May 29th, 2007 07:09 AM
SQL server Inserting duplicate rows. shaileshmark SQL Server 2000 6 December 14th, 2006 11:09 PM
duplicate rows msrnivas Classic ASP Components 1 August 26th, 2004 02:07 AM
Checking Duplicate values and delete rows ppenn Excel VBA 2 February 3rd, 2004 06:57 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.