MySQL Database Handling in PHP

MySQL Database Handling in PHP

by: John L

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 ODBCcompliant, 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!

[email protected]

This article was posted on November 07, 2004

by John L

Flat Tires, Slow Leaks, and Online Marketing

Flat Tires, Slow Leaks, and Online Marketing

by: Richard Kraneis

Does your online marketing campaign have a flat tire? Or is it more like a slow leak?

Today my 16 year old son Fritz called on the cell phone from high school. Mom took the call. ขCould Dad fix my flat tire during the day?ข Of course I would fix it.

And then it hit me. Knowing you have a flat tire is a lot better than not knowing. Unfortunately, when your online marketing campaign has a flat tire or a slow leak, it may take a while before you realize it or fix it.

My ebook website has had a few flat tires and a few slow leaks. Bad ebook cover, slow website loads, advertising to people who would never buy, and other problems. But I think I’ve repaired the flats and the slow leaks. I hope.

Here’s my first online flat tire. When I first published my ebook on the Internet in November of 2003, my ebook cover was running at a loss. As I remember, I was making a dollar for every 3 dollars I spent on advertising. What was the flat tire? Please take a look at my original ebook cover. It’s below. It was horrible.

http://www.theworldsshortestexcelbook.com/HorribleBookCover.jpg

Actually, my website didn’t have a flat tire; it was more like a car wreck. But if you looked at my ebook cover on the link shown above, I definitely had an online flat tire. (There’s a reason why I earned only passing grades in art classes). I went cheap on the ebook cover and built my own. I took a fishing photo of myself and put it on a book cover. Not only did I have a flat tire, I had slashed all my tires with a knife, to save pennies.

Then I found Vaughan Davidson at http://killercovers.com/covers and he did a professional ebook cover for me. Visit my new ebook cover shown below.

http://www.theworldsshortestexcelbook.com/BookC.gif

I think I changed the ebook cover in midDecember 2003, turned off my Google advertising, and took a holiday break. It was nice being away from email for a change. When I checked my email on January 2, 2004, I had sold 3 ebooks.

Using Excel to measure my conversion rates, my conversion rate during late 2003 was about .003. I was selling 3 books for every 1000 visitors. That was selling at a loss. After my new ebook cover from http://killercovers.com/covers and a week of web page changes, my ebook conversion rate rocketed to .009. A 300% increase. A profit.

So keep a good lookout for the flat tires and slow leaks of online marketing. Using Excel to track your online marketing successes (or not so great successes) would be a great idea.

Copyright 2004 Richard Kraneis

About The Author

Richard Kraneis is a computer training consultant, ebook author, and an aspiring online marketer. Online marketing on the Internet reminds him of his favorite hobby, fishing. His website www.TheWorldsShortestExcelBook.com can help you improve your Excel skills so you can measure your online marketing success. If you’d like to locate some of the best ebooks in the world, visit his 2nd website, www.WorldClassEbooks.com. You can reach Richard at [email protected] if you’d like to share your online marketing ขflat tireข story with him.

This article was posted on September 24, 2004

by Richard Kraneis

Three Ways Flat Rate Conferencing Can Help Grow Yo

Three Ways Flat Rate Conferencing Can Help Grow Your Business

by: Tom Parker

There is no question that teleconferencing is revolutionizing the way that people do business, both on the Internet and offline.

Internet marketers, coaches, consultants, counsellors, major corporations, even Sunday School teachers are using teleconferencing to stay in contact with their staff, clients, potential customers, and classes without having to set up a meeting place.

Teleconferencing has also added a new dimension to the way people can teach and deliver information over the Internet. Seminars and classes can now be held for hundreds of people at the same time without having to leave their homes. With a preset date and time, people can call a number and either learn more information about a topic or participate in a forum of discussion.

Charges for admission to these types of calls can be anywhere from $20 to $30 up to $100กs of dollars for typically one hour.

Three Ways To Boost Your Income And Grow Your Business

Teleconferences can lead to an amazing growth in your income, and credibility as an expert on a given situation. You can also ADD more to your conferencing by utilizing flat rate conferencing.

Flat rate teleconferencing is simply setting up an account for unlimited monthly conference calls for a set rate. By doing so, marketers are seeing exponential growth in both their reach of new customers and as a byproduct, their income.

