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

    family-safe home cleaners Lincolnshire ..
    In The News:

    Chrome faces its sixth zero-day attack in 2025 as Google patches critical V8 engine flaw CVE-2025-10585 discovered by Threat Analysis Group.
    The Hypershell X Ultra exoskeleton features 12 terrain modes and carbon fiber construction to enhance hiking, cycling and outdoor adventures.
    Personal data from public records and data brokers helps scammers create tailored scam stories, making their calls and emails more believable to potential victims.
    Uber Eats partners with drone delivery startup Flytrex to test autonomous food delivery services in U.S. markets, marking the company's first investment in drone technology.
    OpenAI announces 120-day plan to strengthen ChatGPT safeguards for teens, including parental controls and expert council on AI wellbeing.
    Spam emails can help improve security — if reported correctly. Learn how to report spam across major email services and get key tips to protect your inbox and personal data.
    Harvard researcher Avi Loeb says comet 3I/ATLAS weighs 33 billion tons and spans 3.1 miles, making it far larger than previous interstellar visitors.
    The Glassboro Public School District in New Jersey has partnered with ZeroEyes and InformaCast to detect visible firearms and alert staff and law enforcement within seconds.
    Hackers use fake Google search results to trick users into downloading lookalike apps laced with malware that have been pushed to the top.
    Generative A.I. has lowered barriers for sophisticated cyberattacks as hackers exploit ChatGPT and other tools to forge documents and identities.
    IOS 26's new features include bigger lock screen clocks, dirty lens alerts, and improved spam detection for iPhones.
    Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
    Sophisticated phishing scams now exploit Apple's iCloud Calendar invite system to bypass spam filters and trick users into calling fake support numbers.
    Complete guide to medication tracking apps for iPhone and Android users, featuring built-in Health apps, MediSafe, MyTherapy, and smart pill dispensers.
    Alef Aeronautics plans to begin production of its electric flying car Model A by late 2025, following FAA approval for limited testing at five airport locations.
    Five common overpayment scams use fake checks and third-party payment requests to steal money from unsuspecting victims across various scenarios.
    The Robeta Ananya luxury camper van features a full living room, ceiling bed, washer-dryer, and complete kitchen starting at $295,000 for the limited Founders' Edition.
    Scammers exploit retirees' trust and assets through fake debt collection calls, but proper verification methods and reporting can stop these fraudulent schemes.
    Social media scams are rising as fraudsters create fake accounts and buy verification badges to deceive users, but these simple safety steps can protect you.
    The new groundbreaking Paris Solar-Battery Park in Wisconsin provides renewable energy day and night by capturing excess energy from its solar panels.
    Travelers face increased risks from fake Wi-Fi networks at airports and on flights as attackers exploit growing reliance on in-flight internet for entertainment and services.
    Older Android phones, tablets and car systems will lose access to new Waze features as the app drops support for Android versions below 10.
    Research shows iPhone owners' overconfidence in Apple security makes them easier targets for cybercriminals compared to Android users who take more precautions.
    Five key tech terms reshaping online shopping from mobile payments and instant delivery to social media commerce and inventory-free retail models.
    AI browsers from Microsoft, OpenAI and Perplexity can fall for scams faster than humans, completing fraudulent purchases and clicking malicious links without verification.
  • Eight Deadly Web Site Mistakes and How to Avoid Them

    Creating and maintaining an effective presence on the Web has... Read More

    Having a Domain Name And Web Site Gives Your Business The Key To The Door

    Just like all things in life, the ways to run... Read More

    Make Your Own Website - General Website Design Tips

    Wow, we`ve already taken a domain and chosen a web... Read More

    Disability Discrimination Act (DDA) & Web Accessibility

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

    How Worldly is Your World Wide Web?

    The growth in Internet usage around the world is astounding... Read More

    Develop a Solid Website Presence

    Starting an internet business can be confusing to non-veterans. The... Read More

    Using Mini Websites - Powerful Way to Direct Marketing

    Mini Websites are the most powerful and cost effective solution... Read More

    How to Handle Web Surfers Who Disabled JavaScript

    There are hundreds of millions of Web surfers today. Each... Read More

    Track Your Visitors, Using PHP

    There are many different traffic analysis tools, ranging from simple... Read More

    Freelance Programming is Easy to Manage

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

    Building a Web Site Requires the Right Software to Eliminate Headaches and Keep Money in Your Pocket

    When most people build a web site, they think one... Read More

    10 Things You Should Be Monitoring On Your Website

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

    10 Tips For Running A Profitable Web Site

    1. Address your targeted audience on your business site. Example:... Read More

    Abandonment - Why Visitors Don?t Turn Into Customers

    Every good Internet business understands the value of conversions versus... Read More

    Why You Need a Website

    You hear a great deal about the Internet these days... Read More

    Web Coach Tip: What You Should Know About DIY Web Sites

    Recently, a friend asked "What's the deal with those DIY... Read More

    The Top Ten Benefits of Having a Web Site

    Do you need a web site? Are you considering getting... Read More

    Enhance Your Website With A Yahoo-Style Directory

    Does your website have a links/resources page?Do you exchange reciprocal... Read More

    Have You Got Your Ear To The Ground Or Your Head In The Sand?

    What happened on your website yesterday? What about last week... Read More

    Get a Website!, Register Your Domain!: To Website or Not to Website?

    If you're fairly new to the Internet scene, you may... Read More

    7 Questions to Understand Your Customer Better

    Customer service questionnaires can help your clients better understand their... 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

    Email Addresses in Web Pages

    You want to give your site's visitors a way to... Read More

    Traffic for Webmasters

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

    after renovation cleaning Bannockburn ..