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 gt 0)
• while (counter lt num)
• echo mysql_result(result, counter,
  "emp_id")
• echo " "
• echo mysql_result(result, counter,
  "address")
• echo "ltbr /gt"
• 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 gt 0)
• while (row mysql_fetch_assoc(result))
• echo row'emp_id'
• echo " "
• echo row'address'
• echo ltbr /gt"
• 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