Kangaroo Court {concept47 blog}

Getting rid of duplicate Entries in your MySql database

Thanks for Justin Cook for doing all the leg work on this one.

All you have to do is create a new table from the old one, which filters out the duplicate entries

1
2
3

CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1
GROUP BY [colum_to_remove_duplicates_from];

so as an example

1
2

CREATE TABLE news_new AS
SELECT * FROM news WHERE 1 GROUP BY [title];<!--more-->

Now you just have to go in and rename the news db to news_old (for the newbies to this … don’t drop the table immediately, at least until you’re sure the new one works fine).

Then rename news_new to news

Phpmyadmin should make all this easy (click on the “Operations” tab), but in case you are working from the console, here is the syntax you need to know

1

RENAME TABLE new_table TO old_table;

That isn’t all that you have to do, however. The new table will be missing its primary key and auto increment settings as well as any other indexes that you may have had on the old table.
Go in and set the primary key (first) back on the column that had it, then (secondly … the order matters) clear out the default value on it and set it to auto increment.

You can re-add your indexes later … be sure to put a unique index on the column you were having trouble with.

Add comment March 27th, 2008

Problems with PHP4 and PHP5 sessions not working? … you may be using numeric keys!

I have just blown 4 – 5 hours on this “feature” of PHP and I thought someone else would care to know.

You can’t use numeric keys for sessions in php!

so stuff like

$_SESSION['1234']  = 'boo";

… won’t work because PHP’s session handling mechanism simply refuses to store that particular session variable.

Even worse, it fails silently, leading you to think your brain has fried itself.

Personally, I just thought something funky was up with PHP5 (google searches seemed to indicate problems with PHP5 sessions, so I focused on that).

But after trying version 5.2.4, 5.1.6  and still having the same problem I finally tried it out in PHP4 and … still had the problem. Then I seriously started trying to track down the bug.

After finally finding it and writing the right Google query  (thanks for nothing Google ) … I found a couple of articles that point out this problem.

So I’m writing this with an SEO’ed title that should hopefully grab the right folks, before they blow hours trying to figure this out.

Please go and vote for this feature to be fixed (made to fail loudly … so you know exactly what is wrong).

Add a comment if you do.

Add comment March 27th, 2008