In watching this use of flat rate telenconferencing among individual marketers, corporate executives, coaches, and consultants I have singled out three ways that flat rate conferencing can lead to added growth and income.

1. Talk As Much As You Want.

Typically the average conference call would last for one hour. But, there are times when the conversation, and/or, meeting would take on a whole new life and 60 minutes just isn’t enough time. But, either the call is cut off or the host has to pay extra charges.

With flat rate conferencing you can host your call for as long as you want with no worries of extra charges or disonnections. People are more at ease and can have added time to grasp what is being taught or talked about.

2. No reservations needed.

With regular conference calls you need to reserve your bridge in advance. With flat rate calling, there is no need. You can set up calls wihthin a minutes notice if need be.

This works well with telecommuters and for staff meetings.

3. You can set a conferencing budget.

If you can budget something every month you can use it to grow your income. With flat rate teleconferencing you are able to know every month how much youกll be spending on it and know how much youกll be earning with it.

If you’re using teleconferencing to hold paid seminars or courses you can hold as many as you want each month for the same price.

People Are Learning More And Enjoying It

Teleconferencing has not only changed the way that people do business online, and added a new income avenue, but it has also helped alot of people get started with their own business, changed the way they learn, has helped coaches and counselors give more attention to their clients, and Sunday School teachers lead a lesson to shut ins and people who were on vacation.

And people are enjoying the new option of attending a seminar or class without leaving thier home.

Flat rate conferencing is going to add a new way for people to experience this more often and give businesses, and teachers, a new tool to reach more people.

About The Author

Tom Parker has put together a website to help people who want to host a teleconference or class, and has made it affordable for everyone! http://www.affordableconferencing.com is your place for reliable, affordable, reservationless teleconference lines for easy hosting. Go and check out the rates and get your conference started today! http://www.affordableconferencing.com/flat_rate_conferencing.htm

[email protected]

This article was posted on February 04

by Tom Parker

A Peek Into the Near Future of Electronics Technol

A Peek Into the Near Future of Electronics Technology

by: Terry Mitchell

How long do you think DVDs have around? 20 years? 10 years? Actually, they have only been around for about eight years, but it seems like they have been around much longer. Many of us can hardly remember life before DVDs. That can be attributed to how rapidly we can become acclimated to some innovations in electronics technology. I believe there are other electronics technologies, either just getting ready to take off, not widely available yet, or just around the corner, that are going to become adopted just as quickly in the near future.

Once such item is Voice over Internet Protocol, also known as VoIP. This innovation renders the whole concept of long distance virtually obsolete. It bypasses the traditional telephone company infrastructure and delivers phone service over a broadband internet connection to a regular phone. Similar to cell phones, this service is purchased based on a fixed and/or unlimited number of minutes. However, geographical divisions are generally made by country or continent, rather than by local calling areas or area codes. For example, a typical VoIP contract in the U.S. would stipulate unlimited calling to North America and 300 monthly minutes for calls to everywhere else. Unlike cell phone service, you are not charged for incoming calls. With VoIP service, area codes are not much of an issue, although you still must have one. However, some providers offer plans in which you can select any area code in your country or continent! The area code you choose mainly comes into play for those with traditional phone service who make calls to you. If you pick a California area code, for example, someone calling you from a traditional phone line would be billed as if they called California, even if they lived next door to you in New York.

One of the major advantages of VoIP is that it is less expensive than traditional phone service. Since it bypasses most of the phone companiesก infrastructure, it also bypasses many of the taxes associated with it. So far, Congress has maintained a handsoff approach when it comes to taxing VoIP services. Most of the major phone companies are either now offering VoIP or plan to start by mid2005. However, there are some smaller companies that are offering it at a much lower cost. Vonage (www.vonage.com) is a small company that was one of the pioneers of VoIP. Lingo (www.lingo.com) and Packet8 (www.packet8.com) are two other small companies offering VoIP at a cutrate price.

Another such technology is Broadband over Power Line, or BPL. Already in wide use in many other countries and currently being tested in the U.S., BPL is the delivery of broadband internet service over traditional power lines. A computer is connected to a special modem which is simply plugged into an electrical outlet. This kind of service could prove useful for those who cannot get traditional broadband services like cable modem or Digital Subscriber Line (DSL), as almost everyone has access to electricity now. Once refined, BPL could eventually prove to be cheaper and faster than these more established services and attract away some of their customers. By the way, be careful when you’re discussing BPL and make sure people don’t think you’re saying, ขVPL.ข Otherwise, you might encounter quite a bit of snickering!

