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

    housekeepers near Buffalo Grove ..
    In The News:

    Analysts at Silent Push have uncovered a widespread scam that uses counterfeit retail websites to steal the credit card information of online shoppers.
    Chrome users need to update their browsers immediately as Google addresses a critical vulnerability that hackers are actively exploiting. Additional security measures are recommended.
    A study found teens glance at phones for dangerous two-second intervals while driving, using devices primarily for entertainment, texting and navigation despite crash risks.
    Security researchers uncovered a flaw in Windows 11 that allows attackers to disable Secure Boot using Microsoft-signed tools, requiring manual updates to protect against bootkits.
    Carnegie Mellon's noninvasive brain technology allows users to move robotic fingers by thinking about the motion, offering new possibilities for people with motor impairments.
    Multi-factor authentication (MFA) adds an extra layer of protection to your accounts. Instead of relying only on a password, MFA requires you to verify your identity using two or more methods.
    Scientists from Duke, Harvard, and the University of Otago have unveiled a game-changing tool called DunedinPACNI. It uses a single AI brain scan to reveal how fast a person is biologically aging.
    A disturbing wave of fake agent phone scams is sweeping across the United States, catching people off guard and draining their savings.
    Emojis aren't just playful add-ons; they're powerful tools for building stronger connections in our increasingly digital world.
    Google has taken a major step toward the future of clean energy by partnering with Commonwealth Fusion Systems (CFS), an MIT spin-out working to build one of the world’s first commercial fusion reactors.
    If you create and share art online, you might have seen messages warning that Facebook's parent company, Meta, claims the right to use or even sell anything you post, whether it's pictures, poems, or artwork.
    Cybercriminals hit Qantas in a major data breach that exposed information from up to six million customers.
    Having reliable, loud, and timely emergency alerts on your phone or device is important because when severe weather strikes, every second counts.
    Here's how your daily brew is becoming the foundation for greener buildings.
    Artificial intelligence (AI) and large language models (LLMs), such as ChatGPT, are transforming how we learn. But what does this mean for AI and learning retention?
    Modern AI data centers use much more electricity than traditional cloud servers. In many cases, the existing power grid cannot keep up. One innovative solution is gaining traction: repurposed EV batteries for AI data centers.
    Microsoft 365 and Outlook users are being targeted by a tactic that injects fake billing alerts directly into their calendars.
    Researchers are now showing us that old smartphones as data centers could be the next big thing in sustainable tech.
    Scientists have created micro-robots for sinus infection treatment that can enter the nasal cavity, eliminate bacteria directly at the source, and exit without harming surrounding tissue.
    Let's examine how your data is collected in everyday life, who is buying and selling it, what happens to it afterward, and, most importantly, what you can do to protect yourself.
    Researchers at the University of Sheffield in the U.K. developed small robots called "Pipebots" that can travel inside water pipes to find and potentially repair leaks, all without any excavation.
    A groundbreaking new study has uncovered disturbing AI blackmail behavior that many people are unaware of yet.
    Four teams of autonomous humanoid robots competed in China's first AI soccer tournament, demonstrating advanced capabilities in ball detection and independent decision-making.
    The ID. Buzz autonomous van features self-driving technology with 13 cameras, nine LiDAR units and five radars after Volkswagen partnered with Hamburg and Uber for 2026 deployments.
    Social Security phishing scams use urgency and impersonation to steal personal data. Learn how to spot fake SSA emails and implement 10 protective measures.
  • Getting One-way Inbound Links: the 5 Major Strategies

    With search engines putting a damper on direct reciprocal links,... Read More

    Freelance Programming is Easy to Manage

    There are several reverse bid freelance sites out there. Beyond... Read More

    Writing Effective ALT Text For Images

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

    Conceptualize, Build and Publish a Web site

    Conceptualize, Build and Publish a Web site - What's required... Read More

    Why do I Need a Website?

    Cost Effective Advertising. Online selling. Increased geopgraphic range. Wider... Read More

    The Number 1 Reason Most Websites Fail

    Failure, just like success, is measured differently by each and... Read More

    3 Rules of Website Designing and Layout

    Now that you have got your domain name registered, your... Read More

    Internet Marketing Website Promotion -The 7 Biggest Mistakes I See People Make With Websites!

    1. Many people are not getting good or complete advice.... Read More

    Web Development Processes and Technical Environments

    The process of Web application development is critical to the... Read More

    Now You Have a Web Site - Have You Ever Heard of Accessibility?

    An accessible Web site is easily approached, easily understood, and... Read More

    The Number One Reason Most Websites Fail

    Failure, just like success, is measured differently by each and... Read More

    A Web Site for Your Small Business: How to Set Up a Website

    Your web site will be your window to the world.... Read More

    Why to Have a Website for Your Company

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

    5 Essential Traits Of A Home Business Website

    So you've decided to start an online work at home... Read More

    Make Your Own Brand!

    There are millions and millions of websites on the Internet.... Read More

    Make Your Artist Website Sell!

    SummaryHere's the scoop on what you must know when designing... Read More

    2 Key Ways to Make Your Site a Success

    If one more business owner tells me their website sucks... Read More

    Web Success Secret - Keep Your Visitors Interested

    Visitors to your site come looking for information or solutions... Read More

    7 Reasons Why Your Small Business Needs A Web Site

    Many small businesses have the misconception that their business can... Read More

    What Works and What not Works for Your Internet Business

    "Build it and they will come."--Filed of Dreams (1989) If... Read More

    CSS - Maximum Benefits

    What is CSS?CSS is a simple file which controls the... Read More

    Launch Your Own Website Today - Its Easier Than You Think!

    Thousands of people are now making a living online and... Read More

    Beyond Web Usability: Web Credibility

    If you've been developing websites on Mars for the past... Read More

    12 Tips To Great Websites

    Why do some sites succeed while the vast majority of... Read More

    Credibility - 10 Ways to Build it on Your Website

    If you have your own business website and you are... Read More

    custom home cleaning Mundelein ..