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!
One of my most salient memories from when I sold... Read More
There's been widespread speculation about the new legislation being introduced... Read More
If one more business owner tells me their website sucks... Read More
"What's in it for me?" you ask. "Why should I... Read More
If you have some experience in web site design and... Read More
So you have decided that you want to do freelance... Read More
You want to give your site's visitors a way to... Read More
If you are looking for creating your online presence you... Read More
A website lets you put your products in front of... Read More
If you don't have a web site yet, are thinking... Read More
Installment 1Developing State-enabled Applications With PHPWhen a user is browsing... Read More
A lot of people these days are interested in getting... Read More
I can't think of any business alive today that couldn't... Read More
While it may seem like a "quick and easy" task,... Read More
Just starting out, you can start with a simple informational... Read More
Running an effective website is a continuous process. I have... Read More
Does My Web Site Require A Web Database?If your web... Read More
1. Choose a big enough font size. A size 12... Read More
You work hard to build traffic to your web page.... Read More
Server Side Includes (SSI) with ASP People often... Read More
Never has competition been so difficult in the Internet world.... Read More
SiteSpinnerSiteSpinner is a user-friendly web site development tool that includes... Read More
Internet has opened a whole new world for web developers... Read More
Customer service questionnaires can help your clients better understand their... Read More
After deciding what kind of internet business you want to... Read More
The growth in Internet usage around the world is astounding... Read More
Are bloated images slowing down your web site and causing... Read More
My life's greatest passion is a safari hobby. And what... Read More
Conceptualize, Build and Publish a Web site - What's required... Read More
Designing and creating your own website does not have to... Read More
Creating a content management system either from scratch,... Read More
I had a client say something to me the other... Read More
Before you set up any scripts you will need some... Read More
Selecting the Perfect Domain NameThe first issue I have with... Read More
Let's begin by setting some limits. If you're like me,... Read More
It may come as some surprise to a traditionally off-line... Read More
Here's something you may never have thought of before:If I... Read More
I recently received an unsolicited e-mail from some company offering... Read More
The cost of setting up a web site is dirt... Read More
What to do if you are an artist after you... Read More
Conceptualize, Build and Publish a Web site - What's required... Read More
Can you imagine the hypocrisy of spending thousands of dollars,... Read More
Every small business owner knows that they need a website,... Read More
Alternate medicine finds its own place in the hearts and... Read More
Recently, a friend asked "What's the deal with those DIY... Read More
So you've decided to start an online work at home... Read More
Starting an internet business can be confusing to non-veterans. The... Read More
Yahoo has long offered email, an online calendar, notes, bookmarks,... Read More
Keywords in Search Engine Optimizing and Search Engine Marketing are... Read More
A professional online reputation is essential to the flourishing of... Read More
Appealing to others is really important when you have a... Read More
While most marketers are beginning to understand the critical importance... Read More
If you ever want to create a state directory, article... Read More
1. Address your targeted audience on your business site. Example:... Read More
1. Choose a big enough font size. A size 12... Read More
The DDA (Disability Discrimination Act) states that service providers must... Read More
If you haven't made the sales and built your clients... Read More
Building a new website can be a lengthy task. From... Read More
1. Many people are not getting good or complete advice.... Read More
"Build it and they will come."--Filed of Dreams (1989) If... Read More
While creating a Web site may seem like... Read More
If you're anything like most small business owners, you probably... Read More
"I have a small business with just a couple of... Read More
"'Page Not Found' on this Server. Check the URL and... Read More
After having decided to start a business, the next thing... Read More
After years of working with entrepreneurs who developed their first... Read More
Web Development |