Difference between revisions of "Using PHP with MySQL"

From Techotopia
Jump to: navigation, search
(→‎Getting Started with MySQL)
(→‎Inserting Data into a MySQL Database Table)
Line 99: Line 99:
 
3 rows in set (0.00 sec)
 
3 rows in set (0.00 sec)
 
</pre>
 
</pre>
 +
 +
== Connecting wiht PHP to a MySQL Server ==
 +
 +
Now that we have set upo our MySQL databse and entered some data it is time to look at using PHP to connect to the database so that we can start to query the database and add new data. The first step in our PHP script is to connect to our MySQL database server. This is achieved using the PHP ''mysql_connect()'' function. The ''mysql_connect()'' function creates a connection to the database server and returns a database resource handle. The function takes five optional arguments. The first is the address of the server hosting the database. This defaults to ''localhost:3306''. The second argument is the user name to be used to connect to the database. The third argument is the password associated with the user name.
 +
 +
If a second call is made to ''mysql_connect()'' the default behavior is to return the handle from the first call. Setting the fourth argument overrides this behavior and generate a new handle.
 +
 +
The final argument represents the flags for the PHP client which works in the background to connect to the database. Options are MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE and MYSQL_CLIENT_INTERACTIVE.
 +
 +
To disconnect form the database use the ''msql_close()'' function which takes as a sole argument the datbase resource handle returned by ''mysql_connect()''.
 +
 +
We can now write a script which will connect us to our sample database (remember to modify the password to match the one you specified when creating the user account):
 +
 +
<pre>
 +
<?php
 +
 +
        $dbhandle = mysql_connect('localhost', 'phptest', '3579php');
 +
 +
        if ($dbhandle)
 +
        {
 +
                echo "Connected to MySQL Database<br>";
 +
                mysql_close($dbhandle);
 +
 +
        } else {
 +
                echo "Unable to connect to MySQL Database<br>";
 +
        }
 +
 +
 +
?>
 +
</pre>
 +
 +
== Selecting Records from a MySQL Database Using PHP ==
 +
 +
Now tghat we have connected to our MySQL database we can begin accessing the data in our table. To achieve this we need to first select the database we wish to use by calling the ''mysql_select_db()'' function, passing through the database name as an argument. This will return a database handle. We then need to construct a SQL SELECT statement which we will pass to the PHP ''mysql_query()'' function. This function takes the database handle (returned by ''mysql_select_db()'') and the SQL query statement as arguments.
 +
 +
The ''mysql_query()'' function call places the results in an array which we can access using the ''mysql_fetch_array()'' function.
 +
 +
Bringing this all together gives us the following:
 +
 +
<pre>

Revision as of 14:59, 7 June 2007

There is a TV commercial that shows what would happen to the world if we didn't have plastic. In the commercial objects like telephones, chairs and computers disappear before our eyes. The objective is to make us appreciate what we would happen to our infrastructure without plastic. A similar analogy can be drawn to the need for databases on the world wide web. Without databases many of the web sites we rely on would quickly cease to function. It is not an exageration to suggest that databases form the heart of the web and the internet as we know it. Without some way to store and retrieve data the usefulness of the internet would be a greatly reduced.

One of the many advantages of PHP is the seemless way in which it integrates with the MySQL database. In this chapter we will take a close look at how to access information stored in a MySQL database from a PHP script and present that data to a user's web browser.

Before beginning it is important to note that MySQL is an advanced Relatation Database Management System (RDCMS), and as such, may be more sophisticated than is needed for every data storage need. In the next chapter (PHP and SQLite) we will look at a more lightweight solution that may meet less demanding needs.

This chapter assumes that you have MySQL installed and running on your server. There are many resources on the internet that can help you achieve this. The first place to start is the MySQL documentation.


Contents

Creating a MySQL User Account

The first task is to create a MySQL user account we can use for the purposes of this chapter. To set up as user account you will need to log into MySql using an account that has suitable privilages. If you are unsure about which user account to use speak to your system administrator or refer to the MySQL documentation. In this example we will assume you have the root password: 

