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!

    http://www.bimmercenter.com

    expert residential cleaners Park Ridge ..
    In The News:

    The Omoway Omo X features a modular design, obstacle detection and emergency braking, and can drive itself to charging stations, signaling the future of smart urban mobility.
    PillTrek, a tiny wireless smart capsule created by Caltech, provides real-time gut health data by detecting electrolytes, metabolites, glucose, hormones, pH levels and temperature.
    Learn to spot fake bank text messages with five warning signs of Chase phishing scams, including suspicious links and urgent deadlines designed to steal your account information.
    Robot coyotes built on motorized cars reaching 20 mph are being deployed at military airfields as an innovative solution to wildlife threats to aircraft safety.
    Explore Google Drive's strengths and weaknesses as a backup solution, including its encryption security, privacy concerns and comparison to dedicated backup tools.
    As environmental challenges grow, underwater habitats gain momentum with DEEP's Vanguard, supporting short missions and Sentinel designed for 28-day stays at depth.
    The new Urban Eyes safety vest features dual cameras, reflective panels and a wireless remote to deter harassers and provide peace of mind for outdoor workouts.
    The $200 billion data broker industry tracks your personal details across websites, posing privacy risks that can be mitigated through data removal services.
    UCC Holding has teamed up with COBOD to create desert-inspired, 3D-printed schools in Qatar, featuring dune-like wavy walls impossible with conventional construction.
    Darkling beetles equipped with microchip backpacks can be guided through disaster areas using electrical signals, offering a biological solution for search and rescue.
    Scammers use number porting to take control of outdated landlines still connected to financial accounts, bypassing two-factor authentication to steal funds while avoiding detection.
    Twitter co-founder Jack Dorsey developed Bitchat, a privacy-focused messaging app that works offline via Bluetooth without requiring email, phone numbers or personal data.
    Google is making a push to ensure its AI, Gemini, is tightly integrated with Android systems by granting it access to core apps like WhatsApp, Messages, and Phone.
    A new green energy system is set to change how we capture clean power, and it all starts with the ocean. French startup Seaturns has designed technology that taps into the natural motion of the sea.
    Cybersecurity researchers are warning that hackers have started exploiting flaws in chatbots to carry out AI phishing attacks.
    Google has just made it easier than ever to regain control of your inbox with Gmail's new Manage Subscriptions tool.
    Despite the benefits, residential power saver programs come with several potential drawbacks and concerns that have been raised by both customers and experts.
    The Blackdot AI tattoo machine is quiet. It's steady. And according to early users, it hurts a lot less.
    Attackers have started to exploit the very signals that users assume will keep them safe when it comes to add-ons to improve productivity or entertainment.
    Located on Cerro Pachón in Chile, the world's most powerful digital camera is set to transform how we see the universe.
    The innovative DQ Tower stands 28 feet tall with 420 square feet of living space, featuring floor-to-ceiling windows and premium amenities in a prefabricated design.
    A new mmWave imaging system allows warehouse robots to scan and create 3D models of objects inside sealed containers, potentially revolutionizing shipping processes.
    Family fraud endangers seniors when relatives exploit their trust, but removing personal data online and monitoring identity can prevent financial harm to aging parents.
    A study analyzing 500,000 customer service interactions shows chatbots struggle with complex issues while human agents excel at matching customer communication styles.
    Aigen's Element robot uses solar power and AI to provide farmers with a sustainable alternative to herbicides, working efficiently in cotton and soy fields.
  • Business Website Building Do?s and Don?ts

    First, using a lot of web graphics, flash, banners and... Read More

    Flash Deadly Sins (That Can Kill Your Web Business)

    Looks like every client wants a Flash site these days... Read More

    Web Site Promotion: How To Advertise Your Website For Profits

    Making money with your web site is the dream and... Read More

    Top 5 Reasons to Track Your Website Traffic

    #5 User Satisfaction: Learn what your visitors and site users... Read More

    Eight Deadly Web Site Mistakes and How to Avoid Them

    Creating and maintaining an effective presence on the Web has... Read More

    What Hosting Companies Dont Tell You, Could Hurt You?

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

    Find the Purpose of Your New / Proposed Website

    Absolutely everything stems from the purpose of your website -... Read More

    Web Design for the Professional Magician Part I - Selecting the Perfect Domain Name

    Selecting the Perfect Domain NameThe first issue I have with... Read More

    How Your Own Website Helps Your Small Business Grow

    What do you mean, you don't have a website for... Read More

    Bring Your Visitors Back Clamoring for More! Maintain and Improve Your Web Site Weekly - Part 1

    80% of your Web site is Maintenance!Once your Web site... Read More

    Maintaining Your Business Website

    QUESTION: Should I build and maintain my business Web... Read More

    New Years Resolutions: Is Improving Your Website One of Them?

    With the New Year upon us yet again, it's time... Read More

    Top 10 Webmaster Challenges

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

    Making The Business Case For Web Standards

    Through the explosive growth of the Web, companies have realized... Read More

    Increase Hits to Help Increase Your Business

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

    Cost Effective and Powerful Dynamic Websites w. Fantastico Web Hosting

    If you are developing your website on a host 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

    Why a CSS Website Layout Will Make You Money

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

    9 Keys to an Effective Website

    Many small companies choose not to invest in a professionally... Read More

    How to Maximize the Value of Every Visitor to Your Website

    While most marketers are beginning to understand the critical importance... Read More

    5 Essential Traits Of A Home Business Website

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

    Is Your Web Site Talking to Your Customer?

    A while ago one of my clients approached me and... Read More

    Disability Discrimination Act (DDA) & Web Accessibility

    There's been widespread speculation about the new legislation being introduced... Read More

    Benefits Of An Accessible Website: Part 1 - Increase In Reach

    The DDA (Disability Discrimination Act) states that service providers must... Read More

    The Birth of a Professional Web Site

    The Internet has opened a whole New World of opportunity... Read More

    green cleaning service Park Ridge ..