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:
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:
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!
Airbnb cleaning service Arlington Heights ..The needs of screen magnifier users are overlooked when implementing... Read More
Web accessibility has so many benefits that I really do... Read More
We all start out the same. Some have budgets, some... Read More
When you consider the billions of individuals and companies on... Read More
We are clearly well past the innocent "golden age" of... Read More
You don't believe in astrology but you check your horoscope... Read More
When you purchase a new item from a Yahoo! Store,... Read More
You know exactly what your organisation does and what your... Read More
The internet is a sea of knowledge. Getting your information... Read More
How can we add Word Rich Content to our Websites... Read More
When people think about the Internet, they think about technology.... Read More
So your traffic is going through the roof yeah? It's... Read More
The Internet car buyer is an elusive animal. Trying to... Read More
An Introduction to Server Side IncludesBeginner? That's OK!While much has... Read More
What is SQL? SQL stands for Structured Query Language and... Read More
Are you, like many other website owners, frustrated at the... Read More
A website provides invaluable advantages for businesses who have one.7... Read More
Does your website have a links/resources page?Do you exchange reciprocal... Read More
Recently I was reviewing the keyword specific conversion rate data... Read More
Through the explosive growth of the Web, companies have realized... Read More
Are you aware that 90% of the websites on the... Read More
A website is the most versatile and cost-effective marketing tool... Read More
Getting a Targeted Visitor to a website is a waste... Read More
As increasing numbers of people search for information on the... Read More
People like to get immediate responses to requests. Autoresponders are... Read More
reliable maid service Winnetka ..1. Address your targeted audience on your business site. Example:... Read More
For years now I've been looking to start my own... Read More
It amazes me how many people try to build serious... Read More
Before you jump into spending coutnless hours on designing and... Read More
Conceptualize, Build and Publish a Web site - What's required... Read More
They might not be wearing a mask and carrying a... Read More
Why do some sites succeed while the vast majority of... Read More
Even with the steady growth of the World Wide Web,... Read More
After deciding what kind of internet business you want to... Read More
"If you build it, they will come"; is an age... Read More
Everyone wants one.Everyone wishes they knew how to make one.SOME... Read More
Is Your Web Site an Expense or an Investment?Why don't... Read More
You work hard to build traffic to your web page.... Read More
Anyone who knows anything about web accessibility knows that images... Read More
Webmaster tools are vital to becoming both efficient and effective... Read More
Website, website, website, everyone says you need one. But do... Read More
Selecting the Perfect Domain NameThe first issue I have with... Read More
Fresh website content for your visitors can be of real... Read More
One of the biggest mistakes that most online retailers make... Read More
You know exactly what your organisation does and what your... Read More
A company must earn and keep trust or sales don't... Read More
Many websites I come across don't have a single link... Read More
So what do you do if need to get a... Read More
A simple website stats counter is not enough if you... Read More
Think up a name and check if yourname.com is available.... Read More
Web Development |