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

    kitchen deep cleaning Deerfield ..
    In The News:

    X Square Robot unveils Wall-OSS open-source AI model that helps robots adapt to unpredictable real-world tasks beyond narrow scenarios.
    Airport workers report surge in fake lost luggage claims as scammers retrieve discarded baggage tags containing passenger names and travel information.
    Unlike robotaxi competitors, Tensor focuses on consumer-owned self-driving cars that adapt to highways and urban roads with full redundancy systems for safety.
    Medicare scams cost $54 billion in 2024 as fraudsters target beneficiaries with fake calls demanding payments and personal information to steal benefits.
    Scammers create fake Evite invitations that mimic legitimate event emails, requiring users to verify senders and use antivirus software for protection.
    The new Apple Watch hypertension feature passively monitors blood pressure patterns over 30 days using sensors to detect chronic high blood pressure signs.
    Chrome extension spyware disguised as a free VPN service highlights security risks after it captured private browsing data from trusted sites.
    New research shows how fatty acids in cooking oil can safely dissolve and recover silver from circuit boards without harmful chemicals or environmental damage.
    The Fox News AI newsletter gives you information on the latest AI technology advancements, and about the challenges and opportunities AI presents now and for the future.
    Anthropic investigates alarming AI abuse case where hacker automated entire cybercrime campaign using Claude, stealing sensitive data from defense and healthcare firms.
    TikTok, Meta and YouTube restrict Charlie Kirk shooting videos with age gates and warnings while X faces criticism for allowing continued circulation.
    Cybercriminals use fake troubleshooting websites to trick Mac users into running terminal commands that install Shamos malware through ClickFix tactics.
    San Francisco startup Fable launches Showrunner, an AI platform dubbed the 'Netflix of AI' that generates animated episodes from text descriptions with Amazon support.
    Apple raised iPhone prices for some models despite receiving tariff relief from President Donald Trump, with the new lineup starting at $799 for the base model.
    A two-story 3D concrete printed home in Western Australia demonstrates faster construction methods that could reshape American housing amid rising costs.
    Credit scores remain important during retirement for insurance rates and housing applications, while seniors become prime targets for identity theft and financial scams.
    Scammers now send unexpected packages with QR codes that redirect victims to fraudulent websites or download malicious software to steal sensitive information.
    Meeting AI tools record private conversations alongside work discussions, creating privacy risks that can be managed with proper settings and awareness.
    Hotel privacy concerns are valid but rare, with methods to detect hidden tech using smartphone flashlights, mirror tests and scanning apps.
    Improve your Wi-Fi speed and reliability with 10 simple router optimization tips that don't require special apps or expensive subscriptions.
    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.
  • Duplication vs. Individualism

    How can we add Word Rich Content to our Websites... Read More

    Three Things Every Website Should Do

    When I started my company in 2002, I knew I... Read More

    From Search to Sale

    The Internet car buyer is an elusive animal. Trying to... Read More

    Does Your Business Really Need A Website?

    Website, website, website, everyone says you need one. But do... Read More

    Set Up Your Website in Minutes ? For Free

    There was a time not long ago when setting up... Read More

    What ROI You Should Expect From Your Web Site

    Is Your Web Site an Expense or an Investment?Why don't... Read More

    Make Your Own Brand!

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

    Marketing Your Business Online

    As a business you can't afford to ignore the Internet... Read More

    Frequent Business Question: How Do You Start a Web Business?

    Today it seems everyone is jumping on the entrepreneur bandwagon,... 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

    Effectively Using Robots Meta Tags

    The "robots" meta tag, when used properly, will tell the... Read More

    Art, Artists and the Web: Part 4--What to Do After a Website is Designed

    What to do if you are an artist after you... Read More

    A Network Of Web Sites Is Not Enough!

    The big thing these days is your ability to capture... Read More

    Tips for Building a Great Business Website

    A website is lifeline of internet home business. In order... Read More

    3 Ways To Increase Your Websites Conversion rate

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

    Why Local Service Companies Should Have a Website

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

    Pick Up Your Own Domain Name

    Think up a name and check if yourname.com is available.... Read More

    Your Website Reflects Your Business

    Some left shoes are in isle 5, while the right... Read More

    The Birth of a Professional Web Site

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

    How Web Templates Are Helping Online Businesses to MultiplyTheir Income

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

    What is Blogger.com?

    In the late 1990's three San Francisco based web developers... Read More

    Website For The Company Store

    If your company own one or more stores, you might... Read More

    3 Essential Tools Every Website Designer and Marketer Needs

    Appealing to others is really important when you have a... Read More

    You Need a Website!

    No matter the size of your business, having a web... Read More

    Your Content for Nothing and Your Clicks for Free

    The first thing my business consultant (read brother-in-law) asked me,... Read More

    interior house cleaning Highland Park ..