Search Results For : SQL

php SQL Sep 16 , 2012

Delete data from database automatically at regular interval

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.

autoautomaticallycodedeletelinuxmysqlPHProwssample code

SQL May 04 , 2011

SQL Cheat Sheet

Digged out by DBSY ( Database System ) notes from Temasek Polytechnic. I realised that I need to refer to this once in a while. Good to post this out for easy reference. Will update this when I see a need to. If you have feedback on this, please feel free to contact me using the contact me form or email me at [email protected]

SELECT
[ALL | DISTINCT | DISTINCTROW ]
column name, …
[FROM table name]
[WHERE where_condition]
[GROUP BY { column name | expr | position}]
[HAVING where_condition]
[ORDER BY { column name | expr | position}]
[ASC | DESC], …]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

INSERT INTO [table name]
[( [column name]{, [column name]}) ]
VALUES
([constant value] , {[constant value]} ){,([constant
value]{[constant value]})} [select statement])

DELETE FROM
[table name]
[WHERE [selection condition]]

UPDATE [tablename]
SET <column name>=<value expression> {, <column name>=<value expression>}
[WHERE <selection condition>]

Subquery Syntax

SELECT [DISTINCT] select_list
FROM table_list
WHERE
{expression {[NOT] IN | comp_op {ANY|ALL|SOME]} | [NOT] EXISTS}
(SELECT DISTINCT
subquery_select_list
FROM table_list WHERE conditions)
[GROUP BY group_by_list [HAVING conditions]]
[ORDER BY order_by_list]]

C# SQL Connection Code

public void ConnectToDatabase()
{

//string connectionString = “”;
//string connectionString = ConfigurationManager.ConnectionStrings[“wcfConn”].ToString();

if (conn.State != ConnectionState.Open)
{
conn.ConnectionString = connectionString;
conn.Open();
comm.Connection = conn;
}
comm.Parameters.Clear();
}

If you do it from code behind, it will look something like this,

data source={INSERT IP / localhost here };initial catalog={insert database name here};User ID={insert user id here};Password={Insert Password here}

If you insert from web.config, it will look something like this,

<connectionStrings>
<add name="wcfConn"
connectionString="data source={Insert IP/localhost};initial catalog={Insert database name};User ID={Insert username here};Password={Insert Password here};"
providerName="System.Data.SqlClient" />
</connectionStrings>

cheat sheetsql