mysql -p -u root
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10411 to server version: 5.0.27

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Having logged into MySQL we can now create a new user called phptest. In the example below we assign the password mypassword. It is advised you use your own passowrd in place of this one: 

mysql> GRANT ALL PRIVILEGES ON *.* TO 'phptest'@'localhost'
    -> IDENTIFIED BY '3579php' WITH GRANT OPTION;
Query OK, 0 rows affected (0.07 sec)

We now have a user account we can use to create a sample database table and can log into MySQL using this account and password: 

$ mysql -p -u phptest
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10476 to server version: 5.0.27

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Creating and Select MySQL Database

The next step is to create a database to store the data we will use in our PHP database example. We will call the database PHPsampleDb as follows: 

mysql> CREATE DATABASE PHPsampleDB;
Query OK, 1 row affected (0.00 sec)

We can then select the database using the USE command: 

mysql> USE PHPsampleDB;
Database changed

Our database is now created and selected. Next we need to add a table to the database.


Creating a MySQL Database Table

For the purposes of our example we will create a very simple database table. It is going to contain just three fields, a customer name, email address and account number. We will call this table customer and create it as follows: 

mysql> CREATE TABLE customer (name VARCHAR(30), email VARCHAR(30),
    -> account VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)

Inserting Data into a MySQL Database Table

As the final step isn setting our sample database we need to add some data to the table. This is achieved using tyhe INSERT command. We will add three initial rows to the table: 

mysql> INSERT INTO customer VALUES ('Gregory House', '[email protected]', '12345678');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer VALUES ('Robert Chase', '[email protected]', '87654321');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer VALUES ('Lisa Cuddy', '[email protected]', '24688642');
Query OK, 1 row affected (0.00 sec)

We can now check that these records are set up correctly using the SELECT command: 

mysql> SELECT * FROM customer;
+---------------+-----------------+----------+
| name          | email           | account  |
+---------------+-----------------+----------+
| Gregory House | [email protected] | 12345678 |
| Robert Chase  | [email protected] | 87654321 |
| Lisa Cuddy    | [email protected] | 24688642 |
+---------------+-----------------+----------+
3 rows in set (0.00 sec)

Connecting wiht PHP to a MySQL Server

Now that we have set upo our MySQL databse and entered some data it is time to look at using PHP to connect to the database so that we can start to query the database and add new data. The first step in our PHP script is to connect to our MySQL database server. This is achieved using the PHP mysql_connect() function. The mysql_connect() function creates a connection to the database server and returns a database resource handle. The function takes five optional arguments. The first is the address of the server hosting the database. This defaults to localhost:3306. The second argument is the user name to be used to connect to the database. The third argument is the password associated with the user name.

If a second call is made to mysql_connect() the default behavior is to return the handle from the first call. Setting the fourth argument overrides this behavior and generate a new handle.

The final argument represents the flags for the PHP client which works in the background to connect to the database. Options are MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE and MYSQL_CLIENT_INTERACTIVE.

To disconnect form the database use the msql_close() function which takes as a sole argument the datbase resource handle returned by mysql_connect().

We can now write a script which will connect us to our sample database (remember to modify the password to match the one you specified when creating the user account): 

<?php

        $dbhandle = mysql_connect('localhost', 'phptest', '3579php');

        if ($dbhandle)
        {
                echo "Connected to MySQL Database<br>";
                mysql_close($dbhandle);

        } else {
                 echo "Unable to connect to MySQL Database<br>";
        }


?>

Selecting Records from a MySQL Database Using PHP

Now tghat we have connected to our MySQL database we can begin accessing the data in our table. To achieve this we need to first select the database we wish to use by calling the mysql_select_db() function, passing through the database name as an argument. This will return a database handle. We then need to construct a SQL SELECT statement which we will pass to the PHP mysql_query() function. This function takes the database handle (returned by mysql_select_db()) and the SQL query statement as arguments.

The mysql_query() function call places the results in an array which we can access using the mysql_fetch_array() function.

Bringing this all together gives us the following: 





 

Retrieved from "https://www.techotopia.com/index.php?title=Using_PHP_with_MySQL&oldid=1428"