SQL Not Like with Multiple Values

I came across a forum post where someone wanted to use SQL NOT LIKE with multiple values.

They were trying to exclude multiple values from the SQL query, but they were needing to use wildcards.

If you wanted to just filter values without wildcards, you would use  the following query.

select *
from table1
where column1 not in ('value1','value2','value3');

The only problem was that they needed to compare using the LIKE operator.

It would be nice if you could just stick some wildcards in the in clause like this:

where column1 not in ('%value1%','%value2%','%value3%')

But, you can’t stick a wildcard inside of the IN clause. So, here is the easiest solution.

select *
from table1
where column1 not like '%value1%'
and column1 not like '%value2%'
and column1 not like'%value3%';

If you want to play around with the Boolean logic, you rearrange the query like this.

select *
from table1
where not (column1 like '%value1%'
or column1 like '%value2%'
or column1 like'%value3%');

Source: http://www.sqltrainingonline.com/sql-not-like-with-multiple-values/

How to count values with MySQL queries

Let’s say you have the following table called votes that keeps track of how people voted and you want a query to count the number of votes for you instead of having to loop through all the rows with a counter in PHP.

person	vote
obama	yes
mccain	no
obama	yes
obama	no
mccain	yes
obama	yes
obama	yes
obama	no
mccain	no

Here is the MySQL query that would do just the job.

SELECT person,
SUM(IF(vote = "yes", 1,0)) AS `yes_votes`,
SUM(IF(vote = "no", 1,0)) AS `no_votes`,
COUNT(vote) AS `total`
FROM votes
GROUP BY person
ORDER BY yes_votes DESC


The following would be the result of the query:

person	yes_votes	no_votes	total
obama	4	2	6
mccain	1	2	3

The beauty of the query all lies in the SUM IF statements. For example, in order to count the yes votes, the IF statement will check to see if vote = “yes” as it loops through all the rows. If so, the yes_votes column alias is incremented by 1. The same procedure goes for counting the no votes. The COUNT statements keeps a tally on the total number of votes. The GROUP BY person statement allows the vote counters to calculate the numbers by person instead of the total number of yes and no votes.

Now, you do not have to loop through your MySQL results to count column values =)

 

Source: http://www.randomsnippets.com/2008/10/05/how-to-count-values-with-mysql-queries/

Handling multiple selection drop down list box

The code is here:

<form method=post action=''>
<select name='color[]' size=4 multiple>
<option value='blue'>Blue</option>
<option value='green'>Green</option>
<option value='red'>Red</option>
<option value='yellow'>Yelllow</option>
<option value='' selected>Select a Color </option>
<option value='white'>White</option>
</select> 
<input type=submit></form>

///// collecting form data /////

@$color= $_POST['color'];
if( is_array($color)){
while (list ($key, $val) = each ($color)) {
echo "$val <br>";
}
}//
else{echo "not array";}

Source: http://www.plus2net.com/php_tutorial/pb-drop-multiple.php

Creating a Dependent Dropdown List with PHP, jQuery and Ajax.

There are times in a web application where you want to populate a dropdown list based on the value of another drop down list. Scenarios like this can be populating a Country’s State dropdown based on the value of the Country selected, populating product sub-categories based on the parent category. In this example, we will be creating a dropdown list for category/subcategory for a product in an eCommerce website.
Create a database called dependent_list.

We will Create 2 tables: categories and subcategories with the following queries:

CREATE TABLE IF NOT EXISTS `categories` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`category_name` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `subcategories` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`categoryID` INT(11) NOT NULL,
`subcategory_name` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Some data has been inserted into both tables as shown in database. categoryID is a foreign key in subcategories table i.e 1 category has multiple subcategories.

Create a project folder called ‘dependent_list’ in your site root folder. Create a config.php file to store the database connection and add the following code:

1. <?php
3. mysql_connect(‘localhost’, ‘root’, ”);
4. mysql_select_db(‘dependent_list’);
6. ?>

Next Create an index.php file in the project folder and add the following code:

