PHP is the programming language of the web it?s a fact the php is used on more webservers servers than any other web language. At the time of writing php is installed on just under than 24,000,000 Domains..

MYSQL is a commercial grade database application that is made available free under the Open Source to anyone. It?s had over 6 million installs ranging from large muli-national corporations to specialized embedded applications the website also claims mysql is installed on every continent in the world? Including antartica. At least they can blame the weather when the computer freezes? hehe! Currently MYSQL runs on more than 20 platforms including Linux, Windows, OS/X, HP-UX, AIX and Netware. Which is a perfect solution for portability requirements.

PHP - MYSQL it does have uses

The benefits to creating a site that implements a php and mysql setup are really down the site and how it wants to run and taking into account how specific data is stored and displayed, php and mysql can be used in many senarios including:

# Storing and Displaying Lots of Categorised data - For example the article you are reading now, has come from our mysql database. Sites with lots of information that needs to be categorised, stored and easily displayed would benefit from a mysql backend. Lets again take this site for example, if we created the site the old fashioned way then we would have to create a single HTML page for every article. Now lets say we we wanted to change a link in the menu right of this page then we would need to go through each and every single HTML page and manually change every element to it. Not fun I?m sure you?d agree. but with php and mysql all we now need to do is change one single php page and the whole site updates our link.. Saving you time and your sanity.

# Saving customer data into php- Many websites as an example use the duo to save customer data, Including Name, telephone, address etc.. well you get the picture. MYSQL can also track customers locations on the site and save that information into the database. PHP can also store customer purchasing and previous orders into the database which allows you to build up a perfect system for tracking customer trends and customers shopping habits based on country. All you would need to do is create a php that told mysql to grab the particular information only.

Installation

Please take note that your web host will have php and mysql pre installed on the server but and easy way to check is copy the code just below into notepad then save it as ?info.php?. Upload it to your webspace via FTP and then goto your browser and type in www.yourdomain.com/info.php or where ever you dropped the file.

< ?php
phpinfo();
?>

it should show you a lot of information such as the version of php your running and additonal, but useful information about the configuration of your php. A list of installed manuals is also included.

To install PHP & MYSQL on windows you can do it manually or you can use a program called WAMPserver which will automatically install the setup for you, just to let you know the W.A.M.P stands for windows, Apache, Mysql and PHP.

WAMPserver installation on windows

WAMP5 installs the following software on your machine Apache 1.3.31 ,PHP5, MySQL database ,PHPmyadmin and SQLitemanager on your computer. Essentially your going to turn your windows machine into a webserver beacause we are installing apache. PHP does need apache webserver to run in this setup usually.

You can download WAMPserver here: http://www.wampserver.com/en/download.php

Here are the installation instructions from wampservers site: http://www.wampserver.com/

When you install WAMP5, all the files are copied in the directory you choose. Conf files are then modified to point to that directory. It also installs a ?www? directory which will be your Document Root.

At the end of the installation, WAMP5 will automatically install Apache and MySQL as services :

- service? wampapache? : apache service

- service ?wampmysql? : mysql service

WAMP5?s installation is compact. This means that all files are copied to WAMP5?s directory. Only the MySQL conf file (usually my.ini) is copied to the Windows directory but as ?mywamp.ini? to avoid conflicts with other installs. You just have to click on the icon tray to access WAMP5?s menu, The icon tray reflects the status of your server, When you?ll uninstall WAMP5, all the services and files will be automatically deleted.

In part two will be getting down to the nitty gritty by setting up the mysql database and connecting to it through php.

For part 2 of this tutorial visit: http://www.chauy.com/2005/11/php-mysql-part-2/

Thanks!

About the Author: Tutorial provided by http://www.chauy.com - webmaster tutorials, Webmaster News and tools. This notice must remain intact. copyright (c) http://chauy.com - Used with permission

Whether you are an experienced web programmer or a complete novice attempting to provide data interactivity with your web site, MyQSL is an easy to use and free database solution that can allow you to store and configure data to be displayed on your web site.

Whether you are an experienced web programmer or a complete novice attempting to provide data interactivity with your web site, MyQSL is an easy to use and free database solution that can allow you to store and configure data to be displayed on your web site.

The best way to create and manage a MySQL database is to download an open source (free) program called PhpMyAdmin. PHPMyAdmin allows you to manage all aspects of both your database structure and data from one easy to use interface. This tool is intended to handle the administration of MySQL over the Web.

This tool provides an interface that allows you to create and drop databases, create, drop, or alter tables, delete, edit, or add fields, execute any SQL statement, manage keys on fields, manage privileges, and import and export data into various formats. That sounds like a complicated set of activities, but the easy to use graphical tools make things quite simple and easy to understand. If you make a mistake, the software even provides instructions on where you made your error.

For a complete demo see:
http://www.phpmyadmin.net/phpMyAdmin/
For documentation visit:
http://www.phpmyadmin.net/home_page/docs.php

