MySQL Database Handling in PHP
Most interactive websites nowadays require data to be presented
dynamically and interactively based on input from the user. For
example, a customer may need to log into a retail website to
check his purchasing history. In this instance, the website
would have stored two types of data in order for the customer to
perform the check - the customer’s personal login details; and
the customer’s purchased items. This data can be stored in two
types of storage - flat files or databases.
Flat files are only feasible in very low to low volume websites
as flat files have 3 inherent weaknesses: 1.The inability to
index the data. This makes it necessary to potentially read ALL
the data sequentially. This is a major problem if there are a
lot of records in the flat file because the time required to
read the flat file is proportionate to the number of records in
the flat file. 2.The inability to efficiently control access by
users to the data 3.The inefficient storage of the data. In most
cases, the data would not be encrypted or compressed as this
would exacerbate the problem no. 1 above
The alternative which is, in my opinion, the only feasible
method, is to store the data in a database. One of the most
prevalent databases in use is MySQL. Data that is stored in a
database can easily be indexed, managed and stored efficiently.
Besides that, most databases also provide a suite of
accompanying utilities that allow the database administrator to
maintain the database - for example, backup and restore, etc.
Websites scripted using PHP are very well suited for the MySQL
database as PHP has a custom and integrated MySQL module that
communicates very efficiently with MySQL. PHP can also
communicate with MySQL through the standard ODBC as MySQL is
ODBC-compliant, However, this will not be as efficient as using
the custom MySQL module for PHP.
The rest of this article is a tutorial on how to use PHP to:
1.Connect to a MySQL database 2.Execute standard SQL statements
against the MySQL database
Starting a Session with MySQL
Before the PHP script can communicate with the database to
query, insert or update the database, the PHP script will first
need to connect to the MySQL server and specify which database
in the MySQL server to operate on.
The mysql_connect() and mysql_select_db() functions are provided
for this purpose. In order to connect to the MySQL server, the
server name/address; a username; and a valid password is
required. Once a connection is successful, the database needs to
be specified.
The following 2 code excerpts illustrate how to perform the
server connection and database selection:
@mysql_connect("[servername]", "[username]", "[password]") or
die("Cannot connect to DB!"); @mysql_select_db("[databasename]")
or die("Cannot select DB!");
The @ operator is used to suppress any error messages that
mysql_connect() and mysql_select_db() functions may produce if
an error occurred. The die() function is used to end the script
execution and display a custom error message.
Executing SQL Statements against a MySQL database
Once the connection and database selection is successfully
performed, the PHP script can now proceed to operate on the
database using standard SQL statements. The mysql_query()
function is used for executing standard SQL statements against
the database. In the following example, the PHP script queries a
table called tbl_login in the previously selected database to
determine if a username/password pair provided by the user is
valid.
Assumption: The tbl_login table has 3 columns named login,
password, last_logged_in. The last_logged_in column stores the
time that the user last logged into the system.
// The $username and $passwd variable should rightly be set by
the login form // through the POST method. For the purpose of
this example, we’re manually coding it. $username = "john";
$passwd = "mypassword";
// We generate a SELECT SQL statement for execution.
$sql="SELECT * FROM tbl_login WHERE login = '".$username."' AND
password = '".$passwd."'";
// Execute the SQL statement against the currently selected
database. // The results will be stored in the $r variable. $r =
mysql_query($sql);
// After the mysql_query() command executes, the $r variable is
examined to // determine of the mysql_query() was successfully
executed. if(!$r) { $err=mysql_error(); print $err; exit(); }
// If everything went well, check if the query returned a result
- i.e. if the username/password // pair was found in the
database. The mysql_affected_rows() function is used for this
purpose. // mysql_affected_rows() will return the number of rows
in the database table that was affected // by the last query
if(mysql_affected_rows()==0){ print "Username/password pair is
invalid. Please try again."; } else {
// If successful, read out the last logged in time into a $last
variable for display to the user $row=mysql_fetch_array($r);
$last=$row["last_logged_in"]; print "Login successful. You last
logged in at ".$last.".";
}
The above example demonstrated how a SELECT SQL statement is
executed against the selected database. The same method is used
to execute other SQL statements (e.g. UPDATE, INSERT, DELETE,
etc.) against the database using the mysql_query() and
mysql_affected_rows() functions.