1. <?php
2. include(‘config.php’);
3. $query_parent = mysql_query(“SELECT * FROM categories”) or die(“Query failed: “.mysql_error());
4. ?>

1. <!doctype html>
2. <html>
3. <head>
4. <meta charset=“utf-8”>
5. <title>Dependent DropDown List</title>
6. <script type=“text/javascript” src=“js/jquery.js”></script>
7. <script type=“text/javascript”>
8. $(document).ready(function() {
10. $(“#parent_cat”).change(function() {
11. $(this).after(‘<div id=“loader”><img src=“img/loading.gif” alt=“loading subcategory” /></div>‘);
12. $.get(‘loadsubcat.php?parent_cat=’ + $(this).val(), function(data) {
13. $(“#sub_cat”).html(data);
14. $(‘#loader’).slideUp(200, function() {
15. $(this).remove();
16. });
17. });
18. });
20. });
21. </script>
22. </head>
24. <body>
25. <form method=“get”>
26. <label for=“category”>Parent Category</label>
27. <select name=“parent_cat” id=“parent_cat”>
28. <?php while($row = mysql_fetch_array($query_parent)): ?>
29. <option value=“<?php echo $row[‘id’]; ?>“><?php echo $row[‘category_name’]; ?></option>
30. <?php endwhile; ?>
31. </select>
32. <br/><br/>
34. <label>Sub Category</label>
35. <select name=“sub_cat” id=“sub_cat”></select>
36. </form>
37. </body>
38. </html>

On line 3, we queried our categories table to get all categories. We then populate the parent_cat dropdownlist with the categories on lines 33-37. Whenever the dropdown value for category is changed, a jquery changed event is triggered for the category dropdown list on line 10. On lines 10-18,it sends the id value of the selected category through jquery Ajax to a php script called loadsubcat.php which then queries the subcategories table for subcategories that belongs to the parent category id value using $_GET[] super global . The values returned is now appended to the sub_cat dropdown list. We also added an animated loading gif for user experience, it is displayed when the a value is selected for the parent category and removed using a jquery “slideUp” method after the subcategory has been populated.

Lastly, create a loadsubcat.php file in the project folder and add the following code.

1. <?php
2. include(‘config.php’);
4. $parent_cat = $_GET[‘parent_cat’];
6. $query = mysql_query(“SELECT * FROM subcategories WHERE categoryID = {$parent_cat}“);
7. while($row = mysql_fetch_array($query)) {
8. echo “<option value=’$row[id]‘>$row[subcategory_name]</option>”;
9. }
11. ?>

Now go to http://localhost/dependent_list and you will see how the dependent dropdownlist works

Source: http://www.sourcecodester.com/tutorials/php/5568/creating-dependent-dropdown-list-php-jquery-and-ajax.html

 

 

Refresh form but do not resubmit (PHP)

This question has been asked so many times that most of you don’t want to hear it again. Unfortunately, the solutions that people are proposing are less than ideal.

For those who are new to the concept, a quick explanation is in order. You have a form that you display, validate and act upon (send e-mail, save to database, upload files, etc.) Once this is done, the user might refresh the page and you will end up with duplicate entries.

Bad ideas to handle it are: splitting into multiple files to work around this problem, using the meta-refresh tag, setting unique identifiers and store them in session or in hidden fields, checking for duplicates after resubmission, using javascript…
If you want to know why they are bad, just post a comment and I will give you a detailed explanation of the particular case.

I found a quick and neat way to do it (perhaps I’m not the first, but I feel the need to share it). Append “?submit=true” to your form action and use header(“Location: …”) with the current URL. Thus, once form data has been saved, you will be redirected to the same page without the POST variables. If PHP complains that output was already sent, then you have a bad approach for your output. I usually store all my output in a variable(s) and echo it when everything is okay. More on output in another entry.

Example:

 <form class=”form-horizontal” role=”form” method=”post” action=”../add?submit=true”>

 

Then

