How to delete DUPLICATE records from MYSQL table

Some times we need to delete duplicate rows from a table. Because there is no need for keeping duplicate records in same table. Deleting these duplicate records is very easy. It can be don using some simple SQL queries.

Here’s two easy ways to clean out that table quickly.

1) Use ALTER IGNORE on MySQL 5.1+
MySQL will allow you to create a unique index on a table with duplicate records with its IGNORE SQL extension:

 ALTER IGNORE TABLE 'SHIPMENTS' ADD UNIQUE INDEX (CART_ID, TRACKING_NUMBER)  

Duplicates will be deleted.

2) Recreate the table with GROUP BY

 execute 'CREATE TABLE shipments_deduped like shipments;'  
 execute 'INSERT shipments_deduped SELECT * FROM shipments GROUP BY cart_id, tracking_number;'  
 execute 'RENAME TABLE shipments TO shipments_with_dupes;'  
 execute 'RENAME TABLE shipments_deduped TO shipments;'  
 add_index :shipments, [:cart_id, :tracking_number], :unique => true  
 execute 'DROP TABLE shipments_with_dupes;'  

Recreating the table is much, much faster than trying to delete the records in the existing table and doesn’t lock the existing table, making your application downtime minimal.

Insurance

How to download and unzip a .ZIP file using php

Today I was working on a website which will be an affiliate member of another big company with already running a very good website ( i don't want to mention the name of that affiliate program because with this my idea is some how disclosed to the public and i don't want to do so :) ). In that affiliate program I have to download the feeds in .zip form and then importing that in my database. So I needed a script to automatically download, unzip and then import that data in my database. By using my experience and skills and also with the help of Google, I have successfully written a script for this purpose. In this post I am going to share the same,

First part of my script checks that php zip extension is loaded or not.

 if (!extension_loaded('zip')) {  
                dl('zip.so');  
           }  

After that, mention the path of download file and destination folder and rest of the script is as follows,

 $target_url = "http://www.example.com/test.zip";  
 $userAgent = 'Googlebot/2.1 (http://www.googlebot.com/bot.html)';  
 $file_zip = "downloads/new.zip";  
 $file_txt = "downloads/".md5(time())."new.txt";  
 echo "<br>Starting<br>Target_url: $target_url";  
 echo "<br>Headers stripped out";  
 // make the cURL request to $target_url  
 $ch = curl_init();  
 $fp = fopen("$file_zip", "w");  
 curl_setopt($ch, CURLOPT_USERAGENT, $userAgent);  
 curl_setopt($ch, CURLOPT_URL,$target_url);  
 curl_setopt($ch, CURLOPT_FAILONERROR, true);  
 curl_setopt($ch, CURLOPT_HEADER,0);  
 curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);  
 curl_setopt($ch, CURLOPT_AUTOREFERER, true);  
 curl_setopt($ch, CURLOPT_BINARYTRANSFER,true);  
 curl_setopt($ch, CURLOPT_TIMEOUT, 10);  
 curl_setopt ($ch, CURLOPT_SSL_VERIFYHOST, 0);  
 curl_setopt ($ch, CURLOPT_SSL_VERIFYPEER, 0);   
 curl_setopt($ch, CURLOPT_FILE, $fp);  
 $page = curl_exec($ch);  
 if (!$page) {  
   echo "<br />cURL error number:" .curl_errno($ch);  
   echo "<br />cURL error:" . curl_error($ch);  
   exit;  
 }  
 curl_close($ch);  
 echo "<br>Downloaded file: $target_url";  
 echo "<br>Saved as file: $file_zip";  
 echo "<br>About to unzip ...";  
 // Un zip the file  
 $zip = new ZipArchive;  
   if (! $zip) {  
     echo "<br>Could not make ZipArchive object.";  
     exit;  
   }  
   if($zip->open("$file_zip") != "true") {  
       echo "<br>Could not open $file_zip";  
         }  
   $zip->extractTo("$file_txt");  
   $zip->close();  
 echo "<br>Unzipped file to: $file_txt<br><br>";  

