Day 4

MySQL is currently the most popular open source database server in existence. On top of that, it is very commonly used in conjunction with PHP scripts to create powerful and dynamic server-side applications.

MySQL Connect

Before you can do anything with MySQL in PHP you must first establish a connection to your web host’s MySQL database. This is done with the MySQL connect function.

Opening Database Connection:

PHP provides mysql_connect function to open a database connection. This function takes five parameters and returns a MySQL link identifier on success, or FALSE on failure.

Syntax:
connection mysql_connect(server,user,passwd,new_link,client_flag);

Parameter	Description
server	Optional – The host name running database server. If not specified then default value is localhost:3036.
user	Optional – The username accessing the database. If not specified then default is the name of the user that owns the server process.
passwd	Optional – The password of the user accessing the database. If not specified then default is an empty password.
new_link	Optional – If a second call is made to mysql_connect() with the same arguments, no new connection will be established; instead, the identifier of the already opened connection will be returned.
client_flags	Optional – A combination of the following constants: MYSQL_CLIENT_SSL – Use SSL encryption MYSQL_CLIENT_COMPRESS – Use compression protocol MYSQL_CLIENT_IGNORE_SPACE – Allow space after function names MYSQL_CLIENT_INTERACTIVE – Allow interactive timeout seconds of inactivity before closing the connection

NOTE: You can specify server, user, passwd in php.ini file instead of using them again and again in your every PHP scripts.

Closing Database Connection:

Its simplest function mysql_close PHP provides to close a database connection. This function takes connection resource returned by mysql_connect function. It returns TRUE on success or FALSE on failure.

Syntax:

bool mysql_close ( resource $link_identifier );
If a resource is not specified then last opend database is closed.

Example:

Try out following example to open and close a database connection:
<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘guest’;
$dbpass = ‘guest123’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘Connected successfully’;
mysql_close($conn);
?>

MySQL localhost

If you’ve been around the internet a while, you’ll know that IP addresses are used as identifiers for computers and web servers. In this example of a connection script, we assume that the MySQL service is running on the same machine as the script.

When the PHP script and MySQL are on the same machine, you can use localhost as the address you wish to connect to. localhost is a shortcut to just have the machine connect to itself. If your MySQL service is running at a separate location you will need to insert the IP address or URL in place of localhost. Please contact your web host for more details if localhost does not work.

PHP & MySQL Code:

<?php
mysql_connect(“localhost”, “admin”, “1admin”) or die(mysql_error());
echo “Connected to MySQL<br />”;
?>

Display:

Connected to MySQL

If you load the above PHP script to your webserver and everything works properly, then you should see “Connected to MySQL” displayed when you view the .php page.

The mysql_connect function takes three arguments. Server, username, and password. In our example above these arguments were:

Server – localhost
Username – admin
Password – 1admin

