Transcript Accessing mySQL database

Accessing mySQL
relational database
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.
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.
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.
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);
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
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
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