So far you’ve been using a program called MySQL, and you’ve been talking to that program using SQL, the Structured Coer)’ iJ!n9uaQ2. And you’ve already written a couple of SQL queries
Both of those commands are SQL Queries, or expressed more accurately, SQL. The Structured in SQL comes from the idea that you’re accessing a relational database, something with a lot of structure, and you’re using a language that itself is very structured. You’ll soon see that SQL is very easy to learn, mostly because it’s very predictable. That’s why you can look at a command like the following and probably figure out what it does:
mysql> select User, Password
where first name = ‘Dirk’
and country = ‘Germany’;
Even though you’ve never seen the where keyword, it’s obvious what it does: this returns only the User and Password column from the’ isers table, where the user’s first_name field is “Dirk” and the country field is “Germany.”
You could buy a SQL book and start memorizing all the keywords, but it’s a much better idea to simply begin building your own tables and learn as you go. To do that, though, you need to get connected to the database with which all your PHP programs will talk to
Logging In to Your Web Server’s Database
Now that you’ve got a basic lay of the land for how MySQL behaves, it’s time to get things set up on the database your web server uses. You’ll probably need to use a tool like telnet or ssh to log in to your web server.
Once you’re logged in, you should be able to use the MySQL command-line client, mysql. Almost every hosting provider that supports PHP also supports MySQL, which means that just typing mysql is usually the way to get started.
Unfortunately. you’re likely to get an error like the following, right out of the gate:
[email protected]:-$ mysql
ERROR 2002 (HYOOO): Can’t connect to local MySQL server through socket ‘/tmp/
mysql. sock’ (2)
This usually means that MySQL isn’t installed on your server, or at least that it’s not been configured correctly. But that’s’ probably by intention: most hosting providers keep their MySQL installation either on a different machine, or they at least limit accessibility by using a different domain name, like m’i.~qi.kan)’e.\·()m. That adds some protection, isolation, and security to the MySQL databases they host, all of which are good things.
No matter where MySQL is installed, your task is simple. Run mysql and instruct it exactly where to connect. The –host= option lets you give mysql the hostname of your MySQL database server, and +user= lets you give it your own user name.
Put all this together on the command line, and you’ get something like this
[email protected]:-$ mysql –host=dc2-mysql-02.kattare.com
That last option, – -password, instructs MySQL to ask you for a password. You could put your password on the command itself, like –password=this_is_not_very_
secure, but then that slightly nosy cube-mate would be able to log in to your MySQL server. And, if you’re wondering what happened to the -u and -p options for mysql, you might want to check out the box that follows
You Say Potato, I Say P
Back on page 98, you used the – u option for a username, and the – p option to instruct MySQLto prompt you for a password. And, as you might guess, there’s a -h option you can use for specifying the host; it works like – – hast
There’s no right or wrong way to do it, which is a good thing, For clarity, this book uses the longer, easier-to-read versions most of the time, But now that you know the shortcuts, feel free to use them anytime you ijke,
Once you type your password, you should see the standard MySOL welcome screen, as demonstrated in Figure 4-5
Selecting a Database with USE
On most MySOL installations that hosting providers give you, you don’t have nearly as much freedom as you do on your own installation. For example, on a remote server, suppose that you type the SOL show command you used on page 100
myqsl> show databases;
If you ran this earlier on your own computer, you probably saw a lot of databases listed here. But now, on your hosting provider, you’ll probably only see one:
myqsl> show databases
That’s because your privileges on your hosting provider’s server are limited, and as such, the company certainly isn’t going to let you log in to the mysql system databases and see what users are in the system’s user table. What you probably see is a single entry: a database named something similar to your login name. So, if you log in to your system with the user name “ljuber”, you might see a database named Ijuber or perhaps db-ljuber or something similar
In fact, you’re probably already set up within the database that’s named after you. “‘ Go ahead and inform MySOL that’s the database in which you want to work:
While you’re acclimating yourself to your new MySOL environment, you also want to get used to seeing and typing SOL commands in all capital letters. So if you get an email from your database buddy and she suggests that you use a WHERE clause or tells you that your SELECT query is goofy, she’s not actually yelling at you. She’s
saying (or more accurately, writing) SOL commands in all uppercase letters, which is the way most database jockeys do it
Using CREATE to Make Tables
When you could get to and USE the mysql database, you had some tables ready for you to SELECJ from: the user« table, for example. However, now you’re on a database server from which you can’t get to those tables. So, before you can get back to working on your SELECT skills, you need to create a table.
As YOl), might have already guessed, you can do that with another handy-dandy SQL keyword: CREATE. The objective is to create a table, put data in it, get data out, and generally have all kinds of database fun.
Type this command into your MySQL command line
CREATE TABLE users(
This time, don’t add the usual semicolon at the end. When you press Enter, you’ll
see something a little weird:
mysql> CREATE TABLE users (
As you know, MySQL commands should end in a semicolon, so when you leave it off, you’re telling MySQL, “Hey, I’m writing a command, but I’m not done yet.” What this demonstrates is that, you don’t have to jam a lot of SQL onto one line; you can split it up over several lines by pressing Enter. As long as you don’t type that semicolon, MySQL won’t try to do anything with your command. And that little arrow, ->, lets you know that MySQL is waiting for you to continue typing.
So be obliging! Keep typing the following lines, each of which sets u~ a different column of information in your table:
mysql> CREATE TABLE users (
-> user_id int,
-> first_name varchar(2o),
-> last_name varchar(30),
-) email varchar(sO),
-) facebook_url varchar(l00),
-) twitter_handle varchar(20)
Press Enter after this last semicolon, and you get a very unimpressive response
mysql> CREATE TABLE users (
-) user_id int,
-) first_name varchar(20),
-) last_name varchar(30),
-) email varchar(so),
-) facebook_url varchar(;OO),
-) twitter_handle varchar(20)
The second type to which you need to pay some attention is a little less obvious: varchar. The varchar type stands for \ ell! l,lJf; r haracw’; which means that it holds character data-strings-of variable lengths. Referring back to our example, a varchar( 2Q) can hold a string as short as the length of 0 all the way up to a length of 20 characters. For advice on deciding on how big to make your columns, see the box below.
The Size of Your Columns Really Does Matter
When most people are creating their tables. they spend a lot of time thinking about what they want to store in their database. and very little time thinking about things like how big the maximum length of a varcharfield can get. Sovou’ll seelots of tables that have 10or 20 varchar (100) columns, even though those columns hold totally different pieces of information
But it’s better to stop and think about these things when you’re destgning your tables. Makeyour columns as long asthey need to be-but not longer. Yes. it may seem “safe” to just come up with crazy, overly long lengths, but then you’re not really doing a very good job of making your database really look like the information it’s going to store
But this is about good design, not making your database hum. Your database only usesspace for the information it holds; you don’t get penalized by wasted disk space or bad performance if all your varchar fields are suoer-lono. What you do get. though, is a database that looks sloppy, making lOt look like you didn’t spend much time thinking about your information. Take the time to do good design now, and it will payoff later. Make your varchar columns as long as they need to be, and maybe even bit ranger, but always remember what information will go in those columns.
The upshot of all these new terms is you’ve directed MySOL to create a table comprising several new columns, one that’s an int (user _id), and several that are varchars of various maximum lengths
Now, you can see that MySOL did what you commanded it to: It created a table called users with all the columns you specified, using the types you gave it. There’s a lot more information there, too, but you don’t need to worry about that just yet
Using DROP to Delete Tables
What goes up must come down, or so the saying goes. For everything MySOL and SOL let you do, there’s a way to undo those things. You’ve created a table, but now you need to delete that table. However, DELETE isn’t the c~mmand you want; instead, it’s DROP.
Suppose that you decide you no longer like that users table, or you want to practice that fancy CREATE command again, you can ditch uSc’.rs with a simple line of SOL
INSERT a Few Rows
At this point. you’ve created and dropped, and created the USl”‘, table. But it’s still empty, and that’s no good. What do you do? Easy: INSERT some data
Try entering this command into your command-line tool
mysql> INSERT INTO users
-> VALUES (1, “Mike”, “Greenfield”, “[email protected]”,
-> ..http://www.facebook.com/profile.php?id=699186223 ..•
Query OK, 1 row affected (0.00 sec)
What a mouthful! Still, you can probably just look at this SQL and figure out what’s going on. You’re inserting information into the users table and then you’re giving it that information (VALUES), piece by piece.
You can actually trace each value and connect it to a column in your table. You might want to DESCRIBE your table again:
The first value,1, is assigned to user _id; the second, “Mike”, to first_name; and so on
And really, that’s all there is to it. You can insert as much into your table as you want, anytime you want. There are lots of ways to fancy up INSERT, and you’ll learn about most of them as you start to work with INSERT in PHP.
Using SELECT for the Grand Finale
Finally, you’re back to where you can use good-old SELECT. By now, that command should seem like ancient history, given that you’ve used DROP and CREATE and INSERT and a few others since that first SELECT* FROM users. But, now you’ve got your own fsers table, so try it out again
No big surprises here; you got back the row you just inserted. However, just like earlier, the screen is a bit of a mess. Too many columns make the results hard to read.
To simplify things, grab just a few columns. For example, let’s assume that you don’t really need to see Mike’s entire Facebook page URL right now. From the code on page 103, you know how to select specific columns of information
That’s a lot more readable. And once you’re writing PHP to talk to MySQL, this formatting won’t be such a problem. PHP doesn’t care about fitting everything into a nice line or two. It’s happy to take a big messy set of results and handle them
If you’d like, take some time to insert a few more rows of users and then play with SELECT. If you want to get really fancy, try using a WHERE clause, like this:
As you can see, WHERE lets you choose’ a specific person or record of information. You’ll see that again on page 152. Try creating tables with more columns, selecting different columns, choosing records with WHERE, and see how far you can get with all the SOL you’ve already picked up .
SOL or MySOL? They’re Not the Same
It’s one thing to know what SOLstands for, and how to install M~SOL.But it’s something else altogether to know what the difference is between SOLand MySOL. In fact, ask around at your local water cooler. You’d be surprised how many novice programmers are not sure what the difference is between SOL the language and MySOLthe database program.
SOLis in fact a language. It’s something that exists separately from MySOLor any other database program, like PostgreSOL or Oracle. That means that SOLcan change, or be updated, without your database automatically changing. In fact, the way it usu- ally works is that SOLgets a new keyword or instruction, and then all the database programs release new versions to support that new keyword. Of course, SOLhas been around for a long time, so this sort of thing doesn’t happen very often anymore. MySOL is a database program. It lets you work with and administrate databases, and you do that with SOL. In other words, MySOL is really just a tool that lets you use SOL.That makes the name-MySQL -either terribly helpful or terribly confusing. Either way, in this book, you’ll be executing SOL commands against your MySQL database.
NOw,notice that’!!””)· all of your SQL will keep working. Each database adds its own twists to how it implements the SOL standard. And most databases add some database-specific features to “add value.” (You can read that as “to sell their product over another product.”) So, you can run into some problems moving from one database to another. But, your understanding of SQL helps there, too, because you’ll be able to diagnose any issues and quickly solve them.
The takeaway here is to learn SOL,use MySQL,and end up with code that works on just about any SQL database.