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

    one time home cleaning Highland Park ..
    In The News:

    Privacy risks are hiding in plain sight, as your personal data is likely being collected, tracked, and sold without your knowledge.
    VenHub, a fully autonomous, AI-powered smart store just opened at the LAX/Metro Transit Center in Los Angeles.
    A woman's Facebook account takeover reveals dangerous social engineering tactics and provides lessons on recovery, avoiding scams and enacting stronger security measures.
    Shanghai engineers are using 432 walking robots to relocate a complex, preserving Shikumen architecture while creating space for a modern underground hub and cultural center.
    Major healthcare data analytics firm Episource had a cybersecurity incident exposing 5 million patients' medical records and personal information in recent breach.
    A new sophisticated PayPal scam sends legitimate-looking emails from official PayPal addresses, using phone numbers instead of links to convince victims to download remote access tools.
    Fox News' AI Newsletter brings you the latest on this rapidly evolving technology.
    The innovative Kara Pod device extracts moisture from air to create mineral-rich water and brew coffee, featuring UV sterilization and compatibility with Nespresso pods.
    Gov. Hochul unveiled New York's nuclear power plant project to power a million homes, which faces permit challenges while promising jobs and a cleaner energy future.
    SAFE introduces Aerie, an underground luxury bunker combining high-end living with advanced security, wellness amenities and interactive walls simulating panoramic views.
    A Facebook scammer posing as Elon Musk tricked a victim with promises of a Tesla and $250,000, requesting gift cards that become untraceable once the codes are shared.
    Experience hands-free golfing with the Robera Neo smart caddie that uses AI to follow you, navigate obstacles and carry your clubs with GPS course mapping.
    Cybersecurity alert: 16 billion passwords exposed in massive credential database affecting major platforms. Protect your accounts with password managers
    The innovative BeBot robot uses electric power to remove pollution from shorelines, covering 20-30 times more area than manual collection while preserving wildlife.
    A new ClickFix campaign targets macOS users with fake Spectrum support sites, tricking victims with CAPTCHA failures to paste commands that install information-stealing malware.
    AI-powered autonomous trucks from Plus Automation are being tested across the U.S., Europe and Asia to solve logistics challenges while facing regulatory hurdles and labor concerns.
    Your health information might feel private and secure with your doctor, but the reality is far more complicated. Data brokers collect a wide range of sensitive health data, from diagnoses and prescription details to personal identifiers, and sell this data to marketers, insurers, and other third parties.
    Kepler Robotics has officially introduced its Forerunner K2 "Bumblebee" humanoid robot at the SAIC-GM automotive plant in Shanghai, marking a significant moment in the real-world deployment of advanced robotics.
    Scammers never seem to be running out of new ways to try and get their hands on your money – and lately, they've been getting sophisticated.
    Thirty people were injured and killed, though the exact number remains currently unknown, when a suicide bomber blew himself up in a Damascus church.
    Unexpected password reset alerts signal potential hacking attempts or phishing scams. Learn how to respond and establish stronger security measures for your accounts.
    A historic electric airplane flight landed at JFK with passengers, marking a milestone as Beta Technologies demonstrates the practicality and efficiency of electric air travel.
    The Chaos ransomware group breached Optima Tax Relief in a double-extortion attack, stealing sensitive customer case files and corporate documents with personal information.
    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 first human brain-computer interface by Paradromics was completed in 20 minutes, featuring microelectrodes to help paralyzed individuals control computers with thoughts.
  • Wheres Your Focus, Money or Customer?

    Is your website Really useful?I had a 'rep' in yesterday... Read More

    Build a Strong Foundation for Creating Your First Online Identity ? Notes for Beginners

    If you are looking for creating your online presence you... 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

    The Web: Your Window of Opportunity

    Thinking about starting up a business? The web is the... Read More

    Web Designer? You Dont Need No Stinkin Web Designer!

    RIVERSIDE, CA August 4, 2004 ?- "Historically, small business owners... Read More

    Unlocking the Power of Your Website!

    Why do some web sites reap huge benefits while others... Read More

    Why to Have a Website for Your Company

    After having decided to start a business, the next thing... Read More

    Ten Quick Tests To Check Your Website For Accessibility

    The Disability Discrimination Act says that websites must be made... Read More

    The Evolution Of Search

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

    Web Measurement: What You Don?t Know Would Make A Great Book

    "What's in it for me?" you ask. "Why should I... Read More

    Building eCommerce Websites that Work - Part 2

    Succeeding with an eCommerce website is a dream for many... Read More

    What Business Owners MUST Know BEFORE They Commit To Having A Web Site

    Q1.Why would I need a web site? A1.- Improve my... Read More

    Successful Web Development: 10 Key Elements

    There are many elements that are key to successful web... Read More

    What Does An Ex-Basketball Coach Know About Pulling Web Metrics?

    In my previous life.....Actually, my previous JOB life, I was... Read More

    10 Things You Should Be Monitoring On Your Website

    Every business needs to know how it is doing. That's... Read More

    The Topic Of Your Website

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

    3 Ways To Increase Your Websites Conversion rate

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

    Making The Business Case For Web Standards

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

    Website Survival Guide

    In the big, wide online world there are millions of... Read More

    Having a Portfolio on the Internet Can Help you Infiltate the Workforce

    If you're an up and coming graphic/web designer just out... Read More

    The Cost Of A Web Site

    Just starting out, you can start with a simple informational... Read More

    How To Evaluate Your Web Sites Performance

    Setting up a website is the very first step of... Read More

    Autoresponders Make You Look Like A Pro

    People like to get immediate responses to requests. Autoresponders are... Read More

    Traffic for Webmasters

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

    Website Value - Whats Your Business Website Worth?

    If you were asked to put a value on your... Read More

    licensed cleaning services Park Ridge ..