header(‘Location: http://www.example.com/&#8217;);

 

Source: http://afilina.com/refresh-form-but-do-not-resubmit-php/comment-page-1/

Getting Multiple Array Form Values With PHP

if (isset($_POST['cname'])) {

for ( $i=0;$i<count($_POST['cname']);$i++) {

$contactname = $_POST['cname'][$i];

$conatctemail = $_POST['cemail'][$i];

$contacttel = $_POST['ctel'][$i];

}

}

Suber dari: http://www.web-design-talk.co.uk/94/getting-multiple-array-form-values-with-php/

How to disable the Enter key on HTML form

Normally when you have a form with several text input fields, it is undesirable that the form gets submitted when the user hits ENTER in a field. Some people are pressing the enter key instead of the tab key to get to the next field. They often do that by accident or because they are accustomed to terminate field input that way. If a browser regards hitting ENTER in a text input field as a request to submit the form immediately, there is no sure way to prevent that.

Add the below script to the <head> section of your page. The following code disables the enter key so that visitors of your web page can only use the tab key to get to the next field.

<script type=”text/javascript”>

function stopRKey(evt) {
var evt = (evt) ? evt : ((event) ? event : null);
var node = (evt.target) ? evt.target : ((evt.srcElement) ? evt.srcElement : null);
if ((evt.keyCode == 13) && (node.type==”text”))  {return false;}
}

document.onkeypress = stopRKey;

</script>

Diambil dari: http://webcheatsheet.com/javascript/disable_enter_key.php

How to Backup Linux? 15 Contoh perintah rsync

sync stands for remote sync.

rsync is used to perform the backup operation in UNIX / Linux.

rsync utility is used to synchronize the files and directories from one location to another in an effective way. Backup location could be on local server or on remote server.

Important features of rsync

• Speed: First time, rsync replicates the whole content between the source and destination directories. Next time, rsync transfers only the changed blocks or bytes to the destination location, which makes the transfer really fast.
• Security: rsync allows encryption of data using ssh protocol during transfer.
• Less Bandwidth: rsync uses compression and decompression of data block by block at the sending and receiving end respectively. So the bandwidth used by rsync will be always less compared to other file transfer protocols.
• Privileges: No special privileges are required to install and execute rsync

Syntax

$ rsync options source destination

Source and destination could be either local or remote. In case of remote, specify the login name, remote server name and location.

Example 1. Synchronize Two Directories in a Local Server

To sync two directories in a local computer, use the following rsync -zvr command.

$ rsync -zvr /var/opt/installation/inventory/ /root/temp
building file list ... done
sva.xml
svB.xml
.
sent 26385 bytes  received 1098 bytes  54966.00 bytes/sec
total size is 44867  speedup is 1.63
$

In the above rsync example:

• -z is to enable compression
• -v verbose
• -r indicates recursive

Now let us see the timestamp on one of the files that was copied from source to destination. As you see below, rsync didn’t preserve timestamps during sync.

$ ls -l /var/opt/installation/inventory/sva.xml /root/temp/sva.xml
-r--r--r-- 1 bin  bin  949 Jun 18  2009 /var/opt/installation/inventory/sva.xml
-r--r--r-- 1 root bin  949 Sep  2  2009 /root/temp/sva.xml

Example 2. Preserve timestamps during Sync using rsync -a

rsync option -a indicates archive mode. -a option does the following,

• Recursive mode
• Preserves symbolic links
• Preserves permissions
• Preserves timestamp
• Preserves owner and group

Now, executing the same command provided in example 1 (But with the rsync option -a) as shown below:

$ rsync -azv /var/opt/installation/inventory/ /root/temp/
building file list ... done
./
sva.xml
svB.xml
.
sent 26499 bytes  received 1104 bytes  55206.00 bytes/sec
total size is 44867  speedup is 1.63
$

As you see below, rsync preserved timestamps during sync.

$ ls -l /var/opt/installation/inventory/sva.xml /root/temp/sva.xml
-r--r--r-- 1 root  bin  949 Jun 18  2009 /var/opt/installation/inventory/sva.xml
-r--r--r-- 1 root  bin  949 Jun 18  2009 /root/temp/sva.xml

Example 3. Synchronize Only One File

To copy only one file, specify the file name to rsync command, as shown below.

$ rsync -v /var/lib/rpm/Pubkeys /root/temp/
Pubkeys

sent 42 bytes  received 12380 bytes  3549.14 bytes/sec
total size is 12288  speedup is 0.99

Example 4. Synchronize Files From Local to Remote

rsync allows you to synchronize files/directories between the local and remote system.

$ rsync -avz /root/temp/ thegeekstuff@192.168.200.10:/home/thegeekstuff/temp/
Password:
building file list ... done
./
rpm/
rpm/Basenames
rpm/Conflictname

sent 15810261 bytes  received 412 bytes  2432411.23 bytes/sec
total size is 45305958  speedup is 2.87

While doing synchronization with the remote server, you need to specify username and ip-address of the remote server. You should also specify the destination directory on the remote server. The format is username@machinename:path

As you see above, it asks for password while doing rsync from local to remote server.

Sometimes you don’t want to enter the password while backing up files from local to remote server. For example, If you have a backup shell script, that copies files from local to remote server using rsync, you need the ability to rsync without having to enter the password.

To do that, setup ssh password less login as we explained earlier.

Example 5. Synchronize Files From Remote to Local

When you want to synchronize files from remote to local, specify remote path in source and local path in target as shown below.

$ rsync -avz thegeekstuff@192.168.200.10:/var/lib/rpm /root/temp
Password:
receiving file list ... done
rpm/
rpm/Basenames
.
sent 406 bytes  received 15810230 bytes  2432405.54 bytes/sec
total size is 45305958  speedup is 2.87

Example 6. Remote shell for Synchronization

rsync allows you to specify the remote shell which you want to use. You can use rsync ssh to enable the secured remote connection.

Use rsync -e ssh to specify which remote shell to use. In this case, rsync will use ssh.

$ rsync -avz -e ssh thegeekstuff@192.168.200.10:/var/lib/rpm /root/temp
Password:
receiving file list ... done
rpm/
rpm/Basenames

sent 406 bytes  received 15810230 bytes  2432405.54 bytes/sec
total size is 45305958  speedup is 2.87

Example 7. Do Not Overwrite the Modified Files at the Destination

In a typical sync situation, if a file is modified at the destination, we might not want to overwrite the file with the old file from the source.

Use rsync -u option to do exactly that. (i.e do not overwrite a file at the destination, if it is modified). In the following example, the file called Basenames is already modified at the destination. So, it will not be overwritten with rsync -u.

$ ls -l /root/temp/Basenames
total 39088
-rwxr-xr-x 1 root root        4096 Sep  2 11:35 Basenames

$ rsync -avzu thegeekstuff@192.168.200.10:/var/lib/rpm /root/temp
Password:
receiving file list ... done
rpm/

sent 122 bytes  received 505 bytes  114.00 bytes/sec
total size is 45305958  speedup is 72258.31

$ ls -lrt
total 39088
-rwxr-xr-x 1 root root        4096 Sep  2 11:35 Basenames

Example 8. Synchronize only the Directory Tree Structure (not the files)

Use rsync -d option to synchronize only directory tree from source to the destination. The below example, synchronize only directory tree in recursive manner, not the files in the directories.

$ rsync -v -d thegeekstuff@192.168.200.10:/var/lib/ .
Password:
receiving file list ... done
logrotate.status
CAM/
YaST2/
acpi/

sent 240 bytes  received 1830 bytes  318.46 bytes/sec
total size is 956  speedup is 0.46

Example 9. View the rsync Progress during Transfer

When you use rsync for backup, you might want to know the progress of the backup. i.e how many files are copies, at what rate it is copying the file, etc.

rsync –progress option displays detailed progress of rsync execution as shown below.

$ rsync -avz --progress thegeekstuff@192.168.200.10:/var/lib/rpm/ /root/temp/
Password:
receiving file list ...
19 files to consider
./
Basenames
     5357568 100%   14.98MB/s    0:00:00 (xfer#1, to-check=17/19)
Conflictname
       12288 100%   35.09kB/s    0:00:00 (xfer#2, to-check=16/19)
.
.
.
sent 406 bytes  received 15810211 bytes  2108082.27 bytes/sec
total size is 45305958  speedup is 2.87

You can also use rsnapshot utility (that uses rsync) to backup local linux server, or backup remote linux server.

Example 10. Delete the Files Created at the Target

If a file is not present at the source, but present at the target, you might want to delete the file at the target during rsync.

In that case, use –delete option as shown below. rsync delete option deletes files that are not there in source directory.

# Source and target are in sync. Now creating new file at the target.
$ > new-file.txt

$ rsync -avz --delete thegeekstuff@192.168.200.10:/var/lib/rpm/ .
Password:
receiving file list ... done
deleting new-file.txt
./

sent 26 bytes  received 390 bytes  48.94 bytes/sec
total size is 45305958  speedup is 108908.55

Target has the new file called new-file.txt, when synchronize with the source with –delete option, it removed the file new-file.txt

Example 11. Do not Create New File at the Target

If you like, you can update (Sync) only the existing files at the target. In case source has new files, which is not there at the target, you can avoid creating these new files at the target. If you want this feature, use –existing option with rsync command.

First, add a new-file.txt at the source.

[/var/lib/rpm ]$ > new-file.txt

Next, execute the rsync from the target.

$ rsync -avz --existing root@192.168.1.2:/var/lib/rpm/ .
root@192.168.1.2's password:
receiving file list ... done
./

sent 26 bytes  received 419 bytes  46.84 bytes/sec
total size is 88551424  speedup is 198991.96

If you see the above output, it didn’t receive the new file new-file.txt

Example 12. View the Changes Between Source and Destination

This option is useful to view the difference in the files or directories between source and destination.

At the source:

$ ls -l /var/lib/rpm
-rw-r--r-- 1 root root  5357568 2010-06-24 08:57 Basenames
-rw-r--r-- 1 root root    12288 2008-05-28 22:03 Conflictname
-rw-r--r-- 1 root root  1179648 2010-06-24 08:57 Dirnames

At the destination:

$ ls -l /root/temp
-rw-r--r-- 1 root root    12288 May 28  2008 Conflictname
-rw-r--r-- 1 bin  bin   1179648 Jun 24 05:27 Dirnames
-rw-r--r-- 1 root root        0 Sep  3 06:39 Basenames

In the above example, between the source and destination, there are two differences. First, owner and group of the file Dirname differs. Next, size differs for the file Basenames.

Now let us see how rsync displays this difference. -i option displays the item changes.

$ rsync -avzi thegeekstuff@192.168.200.10:/var/lib/rpm/ /root/temp/
Password:
receiving file list ... done
>f.st.... Basenames
.f....og. Dirnames

sent 48 bytes  received 2182544 bytes  291012.27 bytes/sec
total size is 45305958  speedup is 20.76

In the output it displays some 9 letters in front of the file name or directory name indicating the changes.

In our example, the letters in front of the Basenames (and Dirnames) says the following:

> specifies that a file is being transferred to the local host.
f represents that it is a file.
s represents size changes are there.
t represents timestamp changes are there.
o owner changed
g group changed.

Example 13. Include and Exclude Pattern during File Transfer

rsync allows you to give the pattern you want to include and exclude files or directories while doing synchronization.

$ rsync -avz --include 'P*' --exclude '*' thegeekstuff@192.168.200.10:/var/lib/rpm/ /root/temp/
Password:
receiving file list ... done
./
Packages
Providename
Provideversion
Pubkeys

sent 129 bytes  received 10286798 bytes  2285983.78 bytes/sec
total size is 32768000  speedup is 3.19

In the above example, it includes only the files or directories starting with ‘P’ (using rsync include) and excludes all other files. (using rsync exclude ‘*’ )

Example 14. Do Not Transfer Large Files

You can tell rsync not to transfer files that are greater than a specific size using rsync –max-size option.

$ rsync -avz --max-size='100K' thegeekstuff@192.168.200.10:/var/lib/rpm/ /root/temp/
Password:
receiving file list ... done
./
Conflictname
Group
Installtid
Name
Sha1header
Sigmd5
Triggername

sent 252 bytes  received 123081 bytes  18974.31 bytes/sec
total size is 45305958  speedup is 367.35

max-size=100K makes rsync to transfer only the files that are less than or equal to 100K. You can indicate M for megabytes and G for gigabytes.

Example 15. Transfer the Whole File

One of the main feature of rsync is that it transfers only the changed block to the destination, instead of sending the whole file.

If network bandwidth is not an issue for you (but CPU is), you can transfer the whole file, using rsync -W option. This will speed-up the rsync process, as it doesn’t have to perform the checksum at the source and destination.

#  rsync -avzW  thegeekstuff@192.168.200.10:/var/lib/rpm/ /root/temp
Password:
receiving file list ... done
./
Basenames
Conflictname
Dirnames
Filemd5s
Group
Installtid
Name

sent 406 bytes  received 15810211 bytes  2874657.64 bytes/sec
total size is 45305958  speedup is 2.87

Source Link: http://www.thegeekstuff.com/2010/09/rsync-command-examples/

How Do I install or create or edit my own cron jobs?

To edit your crontab file, type the following command at the UNIX / Linux shell prompt:
$ crontab -e

Syntax of crontab (field description)

The syntax is:

 1 2 3 4 5 /path/to/command arg1 arg2

OR

1 2 3 4 5 /root/backup.sh

Where,

• 1: Minute (0-59)
• 2: Hours (0-23)
• 3: Day (0-31)
• 4: Month (0-12 [12 == December])
• 5: Day of the week(0-7 [7 or 0 == sunday])
• /path/to/command – Script or command name to schedule

Easy to remember format:

* * * * * command to be executed
- - - - -
| | | | |
| | | | ----- Day of week (0 - 7) (Sunday=0 or 7)
| | | ------- Month (1 - 12)
| | --------- Day of month (1 - 31)
| ----------- Hour (0 - 23)
------------- Minute (0 - 59)

Your cron job looks as follows for system jobs:

1 2 3 4 5 USERNAME /path/to/command arg1 arg2

OR

1 2 3 4 5 USERNAME /path/to/script.sh

Example: Run backup cron job script

If you wished to have a script named /root/backup.sh run every day at 3am, your crontab entry would look like as follows. First, install your cronjob by running the following command:
# crontab -e
Append the following entry:
0 3 * * * /root/backup.sh
Save and close the file.

More examples

To run /path/to/command five minutes after midnight, every day, enter:
5 0 * * * /path/to/command
Run /path/to/script.sh at 2:15pm on the first of every month, enter:
15 14 1 * * /path/to/script.sh
Run /scripts/phpscript.php at 10 pm on weekdays, enter:
0 22 * * 1-5 /scripts/phpscript.php
Run /root/scripts/perl/perlscript.pl at 23 minutes after midnight, 2am, 4am …, everyday, enter:
23 0-23/2 * * * /root/scripts/perl/perlscript.pl
Run /path/to/unixcommand at 5 after 4 every Sunday, enter:
5 4 * * sun /path/to/unixcommand

How do I use operators?

An operator allows you to specifying multiple values in a field. There are three operators:

1. The asterisk (*) : This operator specifies all possible values for a field. For example, an asterisk in the hour time field would be equivalent to every hour or an asterisk in the month field would be equivalent to every month.
2. The comma (,) : This operator specifies a list of values, for example: “1,5,10,15,20, 25”.
3. The dash (-) : This operator specifies a range of values, for example: “5-15” days , which is equivalent to typing “5,6,7,8,9,….,13,14,15” using the comma operator.

How do I disable email output?

By default the output of a command or a script (if any produced), will be email to your local email account. To stop receiving email output from crontab you need to append >/dev/null 2>&1. For example:
0 3 * * * /root/backup.sh >/dev/null 2>&1
To mail output to particular email account let us say vivek@nixcraft.in you need to define MAILTO variable as follows:

MAILTO="vivek@nixcraft.in"
0 3 * * * /root/backup.sh >/dev/null 2>&1

Task: List all your cron jobs

Type the following command:
# crontab -l
# crontab -u username -l
To remove or erase all crontab jobs use the following command:
# crontab -r
crontab -r -u username

Use special string to save time

Instead of the first five fields, you can use any one of eight special strings. It will not just save your time but it will improve readability.

Special string	Meaning
@reboot	Run once, at startup.
@yearly	Run once a year, “0 0 1 1 *”.
@annually	(same as @yearly)
@monthly	Run once a month, “0 0 1 * *”.
@weekly	Run once a week, “0 0 * * 0”.
@daily	Run once a day, “0 0 * * *”.
@midnight	(same as @daily)
@hourly	Run once an hour, “0 * * * *”.

Examples

Run ntpdate command every hour:
@hourly /path/to/ntpdate
Make a backup everyday:
@daily /path/to/backup/script.sh

More about /etc/crontab file and /etc/cron.d/* directories

/etc/crontab is system crontabs file. Usually only used by root user or daemons to configure system wide jobs. All individual user must must use crontab command to install and edit their jobs as described above. /var/spool/cron/ or /var/cron/tabs/ is directory for personal user crontab files. It must be backup with users home directory.

Understanding Default /etc/crontab

Typical /etc/crontab file entries:

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/
# run-parts
01 * * * * root run-parts /etc/cron.hourly
02 4 * * * root run-parts /etc/cron.daily
22 4 * * 0 root run-parts /etc/cron.weekly
42 4 1 * * root run-parts /etc/cron.monthly

First, the environment must be defined. If the shell line is omitted, cron will use the default, which is sh. If the PATH variable is omitted, no default will be used and file locations will need to be absolute. If HOME is omitted, cron will use the invoking users home directory.

Additionally, cron reads the files in /etc/cron.d/ directory. Usually system daemon such as sa-update or sysstat places their cronjob here. As a root user or superuser you can use following directories to configure cron jobs. You can directly drop your scripts here. The run-parts command run scripts or programs in a directory via /etc/crontab file:

Directory	Description
/etc/cron.d/	Put all scripts here and call them from /etc/crontab file.
/etc/cron.daily/	Run all scripts once a day
/etc/cron.hourly/	Run all scripts once an hour
/etc/cron.monthly/	Run all scripts once a month
/etc/cron.weekly/	Run all scripts once a week

How do I use above directories to put my own scripts or jobs?

Here is a sample shell script called clean.cache. This script is created to clean up cached files every 10 days. This script is directly created at /etc/cron.daliy/ directory. In other words create a text file called /etc/cron.daily/clean.cache as follows.

 #!/bin/bash
# A sample shell script to clean cached file from lighttpd web server
CROOT="/tmp/cachelighttpd/"

# Clean files every $DAYS
DAYS=10

# Web server username and group name
LUSER="lighttpd"
LGROUP="lighttpd"

# Okay, let us start cleaning as per $DAYS
/usr/bin/find ${CROOT} -type f -mtime +${DAYS} | xargs -r /bin/rm

# Failsafe 
# if directory deleted by some other script just get it back 
if [ ! -d $CROOT ]
then
        /bin/mkdir -p $CROOT
        /bin/chown ${LUSER}:${LGROUP} ${CROOT}
fi

Save and close the file. Set the permissions:
# chmod +x /etc/cron.daily/clean.cache

How do I backup installed cron jobs entries?

Simply type the following command to backup your cronjobs to a nas server mounted at /nas01/backup/cron/users.root.bakup directory:
# crontab -l > /nas01/backup/cron/users.root.bakup
# crontab -u userName -l > /nas01/backup/cron/users.userName.bakup

Source Link : http://www.cyberciti.biz/faq/how-do-i-add-jobs-to-cron-under-linux-or-unix-oses/

Compacting MYSQL

DB’s with lots of activity should be
optimized regularly. Doing so is easy.

LOCK TABLES tablename READ;

CHECK TABLE affils;

UNLOCK TABLES;

OPTIMIZE TABLE tablename;

Source: http://bytes.com/topic/mysql/answers/75718-compacting-mysql