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!

    [email protected]

    In The News:

    'w0zniak' Pleads Not Guilty To Selling MSP Access On Dark Web  CRN: Technology news for channel partners and solution providers
    The Future of Web Development  Business 2 Community
    Jail Log: February 27, 2020  ConchoValleyHomepage.com

  • Web Success Secret - Keep Your Visitors Interested

    Visitors to your site come looking for information or solutions... Read More

    12 Essential Tips to a Professional Business Website

    1. Choose a big enough font size. A size 12... Read More

    How To Evaluate Your Web Sites Performance

    Setting up a website is the very first step of... Read More

    If You Build It, They Will Come?

    Building a new website can be a lengthy task. From... Read More

    5 Essential Traits Of A Home Business Website

    So you've decided to start an online work at home... Read More

    50 Ways To Use Your Website

    A website is the most versatile and cost-effective marketing tool... Read More

    Art, Artists, and the Web:Part: 2--First Steps in Building an Artist?s Website

    What you should do and know if you are an... Read More

    The Internet Road Map

    1) Data CaptureOne easy way to collect e-mail addresses and... Read More

    Better Web Site ROI: Efficient Online Business with SEO, PPC, Split Testing, and Forums

    If you're looking for better web site ROI, chances are,... Read More

    Make Your Communication Come Alive

    Design and layout can make your communication come alive and... Read More

    Free Websites & Why You Should Avoid Them

    It amazes me how many people try to build serious... Read More

    Why a CSS Website Layout Will Make You Money

    Although CSS layouts have been around for years, they haven't... Read More

    Guide to Internet Business - Design and Content

    After deciding what kind of internet business you want to... Read More

    How One Word Or Even One Letter Can Boost Conversion Rates By Over 400%!

    Recently I was reviewing the keyword specific conversion rate data... Read More

    Creating a Sticky Web Site

    When used to describe a web site, the term "sticky"... Read More

    10 Simple Steps: Its Magic If You can E-Mail You can Update Your Web Page Dynamically

    Dynamically Update Your Web Pages Via E-MailIf you can Send... Read More

    CSS - Maximum Benefits

    What is CSS?CSS is a simple file which controls the... Read More

    Web Analytics - Getting it Right

    Understanding and using web analytics.In recent years, website marketers were... Read More

    35% Revenue Increase? from Your Website!

    2 Golden Rules for an Engaging Website35% of visitors fail... Read More

    Content Management ? The New Standard in Web Design

    The evolution of web development parallels the model of technology... Read More

    The Number 1 Reason Most Websites Fail

    Failure, just like success, is measured differently by each and... Read More

    Duplication vs. Individualism

    How can we add Word Rich Content to our Websites... Read More

    Why Is No-one Buying From My Website?

    Are you, like many other website owners, frustrated at the... Read More

    Website Content & Usability

    Writing for the web is totally different to writing for... Read More

    Get to Know People through Websites

    Web sites are not just places to sell things; they... Read More