MySQL archive records based on date column

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 [crayon-59c7ec99b76d0960128484/] 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 [crayon-59c7ec99b76db449502354/] 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 [crayon-59c7ec99b76e0401636583/] Now lets automate the process of archiving our logs table This can be done through a number of ways. Shell Script [crayon-59c7ec99b76e4222034745/] A shell script can be created with above code. The script can then be added to a cron job. MySQL event scheduler Read more [crayon-59c7ec99b76e8005934772/] PHP Script Create a PHP page to run the query and...
Read More

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 Sync user details from one table to another when a user updates them Geocode users location and store them in a separate table 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: Yes they can automate quite a lot of activities 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...
Read More

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

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 Create a backup file of your database. 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) 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...
Read More

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...
Read More

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 [crayon-59c7ec99b8313316914391/] I disabled the cookie encryption and decryption process in Cookie.php file (in the classes directory) as shown below Comment below lines [crayon-59c7ec99b831b091557309/]   After doing the above changes, try to login again. If everything is done correctly, you should be able to login...
Read More

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 [crayon-59c7ec99b86b9657155571/]...
Read More

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 [crayon-59c7ec99b8872236175586/] For the query to give correct results it needs to be modifed as below [crayon-59c7ec99b887a693638566/]...
Read More