Web Development Information

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


    MORE RESOURCES:
    Internet and Businesses Online:Web Development Articles from EzineArticles.com
    04/13/2020 05:17 PM
    Revisiting WP-Members Plugin
    A review of a membership site plugin for your WordPress based website. Gives the Pros and Cons, along with my final recommendations.
    02/24/2020 10:11 AM
    How to Hire Ecommerce Development Firm With Fewer Efforts
    Hiring the best web development company is must, but not a hectic process if you consider some basic points in mind. The basic filter points will let you find the best portal that can amplify your business.
    02/13/2020 04:19 PM
    Get More Website Leads & Grow Your Sales
    You want your website to effectively market your product or service, keep potential customers engaged and convert them into leads and sales. Here are 9 tips to increase your online sales.
    01/02/2020 11:29 AM
    WordPress Development - Shopify Development Company
    Global Web Solutions is based in Pakistan since 2009. Providing quality services to their clients internationally and locally. We have done 1000+ Projects. Working with a client is a challenge for us. Your expectations we make it in real.
    12/13/2019 11:05 AM
    WPF Application Development: An Idea About the Latest Features
    Windows Presentation Foundation or WPF is a kind of Microsoft development platform that is utilized for building rich client applications. The latest WPF application development involves turning a WPF 4 desktop application into an apparently competent experience.
    12/13/2019 11:05 AM
    Increasing User Engagement Through Sitefinity Development Services
    Sitefinity CMS is a contemporary digital platform that is specifically designed to increase user engagement in a gainful manner. Ever since its first release, Sitefinity development services have cropped up with standard updates and features in its newest versions.
    11/27/2019 03:08 PM
    What Are the Responsibilities of a Web Developer
    A web developer's job is to build and maintain websites, keeping the customer and consumer in mind. This means the finished design should contain the products/services offered, and show the way to access them. For instance, the customer might need a form to get the visitor's email address so they could acquire more information, give a newsletter or thank a customer for doing business with them.
    08/01/2019 08:17 PM
    What Is A Responsive Website And Why Does Your Company Need One?
    The article describes what is a responsive website and why your company needs one. As the variety of screen sizes and resolutions have increased, developers now need to develop responsive websites that render well on all devices and screen sizes.
    08/01/2019 07:40 PM
    Benefits of PHP in Web Development
    With the world moving forward with new innovations there are lots of new technologies lined up to support the future. In this fast and furious race, aesthetic and effective web development is the key to uphold your business to put on higher level.Enhancing websites into effective communication channel.
    07/10/2019 10:15 AM
    5 Things Your Business Website Should Have
    The article describes 5 things your business website should have: a call to action, responsive design, clear contact information, a secure host and website, and social media links. Implementing these 5 ideas will make your website better.
    07/08/2019 12:00 PM
    Say Hello to Progressive Web Apps, the Bridge Between Web and Mobile Apps
    Progressive Web Apps (PWA) are a new mystery word in the world of internet and web. This article will give you a basic idea of what PWA is and how it can help you transform your online business.
    06/24/2019 11:43 AM
    Things to Consider Before Hiring A Web Development Company
    This article focuses on the key points one should keep in mind while hiring a web development company. There is a huge sea of web development companies to choose from. This huge pool of options can be overwhelming. So, how can you ensure that you choose the perfect web development company for your website building and designing?
    05/24/2019 12:50 PM
    Why You Need a Mobile-Friendly Website
    In a survey reveals the fact that more than 80% of people prefer to search on mobile instead of a desktop. In 2018 your website must be responsive otherwise you can't sustain in this fast changeable market. Now building mobile-friendly website doesn't take too much time. Coding technology has changed a lot in the last couple of years, and it takes more or less similar time to build like a static site.
    05/16/2019 11:59 AM
    A Comprehensive 2019 Guide to the Website Redesign Process
    Did you know that poor web design can hurt conversions and sales? An unattractive site deserves a website redesign. No matter what your company size or industry is, though, it's crucial that you take a strategic approach to your website redesign. Know what isn't working, what does currently work, and what goals you wish to achieve.
    05/10/2019 10:26 AM
    How Custom Web Development Can Be Beneficial For Your Business?
    The conventional strategies for web development have been replaced with modern customized web app development. Customized software development facilitates the creation of websites tailored to suit unique business requirements. Therefore, let's take a look at how custom web development can propel your business forward.
    04/23/2019 10:09 AM
    In the Know: Health and Wellness Website Legalities
    Health, wellness and nutrition coaches do help people achieve better health. You are champions of your clients' journeys but not directors of their health care. In these ways, you need to understand your roles professionally, but also how can you make sure you are abiding by them legally?
    11/27/2018 01:00 PM
    Thinking of Updating Your WordPress Site to Gutenberg?
    Some thoughts from our pros about WordPress 5.0 If you have a WordPress website, you've undoubtedly heard about Gutenberg, WordPress' new editor, presently available as a plug in and is expected to be the default in WordPress 5.
    10/08/2018 02:41 PM
    Future of Web Development in the Year 2019
    Trends in the web development field transform now and then. In around a decade web development industry has changed inside out. Whether it's automated chat box, single page websites, or video backgrounds, 2019 brings you a new set of updates.
    08/30/2018 02:35 PM
    7 Key Points You Should Know to Become A Mobile App Developer
    Considering the surging demand for mobile app development, the growth in the aspirants interested in mobile app development is also accelerating. With that said, the few key things to factor in when launching your career as a mobile app developer include an insightful brain and an inventive vision.
    08/27/2018 11:22 AM
    How to Find the Right Web Development Company for Your Business?
    Online Marketing has proven an excellent tool for making a massive impact for any business on the competitive market. Availing Web Designing Services offered by a Web Development Company, thus, becomes inevitable for ensuring a strong online presence.
    08/01/2018 11:08 AM
    Advantages of Buying an Existing Website
    Why create a new e-commerce business from scratch when you can buy an established website which is already successful. Such established websites can be a smart move for those who are looking out for an assured income from day one.
    08/01/2018 08:22 AM
    5 Things To Keep In Mind For An Upright Beauty Products Site
    While none of the industries have been left away from the influence of online businesses, beauty and cosmetics industry too are going with the rage. This article, in the interest of the beauty businesses, points out what they should consider for their eCommerce stores.
    07/31/2018 02:36 PM
    What Is Your Website Worth and How to Sell It for Maximum Value
    A lot of business enthusiasts and tech geeks are getting involved in forming their own e-commerce business where they can sell products or services and make a profit. Maybe out of passion, you have developed a website with an idea of starting a business from home. After a point of time, you might think to pass your baby on a new hand may be to fund a new project.
    07/30/2018 08:29 AM
    Running A Magento 2.0 Store? Witty Ways To Optimise It
    If you are a small retail business with your steps now into the online market with Magento 2.0 store, then this article is the right place for you to stop. It points out some clever, nitty-gritty measures to help small businesses pick up their online store's performance.
    07/28/2018 08:08 PM
    Laravel Application Development: 5 Hacks To Get It Done
    Laravel is the presently the trending-most and full-featured PHP web framework and this article delivers some quirk ways to use the framework for developing robust web apps. Speed up your Laravel 5.x application or whichever version you are using with these simple hacks.
    07/27/2018 09:20 AM
    5 Best CakePHP Shopping Cart Extensions
    This article is going to highlight the top shopping cart plugins offered by CakePHP. Check out the extensions, adopt these and make a change in the web development field.
    07/24/2018 08:42 AM
    Top 5 Benefits Of Hiring The Best Website Development Company
    A website widens the range of potential customers for a business by allowing it to establish its digital identity in the internet world. In order to develop an influential website, it is mandatory for you to hire the best website development company. But before hiring, let's explore the five major advantages of picking the right web development partner.
    07/24/2018 08:18 AM
    React JS: New Trend in Web Development
    This article is going to highlight the key benefits of using ReactJS. Read on and know how ReactJS is changing the face of web development.
    07/19/2018 12:18 PM
    404 Page Not Found - Something Needs Your Attention
    That beautiful website you worked so hard on and checked 12 times for glitches before launching harbors a sleeping saboteur. For reasons unknown and mysterious, what once worked will break. And you don't even have to be in the room.
    07/17/2018 03:42 PM
    Web Design Conversions With Color Psychology of Web Pages
    It's frequently said that colour of a website design can affect a person being's emotions and attitudes greatly. The moment we behold a colour, our eyes carry it to a component of the mind, which exerts countless signals to another associated parts that rule our mind. Adrenal glands play the significant role, by shooting signals and releasing hormones which finally leads to the mood influence and influence from the behavior.
    07/16/2018 10:10 PM
    Why Is Node JS Becoming Popular?
    This article is going to pinpoint the reasons behind the extreme popularity of Node.js. Read on and know how this JavaScript framework can bring a revolution in the web development field.
    07/16/2018 09:02 AM
    The 4 Rules That Developers Follow for a Great Website
    When it comes to creating a website, there is the need for a good development team who would come up with a complete package right from creating trendy designs to making it a user-friendly website. While bringing along more traffic and increased sales is the motto behind a good website, here is what the developers are known to follow that gives great results.
    07/16/2018 08:32 AM
    Want To Trigger Conversions? 5 Persuasive UX Features You Should Consider
    This article tries to help the struggling eCommerce retailers in magnifying their conversions. It presents some persuasive UX features that you need to consider for their sites. So, read on to know how you to craft a successful website with a substantial capacity of conversions.
    07/12/2018 09:59 AM
    What Functionality Should Your Careers Site Have?
    When you're looking to attract candidates, or find the perfect hire, the best place to start is with your careers website. Here's how to deliver a perfect user experience.
    07/11/2018 10:56 AM
    Competitor Price Monitoring - How It Works
    Competitor Price Monitoring is a software put into use that helps in collecting a large amount of data and optimizing marketing, merchandising and pricing in a way that is brings you the maximum amount of profits. In a market where competition is extreme and unavoidable, this tool is essential as it can significantly help in taking strategic decisions and finding out what works the best for your business. It is ethical since it only uses information that is open for the public to see.
    07/11/2018 08:56 AM
    Why Is Website Development Essential For A Business?
    In today's world of online preference, it has become very important for a brand to highlight all its positive points on the go. A website always helps a brand in establishing its pillar on the online front. But the fact should not be ignored that only having a website is not the end of the road. Proper measures are required to be adopted in spreading awareness about your website. In that case, web development is all that you need..
    07/09/2018 08:19 AM
    Rules for Coding! Follow These To Pace Up PHP Development
    PHP development is the most standard way to fast-track a business with a dynamic website or application. This article points at the essential coding practices in PHP development that one ought to follow. Setting up standards is very crucial for team development.
    07/05/2018 08:14 AM
    Choosing A CMS Seems Difficult? Get These Tips In Mind
    This article seems to answer all the queries of the businesses who want to create their own website solutions regarding the choice of a CMS. While CMS is considered as the epicenter of website's marketing strategy, this article read will help all in picking the best of CMSes for their own website.
    07/03/2018 09:25 AM
    How and Why to Make a Mobile Friendly Website
    On April 21, 2015, Google released a new algorithm update in the name of "Mobilegeddon". It punished the websites that were not mobile-friendly. Google designed and released this algorithm update to give more emphasis on mobile-friendly pages. As the consequence of it, many mobile-friendly websites received better position/ranking on the SERPs (Search Engine Result Pages) of Google. At the same time, the websites that lacked mobile-friendly web pages lose their ranks on the SERPs of Google.
    07/02/2018 09:30 AM
    Building an Online Product - Does It Have To Last From 6 to 12 Months?
    Building an online product, an app or any kind of software, can be a long and ineffective process. Is there a solution to do it right, achieve success, and save time and money?
    06/28/2018 02:35 PM
    What Is RankBrain?
    RankBrain is a part of Google's main algorithm which uses artificial intelligence to help filter through millions of search results. This algorithm determines the most useful and relevant results to search engine queries. RankBrain is the third most important ranking signal, part of Hummingbird (Google' search engine algorithm). RankBrain knows what someone is searching for and has the ability to put forward different interpretations of precise information.
    06/27/2018 11:28 AM
    Motivate Yourself to Start Your Very First Website
    Are you ready to motivate yourself to start your very first website? Stop procrastinating.
    06/25/2018 09:20 AM
    IT Company In Durgapur - A Technological Bliss for Developing India
    Durgapur has advanced a lot from a small town to a prime industrial hub of the eastern zone. The software companies in Durgapur have paved the way for a new era where the digital world can get into its actual shape and form by providing services in global IT that are serving the customers living in different parts of the world. On the other hand, this place also has all the facilities of a metropolitan but does not suffer from the unpleasantness of it. Due to the prominence of IT, sector in the country changed the economic landscape of the nation. The concept of liberalisation, globalisation, and privatisation make giant strides in the IT domain of the country. Indian IT industry is regarded as the most robust industry worldwide.
    06/14/2018 09:31 AM
    Benefits of Introducing a Smart Web Application
    Web applications are the latest way of interacting with customers. The application of a company can make or break the company's sales and profits. Customers today are extremely tech savvy, so choosing to promote online is a wise and smart choice. Creating a web application is probably the most effective way of getting in touch with your customers.
    06/07/2018 03:17 PM
    Tips to Upgrade the Android App and Game Development Process
    An Android application is a software application running on the Android stage. Since the Android platform is built for mobile devices, a typical Android application is specially intended & designed for a Smartphone or a tablet PC running on the Android OS. In spite of the fact that an Android application can be made accessible by designers through their websites, most Android applications are uploaded and distributed on the Android Market, an online store particularly devoted to these applications. The Android Market highlights both free and evaluated (priced) applications.
    06/07/2018 09:36 AM
    Simple WooCommerce Tips to Make Your Store Better
    It's very simple to set up a professional looking website to sell your products without spending more money. With these simple WooCommerce tips you don't even need to hire a website development company to make your store awesome. In this post you will get quick and simple tips for WooCommerce that you can use today.
    06/07/2018 09:31 AM
    Why Is It Finally Time to Upgrade to Magento 2?
    In 2018, the rush to migrate to Magento 2 has increased and while most business owners are getting the upgrade, there are many other who have a lot of doubts and questions about it. As migration of the very platform that powers the eCommerce business is a huge step, one needs to be completely sure whether it is right time to migrate or not. Here is why, it is completely safe to migrate to Magento 2 now.
    06/07/2018 08:11 AM
    When Your Website Needs A Rebuild - Find Out Here
    Most of the times, people judge the quality of a company just by its website, or the experience they get from it. After all, it is representing a brand over the web and hence is within reach of customers all over the world. This article explains to you some telltale signs that indicate when a website is in need of some major renovation.
    06/06/2018 08:26 AM
    New Cloud Commerce Features of Magento That You Should Not Miss Out On
    After Magento released its Commerce Cloud version two years back, there has been a substantial number of subscriptions with more than 150 stores becoming live. The cloud offering has already been a powerhouse for eCommerce players supporting advanced features. This article describes the latest features brought by Magento community in its Commerce Cloud.
    06/06/2018 08:26 AM
    Top and Best-Selling Magento Extensions of 2018
    This article is going to pinpoint the most popular Magento plugins of 2018. Read on to know what made these plugins top and best-selling in the market. In today's tech-savvy era, new technologies are emerging and Magento is undoubtedly one of the top entries in that list.


  • home | site map
    © 2006