University of Bahrain

Information Technology College

Computer Science Department

Semester 1, Year 2006/2007

ITCS 373: Class Lab Work

PHP3

 

 

Instructor: Dr. Eshaa M. Alkhalifa

 

You should have completed all the exercises in PHP2 before starting this LAB session.  In this session you will learn all you need to know in order to work with a database based on information you receive from a form on the web.

 

  1. The first thing that you must learn is how to connect to a database.  You can also create a new database from PHP but this function is not well supported on all servers due to the security risks involved in allowing that.  So we will go to the localhost/phpmyadmin page by typing this at a browser window.  You will get a popup window that will ask you for a user name and password.  Type the user name root and the password you gave when installing the server software.
  2. Go to the field that says Create new database and type the word ourdata.  Ignore the other field to the right.
  3. If you get an error when you type that as a URL, then you probably do not have your MYSQL database working so start it from the start/programs/appserv/control server by manual/mysql start.. command.  Once you can see phpmyadmin panel, then this means that the database software is working.
  4. Go to the Privileges tab at the top right of your screen and click it.  You will see a list of the users allowed to use this database.  Click on the image of a person to the right.  Go to the bottom of the screen and replace the word root with user.  Then type in the password field the word pass and write it again in the following line.  Do not change the line that has localhost. Click on the last GO on the screen. (do not click on any of the others).  Click on Privileges again to see that you now have two users that are able to use your database.
  5. Open a new php file and copy the following code into it after deleting all html and save it as connect.php.

<?php

$dbh=mysql_connect("localhost", "user", "pass", ourdata) or die ('I cannot connect to the database because: ' . mysql_error());

mysql_select_db ("itcs373_test");

echo "Display this text";

//Close Connection

mysql_close($dbh);

 ?>

  1. The second line above connects to the database.  It requires, the server location, the user name, the password and the database’s name.  Since you can connect to more than one database at the same time, you must select a database that you wish to use, using the mysql_select_db instruction before you can use it to insert data etc.
  2. There is another version of the connect line where it has only the first three values and you include $dbh in the select command.  You may use any version that works for you to connect.
  3. To test your script go to http://localhost/connect.php.  You should be able to see the line Display this text.  If you see any errors please ask to get your problem corrected.
  4. CREATE TABLE:  If you wish to create a table, open your connect.php script and insert the following code before the mysql_close command.

// 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 "<br><br>Table Created!";

  1. The table name is example and it contains three fields;

id which is integer, cannot be empty and increases with every record that we add by 1.  This is also the primary key to use for searches in the table.

Name it is a variable with characters with the length of 30

Age This is an integer type data item.

  1. Refer to an SQL reference for other types of data fields that you can use in the database.
  2. Save your file, then call it using localhost/connect.php and you should see on the screen; Table Created.
  3. Open an new browser window and type phpmyadmin at the URL field and check if your database has the table.  You select your database on the left, and then see the tables in the lower left side, click on the table name and you can see the structure etc.
  4. INSERT:  In order to insert data into your table you can open an new file in HTML and for extra practice, recopy the connect lines to this one and save it as edit.php
  5. Then copy the following lines before the close instruction;

// Insert rows 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!";

  1. Now go to localhost/edit.php and you should see the line Data Inserted! On the screen.  Then check if the data has really been inserted on the phpmyadmin screen.  You must click the Browse tab, to see the data displayed.
  2. SELECT:  We have a table in our database, so we wants to display all the data in the table.  You can do this by adding to edit.php the following line;

$result = mysql_query("SELECT * FROM example");

  1. This will display the data sequentially without formatting so we can place it into a nice table using the following code;

echo "<table border='1'>

<tr>

<th>Id</th>

<th>Name</th>

<th>Age</th>

</tr>";

while($row = mysql_fetch_array($result))

  {

  echo "<tr>";

  echo "<td>" . $row['id'] . "</td>";

  echo "<td>" . $row['name'] . "</td>";

  echo "<td>" . $row['age'] . "</td>";

            echo "</tr>";

  }

echo "</table>";

  1. Notice that the HTML code is within quotations and is the OUTPUT of the echo instruction.  The . is used to concatenate the output which means that I wish to type the <td> followed by a value that is evaluated using the variable in this case an arrow value, and then the other </td> tag.
  2. The mysql_fetch_array function will be true while the table still has data records that have not been retrieved. And the value $result will contain the record obtained from the database, classified as $rows.
  3. WHERE:  You can select records based upon a condition; so if you wish to display only the record(s) that have an age above 18, you replace your SELECT instruction with this one;

$result = mysql_query("SELECT * FROM example WHERE age>'18' " );

  1. See the results of your changes by looking at localhost/edit.php
  2. ORDER BY:  Add to the $result line at the end ORDER BY name.  You should know by now where it goes and see the result of your addition.  This function sorts the output according to a field that you specify.
  3. UPDATE:  So far the SQL commands allow you to add records and display them.  What if you wish to alter a field in one of the records.  To alter the age of Sandy Smith add the following line, before the one that displays the data.

mysql_query("UPDATE example SET Age = '36'

WHERE name = 'Sandy Smith'  ");

  1. See the results of your changes when you open the file localhost/edit.php.
  2. DELETE:  The last command to try here is how to delete lines from the table.  In this case we want to delete the line with the id =2.  So add this line to edit.php before the function that displays the data

mysql_query("DELETE FROM example WHERE id='2'");

  1. See the results of your work and that is all for this lab.