How do I SQL? (Or, hey, I want to make a database!)

MeepZero

I've been playing a lot of EVE Online recently, and after futzing around with a couple spreadsheets out on google docs I've decided a better use of my time would be to teach myself some kind of SQL so I can both improve my productivity in game and teach myself a valuable technical skill.

Only problem is, where do I start?

My goal in this little project is to create something that I can host out online somewhere and then let other people take a look at information stored in it. A brief search led me to think that php MySQL is the current popular web based database platform. Though I wasn't really able to find a "So you want to make a database?" kind of page.

Do you guys have any good recourses to start? What kind of database engine should I use? I have some experience with MS Access, so I feel like I have a fair understanding of how databases are structured. Is it all text based? Are there any handy front end programs to set this stuff up through?

I have more questions, but lets just start with this here.

GrimReaper

Google is the big help to be honest, w3schools is a great resource and also.. if you're going to be coding sites that use a SQL back end make sure to read up on sanitising input etc.....



If you're wanting gui tools etc then to be honest I personally find the best ones to be made by MS, the front ends to SQL server are fantastic.

mspencer

In general, understanding databases requires understanding three things:

1) how do you organize data in a database so you avoid update anomalies (adds, changes, and deletions don't cause problems) and so you can retrieve information in a way that makes sense
2) how do you communicate with the database server, using a language like SQL
3) what tricks and techniques can you use to transform data and extract meaningful knowledge from the data

Very quick advice for each:

1) the design of a database needs to match the structure of the real-world data it represents. Start here and then read about 1NF, 2NF, 3NF.

2) SQL ("sequel") is relatively easy to learn, once you understand the concepts it's trying to manipulate. Knowing how to tell a database server to add an index or a primary key doesn't help you much if you don't know what those are. I wouldn't worry too much about this -- learn the fundamentals and the language will be easy.

3) I don't know how to teach this, as I learned over a span of several years through trial and error.

Nightslyr

To start, you should see if you can find some free web hosting. Virtually all hosts come with the PHP/MySQL combo, and a free host should be fine to start with. Even better, you'll be able to create your tables through phpMyAdmin, a PHP/web interface to the db itself.

Of course, if you have a *nix box, experimenting will be even easier.

Like mspenser said, normalization is the key. Upfront database design is probably the most critical aspect of what you want to do, as it's pretty difficult to modify a db design after the tables have been made and data inserted. So, for a quick intro/crash course, look at: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

PHP is a natural fit with MySQL and other databases. It's easy to query a db through PHP and retrieve results. You'll become very familiar with code that looks like:

$result = mysql_query("SELECT * FROM table_name");

while($row = mysql_fetch_assoc($result))
{
   echo "{$row['column1']} - {$row['column2']}<br />";
}

DrunkMc

If you're going to drop this on a webserver, phpadmin works well enough as a GUI interface to it. And when you do operations through the GUI it'll give you the SQL that would do it as well. It's great for figuring out syntax, and then putting it in your own code.

I personally make my websites in GWT and use JDBC on the server side to talk to my DB's.

MeepZero

Ok, sounds like the online hosted option would work. Do they have free stuff that I can mess with and use? Or is it all stuff I would have to throw money down on?

Nightslyr

MeepZero wrote: »

Ok, sounds like the online hosted option would work. Do they have free stuff that I can mess with and use? Or is it all stuff I would have to throw money down on?

It'll all be included in the price of whatever hosting you get. This is why I suggested free hosting - you get enough functionality (PHP + 1 MySQL database) to at least start learning the ropes for free. You may need to shell out some $$ for a domain name, depending on where you go.

EDIT: I use Awardspace for my free shit. They're bare bones, and their domain names expensive ($10-$15), but if you can get a name for cheaper (GoDaddy?) and point them to the free hosting, you should be go to go.

If you're more comfortable with a cheap paid solution, I use ICDSoft for my real hosting. The price is ~$6.50 a month, but you can only pay in yearly intervals. Uptime seems to be 100%, their shit works immediately after signing up with no hoops to jump through, and they have a great admin panel. They also have great technical support. My only complaint is that they always seem to be one PHP version behind. The current version is 5.3.something, and they're still on 5.2.6.

solsovly

GrimReaper wrote: »

Google is the big help to be honest, w3schools is a great resource and also.. if you're going to be coding sites that use a SQL back end make sure to read up on sanitising input etc.....



If you're wanting gui tools etc then to be honest I personally find the best ones to be made by MS, the front ends to SQL server are fantastic.

That is an awesome comic.

MS Access is really not a terrible tool to learn concepts. Not a long term thing though.

edit: oops didn't see you already started.

