Accessing mySQL relational database

1. Accessing mySQL relational database

2. MySQL database. • Today, we will attempt and open a connection to the MySQL server. • We need to specify the database • Issue queries (no updates at this stage) • display the results. • Close the connection.

3. Connecting to the MySQL Server • To connect to a MySQL server, you need to invoke the mysql_connect() function. • Here is a typical use of the function: $db = mysql_connect("localhost", "userName", "password"); if (!($db)) die("Failed to connect to database server"); • If mysql_connect() fail to open the database, then $db is set to false.

4. Once connected! • Once connected, you need to select the database using the mysql_select_db() function. • Here is a typical use of the function: $dbOK = mysql_select_db("testdb"); if (!($dbOK)) die("Failed to access database"); • Similarly if mysql_select_db() fail to access the database, then $dbOK is set to false.

5. Lets start with simple SELECT • You know SQL commands in your database class. Now is the time to use it. Suppose you want to list all the attributes of the employee table. select * from employee • In MySQL you would do the following: $query = "SELECT * FROM employee"; $result = mysql_query($query); if (mysql_error()) { die("cannot processed select query"); } $num = mysql_num_rows($result);

6. Displaying the results or sometimes call Resultsets • Previously you were able to know the number of rows. If you know the attribute names, $num = mysql_num_rows($result); // from before $counter = 0; if ($num > 0) { while ($counter < $num) { echo mysql_result($result, $counter, "emp_id"); echo " "; echo mysql_result($result, $counter, "address"); echo "<br />"; $counter++; } } else { echo "No rows found"; } mysql_free_result($result); // it is good to free memory mysql_close(); // close the database

7. Alternatively, you can use this way $num = mysql_num_rows($result); // from before if ($num > 0) { while ($row = mysql_fetch_assoc($result)) { echo $row['emp_id']; echo " "; echo $row['address']; echo “<br />"; } } else { echo "No rows found"; } mysql_free_result($result); // it is good to free memory mysql_close(); // close the database

8. pex5.php, pex6.php • In this exercise, you will both ways to connect to a database, and print the contents out of a table. • You will display this in a table (see demo). • The database is test_alum • The table I want you to look is call 'users' • You are to list all the users and the following attributes last_name first_name admin phone cell email