PHP Tutorial – Lesson 12 Hello!? Are you there?! – Using the SELECT statement
This is both the easiest and most difficult of the four SQL statements. The SELECT statement.
We have covered putting data in the database (INSERT), removing data (DELETE), and changing data that already exists (UPDATE). Aside from the INSERT statement, which simply jams data into a table, you append a WHERE clause onto your statements to help specify which rows you want to effect (eg: you generally don’t want to delete ALL records). The SELECT statement uses those same WHERE clauses. In a SELECT statement, the WHERE clause can be so very simple or it can be the most mind bending part of working with a database. I sugest you keep it simple where possible.
Lets start with the simplest of SELECT statements. When we remove information using the DELETE statement, it is VITAL that we use the appropriate WHERE clause or we could end up loosing more data than we intend. With a SELECT statement, leaving off the WHERE clause just gives us every record in the table. How much of a problem this is depends on your current situation. For instance, we created a user table. If a user is updating his profile information, it is pretty vital that the user only gets his/her information.
Ok, here is the SELECT statement. Place this into your SQL admin software (PHPMyAdmin or SQLYog).
SELECT * FROM mytable
From this statement, you should get all the records you previously entered. If you went crazy during the DELETE tutorial, you may not have anything left. Refer to the tutorial on INSERT or use your SQL GUI to add a few rows.
Lets break down the current SELECT statement. We start with our statement, SELECT, so that the database knows that we want to get information. Next we follow that with the * character. This is called a wild card and means give me all of it. For those of you who have worked with a dos or linux/unix command line, you will recognize this character. When asking for information, you can limit the colums that are returned. If all you wanted was the user name, you could keep it from returning the password. This can enhance security and make the network communications more efficient. Lets say you stored files in a database. These files could be quite large. If all you want is the information about the file, it could save quite a bit of network bandwidth to limit the number of columns returned per row. It is important to note that the * character referrs only to the columns you want and does not effect the number of rows returned (that is taken care of by the WHERE clause).
Here is a SELECT statement in which only the user_name column is returned.
SELECT user_name FROM mytable
Here is the same SELECT statement that returns the user_name column and the age column.
SELECT user_name, password FROM mytable
You use the comma to seperate the column names.
Just like the other SQL statements, you have to specify which table you are talking about. Which table should we get the information from? With a SELECT statement, you can do what we call a JOIN and get information from multiple tables. We won’t cover that here both because it would make this article very long and because it would complicate things greatly (maybe in another tutorial later). The reason I bring JOIN up now is that if you use a JOIN, you may have a comma deliminated list of tables. For our exercises though we will only have one table specified after the FROM clause.
That is the basics of the SELECT statement. Our examples just return every row in the table. Some times this is appropriate (if the administrator wanted a complete list of users in the system). Often you want to limit the information returned. Lets say that you only wanted to look at users who were older than 18 years old. Lets say you are sleezy and want to send out inapropriate emails to all your users, but because of the laws, you better not send these emails to users who are too young. I know this scenario sounds bad, but it happens all too often in the real world. When you sign up for some services, if you report that you are younger, these services may not let you into certain areas or may take extra steps to protect your information. Here is the statement that would grab all users who were 18 and older.
SELECT * FROM mytable WHERE age >= 18
If you refer back to the tutorial on If statements, you will see that expressions in the WHERE clause are very similar. The cool thing about WHERE statements in SQL is that it will automatically apply this expression to every row in the table. All rows that are true (the age is greater than or equal to 18) will be returned. For this statement to appear correct, you will need a few users in your table of varying ages (some below 18 and some above).
Lets pretend that we are looking at the user list for Hotmail. The base list would be millions of users. If you cut out everyone below 18, you still have millions of users! Lets limit things a bit more. Lets get everyone 18 or over whos user name starts with the letter A.
SELECT * FROM mytable WHERE age >= 18 AND user_name LIKE 'a%'
In this statement, we say that the user has to be 18 or over (age >= 18) and the user_name has to begin with A (SQL text matches are case insensitive). Instead of using the user_name = notation, we use the LIKE keyword. This allows us to use the % symbol as a wildcard (a+anything else). If we would have put user_name=’a’, then we would have only gotten records where the user name was just the letter a! Only records where both parts of the expression are true will be returned. This is the kind of SQL statement you use if you want to have A-Z links where you can click A and get all of the A’s.
The WHERE clause is very powerful. Lets now say that you know you are dealing with a user that has the ID of 1. To get this user, you can do this.
SELECT * FROM mytable WHERE id=1
This will return just the record where the field called ID = 1.
There are many many more things you can do with a SELECT statement. Check out the GROUP BY and LIMIT clauses if you get a chance in a MySQL reference (http://www.mysql.com). I will be showing more examples later when we get back into PHP coding and show you how to integrate PHP with MySQL to produce data driven web sites!
Again, because of the versatility of the SELECT statement, it can take quite a while to master. Always try to keep things simple. Some of these features are great, but they should not be over used. That only leads to headaches and missed deadlines 🙂
Ray Pulsipher
Owner
Computer Magic And Software Design