The “or die(mysql…” code displays an error message in your browser if –you’ve probably guessed it — there is an error in processing the connection! Double-check your username, password, or server if you receive this error.

Choosing the Working Database

After establishing a MySQL connection with the code above, you then need to choose which database you will be using with this connection. This is done with the mysql_select_db function.

PHP & MySQL Code:
<?php
mysql_connect(“localhost”, “admin”, “1admin”) or die(mysql_error());
echo “Connected to MySQL<br />”;
mysql_select_db(“test”) or die(mysql_error());
echo “Connected to Database”;
?>

Display:
Connected to MySQL
Connected to Database

MySQL Tables

A MySQL table is completely different than the normal table that you eat dinner on. In MySQL and other database systems, the goal is to store information in an orderly fashion. The table gets this done by making the table up of columns and rows.

The columns specify what the data is going to be, while the rows contain the actual data. Below is how you could imagine a MySQL table. (C = Column, R = Row)

C1 (Name)    C2 (Age)    C3 (Weight)
R1    R1 C1 (John)    R1 C2 (21)    R1 C3 (120)
R2    R2 C1 (Big Sally)    R2 C2 (27)    R2 C3 (400)
R3    R3 C1 (Tiny Tim)    R3 C2 (6)    R3 C3 (35)
R4    R4 C1 (Normal Ned)    R4 C2 (35)    R4 C3 (160)

We added the row and column number (R# C#) so that you can see that a row is side-to-side, while a column is up-to-down. In a real MySQL table only the value would be stored, not the R# and C#!

This table has three categories, or “columns”, of data: Name, Age, and Weight. This table has four entries, or in other words, four rows.

Create Table MySQL

Before you can enter data (rows) into a table, you must first define what kinds of data will be stored (columns). We are now going to design a MySQL query to summon our table from database land. In future lessons we will be using this table, so be sure to enter this query correctly!
PHP & MySQL Code:

<?php
// Make a MySQL Connection
mysql_connect(“localhost”, “admin”, “1admin”) or die(mysql_error());
mysql_select_db(“test”) or die(mysql_error());

// Create a MySQL table in the selected database
mysql_query(“CREATE TABLE example(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(30),
age INT)”)
or die(mysql_error());

echo “Table Created!”;

?>
Display:
Table Created!

Wow! That’s a lot of code all at once! Let’s get down in the dirt and figure this stuff out. We will be going through the code line by line.

MySQL Insert

When data is put into a MySQL table it is referred to as inserting data. When inserting data it is important to remember the exact names and types of the table’s columns. If you try to place a 500 word essay into a column that only accepts integers of size three, you will end up with a nasty error!

Inserting Data Into Your Table

Now that you have created your table, let’s put some data into that puppy! Here is the PHP/MySQL code for inserting data into the “example” table we created in the previous lesson.

PHP & MySQL Code:
<?php
// Make a MySQL Connection
mysql_connect(“localhost”, “admin”, “1admin”) or die(mysql_error());
mysql_select_db(“test”) or die(mysql_error());

// Insert a row of information into the table “example”
mysql_query(“INSERT INTO example
(name, age) VALUES(‘Timmy Mellowman’, ’23’ ) “)
or die(mysql_error());

mysql_query(“INSERT INTO example
(name, age) VALUES(‘Sandy Smith’, ’21’ ) “)
or die(mysql_error());

mysql_query(“INSERT INTO example
(name, age) VALUES(‘Bobby Wallace’, ’15’ ) “)
or die(mysql_error());

echo “Data Inserted!”;

?>
Display:
Data Inserted!
This code is much simpler to understand than the create table code, as will be most of the MySQL queries you will learn in the rest of this tutorial. Once again, we will cover the code line by line.

Retrieving Data With PHP & MySQL

Usually most of the work done with MySQL involves pulling down data from a MySQL database. In MySQL, data is retrieved with the “SELECT” keyword. Think of SELECT as working the same way as it does on your computer. If you wanted to copy some information in a document, you would first select the desired information, then copy and paste.

Using MySQL SELECT & FROM

Before attempting this lesson, be sure that you have created a table that contains some data, preferably the same data that we had in the MySQL Insert lesson. In this example, we will output the first entry of our MySQL “examples” table to the web browser.

PHP & MySQL Code:
<?php
// Make a MySQL Connection
mysql_connect(“localhost”, “admin”, “1admin”) or die(mysql_error());
mysql_select_db(“test”) or die(mysql_error());

// Retrieve all the data from the “example” table
$result = mysql_query(“SELECT * FROM example”)
or die(mysql_error());

// store the record of the “example” table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry

echo “Name: “.$row[‘name’];
echo ” Age: “.$row[‘age’];

Display:
Name: Tim Mellowman Age: 23

This is an example of how to use MySQL’s SELECT statement in PHP. Although the MySQL code is simple, printing out the information with PHP is somewhat more involved.

mysql_fetch_array: Why Use It?

Do you know what is returned when you used the mysql_query function to query a MySQL database? It isn’t something you can directly manipulate, that is for sure. Here is a sample SELECT query of a table we created in the MySQL Create Table lesson.

PHP and MySQL Code:
<?php
$result = mysql_query(“SELECT * FROM example”);
?>
The value that mysql_query returns and stores into $result is a special type of data, it is a MySQL Resource. Additional PHP functions are required to extract the data from this Resource.

A Row of Data

The mysql_fetch_array function takes a MySQL query resource as an argument ($result) and returns the first row of data returned by the mysql_query. Our table example basically looks like the table below.

example MySQL Table:

name    age
Timmy Mellowman    23
Sandy Smith    21
Bobby Wallace    15

The first row of data in this table is “Timmy Mellowman” and “23”. When we fetch an array from our MySQL Resource $result it should have Timmy’s name and age in it.

Getting a Row of Data using mysql_fetch_array

mysql_fetch_array returns the first row in a MySQL Resource in the form of an associative array. The columns of the MySQL Result can be accessed by using the column names of the table. In our table example these are: name and age. Here is the code to print out the first MySQL Result row.

PHP and MySQL Code:
<?php
// Make a MySQL Connection
$query = “SELECT * FROM example”;

$result = mysql_query($query) or die(mysql_error());

$row = mysql_fetch_array($result) or die(mysql_error());
echo $row[‘name‘]. ” – “. $row[‘age‘];
?>

Display:
Timmy Mellowman – 23

This is just what we expected would happen! Now, the cool thing about mysql_fetch_array is that you can use it again on the same MySQL Resource to return the second, third, fourth and so on rows. You can keep doing this until the MySQL Resource has reached the end (which would be three times in our example).

Sounds like an awfully repetitive task. It would be nice if we could get all our results from a MySQL Resource in an easy to do script.

Fetch Array While Loop

As we have said, the mysql_fetch_array function returns an associative array, but it also returns FALSE if there are no more rows to return! Using a PHP While Loop we can use this information to our advantage.

If we place the statement “$row = mysql_fetch_array()” as our while loop’s conditional statement we will accomplish two things:

We will get a new row of MySQL information that we can print out each time the while loop checks its conditional statement.
When there are no more rows the function will return FALSE causing the while loop to stop!

Now that we know what we need to do and how to go about doing it, the code pretty much writes itself, so let’s move on to the next lesson. Just kidding! Here is the code that will print out all the rows of our MySQL Resource.

PHP and MySQL Code:
<?php
// Make a MySQL Connection
$query = “SELECT * FROM example”;

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo $row[‘name’]. ” – “. $row[‘age’];
echo “<br />”;
}
?>

Display:
Timmy Mellowman – 23
Sandy Smith – 21
Bobby Wallace – 15

And there we have all the rows from our example table! You could apply this script to any MySQL table as long as you change both the table name in the query and the column names that we have in the associative array.

MySQL Update

Imagine that you have a MySQL table that holds the information of all the employees in your company. One of the columns in this table is called “Seniority” and it holds an integer value of how many months an employee has worked at your company. Unfortunately for you, your job is to update these numbers every month.

MySQL Update Example

Sandy has just had a birthday and she now 22 years old. Our job now is to update her age using MySQL commands like UPDATE, SET, and WHERE.

UPDATE – Performs an update MySQL query
SET – The new values to be placed into the table follow SET
WHERE – Limits which rows are affected

PHP & MySQL Code:
<?php
// Connect to MySQL

// Get Sandy’s record from the “example” table
$result = mysql_query(“UPDATE example SET age=’22’ WHERE age=’21′”)
or die(mysql_error());

$result = mysql_query(“SELECT * FROM example WHERE age=’22′”)
or die(mysql_error());

// get the first (and hopefully only) entry from the result
$row = mysql_fetch_array( $result );
echo $row[‘name’].” – “.$row[‘age’]. “<br />”;
?>
Display:
Sandy Smith – 22

MySQL Delete

Maintenance is a very common task that is necessary for keeping MySQL tables current. From time to time, you may even need to delete items from your database. Some potential reasons for deleting a record from MySQL include when: someone deletes a post from a forum, an employee leaves a company, or you’re trying to destroy your records before the federalies come!
MySQL DELETE Example

The DELETE query is very similar to the UPDATE Query in the previous lesson. We need to choose a table, tell MySQL to perform the deletion, and provide the requirements that a record must have for it to be deleted.
Say we want to delete the youngest employee from our previously created table because he has to go back to school. This is how we do it.

PHP & MySQL Code:
<?php
// Connect to MySQL

// Delete Bobby from the “example” MySQL table
mysql_query(“DELETE FROM example WHERE age=’15′”)
or die(mysql_error());
?>

It is important to note that this query would have deleted ALL records that had an age of 15. Since Bobby was the only 15 year old this was not a problem.

Creating a Database:

To create and delete a database you should have admin priviledge. Its very easy to create a new MySQL database. PHP uses mysql_query function to create a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax:
bool mysql_query( sql, connection );

Parameter    Description
sql    Required – SQL query to create a database
connection    Optional – if not specified then last opend connection by mysql_connect will be used.

Example:
Try out following example to create a database:

<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = ‘rootpassword’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘Connected successfully’;
$sql = ‘CREATE Database test_db’;
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die(‘Could not create database: ‘ . mysql_error());
}
echo “Database test_db created successfully\n”;
mysql_close($conn);
?>

