PHP + MySQL Quick Tutorial for Advanced LIS7008 Students

Yejun Wu
created on 6/18/2009, updated on 11/28/2015

This simple tutorial is for the advanced LIS 7008 students who are interested in integrating a database with the Web using PHP and MySQL. However, PHP and MySQL coding is not required for LIS 7008 students, therefore my technical support for your coding is very limited, and you are encouraged to explore and learn by yourself. Numerous PHP+MySQL tutorials are available on the Web, and you can also get a book from the library or Amazon.com, such as Julie C. Meloni, PHP, MySQL and Apache All in One, SAMS, 2008.

Preparation

Our Web+PHP+MySQL server (host) is assurbanipal.slis.lsu.edu (or classes.slis.lsu.edu), which is a LAMP (Linux Apache MySQL PHP) server. You need to install an SSH client (e.g., Putty SSH client, available at TigerWare via your PAWS portal) on your local computer to access this server. When accessing the Web server using an SSH client, probably you need to use "keyboard interactive" as the "Authentication Methods," but that can be client-dependent.

You also need to use a unix/linux text editor, such as emacs or vi, to write PHP code on the SSH client. Tutorials on emacs (or vi) in a unix/linux environment are available on the Web.

I will need to create a MySQL account for you. You can request the MySQL account by sending an email to me.

Testing the PHP server

Now open your SSH client to access the server. Your Web directory on the server is "public_html", so go into that directory to create your files.

First, let's test the PHP server. Create "my_phpinfo.php" with the following content:

  <?php
     phpinfo();
  ?>

Testing the MySQL server

Now let's test the MySQL server. First, initiate the MySQL server by typing this command (note "wuyj" below is MY userid):

  %mysql -u wuyj -p
  Enter password:
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 150
  Server version: 5.1.33 FreeBSD port: mysql-server-5.1.33
 
  Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Creating a database

Now create a database "wuyj_testdb" on the MySQL server and a table "person" in the database, and insert some data into the table.

  mysql> create database wuyj_testdb;
  Query OK, 1 row affected (0.00 sec)
 
  mysql> use wuyj_testdb;
  Database changed

  mysql> create table person(
      -> personid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      -> firstname VARCHAR (50) NOT NULL,
      -> lastname VARCHAR (50) NOT NULL,
      -> email VARCHAR (50) );

  Query OK, 0 rows affected (0.00 sec)
 
  mysql> insert into person VALUES ('1010', 'John', 'Smith', 'jsmith@yahoo.com');
  Query OK, 1 row affected (0.00 sec)
 
  mysql> insert into person VALUES ('1011', 'Julia', 'Roberts', 'jroberts@hotmail.com');
  Query OK, 1 row affected (0.00 sec)
 
  mysql> select * from person;
  +----------+-----------+----------+----------------------+
  | personid | firstname | lastname | email                |
  +----------+-----------+----------+----------------------+
  |     1010 | John      | Smith    | jsmith@yahoo.com     | 
  |     1011 | Julia     | Roberts  | jroberts@hotmail.com | 
  +----------+-----------+----------+----------------------+
  2 rows in set (0.00 sec)
 
  mysql> exit
  Bye
  %

Connecting the database using PHP

Note that the database "wuyj_testdb" is created on the MySQL server, rather than on your local computer or your own directory on the Web server. Now connect the database using PHP .

Create "connect_db.php" file with the following content:

  <?php
    $mysqli = new mysqli("localhost", "wuyj", "xxxxxxxx", "wuyj_testdb");
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    } else {
        printf("Host information: %s\n", mysqli_get_host_info($mysqli));
        mysqli_close($mysqli);
    }
  ?>

Note that "wuyj" is my userid and "xxxxxxxx" is my password to access the MySQL server. "wuyj_testdb" is the database I created on the MySQL server. "localhost" is assurbanipal.slis.lsu.edu because I SSH'ed this server to create the PHP code. You need to use your userid, password, and database in your code, but keep "localhost." Now run the code on a browser with this URL: http://assurbanipal.slis.lsu.edu/~wuyj/7008/php/connect_db.php.
Response: Host information: Localhost via UNIX socket

Quering the database and posting results

Now test retrieving data from the database. Create "retrieve_data.php" file with the following content:

  <?php 
    $dbc = mysqli_connect("localhost", "wuyj", "xxxxxxxx", "wuyj_testdb")
    or die ('Error connecting to MySQL server.');
 
    $query = "select * from person";
    $result = mysqli_query($dbc, $query) or die ('Error querying database.');
 
    while ($row = mysqli_fetch_array($result)) {
        $first_name = $row['firstname'];
        $last_name = $row['lastname'];
        $email = $row['email'];
        $msg = "$first_name $last_name, Email: $email <BR>";
        echo $msg;
    }
    mysqli_close($dbc);
  ?>