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!
affordable house cleaning Wilmette ..An Introduction to Server Side IncludesBeginner? That's OK!While much has... Read More
Often time I've browsed sites and seen the same thing... Read More
Why do some web sites reap huge benefits while others... Read More
When used to describe a web site, the term "sticky"... Read More
There are several reverse bid freelance sites out there. Beyond... Read More
Wow, we`ve already taken a domain and chosen a web... Read More
Everywhere you look these days, people are telling you that... Read More
A website lets you put your products in front of... Read More
"'Page Not Found' on this Server. Check the URL and... Read More
Some left shoes are in isle 5, while the right... Read More
2 Golden Rules for an Engaging Website35% of visitors fail... Read More
The following is a list of patterns that many visitors... Read More
The Internet car buyer is an elusive animal. Trying to... Read More
Before you set up any scripts you will need some... Read More
Like wine cars get more attractive to collectors as years... Read More
When we review the sites of potential clients, there are... Read More
Globalize Your WebsiteThe Internet has unlocked a wide array of... Read More
If you have your own business website and you are... Read More
An interesting eCommerce success factor that isn't precisely overlooked, but... Read More
To the starting internet entrepreneur, there is nothing more discouraging... Read More
One of the biggest mistakes that most online retailers make... Read More
Creatively marketing your corporate site takes time in the set... Read More
Many of site owners still don't realize all advantages of... Read More
In 1995 a new era of accessibility for disabled people... Read More
This article was prompted by some customer support work I... Read More
quick home cleaning Northbrook ..Recently, a friend asked "What's the deal with those DIY... Read More
With the New Year upon us yet again, it's time... Read More
1. As the first step, "Hire a web designer."First create... Read More
We are clearly well past the innocent "golden age" of... Read More
Before you start building your site, ask yourself "WHY?". Why... Read More
Huge Mistake #1: Creating a Website with Flash -- Did... Read More
Is your website Really useful?I had a 'rep' in yesterday... Read More
Absolutely everything stems from the purpose of your website -... Read More
One of the most useful features offered by some web... Read More
In a previous ezinearticles.com article,"I Need real visitors, Not Search... Read More
How popular is online shopping? And is there room for... Read More
What happened on your website yesterday? What about last week... Read More
As a business you can't afford to ignore the Internet... Read More
A lot has changed in the way sites are optimized... Read More
If you haven't made the sales and built your clients... Read More
A simple website stats counter is not enough if you... Read More
Internet Marketing is not a miracle marketing strategy ? competition... Read More
If you like the Internet and surfing through the billions... Read More
You have seen those web pages where they have the... Read More
Have you ever wondered what challenges are faced by other... Read More
What do you mean, you don't have a website for... Read More
In this example I have used the map of the... Read More
Internet has opened a whole new world for web developers... Read More
More and more non-profit organizations are developing websites to help... Read More
Have you ever thought the secret behind the success of... Read More
Web Development |