Introduction

PHP (recursive acronym for "PHP: Hypertext Preprocessor") is a server-side scripting language that can be used on a host of webservers and platforms.

Server-side scripting language means the script is put into the HTML files that make up a site, but the server processes the script BEFORE it is sent to the client browser. PHP code is not visible if you view the source of a page because the server processes the code and returns only the output. This is easier to code and debug than writing CGI scripts in Perl or C since the HTML form and related code are all in one page and PHP puts any errors on the browser.

Assumptions: you are viewing this tutorial because you have access to a machine that can connect to an MySql server and you also have access to PHP.

Purpose: Demonstrate how to connect to an MySql database using PHP as a web front end/web interface for the database. You should be familiar with how to create tables using MySql and how a PHP script is structured.

There are several good tutorials on the web that introduce the fundamentals of PHP and MySql ->resources. I recommend that you read and try these tutorials before or in conjunction with the tutorial on this site. I especially recommend going through an introductory tutorial on PHP scripting to make sure that PHP is working ->phpinfo and some other script as examples.

Create a test database using MySql - you will need to know the name of your host machine (the machine that has the physical database),your username and password for the database.

Creating a database

Once you've determined that PHP and MySql database is working,you can then use them together. Insert,update or delete data in the database using an HTMl form.Information entered into the form and submitted, is read by a PHP script that will then place it into the database. This is passible by making PHP script/similar to CGI script/the action of a form, the form variables are created as PHP variables.

You can use PHP to create an MySql database without having to type commands into your MySql server through a shell prompt. PHP has several built in functions to interface with MySql as well as several other databases. MySql_create_db_function creates a new database. But before you can create a new database, you need to connect to the MySql server. The MySql_connect function opens a connection to a MySql server. The connection to the server is automaticallu closed when your PHP script finishes execution.

usage:
MySql_conect(hostname,username,password)
hostname - the name of the server that houses your MySql database
username - your username that you will use to connect to the db
password - your password

"FAQ: What is a 'localhost'?

The localhost can be considered a synonym for hostname, if the client and the server are using the same host. i.e. The same computer as that in which MySQL is installed."

mysql_create_db(database,link) *If the link argument is not used, the last opened connection will be used.

<?php
$MySql_link=mysql_connect("hostname","username","password") or die("unable to connect to database");
mysql_create_db("database",$MySql_link) or die("unable to create database");
?>

Creating database table

Creating a table for your database:

Typically you assign your SQL command that creates a table and the fields in the table to a variable. You can then use the MySql_db_query or MySql_query functions to execute a query on a database.

usage:
mysql_db_query(database,query,link)
mysql_query(query,link)

<?php
$query="create table tablename(field1 char,field2 varchar(5),field3 varchar(15))";
$result=mysql_db_query("database",$query,$MySql_link); 
//or $result=mysql_query($query,$MySql_link);
?>

If you have already created your database, you can use the MySql_select_db function to select it,

usage:
mysql_select_db(database,link);

<?php
$name=test;
$MySql_link=mysql_connect("hostname","username","password");
$MySql_select_db("database",$MySql_link);
$query="Update table set field2='$name'";
$result=mysql_query($query);
?>

Other functions to keep in mind:
If you want to close a connection that has been opened with MySql_connect before it has closed automatically when a script finishes execution, use MySql_close.

usage:
mysql_close(link);

<?php
$MySql_link=mysql_connect("hostname","username","password");
mysql_close($MySql_link);
?>

If you want to maintain a persistent connection to your MySql server that is not closed when the PHP script finishes execution, you would use the MySql_pconnect function.

usage:
mysql_pconnect(hostname,username,password)

<?php
$MySql_link=MySql_pconnect("hostname","username","password");
?>

To delete a database, use MySql_drop_db

usage:
mysql_drop_db(database,link)

<?php
$MySql_link=mysql_connect("hostname","username","password");
mysql_drop_db("database",$MySql_link);
?>

Working with the database

Performing basic operations on your database table.

To insert,delete,modify and search data into your database, you can use the MySql_query or MySql_db_query in conjunction with a variable, or without.

usage:
mysql_db_query(database,query,link)
mysql_query(query,link)

Adding data to a table

To add data to a table:

<?php
$query="INSERT INTO table (field) WHERE (field='Somevalue')";
$result=mysql_query($query); 
//or $result=mysql_query("INSERT INTO table");
?>

To insert more than one record:

<?php
$query="INSERT INTO table (field1,field2,field3) VALUES(field1='Somevalue',field2= 'Somevalue2',field3='Somevalue3')";
$result=mysql_query($query); 
//or $result=mysql_query("INSERT INTO table (field1, field2,field3) VALUES (field1='Somevalue',field2= 'Somevalue2',field3='Somevalue3')";
 
mysql_connect("hostname","username","password");
mysql_Select_db("database")
#query="INSERT INTO table VALUES('valuea','valueb','valuec')";
$result=mysql_query($query); 
//or $result=mysql_query("insert into table values('valuea', 'valueb','valuec'");
mysql_close(); //(optional)
?>

The value inserted into a table should match the amount of fields present in a table. In the table previously created, three fields were defined, so we are inserting three values. Valuea goes into field1, valueb goes into field2,etc. If the number of values being inserted is not equal to the number of fields, your insertion will fail.

Deleting data from a table

To delete all the data from a table:

<?php
$query="DELETE FROM table";
$result=mysql_query($query); 
//or $result=mysql_query("DELETE FROM table");
?>

To delete specific records the query would include a 'where' clause:

<?php
$query="DELETE FROM table WHERE(field1='Somevalue')";
$result=mysql_query($query); 
//or $result=mysql_query("DELETE FROM table WHERE(field1= 'SomeValue'");
?>

Modifying data already in a table

To change previously entered data:

<?php
$query="UPDATE table SET field='SomeValue'";
$result=mysql_query($query);
$query2="update table SET field='another value' WHERE(field='somevalue')",
$result2=mysql_query($query2);
?>

Searching data in a table

To return all the data in the table:

<?php
$query="SELECT * FROM table";
?>

You would typically look for a particular value in your table:

<?php
//Select all rows, everything in your table where field1= specified value
$query="SELECT * FROM table WHERE(field1='SomeValue')";
?>

The SELECT statement is used to retrieve data from a table.

usage:
SELECT what to select FROM which table WHERE conditions to satisfy.

What to select - is what field you want to see in the table, this has a list of field names or * to mean all fields.

Which table - is the table from which you want to retrieve data. The WHERE clause is optional - conditions to satisfy - indicates conditions that need to be met before data is retrieved.

<?php
$query="Select field1 FROM table";
 
$query="Select field1,field3 FROM table";
?>