MySQL archive records based on date column

Photo credit: 27892629@N04 - cc
Photo credit: 27892629@N04cc

Let’s assume you have a logs table and you want to delete the logs which are more than 1 year old.

Ideally you would like to automate this using a cron job.

MySQL Between query

Using the above query we can delete all the logs for the year 2014. However we cannot automate this query since we are providing the dates manually.

MySQL DATE_SUB query

Above query deletes all the records which are older than a year. Here we do not need to provide dates. It automatically finds the records which are older than a year using NOW and INTERVAL parameters.

So let’s say you want to delete all the records which are 6 months old then the query would be

Now lets automate the process of archiving our logs table

This can be done through a number of ways.

Shell Script

A shell script can be created with above code. The script can then be added to a cron job.

MySQL event scheduler

Read more

PHP Script

Create a PHP page to run the query and then create a cron job for the PHP page

A php script is the most recommeded system in this case because deleting records does not need lot of memory and it is easier to manage the PHP Script.

When to use Database Triggers ?

What are Database Triggers ?

A Database trigger is an SQL code which is made to run just before or after a certain event. That event could be an INSERT, UPDATE or a DELETE query on a particular database table.

Thus a trigger is used to automate some of the events on your server/site/application.

Examples of some Database triggers

  1. Sync user details from one table to another when a user updates them
  2. Geocode users location and store them in a separate table
  3. Maintaing log of certain events e.g. a product addition, updation or deletion (In this case we wish to know who did the change)

When to use Database triggers

There are few pros and cons about using database triggers.

Pros:

  1. Yes they can automate quite a lot of activities
  2. For things like maintaining logs if you are doing this through your code then most probably you need to add the piece of code in a number of files. e.g. If you are maintaing a log about article updates then there may be a number of files in your code related to updating articles. You will need to add the log related code in every file. But if this is done through trigger then you do not need to worry about updating your code.
  3. In the above example if the log table structure changes then you just need to update your trigger code and you are done.
  4. Triggers are very handy in case you have completely different systems on different platforms and coding is not really possible. This is mostly the case in large organisations.

Cons:

  1. Since the trigger is not part of your normal code it may create lot of confusion later as to how certain things are happening especially if you are new to the system and things are not properly documented. Due to this lot of people prefer to write event driven procudures in their code instead of creating triggers as that way they are in full control of the application.
  2. If the business logic changes then the triggers can be difficult to handle/update. At that point of time it may happen that a certain event may not be possible through a trigger and you may need to revert back to your usual way to handling events through your code. This may further increase development time.

Conclusion

Based on the above pros and cons we can understand that using a trigger or not is not really a straightforward decision. It really depends on case by case basis.

  1. If you are coding in an object oriented manner then most probably you are writing all your events in structured manner and there is no duplication of code. In that case it becomes quite easy to add the event based procedures within your code.
  2. However in some cases the code is not object oriented as it may be quite an old application. In that case you can make a judgement call whether its the time to update the code or create triggers.
  3. In case 2 if the code is done by some other developer then it is likely that you would use triggers.
  4. For applications built on different platforms or different scripting languages sharing common resources triggers can be handy to achieve some form of integration.

 

 

 

Shell script to backup database and send it to remote server automatically

Database backup script
Photo credit – 132889348@N0722868800432

This article explains how to create a database backup script to send the backup to remote server  without any manual intervention. For security reasons or to enable disaster recovery it is important to keep database backup copies on some additional server outside your network.

The process to create a script for database backup and to autosend it to a remote server is not really so complicated as many would think.

To make it simpler let’s divide the whole task in 3 different sub tasks

  1. Create a backup file of your database.
  2. Authorize the origin server i.e. Your current server to send the files to the remote server (So that the remove server knows that it is receiving the files from a genuine source)
  3. Create a script to simply SCP the files from the origin server to the remove server

Above mentioned are mandatory steps. Additionally you may also want to create a log file to log the status of every step just in case if something goes wrong.

Now lets discuss each step in detail

Step 1: Create a backup file of your database

The backup file of your database can simply be a compressed sql file. There are lot of automated tools to achieve this. But to create a script is also quite simple.

For those who want to script everything here is a sample script (and assuming you have only one database)

Above command just creates a dump of the database. The dump is in sql format. Let’s say db.sql

The size of the file however would be too big (depending upon your database) as it is an uncompressed database file. So you may wish to change the above command to output a gzip or bzip2 file.

GZip

bzip2

Note: Although the compressing provided by bzip2 is better than Gzip it takes much longer (around 6-10 times) to compress a file in comparison to Gzip. However in our case the process will mostly run during the night and will be automated so you might wish to use bzip2. So in this case both the options are ok.

Step 2: Authorize the origin server to send the files to the remote server

As you would have guessed this steps is to avoid password prompts so that the process happens automatically without any manual intervene.

It involves only 2 steps

A. Create an SSH key on the origin server

Command: ssh-keygen

This will create a key in your USER/.ssh folder with the name of the key provided while running above command.

When asked to enter passphrase, leave it blank (unless you will be caching the passphrase on the remote server)

If you want to use your current SSH keys thats fine too however they may have a passphrase on it. You can remove the passphrase using below command

Command: ssh-keygen -p

