MySQL Database Handling in PHP by John L
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.
About the Author
This PHP scripting article is written by John L. John L is the Webmaster of The
Ultimate BMW Blog! (http://www.bimmercenter.com).
The Ultimate BMW Blog!
|
