SQL Remove Duplicate Records Solutions

Today i face a little problem. I wanted to change one of my field into unique however there were already some duplicate records (around 900+ records) in that field. And here are some of the ways i can remove duplicate records i though that i would share it out. However, before doing any fixing on our duplicate records. How about we find out which records were duplicated? If you want to know which records is duplicated, just fire the below command,

SELECT photo
FROM photograph
GROUP BY photo
HAVING count(photo) > 1

This sort of sql command is tested in most interview question. However, it is pretty simple by using groupby and having to show which data have more than 1 records.

Duplicate another table

The easiest way is to extract all unique records into temporary table, remove your current table and rename your temporary table.


CREATE TABLE photograph_tmp AS SELECT * FROM photograph GROUP BY photo
DELETE TABLE photograph
RENAME TABLE photograph_tmp TO photograph

The above will definitely fix your duplication problem but do remember to backup your records before doing anything silly.

Similar Duplicate

Another similar way instead we backup our records first, empty our original table and insert the non duplicated records in.

CREATE TABLE photograph_backup AS SELECT * FROM photograph GROUP BY photo
TRUNCATE photograph
INSERT INTO photograph SELECT * FROM photograph_backup  GROUP BY productid

This way, you don't have to worry about backing up or destorying your precious data.

Delete duplicate records

Another method is to delete all the duplicated data in the table without creating another table.

DELETE FROM photograph
   WHERE photo IN
   (SELECT photo
       FROM photograph
	   GROUP BY photo HAVING count(photo) > 1);

bascailly we search all our duplicated data and delete them from the real table. This is quite risky so don't be lazy and try out the safer way above.

Hope it helps 🙂

How to extract word from a string given a position in php

