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

    after renovation cleaning Glenview ..
    In The News:

    Sens. Josh Hawley and Richard Blumenthal introduce bipartisan GUARD Act to protect minors from AI chatbots through mandatory age verification and disclosure requirements.
    Ghost-tapping scammers exploit wireless technology to drain accounts through small transactions, but RFID-blocking wallets and transaction alerts can protect you.
    French pilot project demonstrates wireless charging roads that can deliver over 300 kilowatts of power to EVs while driving, potentially eliminating range anxiety.
    YouTube's Ghost Network spreads information-stealing malware through thousands of fake videos offering cracked software, using compromised accounts and fake engagement.
    Protect your privacy by disabling your smart TV microphone. Most TVs have hidden mics that listen even when voice commands are off. Learn quick steps to stop unwanted audio capture.
    SessionReaper vulnerability hits Magento and Adobe Commerce stores, compromising 250+ sites in one day. Hackers steal data and hijack shopping sessions.
    Master essential parental controls and digital safety tools to protect your kids online. Learn screen time limits, location settings and privacy controls every parent needs.
    Cybersecurity expert shares six essential steps to protect against dark web threats, including data removal services, password managers and antivirus software.
    NVIDIA GPU space mission could revolutionize cloud services by enabling orbital data centers that make apps, games and AI tools faster and more efficient.
    Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
    Microsoft warns Windows 10 users face serious security risks as 90% of ransomware attacks target unsupported systems. Learn why upgrading to Windows 11 is crucial.
    Nike unveils Project Amplify, revolutionary motorized shoes developed with Dephy that add powered assistance to every step for runners and walkers.
    Cybersecurity experts warn about one of the largest credential compilations ever found, urging users to check "Have I Been Pwned" and change passwords immediately.
    Quick iPhone and Android battery optimization techniques help your device stay powered all day by turning off hidden features that secretly drain power in the background.
    Kodiak Driver autonomous truck achieves perfect 98 safety score, matching top human fleets in groundbreaking AI evaluation by Nauto's VERA system.
    New 401k catch-up contribution rules in 2026 will change taxes for high earners over 50. Learn how scammers exploit these changes and protect your retirement savings.
    Kurt Knutsson's guide covers social media privacy protection through location settings, account privacy controls and two-factor authentication to prevent scams and data breaches.
    Revolutionary retinal implant restores central vision in 80% of patients with advanced macular degeneration, offering hope where treatments once only slowed blindness.
    Learn how to use passkeys on Windows and Mac computers without cameras or fingerprint readers. Discover secure authentication methods that replace passwords.
    Tesla's FSD v14.1.2 update reintroduces Mad Max mode, enabling higher speeds and more frequent lane changes than the standard Hurry profile setting.
    A phishing email scam targeting American Express customers shows how cybercriminals use fake urgent messages to steal personal and financial information.
    Facebook's new Meta AI feature analyzes your camera roll photos to create polished collages automatically, but requires cloud processing and raises privacy concerns.
    A New Jersey teenager filed a major lawsuit against AI/Robotics Venture Strategy 3 Ltd. over ClothOff, an AI tool that created fake nude images from her social media photos.
    Microsoft reports Storm-2657 cybercriminals sent phishing emails to 6,000 addresses at 25 universities to steal payroll credentials and redirect funds.
    Astronomers have discovered asteroid 2025 SC79, a skyscraper-sized space rock orbiting the sun in just 128 days. the second-fastest known.
  • Top 7 Tips for Building an Antique Car Website

    Like wine cars get more attractive to collectors as years... Read More

    Make Your Web Site Stand Out

    When visitors add your web site to their Favorites or... Read More

    Growing Your Meetings In CyberSpace

    As increasing numbers of people search for information on the... Read More

    Why You Need a Website

    You hear a great deal about the Internet these days... Read More

    How Your Own Website Helps Your Small Business Grow

    What do you mean, you don't have a website for... 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

    Building Websites with Directory Generator

    Directory Generator, a product from Armand Morin and Marc Quarles,... Read More

    7 Questions to Understand Your Customer Better

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

    Do Authors Need a Website?

    "I am a writer, not a business-person. I don't want... Read More

    How to Build a Database Driven Web Site

    If you ever want to create a state directory, article... Read More

    Free Webmaster Tools - 7 Things Every Webmaster Needs in Their Toolbox

    Webmaster tools are vital to becoming both efficient and effective... Read More

    In Business? 10 Reasons Why You Need a Website -- Now!

    1) Word of Mouth The single most powerful form of... Read More

    Instant Relevant Web Site Content - No Its Not a Bot

    In about the Time it takes to Watch a Reality... Read More

    So You Want To Have A Website

    So you want to have a website. You have gone... Read More

    The Topic Of Your Website

    The first thing you will have to deal building your... Read More

    The Secret Benefit Of Accessibility: Part 2 - A Higher Search Engine Ranking

    An additional benefit of website accessibility is an improved performance... Read More

    12 Essential Tips to a Professional Business Website

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

    The Dos and Donts of Launching a Small Business Website

    Launching a new small business website is often a long... Read More

    10 Tips For Running A Profitable Web Site

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

    Tips for Building a Great Business Website

    A website is lifeline of internet home business. In order... Read More

    Setting Up A CGI Script On Your Web Site

    Before you set up any scripts you will need some... Read More

    Traffic for Webmasters

    "If you build it, they will come"; is an age... Read More

    The Problem with Automated Accessibility Testing Tools

    An automated accessibility tool is a piece of software which... 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 Importance of Website Stats to You

    One of the best tools you have as a webmaster... Read More

    no-contract cleaning service Lincolnshire ..