Webmaster Papers




Google
 
Web webhostingpapers.com




/pagead2.googlesyndication.com/pagead/show_ads.js">

MySQL Database Handling in PHP


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 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.

  • The inability to efficiently control access by users to the data

  • 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:

  • Connect to a MySQL database

  • 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!

    daboss@bimmercenter.com

    RELATED ARTICLES


    How One Word Or Even One Letter Can Boost Conversion Rates By Over 400%!
    Recently I was reviewing the keyword specific conversion rate data of a consulting client of mine. I have been working with this client for a few months now, helping her improve the sales conversion rate of her website and we have had very good results, taking average conversion rates at her site from below 1% to just over 4.3%.
    Why a CSS Website Layout Will Make You Money
    Although CSS layouts have been around for years, they haven't become so commonplace until recently. This was basically due to limited browser support (especially from Netscape 4) - nowadays though, CSS 2.0 (which introduced positioning) is compatible with over 99% of browsers out there (check out the browser stats over at http://www.thecounter.com/stats/2004/August/browser.php).
    Wheres Your Focus, Money or Customer?
    Is your website Really useful?
    What is SQL?
    What is SQL? SQL stands for Structured Query Language and is the lingua franca in the database world. SQL is a standard that is used by all database vendors and programmers to define, extract and access the information that is stored in databases. SQL began life as an IBM creation but was standardized by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) as ANSI/ISO SQL in 1988. Since then ANSI/ISO SQL standard continued to evolve. The ANSI-SQL group has since published three standards over the years:
    Website Strategy!
    A website lets you put your products in front of a world-wide audience. It can help you generate new revenue, cut costs and build better relationships with both customers and suppliers.
    Using Psychology Easily on Your Website to Make More Sales!
    Psychology is a long word which everyone thinks, "no, that's not for me, it'll be too difficult". Apart from being lazy, anyone who thinks this is wrong. Applying psychology on your website is easy and extremely effective. Also, the fact that most webmasters take the lazy view of applying psychology gives you the edge over your competition.
    Eight Deadly Web Site Mistakes and How to Avoid Them
    Creating and maintaining an effective presence on the Web has become increasingly complex and challenging as the power of the Internet as a marketing tool becomes more and more necessary to entrepreneurs and emerging businesses.
    Web Site Development Process - The Life-cycle Steps
    A system development process can follow a number of standard or company specific frameworks, methodologies, modeling tools and languages. Software development life cycle normally comes with some standards which can fulfill the needs of any development team. Like software, web sites can also be developed with certain methods with some changes and additions with the existing software development process. Let us see the steps involve in any web site development.
    3 Ways To Increase Your Websites Conversion rate
    Everywhere you look these days, people are telling you that in order to sell through your website, you need tons of traffic.
    Server Side Includes (SSI) with ASP
    People often have identical sections of code spread throughout many ASP pages, typically code at the top of the page (usually referred to as the header) and the lower part of the page (the footer). Similarly, you may have code that performs a certain set of instructions that you are repeating across multiple pages. Consider an example:-
    Effective Webdesign
    The Basics
    Getting a Web Site Can Be Fast, Easy, and Inexpensive When You Know Your Options
    So what do you do if need to get a web site but you're not sure how to go about getting one. The good news is you have several options. To help determine which option is best for you, ask yourself the following five questions:
    Why do I Need a Website?
    Cost Effective Advertising. Online selling. Increased geopgraphic range. Wider customer base. Efficient marketing. Improved customer support. Succesful market research. Customer feedback.
    Trinkets, Lockets And Empty Pockets
    It never ceases to amaze me how many people set-up a website, without any type of plan or clue about how to promote it, how to write a sales letter, how to write an ad-- nothing!
    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.
    Build a Strong Foundation for Creating Your First Online Identity ? Notes for Beginners
    If you are looking for creating your online presence you should follow some of the basic requirements so that your new website will have a strong foundation. Today internet is a growing media which can provide you maximum results, which no other media can do for you. If you think on right direction and take your action step by step, it is almost certain that your site will have a strong online presence and reach to the maximum of you potential clients.
    Having a Portfolio on the Internet Can Help you Infiltate the Workforce
    If you're an up and coming graphic/web designer just out of university, you'll know it's quite hard to break into the workforce. You have to contact employers one by one, spend your time traveling and lugging your portfolio to their offices. Well, things have changed, and for the better let me tell you. Now you can make your own on-line portfolio on the Internet, and send its web page address to numerous design companies at once so they can all see your work easily. You don't even have to leave your house and organizations all over the world can access your work easily.
    Get Your Business on the Web
    I can't think of any business alive today that couldn't benefit from at least a minor presence on the web. Now that I've made that statement, I'm sure someone will point out one. My point is, however, that having a web presence today is about as cheap as anything going and if you ever get one client or sale from the web you are way ahead.
    Why Local Service Companies Should Have a Website
    The Internet is being used by local service business to improve sales today. Sales are improved because a website gives a company more credibility and in some cases generates leads.
    What Your Customers Say About YOUR Site! Part 1
    The following is a list of patterns that many visitors follow, and you can use these suggestions to better your site.