Selecting a Database:

Once you estblish a connection with a database server then it is required to select a particular database where your all the tables are associated.
This is required because there may be multiple databases residing on a single server and you can do work with a single database at a time.
PHP provides function mysql_select_db to select a database.It returns TRUE on success or FALSE on failure.

Syntax:
bool mysql_select_db( db_name, connection );

Parameter    Description
db_name    Required – Database name to be selected
connection    Optional – if not specified then last opend connection by mysql_connect will be used.

Example:

Here is the example showing you how to select a database.

<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘guest’;
$dbpass = ‘guest123’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘Connected successfully’;
mysql_select_db( ‘test_db’ );
mysql_close($conn);
?>

Creating Database Tables:

To create tables in the new database you need to do the same thing as creating the database. First create the SQL query to create the tables then execute the query using mysql_query() function.

Example:
Try out following example to create a table:

<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = ‘rootpassword’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘Connected successfully’;
$sql = ‘CREATE TABLE employee( ‘.
’emp_id INT NOT NULL AUTO_INCREMENT, ‘.
’emp_name VARCHAR(20) NOT NULL, ‘.
’emp_address VARCHAR(20) NOT NULL, ‘.
’emp_salary INT NOT NULL, ‘.
‘join_date timestamp(14) NOT NULL, ‘.
‘primary key ( emp_id ))’;