If you like this script, so please post your comments and feedback. Your good feedback is the main desire of me to continue writing useful posts for my blog reader. Thanks

Password protection using .htaccess

Some times, some portions of website need to be password protected in order to prevent it from UN-autorized access. There are many ways to do this. One way is achieved by server side programming and other way to achieve this is client side programming like java-script. In this post I will discuss password protection using .htaccess file. This method have following steps to follow,

1. First decide what portion or directory you want to make password protected. I am considering root directory in this post.

2. After this go to this URL http://www.tools.dynamicdrive.com/password/ and generate code for your .htaccess and .htpasswd files.

3. After this you need to save these codes in .htaccess and .htpasswd files respectively. I you are unable to create .htaccess file, you can read this http://our-knowledge-base.blogspot.com/2012/05/how-to-create-htaccess-file-simple.html article to learn how to create this. Creating .htpasswd file is exactly same like creating .htaccess file.

4. Now upload the both file in the corresponding directory. Your website is password protected now.

feel free to post your feedback

Thanks

How to create .htaccess file - Simple steps

I this simple step by step tutorial I will tell you how to create .htaccess file using a notepad editor.
Here are the steps to do this,
 
1. Open your Windows notepad editor.

2. Make sure that the word wrap function is off.

3. Select File, then select New

 

 4. You should have a screen that doesn’t contain any text.

5. Next, select Save As

6. Type the following for your file name, .htaccess

Browse your folders and select which folder you want to save the .htaccess file in, then click Save
The next step is to verify that the file was named .htaccess by the editor. You simply open the Windows file manager, locate the file, right-click the file icon and selecting Properties.) If the file was named with the .txt suffix, you can rename the file to .htaccess and your done.

Thanks

Balanced Diet Plane

Hey guys, if you want a perfect diet plane chart, then this post is just for you.

Cereals (per 100 grams) Calories	Breads (per slice) Calories	Vegetables (per 100 grams) Calories	Fruits (per 100 grams) Calories	Desserts (per 100 grams) Calories	Milk Products (per cup) Calories	Others Calories
Bajra 360 Maize flour 355 Rice 325 Wheat flour 341	Chapati (wheat bread) (medium) 119 White bread 60 Paratha (not stuffed) 280	Cabbage 45 Carrot 48 Cauliflower 30 Corn (baked) 84 Cucumber 12 Eggplant (Fresh) 24 Eggplant (Cooked) 69 Fenugreek (methi) 49 French Beans 26 Lettuce 21 Peas 93 Potato 97 Potato (fried- 1 cup) 450 Potato (baked- 1 cup) 100 Potato (mashed- 1 cup) 245 Potato (boiled- 1 cup) 83 Pumpkin (cooked) 33 Spinach 26 Tomato (fresh) 21 Tomato (stuffed and baked) 58 Tomato (baked) 39	Apple 56 Banana 153 Black Grapes 45 Chickoo (Sapota) 94 Cherries 70 Dates 281 Guava 66 Litchies 61 Mango 70 Muskmelon 74 Orange 53 Papaya 32 Peach 50 Pears 51 Pineapple 46 Plums 56 Pomegranate 77 Watermelon 16	Biscuits 399 Boondi ladoo 150 Cake (with icing) 302 Cake (without icing) 218 Cookies (butter cookies) 482 Custard 205 Fruit pie 236 Fruit salad 80 Gujia 501 Gulab Jamun 387 Halwa (atta) 263 Halwa (rawa) 181 Halwa (sohan halwa) 399 Jalebi 494 Jelly 65 Kheer (rice) 141 Kheer (rice-carrot) 226 Malpua 325 Sandesh 57	Buffalo milk 115 Butter (100gms.) 750 Buttermilk 19 Cheese 315 Cow milk 100 Cream (100gms) 210 Ghee (100gms) 910 Skimmed milk 45	Coconut water (100 ml) 25 Coffee 40 Honey (1 tbsp) 90 Orange juice (100 ml) 47 Sugar (1 tbsp) 48 Tea 30 Tomato juice (100ml) 22