| |
| |
|
Go Back | About | Contact | Web Design News | |
|
|
|
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:
Web-Development - Google News |
|
|
|
RELATED ARTICLES
The Number One 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. Search Engines and Customers Want Focused Web Site Content How do you decide on the content, products and or services you will promote on your Web site.Or should I go back one step further and ask why did you decide to create a website in the first place. Your Content for Nothing and Your Clicks for Free The first thing my business consultant (read brother-in-law) asked me, when I told him that I wanted to build a website and put AdSense on it was, "Do you want to build a site to help people or to make money?" After thinking about it for a while I couldn't imagine why I shouldn't do both. Why not a website that helps people and generates money for me? My consultant looked at me like I had taken one too many falls off my bike, which he knew I road primarily to save on the cost of gas, but he proceeded forward. Thinking Of Taking Money Online? Whats Best For Merchants? PayPal, WorldPay or Something Else? It's now easier than ever to take payments on the web. As people selling their wares take pot shots at eCommerce providers in an ever-increasing battle to sabotage their "competitors'" success it gets harder and harder to find an honest unbiased opinion. 5 Tips For A Better Website Having an eye catching website is great, but are you optimizing your website to allow the best performance possible? I receive several emails a day from clients asking how they can get more hits to their websites, what can be done to increase sales, and how to prepare their sites for search engines. There are very simple things that can be done to help your website perform its best. You Need a Website! No matter the size of your business, having a web site is a low cost way to market your services and products twenty-four hours a day. Web sites are a constant advertisement for your business and will help you reach your targeted customers effectively. A Web Site for Your Small Business: How to Set Up a Website Your web site will be your window to the world. Even if you are not selling any product online still you need your customers to access you 24 hours a day 365 days in a year and you can pass important information to them by hosting frequently asked pages, product descriptions, product improvements etc. Frequent Business Question: How Do You Start a Web Business? Today it seems everyone is jumping on the entrepreneurbandwagon, and who could blame them? Setting your ownschedule, working from the comfort of your own home, andnever having to deal with a horrible commute or officepolitics again, this option is quite popular among all ages.With the advent of the technology age, it seems moreindividuals are looking to the internet to provide themwith goods or services. Trust Us! Sending Credible Messages A company must earn and keep trust or sales don't happen. Imagine that last Sunday, while you were reading the paper, you saw an ad for a great deal on a digital camera. Web Content: How Much Should I Pay? When web content gets discussed on webmaster bulletin boards, the most common question is, "how much should I pay?" That question is both perfectly logical, and perfectly stupid:Logical, because the biggest expense of any website, with the possible exceptions of advertising and promotion, is the content. You only have to get web development and design once, but content needs to be added regularly for your site to be successful. 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. Content Management - The New Standard in Web Design The evolution of web development parallels the model of technology spending by companies today. The style and method has transformed along with the business spending trend. Free Webmaster Tools - 7 Things Every Webmaster Needs in Their Toolbox Webmaster tools are vital to becoming both efficient and effective in your online efforts. The following are my picks for the top types of webmaster tools:Tool #1 Link FindersEvery webmaster needs this free tool. Linking Out is Good Many websites I come across don't have a single link to another website. Ask the webmaster why not, and the answer you get is simple enough: "If I link to other websites people might leave my site. Why a CSS Website Layout Will Make You Money Although CSS layouts have been around for years, they haven't become so commonplace until recently. This was basically due to limited browser support (especially from Netscape 4) - nowadays though, CSS 2. Planning A Usable Website: A Three-step Guide A website is like an information flow, with you as the provider and your site visitors as the receivers of the information. If you don't plan your website with this in mind right from the start, you could find yourself with a brand new website that solves all your immediate needs. Website Strategy! A website lets you put your products in front of a world-wide audience. It can help you generate new revenue, cut costs and build better relationships with both customers and suppliers. Web Success Secret - Keep Your Visitors Interested Visitors to your site come looking for information or solutions to their problems. They will remain there if they find what they are looking for. Profitable Websites for Exclusive Industries In the region where my company is located, South Bend, Indiana, the demand for web design and associated services could be described as: leery. It's not that the locale is horribly lacking evolvement technologically; rather that so many proprietors in this area are very uncertain as to what the internet can do and how they can go about forming profitable web sites for their exclusive industries. Do Authors Need a Website? "I am a writer, not a business-person. I don't want to have anything at all to do with business," you might think.
|
ChicagoAnimation's Acquisitions: Market Polls - 1pennyStock.com
Sponsors: Paving | VTL's | Religious Statues