Most Linux based web hosting companies provide PhpMyAdmin as a standard feature with their packages. It is also available in a Windows IIS version. If your hosting provider does not already have this product installed they will often install it for you, or even allow you to install it yourself. Setup is quick and easy if you follow the step-by-step installation documentation.

Step One: Creating your new database

When you log in to your PhpMyAdmin welcome page, the first step is to enter a name for your new database in a text box provided. You can name your database anything that you wish, however if you are creating the database to use with a script or software package that you purchased somewhere, the script provider will often suggest a preferred database name.
You should always create your database using the following format:

username_ databasename
Example: myusername_mydatabase

Your complete database name should always begin with your username followed by an underscore, then followed by the database name. This allows the server to know which user is in control of the new database, and it will also provide permission to access the database to only specific users. This also allows different users on the same server to use the same name for their own database, as you did, without interfering with your data that is helpful if more than one user on your server bought similar software for their own site. They can then also use the software providers preferred database name.

Step Two: Creating a table for your new database

After you have created a database, the next step is to create a table, or even multiple tables, for you to store data. A table is the part of your new database that actually stores data.

You create a table by selecting the database that you created from the drop box list of databases. Once a database is selected a new form appears and asks for you to create a new table.

You must decide what you want to name your table and enter that name into the name box. Try to choose a name that reflects the type of data that will be stored in the table, such as orders, users, or inventory.

You then must decide how many fields or columns of data that you want to store for each record. If you need for the table to store five (5) different items, such as username, users email address, users telephone number, users account number, and the users age, than you would need five (5) fields. Simply enter the number 5 in the appropriate box. Once you hit create, the system will create a table and will add those fields into the table for you. Dont worry about the number of fields you might need right now, as you can always add or delete fields later.

Step Three: Defining Fields

Once you have created your table you will be prompted to tell the database what features that you want each field to have. This looks complicated, but its not if you select your data type from the information below. You basically have to decide between three common data types and select the best choice for storing your data. If you make a mistake you can go back and edit the field.

If the field is to be used to store numbers, here are some choices:

TINYINT A very small integer. The signed range is -128 to 127.
SMALLINT - A small integer. The signed range is -32768 to 32767.
MEDIUMINT - A medium-size integer. The signed range is -8388608 to 8388607.
INT - A normal-size integer. The signed range is -2147483648 to 2147483647.
BIGINT A very large integer.

Some other less common number options include:

FLOAT- A floating-point number.
DOUBLE A double-precision floating-point number.
DECIMAL - A packed exact fixed-point number.

If the field is to be used to store text or both text and numbers combined, here are some choices:

VARCHAR is for varying characters and can be up to 255 characters in length.
TEXT is a column with a maximum length of 65,535 characters easy to search.
BLOB is a column with a maximum length of 65,535 characters case-sensitive.

If the field is to be used to store dates, here are some choices:

DATE - A date.
DATETIME - date and time combination.
TIMESTAMP - useful for recording the date and time of an INSERT or UPDATE operation.
TIME - A time.

Once you have selected the data type for your fileds you will need to let the system know how many characters that you will need to store in the field.

Example: if you are storing a username, you might want to select VARCHAR as your data type and allow up to 100 characters for that field. If you are creating a User Identification number you might want to select INT and allow up to six characters that would allow you to have up to 999,999 users.

The last step to creating your data fields is to select any special attributes that you may find helpful.
Some examples are:

Auto Increment : Auto-Increment fields are useful for assigning unique identification numbers for users, products, and customers, etc. By default, fields are incremented using number characters (like “1″, “2″).

Primary Key: The primary key is a data column that uniquely identifies a specific instance of that data. At least one of your fields must be a Primary Key. Username is an example of a good primary key. You do not want to have more than one individual having the same username.

Index Key: Allows you to speed up searches by designating a field as a preferred data source, especially when combining data from multiple tables.

Congratulations, once you have completed these steps you are ready to import data into your new database.

ABOUT THE AUTHOR

Backing Up And Restoring Your MySQL Database

 by: Vinu Thomas

If you’ve been using MySQL database to store your important data, it is imperative that you make a backup of your data to prevent any loss of data. This article shows you how to backup and restore data in your MySQL database. This process can also be used if you have to move your data to a new server.

Backing up your database

The quickest and easiest way to backup and restore your database would be to use MySQLDump. If you’ve got shell or telnet access to your server, you can backup MySQL data by issuing the mysqldump command. The syntax for the command is as follows.

mysqldump -u [uname] -p [pass] [dbname] > [backupfile.sql] [uname] - this is your database username [pass]- this is the password for your database [dbname] - the name of your database [backupfile.sql] - the filename for your database backup

To backup your database ‘Customers’ with the username ’sadmin’ and password ‘pass21′ to a file custback.sql, you would issue the command

mysqldump -u sadmin -p pass21 Customers > custback.sql

Issuing this command will backup the database to custback.sql. This file can be copied to a safe location or a backup media and stored. For more information on MySQLDump, you can check out : http://www.mysql.com/doc/en/mysqldump.html