mysql_select_db(‘test_db’);
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die(‘Could not create table: ‘ . mysql_error());
}
echo “Table employee created successfully\n”;
mysql_close($conn);
?>

In case you need to create many tables then its better to create a text file first and put all the SQL commands in that text file and then load that file into $sql variable and excute those commands.

Consider the following content in sql_query.txt file

CREATE TABLE employee(
emp_id INT NOT NULL AUTO_INCREMENT,
emp_name VARCHAR(20) NOT NULL,
emp_address VARCHAR(20) NOT NULL,
emp_salary INT NOT NULL,
join_date timestamp(14) NOT NULL,
primary key ( emp_id ));

$fp = fopen($query_file, ‘r’);
$sql = fread($fp, filesize($query_file));
fclose($fp);

Deleting a Database:

If a database is no longer required then it can be deleted forever. You can use pass an SQL command to mysql_query to delete a database.

Example:

Try out following example to drop a database.

<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = ‘rootpassword’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die(‘Could not connect: ‘ . mysql_error());
}
$sql = ‘DROP DATABASE test_db’;
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die(‘Could not delete database db_test: ‘ . mysql_error());
}
echo “Database deleted successfully\n”;
mysql_close($conn);
?>

WARNING: its very dangerous to delete a database and any table. So before deleting any table or database you should make sure you are doing everything intentionally.

Deleting a Table:
Its again a matter of issuing one SQL command through mysql_query function to delete any database table. But be very careful while using this command because by doing so you can delete some important information you have in your table.

Example:

Try out following example to drop a table:

<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = ‘rootpassword’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die(‘Could not connect: ‘ . mysql_error());
}
$sql = ‘DROP TABLE employee’;
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die(‘Could not delete table employee: ‘ . mysql_error());
}
echo “Table deleted successfully\n”;
mysql_close($conn);
?>

Data can be entered into MySQL tables by executing SQL INSERT statement through PHP function mysql_query. Below a simle example to insert a record into employee table.

Example:

Try out following example to insert record into employee table.

<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = ‘rootpassword’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die(‘Could not connect: ‘ . mysql_error());
}
$sql = ‘INSERT INTO employee ‘.
‘(emp_name,emp_address, emp_salary, join_date) ‘.
‘VALUES ( “guest”, “XYZ”, 2000, NOW() )’;

mysql_select_db(‘test_db’);
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die(‘Could not enter data: ‘ . mysql_error());
}
echo “Entered data successfully\n”;
mysql_close($conn);
?>

In real application, all the values will be taken using HTML form and then those values will be captured using PHP script and finally they will be inserted into MySQL tables.

While doing data insert its best practice to use function get_magic_quotes_gpc() to check if current configuration for magic quote is set or not. If this function returns false then use function addslashes() to add slashes before quotes.

Example:

Try out this example by putting this code into add_employee.php, this will take input using HTML Form and then it will create records into database.

<html>
<head>
<title>Add New Record in MySQL Database</title>
</head>
<body>
<?php
if(isset($_POST[‘add’]))
{
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = ‘rootpassword’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die(‘Could not connect: ‘ . mysql_error());
}

