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:

    This RSS feed URL is deprecated, please update. New URLs can be found in the footers at https://news.google.com/news

    Web Developer — Remsoft
    Huddle Today
    As a key contributor to an experienced and vibrant team of professionals, the Full Stack ASP.Net Developer will have the opportunity to grow, learn and expand their career, while making an immediate positive impact on the business and its offerings to ...


    PetComfort and Americaneagle.com: Branding and Web Development for New Pet Product Line
    PR Newswire (press release)
    From floor mats to pet bowls, MacNeil has partnered with Americaneagle.com since 1999, as the web development company has grown from a small but dynamic company to an employer of 400+ while being listed on the Inc. 5000 List five times. Americaneagle ...

    and more »

    The Verge

    Setting up an iPad for coding is my greatest feat as a computer user
    The Verge
    But it makes developing software nearly impossible. It's ironic because the iPad is excellent for creators in so many ways like drawing, design, and music. Under the hood is the same Unix kernel that makes macOS excellent for programmers. Anyway, you ...
    Apple introduces new 9.7-inch iPad with Apple Pencil supportApple Newsroom
    iPad — Homework (Full Version) — AppleYouTube

    all 1,439 news articles »

    The best web programming languages for development
    TheServerSide.com
    PHP made it practical for the first time to build dynamic websites and so transformed the web from a collection of mostly static content into an interactive medium. Two decades after PHP's first release, its popularity has waned somewhat. A strong anti ...


    Top Web Development Company, Lounge Lizard, Offers Tips to Keep Your Website at an Optimal Performance Level
    Benzinga
    NEW YORK, April 20, 2018 /PRNewswire-iReach/ -- Lounge Lizard is globally recognized as a top New York web design company within the website and mobile app development industry. Lounge Lizard's brandtenders are creative, tech-savvy, and passionate in ...


    Marketing Web Developer
    Built In Austin
    https://jobs.lever.co/aceable/c6e2e6e4-5c69-4f1f-a43d-11e463eb031d/apply?lever-origin=applied&lever-source%5B%5D=BuiltInAustin. Apply now. Save. ABOUT THIS POSITION. We're looking for a Web Developer to join the Marketing Dev team who will focus ...

    and more »

    Neowin

    Pay What You Want for the Full Stack Web Development Bundle
    Neowin
    Today's highlighted deal comes via our Online Courses section of the Neowin Deals store, where you can Pay What You Want for the Full Stack Web Development Bundle. The world's best developers are Full Stack developers. Here's 122+ hours of instruction ...


    Columbia Engineering Launches First Web Development Bootcamp In Partnership With Trilogy Education Services
    PR Newswire (press release)
    NEW YORK, March 27, 2018 /PRNewswire/ -- Columbia Engineering announced today the launch of its first coding bootcamp in partnership with Trilogy Education Services. Geared toward adult learners and working professionals, this 24-week, part-time ...

    and more »

    Asbury Park Press

    Freehold Regional voters might be asked to spend $39.8M on high school improvements
    Asbury Park Press
    The Freehold Regional High School District may decide to put a $39.8 million design and infrastructure proposal to a vote in the fall. The district's board of education meets Monday night and could authorize a referendum to approve security upgrades ...


    InfoWorld

    Stencil web components compiler beta due soon
    InfoWorld
    Stencil, an open source compiler for building web components and progressive web apps (PWAs), is due to move to a beta release in May, with a Version 1.0 production release expected in midsummer. Developed by tools builder Ionic, Stencil enables ...

    and more »
    Google News

  • Search Engines and Customers Want Focused Web Site Content

    How do you decide on the content, products and or... Read More

    Conceptualize, Build and Publish a Web site

    Conceptualize, Build and Publish a Web site - What's required... Read More

    Does Your Business Really Need A Website?

    Website, website, website, everyone says you need one. But do... Read More

    3 Ways To Increase Your Websites Conversion rate

    Everywhere you look these days, people are telling you that... Read More

    Remember This When Building A New Site - Beginners Guide

    I recently helped my mom to launch a website (www.mom2me.com)... Read More

    How to Make Your Own Website For Free

    If you like the Internet and surfing through the billions... Read More

    12 Tips To Great Websites

    Why do some sites succeed while the vast majority of... Read More

    Ecommerce for Beginners

    Like most average persons, I wanted some extra money to... Read More

    What Hosting Companies Dont Tell You, Could Hurt You?

    Did you know that hosting companies overcrowd their servers despite... Read More

    How to Create Sizzling Sales online

    "Don't Sell the Steak, Sell the Sizzle."Have you heard of... Read More

    Database Driven Web Site - Do You Need It?

    Many of site owners still don't realize all advantages of... Read More

    The Power Is In The Pipes: How To Get Maximum Leverage From Your Website

    What is the most important part of your online business?Many... Read More

    You Need To Treat Your Website Like An Employee - Heres 5 Ways To Do It

    People often come to me in a state of crisis... Read More

    Building a Web Store on Shoestring Budget in less than Four Hours

    Starting a business online is no longer as difficult as... Read More

    Improving Usability for Screen Reader Users

    Simply ensuring your website is accessible to screen reader users... Read More

    Writing Effective ALT Text For Images

    Anyone who knows anything about web accessibility knows that images... Read More

    10 Tips For Running A Profitable Web Site

    1. Address your targeted audience on your business site. Example:... Read More

    Email Addresses in Web Pages

    You want to give your site's visitors a way to... Read More

    Art, Artists and the Web: Part 4--What to Do After a Website is Designed

    What to do if you are an artist after you... Read More

    Effectively Using Robots Meta Tags

    The "robots" meta tag, when used properly, will tell the... Read More

    Effective Webdesign

    D.zigns dzignerwebs The Basics Before starting on... Read More

    Increase Hits to Help Increase Your Business

    In this day and age most businesses consider having their... Read More

    7 Questions to Understand Your Customer Better

    Customer service questionnaires can help your clients better understand their... Read More

    Server Side Includes (SSI) with ASP

    Server Side Includes (SSI) with ASP People often... Read More

    Top 10 Webmaster Challenges

    Webmasters deal with a myriad of complex design challenges every... Read More