Recently I attended a session where a few developers would discuss about the website they have done. One of the developers approached me and asked me if there is a way that you could delete data from MySQL database once a week / at regular interval. I thought this short tutorial will be useful for one who wish to do so on the LAMP stack.
Step 1: We will come up with the php file that will do the delete function, from MySQL database and upload it.
$link = mysql_connect(‘localhost’, ‘mysql_user’, ‘mysql_password’);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}mysql_select_db(‘mydb’);
mysql_query(“DELETE FROM mytable WHERE WHERE dateInserted > ‘2012-9-9′”);
In my sample code above, i did, “DELETE FROM mytable WHERE WHERE dateInserted > ‘2012-9-9′”. It depends on what you want to do, you can always change the conditions on why it should be deleted. I have also saved it as DeleteData.php
Step 2: Since I have cPanel, i used cPanel and go into Cron Jobs
Step 3: I will setup the cron job to run at once a week.(Of course you can set otherwise)
Note that for Command, you have to specify where the file lies, after you upload.
You can download the mentioned, DeleteData.php here.
Delete records from mysql automatically
Hope this is helpful for those who are finding a quick tutorial on how this can done. If you have any questions, please feel free to email [email protected], use the contact us form or alternatively you can comment. 🙂
I will post a tutrial on how we can do this on WISA stack.
To start, I would like to first say that although many PHP developers usually recommend MySQL as the accompanying database for PHP, it is not difficult to interface PHP with other databases such as SQL Azure, Microsoft’s cloud-based relational database offering.
PHP connects with SQL Azure in a similar manner as how it does with Microsoft SQL Server, ie through an interface known as the Open Database Connectivity (ODBC), which is actually the standard software interface for accessing databases. Each platform and database has its own implementation following the ODBC standard but for this tutorial, I’ll focus on PHP.
There are a few ways to connect your php site to MS SQL but the 2 main approaches are as shown in Figure 1.0.
1. Using the “php_mssql.dll” php extension requiring MS SQL Client Tools installed (Figure 1.0, right column).
2. Using the “sqlsrv” driver (“Microsoft Drivers for PHP for SQL Server”) requiring MS SQL Native Client installed (Figure 1.0, left column)
I will be using the 2nd approach for this tutorial because it supports both PHP 5.2 and 5.3, unlike the 1st which is not available for PHP 5.3.
Microsoft Drivers for PHP for SQL Server
As of 1 February 2011, the latest version of the driver is version 2.0.1 (30 November 2010).
You can grab it from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=80e44913-24b4-4113-8807-caae6cf2ca05
Once you have installed the drivers, you should see the following in the installation directory:
We will be using the “php_sqlsrv_53_nts_vc9.dll” library for this tutorial.
• “php_sqlsrv” –> Driver name
• “53” –> PHP 5.3
• “nts” –> Non-thread safe (The PHP FastCGI Handler of IIS handles thread-safe operations for PHP, use the non-thread safe version to reduce performance issues)
• “vc9” –> Library compiled using VS 2008, use vc6 (VS 6) if PHP is running on Apache
Configure PHP
1. Copy “php_sqlsrv_53_nts_vc9.dll” into the “ext” folder of your php installation directory.
2. Edit the php.ini to include the library
Microsoft SQL Server 2008 R2 Native Client
In order for the PHP for SQL Server Drivers to work, the necessary SQL Server ODBC drivers must be installed on the web server.
The version of the ODBC driver needed for SQL Azure comes with the SQL Server 2008 R2 Native Client.
You can grab it from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52
PHP Syntax
After all the preparation and configuration, here comes the actual thing -> Coding!
This tutorial showcases how to do simple CRUD (Create, Retrieve, Update, Delete) commands.
1. Connect to Database:
$serverName = “servername.database.windows.net”;
$connInfo = array(“UID”=>”username@servername”,
“PWD”=>”password”,
“Database”=>”databasename”);
$conn = sqlsrv_connect($serverName, $connInfo);
2. Insert data to Database (taking data from a html form text field):
$comment = $_POST[“txtComment”];
$comm = “INSERT INTO commentsqlazure (commentContent) VALUES (?)”;
$stmt = sqlsrv_prepare($conn, $comm, array(&$comment));
$result = sqlsrv_execute($stmt);
3. Update data in Database:
$Id = $_POST[“txtUpdateId”];
$comment = $_POST[“txtUpdateComment”];
$comm = “UPDATE commentsqlazure SET commentContent = ? WHERE id = ?”;
$stmt = sqlsrv_prepare($conn, $comm, array(&$comment, &$Id));
$result = sqlsrv_execute($stmt);
4. Remove data from Database:
$Id = $_POST[“txtRemoveId”];
$comm = “DELETE FROM commentsqlazure WHERE id = ?”;
$stmt = sqlsrv_prepare($conn, $comm, array(&$Id));
$result = sqlsrv_execute($stmt);
5. Retrieve data from Database:
$comm = “SELECT id, commentContent FROM commentsqlazure”;
$stmt = sqlsrv_query($conn, $comm);
while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
{
echo $row[“id”].” “.$row[“commentContent”].”
“;
}
6. Close connection and release resources:
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
You can download the project file from here.
http://cid-29f099c37b76ca59.office.live.com/browse.aspx/Blog/Code%20Guide/SQL%20Azure?uc=1
Please take note that you have to change the Server Name, Username, Password at the connection string to make this work. You should run the following script to the table first.
http://cid-29f099c37b76ca59.office.live.com/self.aspx/Blog/Code%20Guide/SQL%20Azure/SQLAzureConnection.sql
Credit to Luke Ng.