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

    spotless home service Lake Forest ..
    In The News:

    A Columbia University breach exposed names, Social Security numbers and academic records of nearly 869,000 people, with notifications beginning in August.
    Rental car drivers use AI-powered apps like Proofr to protect themselves from unfair damage fees as major companies deploy automated inspection tools.
    Fox News' AI newsletter brings you the latest on technology advancements around artificial intelligence.
    OnTrac data breach between April 13-15, 2025, exposed personal information of over 40,000 people including Social Security numbers and medical records.
    A woman named Wika announces her engagement to an AI chatbot sparking worldwide debate about virtual relationships and technology.
    The notorious people search site National Public Data relaunches despite a previous breach affecting 3 billion individuals, raising fresh privacy concerns.
    Revolutionary TRAUMAGEL gel controls life-threatening bleeding from gunshot wounds and traumatic injuries, helping first responders prevent prehospital deaths.
    Protect your home network by enabling proper encryption, creating strong passwords, checking connected devices and using VPN and antivirus software.
    The Navy's solar-powered Skydweller drone flew nonstop for 73 hours in Mississippi, proving renewable energy can power long-endurance military missions.
    Moving and downsizing expose seniors to identity theft and scams as data brokers collect real estate records and personal information to sell to criminals.
    ShengShu's Vidar technology revolutionizes humanoid robot training by using AI-generated synthetic video, reducing required training data from hours to just 20 minutes.
    Apple's older Mac computers face security risks without regular updates, but 10 essential tips including FileVault encryption and strong passwords can keep aging Macs protected.
    Self-driving trucks from PlusAI could reshape freight transportation by 2027, addressing driver shortages and reducing logistics costs for businesses.
    Solar farms are turning to sheep instead of mowers to cut grass under solar panels, lowering costs, reducing emissions and creating new income for farmers.
    Fraudsters posing as local officials are making fake calls about missed jury duty, demanding payments in a rising scheme that exploits fear of arrest.
    Honeywell Aerospace engineers developed a "surface alert system" that gives pilots two aural warnings when they are 15 and 30 seconds away from a collision on the runway.
    Anonymous researcher has scraped public Spotify accounts of politicians and celebrities, highlighting major privacy risks in default platform settings.
    AI chatbot toys marketed as screen-free playmates could undermine children's empathy and critical thinking skills, according to pediatric specialists.
    TransUnion confirms a major data breach affecting 4.4 million U.S. consumers after hackers exploited third-party Salesforce apps to steal personal info.
    Scammers use fake DocuSign emails claiming Apple Pay charges to steal personal information using fraudulent phone numbers and fake receipts.
    Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
    Farmers Insurance confirms data breach affecting over 1.1 million customers nationwide, exposing customer info including addresses.
    The iconic Mary Kay pink Cadillac goes electric with the Cadillac Optiq, available only to the company's top 1% of sales force performers.
    Cybercriminals abuse trusted Intel driver to gain kernel access and shut down Windows Defender, enabling undetected malware deployment since July 2025.
    Using email aliases for online shopping and subscriptions can protect your privacy by preventing companies from linking your activity across websites.
  • Disability Discrimination Act (DDA) & Web Accessibility

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

    10 Tips For A Successful Website

    Have you ever thought the secret behind the success of... Read More

    Optimal Website Design

    Optimal website design is the art of logical navigation.It will... Read More

    3 Reasons Why You Need URL Rewriting Module To Enchance Your Web

    URL rewriting are major needs for your sites that produce... Read More

    Why Your Brick-and-Mortar Biz Needs A Website

    The internet has taken over our lives with a vengeance,... 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 Own Website - General Website Design Tips

    Wow, we`ve already taken a domain and chosen a web... Read More

    Make Your Web Site Stand Out

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

    Abandonment - Why Visitors Don?t Turn Into Customers

    Every good Internet business understands the value of conversions versus... 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

    Writing Effective ALT Text For Images

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

    Why You Really Need Your Own Web Page In-order To Make Online Business!

    How many Web Sites did you stumble over telling you... Read More

    Are You Overlooking the Benefits of Using Sub-Domains within Your Web-hosting account?

    One of the most useful features offered by some web... Read More

    Maximizing ROI via Web Site Traffic Analysis

    We are clearly well past the innocent "golden age" of... Read More

    Revealing the Firefox Secret Used by Top Web Developers and Power-Browsers

    Profiles are one of the best kept secrets around regarding... Read More

    How Improving Your Sites Usability Can Increase Your Buyers by 40% and Boost Each Order By 10%!

    With all of the factors involved in designing and building... Read More

    3 Ways To Increase Your Websites Conversion rate

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

    Have You Got Your Ear To The Ground Or Your Head In The Sand?

    What happened on your website yesterday? What about last week... Read More

    Web Site Promotion: How To Advertise Your Website For Profits

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

    The Two Most Common Mistakes That Scare Away Web Visitors

    Can you imagine the hypocrisy of spending thousands of dollars,... Read More

    Planning Your Website for Success

    Most people know they need a website in order to... Read More

    Building a Great Intranet Taxonomy

    While it may seem like a "quick and easy" task,... Read More

    Find the Purpose of Your New / Proposed Website

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

    Trust Us! Sending Credible Messages

    A company must earn and keep trust or sales don't... Read More

    Streamline your Business Website with a Content Management System

    I talk with so many people who have small businesses... Read More

    weekly home cleaning Mundelein ..