GeekLimit

Getting started on a Web Application (with PHP and MySQL)

Posted by The Technocrat | Filed under Geeky

We’ve all got great ideas for the next big thing. Before you invest in a personalized license plate that says ‘Web20′ or ‘AJAX 7334′, you’ll need to develop a simple way of getting information in and out of a web server.

There are many ways to do this, but I like PHP and MySQL, because they are easy and cheap. (PHP and MySQL are free, but you can find hosting on a PHP-MySQL server for as low as $8/month.)

So let’s say we want to build a web app…

Step 1: lay out what info you’re going to need

I think of it like this: if you were to do this process on paper, what would you need? First of all, you’ll need User information. On a ‘User Information’ sheet, I might have the following info:

User Info

• First Name
• Last name
• Employee ID number
• Email Address

OK, so that’s pretty easy to figure out. It’s also easy to take this info and plan out how you’re going to store the info in a MySQL table. (a MySQL table is like a spreadsheet in Excel (OpenOffice Calc!)) So I will take my data requirements and make a table called user_info, as follows. I will also define what will be in each data type. I also need to add some more fields…

user_info

• *user_id
• passwd
• fname
• lname
• email

(* this data will be required to be unique – I can’t have 2 users with the same name!)

This is going to lay out a table in MySQL that looks like this when it is full:

user_id	passwd	fname	lname	email
php_ddy	phpisneat	John	Doe	jdoe@isp.com
supr_lady	mysqlissweet	Jane	Doe	janed@isp.com

each row is called a record, and contains all of that person’s info. The user info table is pretty standard, but what other tables you will need depend on your application. Let’s say we’re making a simple chat room web page. Another table we will need will be for people’s comments, like this:

user_id	time_posted	date_posted	text
php_ddy	13:10:09	03-15-2006	hi jane!
supr_lady	13:10:13	03-15-2006	hi john, what’s up?
php_ddy	13:10:17	03-15-2006	nothing much, just saying hi.

Now the important thing to notice here is my re-use of user_id. By re-using this data, I can now link the two tables together.

Step2: Planning how to use the information

This is where things get interesting. By re-using the user_id values, I can link the two tables. If I didn’t use user_id in the second table, I would have only been able to display the following from the second table, using PHP:

hi jane!
posted by php_ddy 1:10:09 PM

hi john, what’s up?
posted by supr_lady 1:10:13 PM

nothing much, just saying hi.
posted by php_ddy 1:10:17 PM

This is okay, but since I can link the tables together, I can have PHP temporarily combine them, display the info, and then let them go back to normal. I get something like this:

hi jane!
posted by John Doe 1:10:09 PM

hi john, what’s up?
posted by Jane Doe 1:10:13 PM

nothing much, just saying hi.
posted by John Doe 1:10:17 PM

Now this is what web apps are all about. I’ve only defined two very, very basic tables of data for MySQL, and by using PHP to access the data and work with it, I can make a word of difference!

So far, I’ve explained everything you need to know about making a web application. You see, web applications aren’t about the code you know, etc. You can work on that. Web applications are made great by what you do with the data.

Some of the best applications on the web are so simple, but extremely powerful because of how they cross-index information like this:

• Combine historical traffic records with a list of streets from some driving directions. You now can semi-accurately predict traffic for your route.

• Combine a history of who has bought what at a grocery store with current specials. You can now give a sheet full of coupons that are relevant to a user’s particular tastes.

You can see how useful these are, but are so simple to create. The hardest part is thinking this stuff up, and planning how you will store and cross-reference the data. If you learn anything from this posting, it is that a successful web app has nearly nothing to do with how many years you’ve been programming, its success depends 99.9% on your own imagination. Check out Zoomr, for example. It’s a photo sharing site that was built by a 17-year-old. Since he re-thought what it means to have a photo sharing site online, he’s now threatening Yahoo’s Flickr.

Step 3: the minor details

Until now, I’ve been concentrating on the most important part of a web application. This part should go back and forth in your head, about what data you will store, what info you will be able to display by linking tables of information together, etc. Do all of this planning several times over until you are sure it is the way you want. Once you start programming PHP that is dependent on tables being named a certain way, etc., you’re pretty much stuck with it, or will be forced to do a lot of re-do work.

This isn’t a PHP tutorial, but I chose PHP because it is the easiest data-retrieval language to learn. In fact, if you know HTML, you can easily learn PHP n a few hours. The same goes for MySQL, although it may take you a try or two to learn how to pull the data you want out of the tables in a format you can use (arrays, etc).

I learned PHP and MySQL in these tutorials, and am able to make just about any web app I please, after proper forethought into what I want to do.

I wouldn’t recommend making your own php/MySQL server starting out, but by all means, have a go if you want. My advice would be to check if your hosting provider (if you have a web site) supports PHP and MySQL. They probably do, since php and MySQL are immensely popular, and aren’t going anywhere.

Well, hopefully you aren’t disappointed that this isn’t a PHP or MySQL tutorial, but as I mentioned above, immediately jumping onto PHP and MySQL is a terrible way to design a web application.

To recap, design the app on paper, in a spreadsheet, or whatever. Plan out what info you will be storing, and how you are going to mix your data tables to provide a service. Plan everything out ahead of time, and you won’t have to go back and re-do anything later (like when you forgot to gather your 10,000 user’s zip codes or something)

Comments (1) | March 15th, 2006

One Response to “Getting started on a Web Application (with PHP and MySQL)”

1. kohls coupons Says:
   April 21, 2010 at 6:56 am

   Hey! This is really cool, exactly what I was looking for. The designs here are so fabulous!. Thanks for uploading this stuff.