PHP Tutorial – Lesson 9 Putting data in the database – Using the INSERT statement
Ok, take two. The web can be unreliable at times, and it is never more frustrating than when you complete an article and hit save only to have the post fail. My impatience may result in a slightly abbreviated version of the last attempt. Remember folks, always copy long articles before submitting them on the internet 🙂
In our last tutorial, we talked about real databaes (database servers as opposed to Access or Excel). Remember that the database server can contain many different databases which can each contain many different tables. Each database is like an Excel file (workbook) and doesn’t actually hold data. The database is just a container for tables (many work sheets in a single workbook). Each table can hold rows of data. This data is defined very specifically (it helps keep the database servers FAST). In our last tutorial, we used a small table that can hold information about user accounts. This included a user name, a password, and an age. Additionally we include a numeric ID. This id would auto increment each time a new record was added and would make sure we had a unique value to identify each record. This is an important concept that will be discussed later. Lets just say that with a unique id (often called a primary key) you could have two different user accounts with the same user name and each one could be identified uniquely.
Please refer back to the previous article for the definition of the table we will use (PHP Tutorial 8).
A database with no data is pretty worthless. Once we have defined a place for our data to go (think of it as forward variable declaration in a programming language for those of you who know what means) we can start putting data into the server. It is important that the server knows where to put this data.
For those of you who are familiar with a file system (your hard drive), you know that each file has a path that identifies where the file is located. This file path is simply an organizational trick to help you store and locate your files easily. The fact that we often have to traverse 20 directories just to find the file we want makes this less then intuitive at times. The sole purpose though is to know what folder to place your files so that they don’t all end up in the root of the C drive (c:\my documents\my file.doc). In the database, we also have a hierarchal path. This path is much more simple and structured. At the top level is the database server itself and is assumed when you make the connection to the server. The next step is the database in question. You will often select a current database when you connect so that in the future this is implied. Lastly, you specify the table you want to work with. In certain circumstances, you can then specify a field in that table. The period is the character of choice for seperating the items in the path rather than the slash character in a file system.
--> C:\My Documents\ My File.doc
--> MyDB.MyTable.UserName
Again, you never see the database server itself as part of the SQL statement (unless it is a linked server, but that is outside the scope of this discussion), it is assumed because you are already connected if you are running this SQL statement.
The following SQL statement is written twice. Once with the full path to the table, and once assuming that the statement will run against the currently selected database.
INSERT INTO rayp.mytable (user_name, password, age) VALUES ('bobs', 'fish', 15)
INSERT INTO mytable (user_name, password, age) VALUES ('bobs', 'fish', 15)
The example assumes that the database is named after me (rayp). Since you often select a current database (or it could possibly be set as a default on your login account) you see the full path (db.table) notation as often as not. It isn’t a bad idea to always use that full path just to make sure, but I often leave it off (if you are doing cross database queries, then you ALWAYS use it, but most applications only use one database, so it doesn’t matter).
One thing that you will find about SQL is that it is structured (as indicated by its acronym – Structured Query Language). It is not a language in the sence that you make descisions and loop with it (although you can if you get really crazy) it is simply a descriptive language that allows you to describe what you want the database server to do. In our case, we want to add data, so we start with the INSERT statement. All SQL statements start with the appropriate command (INSERT, UPDATE, DELETE, and SELECT are the big four).
The next thing to determine is where we are going to put that data. In our case, we want to put it in a table called “mytable” in the rayp database (either implied by the currently selected database or specifically written in the SQL statement).
In SQL, you will find that we use the ( and ) symbols to denote a list. This is important as it could easily get confusing as to how many items are in the list. In the case of inserting data, all you really need to do is pass in the values that would go into each column. You can actually leave the column map off (to be explained in just a second). You can put the values in the exact same order as the occur in the table and they will magically find their way to the proper locations. The problem with this approach is that in the future, you may add a field, or re-arrange something and it could break your whole application (now things would be out of order). For this reason, most INSERT statements that you see include a column map to indicate which values should go in which columns. It is also for this reason that I won’t show you an example of the wrong way to do it.
The right way consists of the colunm map. Lets look at our INSERT example again.
INSERT INTO mytable (user_name, password, age) VALUES ('bobs', 'fish', 15)
The first list is the name of the columns. You can re-arrange these any way you want as long as the values follow in the same order later. You can even leave some columns out if they have default values or allow NULL’s. In our case, we have an ID field that automatically grabs the next available number if we leave it out so you wont see it listed in our INSERT statement.
Our fields (user_name, password, age) are contained between the ( and ) symbols to designate the start and end of the list. The commas are to indicate when one field name is done and the other begins. This is our list of columns or our column map. The values that are to be placed in our table follow the VALUES keyword and again are placed between the ( and ) symbols. Notice that the values are in the exact same order specified in the column map. This does not have to be in the exact same order as defined in the table since the column map will make sure that things get where they are supposed to.
The final thing you really need to know is how the data is deliniated in the VALUES section. All values that are string based in nature (non-numeric) have quotes around them (specifically the single quote; we like single quotes in SQL). All of the values that are specifically numeric do not need quotes (in MySQL it is ok to quote numeric values, in MSSQL you can’t). Again, commas are used to seperate one value from the next.
Here is another example, this time adding 3 different users.
INSERT INTO mytable (user_name, password, age) VALUES ('bobs', 'fish', 15)
INSERT INTO mytable (user_name, password, age) VALUES ('franky', 'abcd', 35)
INSERT INTO mytable (user_name, password, age) VALUES ('edf', 'efg', 45)
Notice that to add the three records to the same table, the only thing that needs to change is the VALUES section. Some SQL servers (MySQL in particular) can support an alternate method of inserting multiple records. Much of the previous three statements is just redundant. The abbreviated version would look something like the following:
INSERT INTO mytable (user_name, password, age) VALUES ('bobs', 'fish', 15),
('franky', 'abcd', 35), ('edf', 'efg', 45);
Notice that each new set of values is seprated by commas and it is a good idea to add a semi colon to the end. This can be more efficient if you have many records to add at once, but it is some what proprietary in that this may not work in its exact form on all SQL servers. The first example will. You can use this method, just make sure that you don’t plan on migrating to a different SQL server in the near future.
If you have large numbers of records to insert at once (1000’s or more) then there is generally some bulk import mechanism that works even faster (LOAD DATA command in MySQL).
Later on we will discuss how to do all this from a PHP script. For now, try putting in your own data this way by using the PHPMyAdmin or SQLYog software. There is an SQL or Query tab that you can type or paste SQL statements directly into.
Next time we will talk about changing information in the database with the Update statement. Until then, make sure you get out of that chair and into some sun shine before it forms to your butt.
Ray Pulsipher
Owner
Computer Magic And Software Design
