It is a common requirement in lots of projects to fetch the next and previous items or posts and display a link to them on every item or post page. Getting the previous and next item can be quite tricky especially if they need to be user-specific, although it is very easy. I’ll show you how – Instead of 2 round-trips, you can even query the DB once. The trick is to use UNION :)

There are situations when we need an autoincrement field to reset every month or so. Example : Invoice Number – 2011-10-01 , 2011-10-02 ………….. 2011-10-1000 and then 2011-11-01 Or groupwise autoincrement P1 , P2 , Q1 , Q2 , Q3 , Q4 , P3 ….. and so on Myisam table has a feature where , if a primary key contains 2 columns where one is an autoincrement and other is a varchar , then for [...]

If the mysql server is on ubuntu and other machines on LAN need to access it directly then : 1. Edit /etc/mysql/my.cnf and comment the line bind-address=some_ip 2. Restart mysql , sudo /etc/init.d/mysql restart 2. Open phpmyadmin , go to privileges , and add the ip address with the necessary privileges. Now the new ip can access the mysql database of this machine over LAN.

Mysql Database stores dates in the format : yyyy-mm-dd e.g. 2009-04-15 Whereas humans are more used to the formats like : dd-mm-yyyy or mm-dd-yyyy e.g. 21-04-2009 And while reading a format like : dd-Month-yyyy is more usable e.g. 21-Mar-2009 Checking a valid date : If the date is in format dd-mm-yyyy then a simple way to test its validity would be : But the above has an issue that it will report 31-02-2009 as a [...]

This is a simple class that evolved out of the php code I wrote so far. It has the following functions : 1. dbms() – The constructor to do the initialisation like connecting to the database etc. 2. query($query) – The method to take a sql string and perform the query. 3. close() – Close the database connection 4. backup() – Create a backup of the database as an sql file and then zip it. [...]

In the previous post we saw how the backup of a database can be taken in the form of a sql file and then zipped. In this example the same zip file would be used to restore the database from it. Code :

PHP can be used to backup a mysql database and make a zip file of it. mysqldump is the utility which can be used to perform this function of backing up a mysql database as sql file. The command would be like this : mysqldump –user={$username} –password={$password} –quick –add-drop-table –add-locks –extended-insert –lock-tables –all {$db} > backups/backup.sql $username – mysql username $password – mysql password $db – name of the database to backup In the above [...]