Today i wanted to extract a particular word or text (if the word doesn't make sense) given a position in a string. I tried to search this for php platform but couldn't really find an answer for it. In facts, i cannot find any through google for such functionality in php. However, i manage to find it on python. It was actually pretty simple and straight forward and i believe most of people will get it in one look. But we don't revamp the wheel so here you go.

      function extractWord($text, $position){
         $words = explode(' ', $text);
         $characters = -1; 
         foreach($words as $word){
            $characters += strlen($word);
            if($characters >= $position){
               return $word;
            }   
         }   
         return ''; 
      }   

pretty easy isn't it? The above will basically split the string into individual word and loop these words and calculate the total position. If the position of the total characters is larger or equal to the position you provide, we have reach the word that we want. Here is a little example on how to extract word from a string given a position.

$text = 'This is an example of how to extract word from a string given a position in php
$position = strpos($text, 'examp');
$word = extractWord($text, $position); // return example

It's pretty simple and straight forward but it does save some time and focus on something more important. Hope it helps 🙂

Closing new tab on Google Chrome Extension

This is something very new which i recently having problem with. I would like to open a new website and check for a certain criteria in my google chrome extension and close that particular new tab that i just open using chrome.tabs.create. However, the following code doesn't work when i inject the code into the new tab

windows.close();

The condition of closing a new tab has been fulfilled however i am not able to close that tab. Google for a few times doesn't give me any answer to my question until i try something new and it works. Looking at the below code,

chrome.tabs.create({'url': tab.url + 'feed/'}, 
function(tab) {
	$("#url").val(tab.url.replace("feed/",""));
	chrome.tabs.executeScript(tab.id, {code: "this.close();"});
});

take note that i uses chrome.tabs.create to open up a new tab on my google chrome extension. After i validate my url i injected a close command using "this" instead of "windows" with the id of the tab i just open. Apparently, it works!

Hope it helps someone 🙂

Installing WordPress using cpAddons – You do not have sufficient permissions to access this page.

I have a problem today using cpAddons to install my WordPress into one of my subdirectory. I receive "You do not have sufficient permissions to access this page." error upon logging into my WordPress panel. I believe this is not the first time i encounter this problem but this problem only appear if you are not using the default database "wp" as suggested by cpAddons. I google around for a while but didn't get the answer i want or any valuable solution. Hence, i debugged it myself.

Apparently,  everything generated by cpanel cpAddons was correct. The database data and wp-config.php was set correctly. Even the database user permission was also correctly given.  The file permission of wp-config.php was correct.

Now, i remove all the tables generated by cpAddons and run through WordPress configuration page and setup the WordPress installation again. This will install all the tables that i just deleted into the database. Guess what? It works. This means that the database tables installed by cpAddons is a buggy one. Therefore, we will have to erase the tables through phpmyadmin so that WordPress can be install correctly.

Step by step guide to write a Google Chrome Extension

I have absolutely no experience in writing a Google Chrome Extension myself. But if i can do it. I believe majority of the developer will want to know how i did it and what are the process that involve for me to accomplish this task. Hence, i write this out hopefully people get the idea of how Google Chrome Extension is build and how easy it is to make one once you understand the rule involve in building a google chrome extension. On Google Chrome Extension "Getting Started" page, it's pretty simple and straight forward for anyone to build an app in that scale. However, understanding it will be the task i'm gonna set out for. Hopefully this will be short and easy to understand. Let's start.

Getting Started with Google Chrome Extension

Like many people out there, i started with "HELLO WORLD". Basically you need the following item for a simple Google Chrome extension.

  • a Folder to keep all your files
  • a manifest.json which is also a config file for Google Chrome Extension
  • png or ico image which act as a logo to represent your extension
  • html file which is the display of your chrome extension.

Once you have the above 4 things, your chrome extension is ready to build.  You can head down to the Google Chrome Extension "Getting Started" page to do the above and jump back here to what i wanted to do.

Description of my Google Chrome Extension

Before i get started, just wanted to explain a little bit on what i am going to do and what are the requirement of my extension. I am building a private extension for one of my website to ease my job a little bit. Here are the sequence of this Google Chrome Extension.

  1. I need a chrome extension (obviously)
  2. upon clicking on the extension, it should present me with a form
  3. within the form, the extension should determine the page "url" and "what is the title of the page" and place the correct url and title into the form.
  4. Once i click on the submit button, it will post the record to my website and submit this website feed.

A pretty straight forward chrome extension for any developer but for a starter, this is pretty interesting on how it can be done.

Setup the manifest.json file

Before anything, i believe the most important thing to start would be on the setup file. And here is what i have

{
"name": "PRIVATE SUBMIT",
"version": "1.0",
"description": "PRIVATE app to submit a website into for viewing",
"permissions": ["tabs", "http://*/*"],
"browser_action": {"default_icon": "icon.png","popup": "form.html"}
}

some explanation. Here we are giving permission to any tab with any URL other than https url. The name, description and version is self explained. The browser action is what our extension on the browser looks and will do for us. default icon is an image and popup. That's all we need on your manifest.json.

Our form

Here will list out the form i have when it pop up upon clicking on the extension. I have also added jQuery library from google code into my google chrome extensionfor my conveniences.

<div>
<label for="name">Name*</label>
<input id="name" class="input" type="text" name="name" value="" />
</div>

<div>
<label for="url">URL*</label>
<input id="url" class="input" type="text" name="url" value="" />
</div>

<div class="submit"><input id="button" class="button" type="button" value="Submit!" /></div>

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>

<script type="text/javascript">
  chrome.extension.onConnect.addListener(function(port) {
    var tab = port.sender.tab;

    // This will get called by the content script we execute in
    // the tab as a result of the user pressing the browser action.
    port.onMessage.addListener(function(info) {
      $("#url").val(tab.url);
      $("#name").val(info.title);
  	});
  });

      chrome.tabs.executeScript(null, {file: "content_script.js"});

$(document).ready(function() {

  $('#button').click(function(){
	var name	= $("#name").val(),
	    url		= $('#url').val();
	$.ajax({
            type: "POST", //or GET
            url: "http://private.com/submitasite/",
            data: {
  		name: name,
  		url: url},
            crossDomain:true,
            cache:false,
            async:false,
            success: function(msg){
              console.log(msg);
           },
           error: function(jxhr){
               console.log(jxhr.responseText);
                //do some thing
           }
         });
  	return false;
  });
});
</script>

Upon execution of the popup when a user clicked on the extension, the form will first load out and execute a programming executed content_script.js to fetch some data from the current website page. It will fetch the title of the page and pass it back to the listener where it will store it into our textfield. Now, all we have to do is click on the submit button where the ajax will be fired to wherever your service is located. Before that, you will also need the instruction of the content script.

var additionalInfo = {
  "title": document.title,
};
chrome.extension.connect().postMessage(additionalInfo);

The content script is pretty straight forward. We create a json object and post it to our extension where everything else is explained previously. For the backend part, you should know what to do right? 🙂