MySQL Database Handling in PHP

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!

daboss@bimmercenter.com

More Web Development Articles

The 5 Biggest Mistakes Almost All Web Designers Make
Huge Mistake #1: Creating a Website with Flash -- Did you know in a recent study, top internet marketers discovered that having a website created with Flash, actually DECREASED the response from prospects and customers by as much as three-hundred-and-seventy percent?Here's why: Your prospects and customers are most likely visiting your website using all types of different computers, connection speeds and internet configuration settings..

Track Your Visitors, Using PHP
There are many different traffic analysis tools, ranging from simple counters to complete traffic analyzers. Although there are some free ones, most of them come with a price tag.

Why Do I Need A Web Site?
Even though the Internet has been around for a long time and many people are "educated" about the Internet, most have little knowledge about what a web site is and what can it do for their business.A web site is anything and everything you want it to be.

Web Measurement: What You Don?t Know Would Make A Great Book
?What?s in it for me?? you ask. ?Why should I measure how people use my website? How does it help and what does it all mean?? The purpose of this article is to try to give you some insight into effective web measurement and to talk about the most important page of any website, the landing or home page.

Non-Profits: Successful Online Strategies
More and more non-profit organizations are developing websites to help share information about their mission and programs. This involves strategic planning about the best way to present your image and message online.

The Number 1 Reason Most Websites Fail
Failure, just like success, is measured differently by each and every one of us. What one man treats as success another may view as failure.

Wheres Your Focus, Money or Customer?
Is your website Really useful?I had a 'rep' in yesterday who was telling me he had a website, not realising I'm a man of many talents he was telling me about all the new companies he was working with and the great products he could offer and reminded me to go to his website.When he'd finished, I asked him:-"what is the purpose of your website?" a question which he couldn't answer.

Web Sites ? Should I Learn or not?
A common dilemma most small and medium businesses face when coming to their web site is if they should take the time to learn how to build a web site or not? Here?s my honest thoughts on this topic.If you read my previous article you would know that a web page is fundamentally written in HTML (let?s leave the dynamic stuff out for now).

How To Get Your Customers To Trust Your Website
Research reveals three important facts:1) The Internet is one of the most important sources of information.2) The trustworthiness of the Internet is declining.

Build a Web Site that Works...Even for a Safari Hobby!
My life's greatest passion is a safari hobby. And what would that be, you ask.