In the following examples, "username" refers to your account on the CS graduate network. Also, in the provided code, you need to use your own MySQL username, password and the name of the database you are using wherever you see a bunch of "***". The examples use the Student and Course tables from HW2. Links to sample code actually link to files that have a .txt extension, so you can actually see the code if you just click the link (although that may not work, depending on your browser setings -- it may just ignore the .txt extension and treat the file as HTML or PHP anyway). When you save the code, just make sure you get rid of the .txt extension...

Example 1: in this example, a simple PHP code segment connects to the MySQL server on dbase, executes a simple SELECT query, and displays the results of that query in HTML tabular format. The code is available here. Save it in your public_html folder using the file name list_all_stu.php. You should now be able to see it in action at this link:

http://www.cs.jhu.edu/~username/list_all_stu.php

Here is what the output should look like:

http://www.cs.jhu.edu/~yarowsky/DB_HW3/list_all_stu.php

Example 2: in this example, we will implement a more useful PHP/MySQL interface that, instead of carrying out a MySQL statement with hard-wired values, the user will provide the information. We will use a form through which a value for StuID is provided, and the PHP code will incorporate that value into the MySQL query.

In this case, you will write some simple HTML code for creating the form, which will then call the PHP code that actually executes the query. The HTML code is available here, and the PHP code is available here. Save them in your public_html folder using the file names list_one_stu.html and list_one_stu.php. You should now be able to see it work at this link:

http://www.cs.jhu.edu/~username/list_one_stu.html

Here is what it should look like:

http://www.cs.jhu.edu/~yarowsky/DB_HW3/list_one_stu.html

Example 3: in this example, we will implement an interface similar to the previous example, but that calls a MySQL stored procedure. Before you try this out, make sure you define the following MySQL stored procedure:

Notice that this procedure sets the OUT parameter charCount to a certain value. That being said, what we will illustrate in this example is how to use OUT parameters in a PHP/MySQL interface.

We will again enable the user to input the string to be used as a value for the myStr parameter. This is pretty much the same as in Example 2. The HTML code will call the PHP code, which will in turn make the procedure call and use the value passed through the OUT parameter. The HTML code is available here, and the PHP code is available here. Save them in your public_html folder using the file names str_len.html and str_len.php. Just as in the previous examples, you should be able to try it out at this link:

http://www.cs.jhu.edu/~username/str_len.html

Here is what it should look like:

http://www.cs.jhu.edu/~yarowsky/DB_HW3/str_len.html

Example 4: in this example, we will see one limitation of the PHP MySQL extension. In the previous example, what would happen if the procedure also included some kind of output? It would be nice if we can extract that output, but, unfortunately, this will not work. For example, define the following procedure:

As you can see, this is a very simple procedure that produces a list of courses that match the Credits = numCredits condition. In our case, we know that the output will consist of, for example, 59 rows if numCredits is 3, or 12 rows if numCredits is 4 (the number of courses that match that value for Credits). Unfortunately, if you try to do the same thing we did in Example 1, using HTML code that looks like this and PHP code that looks like this, and using the following link:

http://www.cs.jhu.edu/~yarowsky/DB_HW3/list_courses.html

You will get an error message. And therefore, although the PHP MySQL extension can handle direct SELECT queries, it cannot handle similar kind of output if it were produced by a procedure call.

Obviously, the easiest way to get around this is to migrate the code from the MySQL stored procedure into the PHP code itself, in which case the PHP code would actually prepare the SQL query as opposed to making a procedure call, which is exactly what we did in Example 2. The modified HTML and PHP code can be found here and here, respectively. Save them in your public_html folder using the file names list_courses_improved.html and list_courses_improved.php. You should know by now that you should try:

http://www.cs.jhu.edu/~username/list_courses_improved.html

Here is what it should look like:

http://www.cs.jhu.edu/~yarowsky/DB_HW3/list_courses_improved.html