This command will first ask the ID of the key for which you wish to change the passphrase. Then it will prompt you to add the new passphrase. You can leave it blank.

 

B. Add the private key to the authorized_keys file on the remote server

This step is very simple. Just copy the public key (e.g. id_rsa.pub) contents and paste them on a new line in the authorized_keys file on the remote server

Note: Do not delete any content in this file. Just append the file with the contents of the key starting on a new line.

Step 3: Create a script to simply SCP the files from the origin server to the remove server

Final step is very simple. Here we will just be copying the files from the origin server to the remote server using below command

 

Here the port number is of the remote server where you will be sending the files. If the scp port is non standard or non default only then -P PORT_NUMBER option is necessary

Select records from a table where no corresponding records available in other table

Sometimes there is a need to select records from Table A where there are no corresponding records in Table B

e.g. If you have a products table with productid as primary key and you need to find products which did not get sold in a particular month from the sales table you can use following query

 

MySQL remove duplicate data using DISTINCT

In order to explain how DISTINCT works let us see an example

Let’s say we have table of  books storing Book Title and Author details. It is possible that one author can write several books.

book_title——– author

Book A————–Author A

Book B————–Author B

Book C————–Author A

Book D————–Author C

Now we want to write a query which will return all authors

If we run following query

SELECT author from books

We will get

Author A, Author B, Author A, Author C

Note that Author A appears twice

To correct this problem we run below query

SELECT DISTINCT author FROM books

This will return

Author A, Author B, Author C

Integrating Prestashop shopping cart with existing PHP -MySQL Authentication

Assumptions

Here we are assuming that you are using PHP – MySQL and you have an existing list of clients/users already maintained in a seperate table.

You have your own registration, login and logout mechanisms

Step 1: Prestashop cookies

First let us understand little bit about Prestashop cookies

A customer login prestashop cookie is as below

Cookie name: ps

Cookie content:id_guest%7C1%C2%A4id_customer%7C1%C2%A4customer_lastname%7CLASTNAME%C2%A4customer_firstname%7CFIRSTNAME%C2%A4logged%7C1%C2%A4passwd%7CMD5_PASSWORD%C2%A4email%7CEMAIL%C2%A4

Note: there can be some other parameters as well in the cookie but I have included only the ones which we are interested.

The cookie is ofcourse not readable. It is encrypted and decrypted using Rinjdael or Blowfish.

Apart from that there is also a SALT phrase which is added through the settings.inc.php file

Just for demonstration purposes I have removed the SALT phrase in settings.inc.php (in the config directory) as below

I disabled the cookie encryption and decryption process in Cookie.php file (in the classes directory) as shown below

Comment below lines

 

After doing the above changes, try to login again. If everything is done correctly, you should be able to login successfully. After you login the cookie generated will be readable.

Just to make the cookie bit more clear to us, let us run PHP urldecode

Here’s the output

id_guest|1¤id_customer|1¤customer_lastname|LASTNAME¤customer_firstname|FIRSTNAME¤logged|1¤passwd|MD5_PASSWORD¤email|EMAIL¤

Now this cookie is quite readable and easy to follow.

id_guest=1, id_customer=1, customer_lastname=LASTNAME, etc

Step2: Import your existing users to the prestashop customer table

Export all users from your database and include fields like memberid,firstname, lastname, username, md5(password), salutation and import it into the ps_customer table in prestashop.

Step 3: Single sign on

Once the import-export is done, achieving the single sign on is very easy

All you need to do is when a user logs in on your website apart from the usual cookie which you create, you also need to create one more cookie for the prestashop login (even better is to combine the cookie and name is as ps which might need a corresponding change in your authentication code). The format of this cookie would be same as the one obtained after the urldecode above in Step 1. Cookie name would be ps

Step 4: Enable cookie encryption

Note that we turned off all cookie encryptions in Step 1. We can turn on cookie encryption and decryption provided you are able to replicate the same through a function on your existing site when you create the cookie.

Step 5: Close all loops

A. Disable password reset through Prestashop and send users to your website password reset process and in that process make sure you update password in both the tables

B. When a new user registers make sure you add a new entry in the Prestashop table as well (ps_customer and ps_customer_group)

C. When people login on prestatshop, the form action URL can be changed so that it goes to your custom authentication code which enables you can create the necessary cookies

How to get the ID generated by the last INSERT

Many times there is a need to get the autogenerated ID from the last MySQL INSERT performed.

This ID can then be used to perform some more INSERTs particularly in different tables.

As a typical example lets assume you have members table and a seperate table to store their preference for receiving newsletter.
When a new member registers, we perform the first INSERT to add the member details in the member table.
Now our newsletter table needs the memberID generated from this insert so that we can store their preference (In case you are using their memberID as the primary key instead of the username)

They way to perform this is as below

PHP – MySQL Search on multiple columns using concat

More often there is a need to search part of a text in multiple columns and the search is based on a single keyword text box

For e.g. in a users table first name and last name can be stored in seperate columns

Let’s assume a user named Joe Smith stored in a users table with

first _name => Joe

last_name => Smith

If we have a single keyword text box search and if someone types “Joe Smith” and performs a search, a simple search query as below would give no results

For the query to give correct results it needs to be modifed as below