| |
| |
|
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 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: 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!
MORE RESOURCES:
Goal-Setting - Google News |
|
|
|
RELATED ARTICLES
Effectively Using Robots Meta Tags The "robots" meta tag, when used properly, will tell the search engine spiders whether or not to index and follow a particular page. For the purposes of this article, we will be using the "( )" symbols to represent the "" in html coding. But My Business Doesnt Need A Website! Revenues are often lost because many business owners don't see the value in having a website. They claim their products can't be sold online and technology is overwhelming. Newsletter Management Using PHP w/o mySQL for Beginners Let's begin by setting some limits. If you're like me, you like to keep it simple. How To Start Your Own Website I'm one of those people that always wanted a website, but had no clue as to using the internet. I could check my email and that was about it. Web Coach Tip: What You Should Know About DIY Web Sites Recently, a friend asked "What's the deal with those DIY companies that advertise "How to get a website with everything your business needs for under $20 bucks a month?". "How can they do that?" she said. Growing Your Meetings In CyberSpace As increasing numbers of people search for information on the Internet, it becomes more imperative to have a compelling Website to promote and support your meetings.Here is my list of "The Seven Most Important Things You can do Online":1. Five Reasons You Have to Stop Your Web Site That's right. Your method, behavior and strategy you are now using to manage your website may be justifiable reasons to stop it as soon as possible. Freelance Programming is Easy to Manage There are several reverse bid freelance sites out there. Beyond the big ones, smaller ones are popping up each day. I Did It: Six Months to a Successful Website For years now I've been looking to start my own business, but my highest priority has always been to spend less time working, and more time with my family, while still giving them everything they need (and most of what they want). It's a common dream, right? No boss to answer to, less stress, more vacation, more quality family time. Server Side Includes (SSI) with ASP Server Side Includes (SSI) with ASP People often have identical sections of code spread throughout many ASP pages, typically code at the top of the page (usually referred to as the header) and the lower part of the page (the footer). Similarly, you may have code that performs a certain set of instructions that you are repeating across multiple pages. Your Biggest Mistake When You Create a Website? --------------------------------Why Should You Create a website?--------------------------------A. What do you want when you create a website?B. Building Websites with Directory Generator Directory Generator, a product from Armand Morin and Marc Quarles, builds directory-style websites for you in a matter of minutes. It is simple to use, it's reliable, it does what it claims to do. Why Your Brick-and-Mortar Biz Needs A Website The internet has taken over our lives with a vengeance, and has changed the way many of us do business. More and more consumers are now doing their shopping online and, unfortunately, some business owners have felt the pinch. Tell me what your website does! You know exactly what your organisation does and what your website offers its users. This information has probably become second nature to you, but first-time visitors to your site won't know this. Up The Sandbox! Go to any internet marketing forum you want these days andone of the topics is sure to be whether or not there is a"sandbox" at Google where new sites are forced to come andplay for 3-6 months before joining the ranks of ranked andsearched results. On the surface it would appear that thisis so. How To Keep Your Website Sticky Developing a website with content that entices visitors to return over and over again is something that takes time and effort to put together. This checklist can give you a great start on creating a popular website that entices readers to hang around on your site. Introductory PHP For Non-Programmers When you started reading this sentence you assumed lo acabaría en inglés. However, I didn't finish in English. The Number 1 Reason Most Websites Fail Failure, just like success, is measured differently by each and every one of us. What one man treats as success another may view as failure. Ten Ways NOT to Set Up a Website-based Service Business 1. As the first step, "Hire a web designer. Link Trades That Waste Your Time Never has competition been so difficult in the Internet world. Google and Yahoo keep indexing more and more pages, into the millions upon millions, and yet more and more people are creating super quickie links pages that are supposed to get other webmasters to want to trade links with them.
|
| home | site map | ArticleGecko | A ClashX Production 2006 |
| © 2006 |
