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

    custom home cleaning Lincolnshire ..
    In The News:

    Amazon Prime Day shoppers face threats from 120,000-plus scam websites as cybercriminals prepare phishing traps and malware ahead of the July sales event.
    Fox News' AI Newsletter brings you the latest on this rapidly evolving technology.
    The AEON humanoid robot tackles labor challenges with Nvidia AI, Microsoft Azure cloud and advanced spatial awareness, working alongside industry leaders Schaeffler and Pilatus.
    Google's Ask Photos feature brings AI-powered searches to your photo library, letting you find memories with natural language queries while maintaining privacy controls.
    The return of blue book exams emerges as universities fight widespread AI academic dishonesty and educators debate whether to ban AI tools or teach responsible usage.
    Signs your phone might be hacked include strange behavior, unauthorized texts, battery drain and pop-ups, while protection involves updating software and avoiding public Wi-Fi.
    Tokyo startup H2L has launched Capsule Interface technology enabling full-body robot control with muscle sensors, offering immersive remote operation.
    The A.I. industry seems set for growing pains as Big Tech companies scramble for solutions to the medium's unprecedented strain on the power grid.
    Fourth of July fireworks cause a 60% spike in lost pets, but GPS trackers and AI photo-matching services like Love Lost can help reunite missing dogs with their families.
    Protect yourself from jugging, the rising crime by which thieves monitor ATM users and follow them to steal cash, with six practical safety tips to stay alert and secure.
    U.S. airlines like Delta, American and United are selling your domestic flight records to government agencies through the little-known Travel Intelligence Program.
    Autonomous robots from Uber Eats are rolling out across U.S. cities, featuring LIDAR sensors, secure compartments and all-weather operation for food and grocery delivery.
    Costco has expanded into EV infrastructure with new ultra-fast charging stations that can charge most electric vehicles to 80% in 20-60 minutes while shoppers browse the warehouse.
    The new Gemini Robotics On-Device AI allows robots to perform complex tasks without internet, offering enhanced privacy, reliability and adaptability for real-world use.
    Protect yourself from Amazon phishing scams by spotting red flags like suspicious sender addresses and spelling errors while using Amazon's Message Center to verify communications.
    GAC Group's new Govy AirCab flying car combines lightweight design with advanced safety systems and smart cabin technology to revolutionize city travel with an 18.6-mile range.
    The Berkeley Humanoid Light (BHL) is a lightweight, open source humanoid robot that anyone can build using 3D-printed parts and off-the-shelf components.
    Meta’s new AI chatbot is getting personal, and it might be sharing more than you realize.
    A new threat targets both Android and iPhone users: SparkKitty, a powerful mobile malware strain that scans private photos to steal cryptocurrency recovery phrases and other sensitive data.
    Thanks to a team at the University of California, Davis, there's a new brain-computer interface (BCI) system that's opening up real-time, natural conversation for people who can't speak.
    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.
  • How Web Templates Are Helping Online Businesses to MultiplyTheir Income

    Web templates by nature are created to aid and ease... Read More

    Why A Simple Counter is Never Enough for Your Website

    A simple website stats counter is not enough if you... Read More

    Trust Us! Sending Credible Messages

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

    HTML Editors 101 - Smaller Is Better

    SiteSpinnerSiteSpinner is a user-friendly web site development tool that includes... 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

    Beautiful Web Sites Seldom Make More Sales

    Designing an E-commerce Web site is not as simple as... Read More

    7 Ways to Gain a Professional Online Reputation

    A professional online reputation is essential to the flourishing of... Read More

    If You Build It, They Will Come?

    Building a new website can be a lengthy task. From... Read More

    Why You Need A Web Site

    "I have a small business with just a couple of... Read More

    Web Accessibility Myths

    With more and more countries around the world passing laws... Read More

    Making Your Websites More Compelling

    The Internet is a remarkable publishing medium. With just a... 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

    Are You Being Scammed By Your Web Design Company?

    This is a growing concern amongst many business owners. Does... Read More

    WYSIWYG Versus Coding: HTML With A Purpose

    After years of working with entrepreneurs who developed their first... 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

    Get to Know People through Websites

    Web sites are not just places to sell things; they... Read More

    7 Questions to Understand Your Customer Better

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

    Selecting a Web Content Management Product

    So you want to take a look at a real... 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

    Ever Wondered What Challenges Other People With Their Web Site?

    Have you ever wondered what challenges are faced by other... Read More

    Five Essential Questions to Answer Before Creating Your First (or second...or third) Website

    Right after the thought, "I need a website," people usually... Read More

    Five Question-Five Minute Web Makeover Quiz

    If you haven't made the sales and built your clients... Read More

    Running An Effective Website

    Running an effective website is a continuous process. I have... Read More

    PHP & Account Activation

    When a user signs up at your website, you may... Read More

    Guidelines for Web Page Optimization

    Below, I have listed 8 simple steps that you can... Read More

    move in cleaning service Glencoe ..