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

    residential cleaning services Winnetka ..
    In The News:

    New AI road monitoring system uses sensor-embedded fabric to predict infrastructure problems, potentially reducing maintenance costs and traffic disruptions for cities.
    Holiday charity scams target retirees through lookalike organization names, untraceable payment requests, and data broker information to steal donations.
    The Federal Trade Commission says criminals are posing as IRS agents, law enforcement officers or other officials, often over the phone or online, to steal thousands of dollars at a time.
    AI phishing scams now use voice cloning and deepfake technology to trick victims, but Kurt "CyberGuy" Knutsson reveals warning signs to watch for.
    Inversion Space unveils Arc, a reusable reentry vehicle that can deliver up to 500 pounds of cargo from orbit to anywhere on Earth in under an hour.
    Red flags like processing fees, urgent countdowns and requests for full Social Security numbers expose fraudulent settlement sites targeting consumers.
    Comprehensive analysis of Google Maps, Waze and Apple Maps examines usability, routing accuracy, data handling and features across the top navigation platforms.
    Expert analysis reveals whether wired Ethernet or wireless Wi-Fi connections are safer for home internet use, plus practical steps to secure your network from attackers.
    Australian construction robot Charlotte uses sand, crushed brick and recycled glass to 3D print fireproof, floodproof homes with reduced carbon footprint.
    Cybercriminals are using fake invitation emails to trick recipients into downloading malware and stealing personal information and data.
    Flying drones could help retailers fight a 93% increase in theft rates as Flock Safety promotes airborne security systems to track suspects and deter crime.
    The Fox News Artificial Intelligence Newsletter brings you the latest news on the emerging technology every Saturday, highlighting top stories.
    Hacker group Radiant stole data from 8,000 children at Kido nursery chain, demanding ransom and directly contacting parents with intimidation tactics.
    As 18 states implement bell-to-bell cell phone bans, creative students use Google Docs, iMessage on MacBooks and Post-It notes to stay connected in class.
    A sheriff's captain says deputies often spend hours writing reports between calls, but Axon's AI program, Draft One, helps them save crucial time in the field.
    Sora 2, OpenAI's new video-generation app, can create AI-generated videos based on a singular prompt. The results are both mind-blowing and terrifying.
    iPhone and Android users can reduce Wi-Fi calling battery drainage through settings adjustments, background app limits and stronger Wi-Fi connections.
    Work email scams are becoming harder to detect as criminals use AI and spoofed addresses to trick employees into opening malicious attachments and links.
    From her Arizona living room, Christina Chapman ran a covert hub that helped North Korean operatives infiltrate U.S. firms, netting $17 million in stolen salaries.
    UC Santa Barbara researchers developed a soft robotic intubation system that achieved 100% success rates for experts and 96% for paramedics with minimal training.
    Scammers exploit probate filings to target grieving families with fake fees and debts, Kurt "Cyberguy" Knutsson reports.
    Automotive giant Stellantis becomes latest victim of widespread Salesforce breaches affecting companies like Google, Cisco and Adidas this year.
    A woman from Washington reunited with her missing Maine Coon cat Louie after 11 days using Love Lost, a free AI-powered pet recovery platform.
    Expert cybersecurity tips help Mac owners remove malware infections and strengthen defenses with antivirus software, password managers and system updates.
    Meta's AI chatbot training rules bans sexual roleplay with minors and block access to child abuse material as regulators scrutinize its safety measures.
  • Art, Artists, and the Web:Part: 2--First Steps in Building an Artist?s Website

    What you should do and know if you are an... Read More

    Non-Profits: Successful Online Strategies

    More and more non-profit organizations are developing websites to help... Read More

    7 Tips to Make Your Order Page Work Harder

    So your prospect, Mary, is sitting at the computer reading... Read More

    The Birth of a Professional Web Site

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

    Tips to Protect Your Downloads or Products

    1. Upload robots.txt file in to your root directory and... Read More

    A Network Of Web Sites Is Not Enough!

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

    Develop a Solid Website Presence

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

    Linking Out is Good

    Many websites I come across don't have a single link... Read More

    How One Word Or Even One Letter Can Boost Conversion Rates By Over 400%!

    Recently I was reviewing the keyword specific conversion rate data... Read More

    Do Authors Need a Website?

    "I am a writer, not a business-person. I don't want... Read More

    Improve Web Sales Figures Forever And Ever Amen!

    "How do I improve web site sales figures?" The CEO... Read More

    Increase Hits to Help Increase Your Business

    In this day and age most businesses consider having their... Read More

    7 Reasons YOU Need a Website

    1) A marketing necessityI still hear it said from time... Read More

    Building a Web Store on Shoestring Budget in less than Four Hours

    Starting a business online is no longer as difficult as... 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

    How Worldly is Your World Wide Web?

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

    2 Key Ways to Make Your Site a Success

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

    For Newer Sites, Web Marketing Depends On Titles & Links!

    We all start out the same. Some have budgets, some... Read More

    Why Your Brick-and-Mortar Biz Needs A Website

    The internet has taken over our lives with a vengeance,... Read More

    New Years Resolutions: Is Improving Your Website One of Them?

    With the New Year upon us yet again, it's time... 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

    Beautiful Web Sites Seldom Make More Sales

    Designing an E-commerce Web site is not as simple as... 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

    Database Driven Web Site - Do You Need It?

    Many of site owners still don't realize all advantages of... Read More

    The Secret Benefit Of Accessibility: Part 2 - A Higher Search Engine Ranking

    An additional benefit of website accessibility is an improved performance... Read More

    high-end home cleaning Highland Park ..