MySQL is probably the best. Oracle also has a free version (locally though so it won't meet your hosted needs).

LoneIgadzra

If you're on windows, I recommend XAMPP for a test server environment that you don't have to put much effort into setting up (the "lite" version should include everything you need, if you want to keep it simple).

Do some MySQL tutorials to get a feel for the basics, then use phpMyAdmin which is included with XAMPP (or the official mysql gui tools if you want to jump through a bunch of hoops for one download) to set up a table to contain the data you need to store, and then export the data from your spreadsheet in some kind of text format and load it into mysql, presumably using something similar to the "load data local infile ..." function (which expects tab-separated data by default).

(Empty strings, null values, and date formats are the main things that are likely to cause trouble)

Now you can move on to PHP, which is honestly pretty simple. However do not deploy any code to a public web site without understanding and preventing sql injection, as well as cross-site scripting. Doing this isn't terribly difficult, but don't worry about it until after you know how to work MySQL.

yellowdart2

MeepZero wrote: »

I've been playing a lot of EVE Online recently, and after futzing around with a couple spreadsheets out on google docs I've decided a better use of my time would be to teach myself some kind of SQL so I can both improve my productivity in game and teach myself a valuable technical skill.

Only problem is, where do I start?

My goal in this little project is to create something that I can host out online somewhere and then let other people take a look at information stored in it. A brief search led me to think that php MySQL is the current popular web based database platform. Though I wasn't really able to find a "So you want to make a database?" kind of page.

Do you guys have any good recourses to start? What kind of database engine should I use? I have some experience with MS Access, so I feel like I have a fair understanding of how databases are structured. Is it all text based? Are there any handy front end programs to set this stuff up through?

I have more questions, but lets just start with this here.

A great online resource can be found here: http://www.tuxradar.com/practicalphp

It's a good primer for learning PHP and how that interfaces with MySQL, but it goes over other options as well. Plus, it covers a lot of the security side of things that LoneIgadzra warns about.

Janin

In my opinion, SQLite is the easiest open-source SQL database to get started with. SQLite databases are just files, so you can easily copy them around via SCP or HTTP and don't have to worry about maintaining a separate server installation. Every major language includes SQLite drivers, so you can either write your own basic frontend or just install an existing one. If your goal is just to publish information, let people download the database directly -- anybody who can deal with an EVE spreadsheet is probably used to/can figure out using SQLite.

If/when your software grows beyond what SQLite can support, I'd skip MySQL and go straight for PostgreSQL. It has much better support for "big-boy database" features, such as sequences, views, subqueries, and procedures. The standard graphical browser is pgAdmin.

Google App Engine gives you read-only access for free; Nearly Free Speech provides full CGI/PHP/etc support for cheap. I used GAE to write the MerchI bridge planner back when we still held Delve, and it was a bit of a pain in the ass to deal with. NFS charges based on usage, which for small sites will be only a few cents a month, so IMO it's worth paying for vs GAE.

DrunkMc

Totally off topic; and I apoligize, but Janin, your sig is fantastic!

My sister has that poster hanging in her room. That and the BSG's Last Supper is my favorite type of that poster! Kudos sir!

LoneIgadzra

Janin wrote: »

In my opinion, SQLite is the easiest open-source SQL database to get started with. SQLite databases are just files, so you can easily copy them around via SCP or HTTP and don't have to worry about maintaining a separate server installation. Every major language includes SQLite drivers, so you can either write your own basic frontend or just install an existing one. If your goal is just to publish information, let people download the database directly -- anybody who can deal with an EVE spreadsheet is probably used to/can figure out using SQLite.

If/when your software grows beyond what SQLite can support, I'd skip MySQL and go straight for PostgreSQL. It has much better support for "big-boy database" features, such as sequences, views, subqueries, and procedures. The standard graphical browser is pgAdmin.

Google App Engine gives you read-only access for free; Nearly Free Speech provides full CGI/PHP/etc support for cheap. I used GAE to write the MerchI bridge planner back when we still held Delve, and it was a bit of a pain in the ass to deal with. NFS charges based on usage, which for small sites will be only a few cents a month, so IMO it's worth paying for vs GAE.

I pretty much agree with all this, but I figured MySQL is a good enough beginner choice since it is pretty simple and utterly ubiquitous - maybe even easier to set up than sqlite for certain hosts.

But it's very true that it has nothing on postgresql (apart from a hojillion collation options), and sqlite is an order of magnitude easier to get started with on your own machine than a full-blown database server.

mspencer

I think there's two different motivations for using a database here, and they're getting confused.

On one hand there's "my web page talks to a database server because that's the only method I have to save and load data from a web page."

On the other hand there's "I store my data in a relational database instead of an Excel spreadsheet or a flat file because the data is highly relational / because multiple users need to be able to access it at the same time without update anomalies / because I expect to have millions of records I need quick access to" which I think needs different advice. Even if the OP doesn't have this now, if they expect they might have to know how to solve these kinds of problems in the future they might want to know this.