MySQL, Apache, and PHP

Now that you have an instance running LAMP, it's time to use it. We're going to build a simple web page to put data into a table in a MySQL database and to display the data in that table.

A simple PHP Script

You are going to be building files in notepad (on Windows) and then uploading them to your server. You could do text editing directly on Linux but the program you would use, called "vi", is difficult to learn. Uploading files will be a little cumbersome but much easier than learning "vi".

The first PHP program we will put a short message on the screen saying "This is my first php script".

Put the following in a file using Notepad.

<?php
echo "This is my first php script<br />";
?>

Save the file as phpinfo.php (case does matter — make this all lower case.)

Now, upload this file to your AMI. The Apache webserver installed on your AMI has a directory where it expects your web files (html) to be. This is part of the configuration of the Apache webserver. For example, AU has their webserver read the "www" directory on your "G" drive to look for html files. Navigate in Tunnelier (in the window that shows your directory structure) to:

home/webuser/helloworld/htdocs

In that directory, you'll see a file called phpinfo.php. You are going to replace this file with your file. Drag/drop your phpinfo.php file from your computer to the remote AMI computer.

Now, go back to your web browser and go to the "Public DNS" URL for your AMI. You should see a page that has your message on it.

Connect to the MySQL database

Now we are going to connect to the MySQL database. We are going to use the "root" account (with no password) to login to MySQL. We're going to connect to the database called "test" that comes with the standard MySQL configuration. If you don't remember some of the MySQL from before, review the MySQL lab here

<?php
mysql_connect("localhost", "root") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("test") or die(mysql_error());
echo "Connected to Database";
?>

You can probably understand all of this code. The only part that might be confusing is "localhost". This is a Linux thing where "localhost" means "this computer". So, we are connecting to MySQL on "this computer". We'll see later how to connect to a MySQL database on a different computer.

To run this php script, upload your file as "connect.php" in the htdocs directory on your AMI. Your htdocs directory will now have two files "phpinfo.php" and "connect.php". To run the "connect.php" program, append /connect.php to the end of your "Public AMI". It'll look something like this:

http://ec2-174-129-109-70.compute-1.amazonaws.com/connect.php

Create a Table in MySQL

<?php
mysql_connect("localhost", "root") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("test") or die(mysql_error());
echo "Connected to Database";

$query="CREATE TABLE contacts (id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,phone varchar(20) NOT NULL,mobile varchar(20) NOT NULL,fax varchar(20) NOT NULL,email varchar(30) NOT NULL,web varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";

mysql_query($query);
mysql_close();
?>

In this script, we are issuing a CREATE TABLE statement. Note that $query is a PHP variable. If you were writing a long script in PHP, you could reset the variable multiple times and keep issuing mysql_query($query). Your code would look something like this:

$query = "Some SQL";
mysql_query($query);
$query = "Some more SQL";
mysql_query($query);
$query = "Yet more SQL";
mysql_query($query);

To run this php script, upload your file as "create.php" in the htdocs directory on your AMI. To run the "create.php" program, append /create.php to the end of your "Public AMI".

Create a Form to Enter Data into a Table

We need two parts to make this work. First, you'll create a Form in HTML:

<form action="insert.php" method="post">
First Name: <input type="text" name="first"><br>
Last Name: <input type="text" name="last"><br>
Phone: <input type="text" name="phone"><br>
Mobile: <input type="text" name="mobile"><br>
Fax: <input type="text" name="fax"><br>
E-mail: <input type="text" name="email"><br>
Web: <input type="text" name="web"><br>
<input type="Submit">
</form>

Save this form as index.htm. This is a special name given to the "default" page that the webserver searches for to display a page for a given URL. You can access this page just using the Public DNS URL. You do not need to append /index.htm to this URL (but it will still work if you do.)

You can see that the form "calls" a php script called "insert.php". Let's create that script:

<?php

$first=$_POST['first'];
$last=$_POST['last'];
$phone=$_POST['phone'];
$mobile=$_POST['mobile'];
$fax=$_POST['fax'];
$email=$_POST['email'];
$web=$_POST['web'];

mysql_connect("localhost", "root") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("test") or die(mysql_error());
echo "Connected to Database";

$query = "INSERT INTO contacts VALUES ('','$first','$last','$phone','$mobile','$fax','$email','$web')";
mysql_query($query);

echo "Row Inserted";

mysql_close();
?>

Upload "insert.php" to your AMI in the htdocs directory. Enter data using the form you created. Enter at least 3 rows before going on to the next step.

Display the Data from a Table

<?php
mysql_connect("localhost", "root") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("test") or die(mysql_error());
echo "Connected to Database";

$query="SELECT * FROM contacts";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

$i=0;
while ($i < $num) {

$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");

echo "<b>$first $last</b><br>Phone: $phone<br>Mobile: $mobile<br>Fax: $fax<br>E-mail: $email<br>Web: $web<br><hr><br>";

$i++;
}

?>

This is a little more complicated. Basically, you'll issue a query and put the data from your table into an "array". This is a special structure in php where all 3 rows (assuming you entered 3 rows earlier) are in a single "block" of memory:

row 1 row 2 row 3

The code uses a programming loop to read through and display (using echo) the data from the array. The $i++ increments the value in $i by 1 to the number of rows returned (in this case, 3).

Connect to MySQL on another Host

You have almost everything you need to complete the project (the assignment is at the bottom of this page. Throughout this activity, you've been using "localhost". To connect to MySQL on another host, you use the following connect string:

mysql_connect("url","username","password");

For the database on your current AMI, you would write:

<?php
mysql_connect("ec2-174-129-109-70.compute-1.amazonaws.com", "root") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("test") or die(mysql_error());
echo "Connected to Database";
?>

For the project, you'll be creating a second AMI and connecting to it using this technique. You'll need to log into mysql on your second AMI and grant privileges to your first AMI to access your database on your second AMI. You do this by issuing the following commands in mysql:

use test;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

That's all! Now you are ready for the project.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License