While we’re on the subject of broadband internet services, several technologies just around the corner are going to make them much faster than they are today. The typical download speeds for broadband ranges from 1.5 to 10 megabits per second (mbps) today. Within the next year, speeds of 1520 mbps will be available to the average consumer. Then, shortly thereafter, speeds of up to 25, 50, 75, and even 100 mbps will be available in some places. In the notsodistant future, speeds of 25100 mbps is will be quite common. กFast TCPก, which is currently being tested, has the potential to turbocharge all forms of currently available broadband internet connections without requiring any infrastructure upgrades. It will better utilize the way in which data is broken down and put back together within traditional internet protocols.

All the major phone companies are currently in the process of replacing their copper wires with high capacity fiber optic lines. One example is Verizonกs FibertothePremises (FTTP) initiative. Fiber optic lines will greatly increase the amount of bandwidth that can be delivered. Fiber optics will allow phone companies to deliver video, either via a cable TVtype platform or a TV over Internet Protocol (TVIP) platform (see my October 7 column), and faster DSL speeds. At the same time, the phone companies are working with Texas Instruments to develop a new, more technically efficient form of DSL, called UniDSL. Eventually, the current internet as we know it will be scrapped and completely replaced with a whole new internet called กInternet 2.ก This new internet is expected to provide speeds of up to 6000 times faster than current broadband connections!

Another technology item that youกve probably heard a lot about recently is digital television. Digital TV uses a different wavelength than traditional analog TV and has a much wider bandwidth. It also has a picture that never gets กsnowyก or กfuzzy.ก If the signal is not strong enough, you get no picture at all, rather than the fuzzy picture you sometimes get with analog. In order to receive digital signals over the airwaves, you must have a digital TV set (one with a digital tuner inside) or an analog TV with a settop converter. Cable and satellite TV also use digital formats, but unlike broadcaster signals, their nonHigh Definition digital signals are automatically converted to a format an analog TV can process, so a digital TV or converter is not needed. High Definition Television formats, even on cable to satellite, require a digital TV or a converter (more on High Definition later).

All broadcasters are now doing some broadcasts on their digital channels in addition to their normal broadcasts on their analog channels, but they were originally supposed to completely convert over from analog signals to digital signals by the end of 2006. However, there is an exception that allows them to wait until 85% of the television sets in their market are digital. This could take 10 years or more to happen. Congress and the FCC are now looking at imposing a hard deadline on all broadcasters to convert to digital signals by 2009. Once they all convert to digital signals, their analog channels will taken back by the FCC and used for other purposes like emergency signals.

High Definition Television (HDTV) is one possible use of digital signals. HDTV uses the entire digital bandwidth and is the crystal clear format youกve probably seen on TVs in electronics stores. It has no visible lines on the screen. Someone once described it as being like กwatching a movie in the theater.ก Keep in mind that all HDTV is digital, but not all digital is HDTV. Along those same lines, not all digital TVs are HDTVs. Since digital TVs are very expensive and those with HDTV capability are even more expensive, consumers really need to keep this in mind.

The other possible use of digital signals is channel compression, often referred to as กmulticasting.ก NonHDTV programming does not utilize the entire width of a digital signal. Therefore, it is possible to compress two or more channels of programming into one digital signal. Satellite and cable operators do this all the time with their nonHDTV digital channels, but this process is transparent so many people don’t realize it. Many broadcasters plan to use their digital signals this way during times when they are not being used for HDTV programming. For example, some plan to air all news and all weather channels in addition to their regular channels of programming.

TV recording and playback technology is changing as well. DVD recorders, which debuted about four years ago, have now become affordable to the average family. A couple of years ago, they were priced above $1000, but now you can get them for around $250, in many cases. The main sticking point now with DVD recorders is that not all of them will record/play all three of the competing formats: DVDRAM, DVDRW, DVD+RW. They will have difficulty gaining wide acceptance from the public until one format is settled on or all recorders can record and play all three formats.

