Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in /home/a26f9f83/public_html/articles/includes/config.php on line 159
MySQL Database Handling in PHP > NetSparsh - Viral Content you Love & Share

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!

    daboss@bimmercenter.com

    In The News:

    This RSS feed URL is deprecated, please update. New URLs can be found in the footers at https://news.google.com/news

    Coinpedia (press release) (blog)

    Top 10 Crypto Web Development Company
    Coinpedia (press release) (blog)
    After the emergence of the first cryptocurrency in 2009, a chain of reactions started leading to the growth and development of the cryptocurrency industry. Here we are listing the Top ten companies that are leading in cryptocurrency web and application ...


    Business 2 Community

    4 Ways to Ensure Effective Web Development
    Business 2 Community
    When it comes to building a new website, many businesses choose to focus predominantly on the design aspect of the project when approaching agencies to carry out the work. Whilst design is a vital part to any web development project, there are a vast ...


    Customer Think

    Know about the current trends in web development
    Customer Think
    In case of web development, you may have probably heard that the only constant is the change. It is an industry-defining statement as web development keeps on changing. User expectations are evolving rapidly, and it is quite essential than ever before ...


    Neowin

    Pay What You Want for this Full Stack Web Development Bundle
    Neowin
    Today's highlighted deal comes via our Online Courses section of the Neowin Deals store, where you can Pay What You Want for the Full Stack Web Development Bundle. The world's best developers are Full Stack developers. Here's 122+ hours of ...


    Which are the best tools for front-end web development?
    SitePoint
    They are not really front-end development tools but essential for monitoring your site as far as a user is concerned. Everything may work locally on your desktop but could possibly have online problems. Their suggestions and help pages are also good.


    Chicago Business Journal

    Sun-Times partnering with Vox Media for website development
    Chicago Business Journal
    New York-based Vox Media is coming to the rescue of the Chicago Sun-Times. Vox and the Sun-Times announced Thursday a new partnership to redesign the Sun-Times entire website — top to bottom.

    and more »

    AroundtheO

    New Portland program focuses on coding and web development
    AroundtheO
    In an effort to meet regional demand for web developers, the UO will offer a new 24-week coding course in Portland starting this January. The University of Oregon Coding Boot Camp is a part-time program is geared toward adult learners and working ...

    and more »

    International Association of Athletics Federations

    Job vacancy: Marketing and web development project manager
    International Association of Athletics Federations
    The IAAF is seeking a marketing and web development project manager at its Monaco headquarters. Reporting to the chief information officer, the marketing and web development project manager will oversee several projects and ensure that information ...


    Intermediate PHP Web Developer
    Bizcommunity.com
    Dreamsmiths is searching for a capable Intermediate Web Developer to join our growing team, ideally the candidate should have experience working in a development team or team environment. The candidate should be capable of building and maintaining ...


    Project6 Receives 2018 WebAward for Outsanding Achievement in Web Development for Clackamas Community ...
    Markets Insider
    EMERYVILLE, Calif., Sept. 25, 2018 /PRNewswire-PRWeb/ -- San Francisco Bay Area website design and development company Project6 is proud to announce that it has been awarded the 2018 WebAward for Outstanding Achievement in Web Development ...

    Google News

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

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

    Up The Sandbox!

    Go to any internet marketing forum you want these days... Read More

    Making The Business Case For Web Standards

    Through the explosive growth of the Web, companies have realized... Read More

    Web Content: How Much Should I Pay?

    When web content gets discussed on webmaster bulletin boards, the... Read More

    MySQL Database Handling in PHP

    Most interactive websites nowadays require data to be presented dynamically... Read More

    How To Start Your Own Website

    I'm one of those people that always wanted a website,... Read More

    FREE Amber Alert Tickers for Your Website

    Project Safekids provides webmasters a valuable tool which could save... Read More

    Guidelines for Web Page Optimization

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

    5 Essential Traits Of A Home Business Website

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

    Credibility - 10 Ways to Build it on Your Website

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

    Allocating Your Web Site?s Budget Properly

    I had a client say something to me the other... Read More

    Website Globalization

    Globalize Your WebsiteThe Internet has unlocked a wide array of... Read More

    Set Up Your Website in Minutes ? For Free

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

    7 Questions to Understand Your Customer Better

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

    The 5 Biggest Mistakes Almost All Web Designers Make

    Huge Mistake #1: Creating a Website with Flash -- Did... Read More

    Build a Strong Foundation for Creating Your First Online Identity ? Notes for Beginners

    If you are looking for creating your online presence you... Read More

    Why Local Service Companies Should Have a Website

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

    The 2 Most Common Web Site Mistakes

    When we review the sites of potential clients, there are... Read More

    Unlocking the Power of Your Website!

    Why do some web sites reap huge benefits while others... Read More

    Getting One-way Inbound Links: the 5 Major Strategies

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

    Ten Major Tips to Develop a Multilingual Web Site to Work

    If you are living in a country that its native... Read More

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

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

    Why to Have a Website for Your Company

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

    Database Driven Web Site - Do You Need It?

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

    Streamline your Business Website with a Content Management System

    I talk with so many people who have small businesses... Read More