Restoring your database

If you have to re-build your database from scratch, you can easily restore the mysqldump file by issuing the following command. This method will not work if the tables already exist in your database.

mysql - u sadmin -p pass21 Customers < custback.sql

If you need to restore existing databases, you’ll need to use MySQLImport. The syntax for mysqlimport is

mysqlimport [options] database textfile1

To restore your previously created custback.sql dump back to your Customers Database, you’d issue

mysqlimport -u sadmin -p pass21 Customers custback.sql

For more information on MySQLImport, you can check out : http://www.mysql.com/doc/en/mysqlimport.html

MySQL Database Handling in PHP

 by: John L

Most interactive websites nowadays require data to be presented dynamically and interactively based on input from the user. For example, a customer may need to log into a retail website to check his purchasing history. In this instance, the website would have stored two types of data in order for the customer to perform the check the customers personal login details; and the customers purchased items. This data can be stored in two types of storage flat files or databases.

Flat files are only feasible in very low to low volume websites as flat files have 3 inherent weaknesses:

1. The inability to index the data. This makes it necessary to potentially read ALL the data sequentially. This is a major problem if there are a lot of records in the flat file because the time required to read the flat file is proportionate to the number of records in the flat file.
2. The inability to efficiently control access by users to the data
3. The inefficient storage of the data. In most cases, the data would not be encrypted or compressed as this would exacerbate the problem no. 1 above

The alternative which is, in my opinion, the only feasible method, is to store the data in a database. One of the most prevalent databases in use is MySQL. Data that is stored in a database can easily be indexed, managed and stored efficiently. Besides that, most databases also provide a suite of accompanying utilities that allow the database administrator to maintain the database for example, backup and restore, etc.

Websites scripted using PHP are very well suited for the MySQL database as PHP has a custom and integrated MySQL module that communicates very efficiently with MySQL. PHP can also communicate with MySQL through the standard ODBC as MySQL is ODBC-compliant, However, this will not be as efficient as using the custom MySQL module for PHP.

The rest of this article is a tutorial on how to use PHP to:

1. Connect to a MySQL database
2. Execute standard SQL statements against the MySQL database

Starting a Session with MySQL

Before the PHP script can communicate with the database to query, insert or update the database, the PHP script will first need to connect to the MySQL server and specify which database in the MySQL server to operate on.

The mysql_connect() and mysql_select_db() functions are provided for this purpose. In order to connect to the MySQL server, the server name/address; a username; and a valid password is required. Once a connection is successful, the database needs to be specified.

The following 2 code excerpts illustrate how to perform the server connection and database selection:

@mysql_connect(”[servername]”, “[username]”, “[password]”) or die(”Cannot connect to DB!”);

@mysql_select_db(”[databasename]”) or die(”Cannot select DB!”);

The @ operator is used to suppress any error messages that mysql_connect() and mysql_select_db() functions may produce if an error occurred. The die() function is used to end the script execution and display a custom error message.

Executing SQL Statements against a MySQL database

Once the connection and database selection is successfully performed, the PHP script can now proceed to operate on the database using standard SQL statements. The mysql_query() function is used for executing standard SQL statements against the database. In the following example, the PHP script queries a table called tbl_login in the previously selected database to determine if a username/password pair provided by the user is valid.

Assumption:

The tbl_login table has 3 columns named login, password, last_logged_in. The last_logged_in column stores the time that the user last logged into the system.

The $username and $passwd variable should rightly

be set by the login form  through the POST method.

For the

purpose of this example, were manually coding

it.  $username = john;

$passwd = mypassword;  We generate a

SELECT SQL statement for execution.

$sql="SELECT * FROM tbl_login WHERE

login = '".$username."' AND

password = '".$passwd."'";  Execute the SQL

statement against the currently selected

database.  The results will be stored in the

$r variable.  $r = mysql_query($sql);

After the mysql_query() command executes,

the $r variable is examined to  d

etermine of the mysql_query() was

successfully executed.  if(!$r) {      $err=mysql_error();

print $err;      exit();  }  If everything

went well, check if the query returned a result

i.e. if the username/password  pair was

found in the database. The mysql_affected_rows()

function is used for this purpose.

mysql_affected_rows() will return the number of rows

in the database table that was affected

by the last query  if(mysql_affected_rows()==0){

print "Username/password pair is invalid.

Please try again.";  }  else {  If successful,

read out the last logged in time into a $last

variable for display to the user

$row=mysql_fetch_array($r);      $last=$row

["last_logged_in"];      print Login

successful. You last logged in at .$last..;  }

The above example demonstrated how a SELECT SQL statement is executed against the selected database. The same method is used to execute other SQL statements (e.g. UPDATE, INSERT, DELETE, etc.) against the database using the mysql_query() and mysql_affected_rows() functions.

This PHP scripting article is written by John L. John L is the Webmaster of The Ultimate BMW Blog! (http://www.bimmercenter.com).

The Ultimate BMW Blog!

daboss@bimmercenter.com