Sunday, April 25, 2010

Get duplicates values from values table

Create table
CREATE TABLE IF NOT EXISTS `emp1` (
`first_name` varchar(20) NOT NULL,
`last_name` varchar(20) NOT NULL
)

Insertion

INSERT INTO `emp1` (`first_name`, `last_name`) VALUES (‘roy’, ‘raj’), (‘joy’, ‘josh’);

SELECT `first_name`
FROM emp1
GROUP BY `first_name`
HAVING COUNT(*) = N

Change the HAVING condition to >=2
It outputs roy and joy are duplicates having more then one record wirh same value.

Change the HAVING condition to >1 to list non duplicate values, etc

INSERT IGNORE

Create table
CREATE TABLE IF NOT EXISTS `emp` (
`first_name` varchar(20) NOT NULL,
`last_name` varchar(20) NOT NULL,
PRIMARY KEY (`last_name`)
)

Insertion
INSERT INTO `emp` (`first_name`, `last_name`) VALUES (‘roy’, ‘raj’), (‘joy’, ‘josh’);

Useful Queries

INSERT IGNORE
If a record doesn’t have duplicate date then MySql inserts usual.
If a record have duplicate data, the IGNORE keyword instructs MySql to discard it with out generating any error message.

INSERT IGNORE INTO `emp` (`first_name`, `last_name`) VALUES (‘roy’, ‘raj’);

here insertion is failed but it won’t shows any error message.

INSERT INTO `emp` (`first_name`, `last_name`) VALUES (‘joy’, ‘raj’);

Here Mysql insertion is failed because here record is duplicate so MySql generate some error like.
#1062 – Duplicate entry ‘raj’ for key ‘PRIMARY

Monday, October 05, 2009

Sitepoint's twitaway experience for choosing right MySQL engine

Hi All,

Posting after a two month break sorry busy with new addition to our family my daughter(Manha). Here we go:

There is an interesting article about the experience Sitepoint developers had with twitaway application while distributing Free PDF who followed them. How they found the importance of choosing right MYSQL engine is how important :) please go through the full article for more details.

Wednesday, July 22, 2009

INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO

While reading and article came across one of powerful feature REPLACE INTO,I had used INSERT ON DUPLICATE KEY UPDATE but never used REPLACE INTO.

REPLACE works in a way that if the ID column(Primary Column) doesn't have the given value it will create a new entry in the table, however, IF the value exists already then it updates the existing entry in the table.

REPLACE INTO sessions VALUES ('$id', '$data', $ts).

Read about REPLACE INTO and Difference about INSERT ON DUPLICATE KEY UPDATE vs REPLACE INTO

Sunday, July 19, 2009

PHP: exceptions vs errors?

Was just reading twitts got the nice link about the exceptions handling the post basically explains about handling errors in PHP nice one check out the code how the fatal error are handled and customize message is displayed for user. I have not tested the code but looks ok :) . Click for post here

Wednesday, July 15, 2009

Debug message in PHP?

I am using PHP from past 4 year and used to debug my code using die and echo statement and will log the debug message in file if running backend job(Cron) while using PHP 4 and then we started using Zend Framework and use Zend_log. But for those who won't use Zend Framework here is the function which will be useful in debug debug_backtrace this function is available from PHP4 didn't know i just came to know while i was browsing php.net, This information is for user like me who didn't know about it :)

Monday, July 06, 2009

Install PHP 5.3 on WAMP Server

Just installed PHP 5.3.0 on WAMP, Nice addon facility in WAMP now can switch between 5.2.x and 5.3.0. It was so simple that even a person with little knowledge can do it.

First install WAMP.
Then download version of php you want and install from here.
Done.

Now go to the wamp server icon in tray and switch to the version you want :)

PHP Security

Good article written by Joel Reyes about PHP Security, Author has explained some of the security holes we have to take care also given link for security tool download. Go through the article worth reading.

Thursday, June 11, 2009

Keywords in the URL

Reading the post 8 Tips to Get Domain Diversity came across important tips about keywords in the url. As most of us uses tinyurl or bit.ly for making our long url to short url we forget the feature tinyurl and bit.ly provide "Optional custom name/alias" this is important for SEO.

E.g., for the page http://ansarahmed.blogspot.com/2009/06/fingerprinting-to-dynamically-enable.html, you should probably try to use bit.ly/dynamic-caching