One the other hand, digital video recorders (DVRs) and personal video recorders (PVRs), just two names for something that is really the same thing, seem to be gaining quickly in popularity. DVRs/PVRs utilize a hard drive to record programs, without the need for discs or tapes. DVRs/PVRs with larger hard drives are becoming available and less expensive all the time. These devices can record one show while you are watching another. They can record more than one show at a time. They allow you to watch the part of a show that has already been recorded while the remainder of that show is still being recorded. They allow for easy scanning, searching, and skipping through recorded programs and even allow you to skip commercials with one touch of a button. They allow you to pause live programs while you answer the door or go to the restroom and then pick up where you left off when you get back. With these devices, recording can be automatic, i.e., you can program them to automatically record every episode of your favorite shows, no matter when they air. You can also have them automatically find and record programs that match your interests. In addition, video can be automatically downloaded to the device via a phone connection. TiVo, the leading brand in the industry, has announced that it will be teaming up with Netflix next year to allow downloading of movies on demand via a broadband internet connection (see my October 7 column for more details).

DVRs/PVRs are becoming so popular that cable and satellite TV providers have begun including them as addons to their receivers, either at no extra cost or for a small additional monthly fee. About the only shortcoming of DVRs/PVRs is the fact that they can’t play prerecorded DVDs or tapes, so you would still need your DVD player or VCR if you rent or purchase movies. However, hybrid devices which combine DVRs/PVRs with a DVD player/recorder and/or VCR are now hitting the market. Those devices would not only get rid of that problem but would also give you the option of permanently transferring a recorded show/movie from a hard drive to a recordable DVD.

Flat screen and flat panel TV technology is also starting to boom. Many people are confused about the difference between flat screen TVs and flat panel TVs. Flat screen TVs use the old cathode ray tube (CRT) technology for their picture tubes and are therefore bulky like traditional TV sets. However, they are different from traditional TV sets in that they have a flat screen. They deliver a picture that doesn’t have as much glare as traditional, more round screens. Also, the picture will look the same to everyone in the room, no matter where they are sitting. The picture on a traditional screen looks distorted when viewing it from an angle.

Flat panel TVs, on the other hand, utilize either liquid crystal display (LCD) or plasma technology instead of the old CRT technology and are generally just a few inches thick. Many of them can be hung on a wall. In fact, flat panel TVs that are flatter than a credit card will be coming soon! Whatกs the difference between LCD and plasma? LCD is generally used for flat panel TVs with a display of less than 30 inches and usually has a brighter picture and better contrast than plasma. LCD is used for flat panel computer monitors as well. Plasma is generally used for flat panel TVs with a display of more than 30 inches and has a better color range than LCD. Plasma is becoming more common as TVs get bigger and flatter.

Although Iกm not so sure about this one, I will include กentertainment PCsก because of their tremendous potential to revolutionize home entertainment. The concept of กentertainment PCsก is being hailed right now by both Microsoft and Intel. In fact, Microsoft has developed a special operating system for them. They could be used as the hub for all home entertainment and could enhance a familyกs experience of television, radio/music, and internet and actually help to combine all of these into one. They could be used to download content from the internet and play it on a TV. They could provide such sophisticated TV recording interfaces that VCRs, DVDs, and DVRs/PVRs could all eventually become obsolete. In addition, they could be a better source for photograph and home video editing and processing than regular PCs. With that being said, Iกm not so sure that people will be willing to accept PCs as a source of home entertainment. Bill Gates begs to differ and is willing to put his money where his mouth is.

Obviously, not all of the cutting edge electronics technologies mentioned above will meet with great success. Some of them might actually go the way of Betamax, digital audio tape (DAT), and DIVX. However, many of them are sure to catch fire and become such an intricate part of our everyday lives that weกll wonder how we ever got along without them. Which ones will they be? Only time will tell.

About The Author

Terry Mitchell is a software engineer, freelance writer, and trivia buff from Hopewell, VA. He also serves as a political columnist for American Daily and operates his own website http://www.commenterry.com on which he posts commentaries on various subjects such as politics, technology, religion, health and wellbeing, personal finance, and sports. His commentaries offer a unique point of view that is not often found in mainstream media.

[email protected]

This article was posted on February 04, 2005

by Terry Mitchell