if(! get_magic_quotes_gpc() )
{
$emp_name = addslashes ($_POST[’emp_name’]);
$emp_address = addslashes ($_POST[’emp_address’]);
}
else
{
$emp_name = $_POST[’emp_name’];
$emp_address = $_POST[’emp_address’];
}
$emp_salary = $_POST[’emp_salary’];

$sql = “INSERT INTO employee “.
“(emp_name,emp_address, emp_salary, join_date) “.
“VALUES(‘$emp_name’,’$emp_address’,$emp_salary, NOW())”;
mysql_select_db(‘test_db’);
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die(‘Could not enter data: ‘ . mysql_error());
}
echo “Entered data successfully\n”;
mysql_close($conn);
}
else
{
?>
<form method=”post” action=”<?php $_PHP_SELF ?>”>
<table width=”400″ border=”0″ cellspacing=”1″ cellpadding=”2″>
<tr>
<td width=”100″>Employee Name</td>
<td><input name=”emp_name” type=”text” id=”emp_name”></td>
</tr>
<tr>
<td width=”100″>Employee Address</td>
<td><input name=”emp_address” type=”text” id=”emp_address”></td>
</tr>
<tr>
<td width=”100″>Employee Salary</td>
<td><input name=”emp_salary” type=”text” id=”emp_salary”></td>
</tr>
<tr>
<td width=”100″> </td>
<td> </td>
</tr>
<tr>
<td width=”100″> </td>
<td>
<input name=”add” type=”submit” id=”add” value=”Add Employee”>
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>

Getting Data From MySQL Database

Data can be fetched from MySQL tables by executing SQL SELECT statement through PHP function mysql_query. You have several options to fetch data from MySQL.

The most frequently used option is to use function mysql_fetch_array(). This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.

Below is a simple example to fetch records from employee table.

Example:

Try out following example to display all the records from employee table.

mysql_select_db(‘test_db’);
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die(‘Could not get data: ‘ . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
echo “EMP ID :{$row[’emp_id’]} <br> “.
“EMP NAME : {$row[’emp_name’]} <br> “.
“EMP SALARY : {$row[’emp_salary’]} <br> “.
“——————————–<br>”;
}
echo “Fetched data successfully\n”;
mysql_close($conn);
?>

The content of the rows are assigned to the variable $row and the values in row are then printed.

NOTE: Always remember to put curly brackets when you want to insert an array value directly into a string.
In above example the constant MYSQL_ASSOC is used as the second argument to mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index.

PHP provides another function called mysql_fetch_assoc() which also returns the row as an associative array.

Example:

Try out following example to display all the records from employee table using mysql_fetch_assoc() function.

mysql_select_db(‘test_db’);
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die(‘Could not get data: ‘ . mysql_error());
}
while($row = mysql_fetch_assoc($retval))
{
echo “EMP ID :{$row[’emp_id’]} <br> “.
“EMP NAME : {$row[’emp_name’]} <br> “.
“EMP SALARY : {$row[’emp_salary’]} <br> “.
“——————————–<br>”;
}
echo “Fetched data successfully\n”;
mysql_close($conn);
?>
You can also use the constant MYSQL_NUM, as the second argument to mysql_fetch_array(). This will cause the function to return an array with numeric index.

Example:

Try out following example to display all the records from employee table using MYSQL_NUM argument.

mysql_select_db(‘test_db’);
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die(‘Could not get data: ‘ . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
echo “EMP ID :{$row[0]} <br> “.
“EMP NAME : {$row[1]} <br> “.
“EMP SALARY : {$row[2]} <br> “.
“——————————–<br>”;
}
echo “Fetched data successfully\n”;
mysql_close($conn);
?>

All the above three examples will produce same result.

Releasing Memory:

Its a good dpractice to release cursor memory at the end of each SELECT statement. This can be done by using PHP function mysql_free_result(). Below is the example to show how it has to be used.

Example:

Try out following example

mysql_select_db(‘test_db’);
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die(‘Could not get data: ‘ . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
echo “EMP ID :{$row[0]} <br> “.
“EMP NAME : {$row[1]} <br> “.
“EMP SALARY : {$row[2]} <br> “.
“——————————–<br>”;
}
mysql_free_result($retval);
echo “Fetched data successfully\n”;
mysql_close($conn);
?>