Electronic Gadget

PHP- Automated Housekeeping

Queries are run by users through the web interface and by administrators through either administrative web interfaces or from the MySQL command interpreter. However, sometimes automated querying is necessary to produce periodic reports, update data, or delete temporary data. We discuss how queries can be automated in this section.

To show how queries can be automated, consider an example from the online winestore. The shopping cart in the online winestore is implemented using the winestore database. As discussed in Chapter 12, when an anonymous user adds a wine to their shopping basket, an order row is added to the orders table. The row is for a dummy customer with a cust_id=-1, and the next available order_id for this dummy customer. A related items row is created for each item in the shopping cart. The order_id is maintained in the session variable order_no so that orders by different anonymous customers aren’t confused.

Our system requirements in Chapter 1 specify that if a customer doesn’t purchase the wines in their shopping cart within one day, then the shopping cart should be emptied. This is an example of a DELETE operation that should be automated. It is impractical to require the administrator to run this query each day to remove junk data.

The following query can be run from the Linux shell to remove all orders rows that are more than one day old and are for the dummy customer:

% /usr/local/mysql/bin/mysql -uusername -psecret

-e ‘USE winestore; DELETE FROM orders WHERE

unix_timestamp(date) <

(unix_timestamp(date_add(now( ), interval -1 day)))

AND cust_id = -1;’

The MySQL time and date function unix_timestamp( ) converts a timestamp attribute to an integer that is accurate to the nearest second. In this query, we compare the value of the entry in the orders table with the value of exactly one day earlier from the current date and time. If the row is older than one day, then it is deleted. The same query works for the items table, when orders is replaced with items in the FROM clause.

13.1.1 cron Jobs

Having designed and tested the query, it can be inserted into a Unix cron table to automate the operation. The crond daemon is a process that runs by default in a Linux installation and continually checks the time. If any of the entries in user tables match the current time, then the commands in the entries are executed. Consider an example:

30 17 * * mon-fri echo ‘Go home!’

This prints the string at 5:30 p.m. each working day. The two asterisks mean every day of the month, and every month of the year respectively. The string mon-fri means the days Monday to Friday inclusive. More details about cron can be found by running man crontab in a Linux shell.

We can add our housekeeping query to our cron table by running:

% crontab -e

This edits the user’s cron table.

We have decided that the system should check for old shopping carts every 30 minutes. To do so, we add the following two lines to the file:

0 * * * * /usr/local/mysql/bin/mysql -uusername -psecret

-e ‘USE winestore; DELETE FROM orders WHERE

unix_timestamp(date) <

(unix_timestamp(date_add(now( ), interval -1 day)))

AND cust_id = -1;’

30 * * * * /usr/local/mysql/bin/mysql -uusername -psecret

-e ‘USE winestore; DELETE FROM items WHERE

unix_timestamp(date) <

(unix_timestamp(date_add(now( ), interval -1 day)))

AND cust_id = -1;’

The first line contains the complete query command for the orders table from earlier in this section, and the second line the items query. The shopping cart orders DELETE query runs exactly on each hour, while the items DELETE query runs at 30 minutes past each hour. Different times are used to balance the DBMS load.

Reports, updates, delete operations, and other tasks can be added to the cron table in a similar way. For example, we can output a simple report of the number of bottles purchased yesterday and send this to our email address each morning:

0 8 * * * mon-fri /usr/local/mysql/bin/mysql -uusername

-psecret -e ‘USE winestore; SELECT sum(qty) FROM

items WHERE unix_timestamp(date) >

(unix_timestamp(date_add(now( ), interval -1 day))) AND

cust_id != -1;’ | mail help@webdatabasebook.com

We could also have automatically written the information to a log file or to a table in the database.

More PHP Tutorial

If you are looking for full netting strategy tutorials, it is wise to go online. For, this is wherever you resolve prevail on generally of the property. Here are numerous return of the online tutorials as agreeably. Head of every, they are to be had on the house of expenditure. So, you tin can prevail on assistance lacking payments a definite buck – an value view for learners. What’s more, when you are consulting the online tutorials, you are discount roughly moment in time for your engaged schedule as agreeably. For, they are speedy to admission. Whats added, you tin can admission these tutorials from the comfort of your motherland.

The internet resolve propose you a figure of full strategy tutorials. So, you resolve progress given away of options and if you are not content with one, you tin can continuously toggle to a further one. Changed tutorials are to be had on changed aspects of strategy. So, you tin can continuously focus on your question of expertise. A further precise worthy of note introduce of the online tutorials is that they collapse you the opening to work together with supplementary passionate designers. This resolve continuously assist you augment manually.

Full strategy tutorials are proficient for in cooperation veterans and just starting out comers

Tutorials are not no more than intended for the beginners, roughly of them tin can in actuality assist the veteran persons as agreeably. In information, the certified designers dont have the scope to do a further possibility to train and refresh their erudition. Hence, the strategy tutorials turn out to be a functional tool for them to wait well-run with the most recent developments. Of possibility, the beginners resolve continuously charity performance from these tutorials. As soon as every, these tutorials propose them adequate care to discover the skills of conniving.

Do you know that just like many others who have discovered their money making internet business, you, too can earn extra money online while keeping your day job? Yes, Virginia, there are easy ways to make money online simply by using your skills and creativity and of course your internet connection and computer.

Let’s start with some of your internet skills and creativity. You can venture into designing blogs which are considered as the trend of the moment and seen to be having a strong staying power. In order to make money at home with blog designs, you must have some knowledge and skills like HTML, CSS, as well as PHP to be able to create not just appealing designs but also unique designs.

If designing is really your passion, you can also try your hand at creating custom designs for social networking sites such as twitter in making money from home. You can find several ways to make extra money with these network sites as many people are willing to pay for creating customized designs for them. You can pursue your passion by learning how to create these designs with some software designing programs you can easily find in the internet. You can also find several resources from the internet with regards to tutorials.

You can also offer your services to create high quality videos to be uploaded in the very popular YouTube. This is also considered as a fast way to make money.

You see, the internet is also world wide of opportunities where you can find some of the quick ways to make money.