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

    limo prices to midway Crystal Lake west of Randal .. Lockport Chicago limo O’Hare
    In The News:

    Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
    The number of people affected by a UnitedHealth data breach in February 2024 was actually higher than previously reported and was the largest medical data breach in U.S. history.
    A phishing campaign uses Google Calendar to schedule fake meeting invitations that appear legitimate, redirecting targets to phishing sites.
    Toll road text scam: Fake messages claim unpaid fees, seek payment via fraudulent links. Kurt “CyberGuy" Knutsson says this scam is becoming increasingly sophisticated and widespread.
    Kurt “CyberGuy" Knutsson says these steps ensure that your friends and family get to the exact moment you want them to see. Try it out and streamline your sharing experience.
    Tech expert Kurt “CyberGuy" Knutsson offers tips on how to protect your identity: Be vigilant, monitor accounts, use smart security, know theft response.
    Tech expert Kurt “CyberGuy" Knutsson says cybercriminals are exploiting iMessage phishing protection, tricking users to reactivate links.
    It's hard to imagine life without in-flight Wi-Fi these days – but sky-high web surfing comes with its own set of privacy concerns. Here's how to protect yourself.
    Over 400,000 cases of credit card fraud were reported in 2024 to the Federal Trade Commission. The CyberGuy explains how to protect against becoming another victim.
    A nondescript metal box transforms into a fully functional electric motorcycle that blurs the line between vehicle and urban infrastructure.
    Many apps unrelated to location still ask users for tracking permission, and Texas Attorney General Ken Paxton has filed a lawsuit seeking to end this alleged practice by Allstate.
    Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
    Melody, the artificial intelligence-powered robot, is life-sized and represents a move toward creating robots that resemble people in appearance and interaction.
    Here are five easy smartphone rules from the National Security Agency that you can follow to better protect your mobile device from hackers and scammers.
    A U.S. robotics company has developed "Jennie," a robotic pet powered by artificial intelligence that's designed to comfort those facing mental health challenges.
    Healthcare records for more than 184 million Americans were breached in 2024, but you can take steps to minimize that risk. Kurt the CyberGuy explains.
    The Skyrider X1 from Rictor claims to be the "first amphibious flying passenger motorcycle," and it combines land and air travel in one sleek design.
    Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
    Home inventory and restoration apps help itemize and track every item in your home in the event you have to make an insurance claim after a natural disaster.
    The White House launched a new cybersecurity safety label, the U.S. Cyber Trust Mark, intended to help consumers make informed decisions on smart device safety.
    Today's tech can help make life so much easier — if you know how to use it. Here are a few tips on the quickest and best ways to make your phone, laptop and other devices work for you.
    Tech expert Kurt “CyberGuy" Knutsson offers Windows 11 restart fixes: Update drivers, run system scans and check hardware.
    Sony and Honda's Afeela 1 EV launches with advanced tech and entertainment for $89,900. Tech expert Kurt “CyberGuy" Knutsson gives his takeaways.
    With limited hard drive space, tech expert Kurt “CyberGuy" Knutsson offers these tips for storing large video files and photos.
    Tech expert Kurt “CyberGuy" Knutsson says the $236,000 Yangwang U9 electric hypercar boasts 1,287 horsepower and can jump obstacles.
  • Easy Steps To Website Development and Promotion

    One of the most important parts of establishing a new... Read More

    7 Questions to Understand Your Customer Better

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

    Benefits Of Making Your Website Accessible To Disabled Users ? Part 2: The Business Case

    The Disability Discrimination Act states that service providers must not... Read More

    Server Stats ? Analyzing Traffic To Your Site

    Analyzing traffic to a site is a key factor in... Read More

    Thinking Of Taking Money Online? Whats Best For Merchants? PayPal, WorldPay or Something Else?

    It's now easier than ever to take payments on the... Read More

    How To Start Your Own Website

    I'm one of those people that always wanted a website,... Read More

    7 Profit Producing Reasons Why Every Successful Service Professional Needs A Web Presence

    Your business website is a critical aspect of being able... Read More

    Why Local Service Companies Should Have a Website

    The Internet is being used by local service business to... Read More

    Ecommerce for Beginners

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

    The Evolution Of Search

    There have been many significant changes to the face of... Read More

    9 Keys to an Effective Website

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

    If Its Done Its Done for: Make Your Website Part of Your Business before You Build

    Web sites are an Ongoing Part of Business.All too many... Read More

    How Does Your Website Make Me Feel?

    When people think about the Internet, they think about technology.... Read More

    Newsletter Management Using PHP w/o mySQL for Beginners

    Let's begin by setting some limits. If you're like me,... Read More

    Lead Generation How to -- 10 Tips On Generating More Internet Sales Leads

    Many millions of dollars are being wasted on promoting and... Read More

    Creating Websites that Cater to Exam Preparations

    One of the things that Internet excels is disseminating information... Read More

    Learn to Build a Better Online Business Website Using Keywords and Content

    Are you aware that 90% of the websites on the... Read More

    4 Marketing Tips for Resourceful Webmasters!

    The internet is a sea of knowledge. Getting your information... Read More

    Ten Major Tips to Develop a Multilingual Web Site to Work

    If you are living in a country that its native... Read More

    Why a CSS Website Layout Will Make You Money

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

    How To Get Profits From Your 404 Page Not Found File

    "'Page Not Found' on this Server. Check the URL and... Read More

    How to Promote Your Law Firm Website On the Internet for Maximum Profit

    Making maximum profit from your law firm website is important,... Read More

    The Disability Discrimination Act (DDA) & Web Accessibility

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

    The Birth of a Professional Web Site

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

    Get Your Business on the Web

    I can't think of any business alive today that couldn't... Read More

    taxi o'hare Auburn ..