|
January 24th, 2006
We always have to make changes. In fact, I think that is one of the first things that people quote as an advantage of using computers, “it’s easy to change!”. That is right up there with “these are real time savers!”. Your mileage will vary I am sure…
Information in the database is easy to modify. Using the UPDATE statement, we can modify any record. One concept that we haven’t covered yet is that of filters. Filters are VERY important in three out of four of the big four SQL queries (INSERT, UPDATE, DELETE, SELECT). With INSERT, we simply dumped data into an existing table. There was no reason to filter it, just shove the data in and done. Now that we want to change existing data, we need to be careful to tell the database WHICH data should be changed. For instance, we could use the following UPDATE statement to give every single user in the table the same password:
UPDATE mytable SET password='guess'
After this statement, every single user would have the same password (guess). This is generally not what is inteneded. If you allow a user the ability to change their own password (a common feature in most applications) then you would want to make sure that you only change the password for the user in question. Keep this stewing in your brain for now and we will get back to how to filter what is changed.
The previous UPDATE statement is mostly complete (it is just missing the filter). Lets break that down.
Like all SQL statements, it starts with the command (UPDATE). Next it is followed by the table where the changes are going to take place (this can also be the db.table notation if you are providing the full path). Note that the UPDATE statement will NOT change the structure of the table (like adding a column), you would use a different SQL statement for that (ALTER TABLE). The UPDATE statement only changes existing row(s) of data.
After you identify the table where changes will occur, you need to specify which columns to change. You use the SET keyword to indicate that the changes follow. You only need to specify columns that will change. In this case, we are only updating the password, so we specify the password column and set it equal to our new value. As in the INSERT statement discussed in the last lesson (PHP Tutorial 9) we need to put single quotes around non-numeric values. If we want to change more than one column value at a time, we can use a comma and specify another column=value.
UPDATE mytable SET user_name='frankyr', password='abcd'
In this statement, we will change the value of the user_name column and set it to frankyr and change the value of the password column and set it to abcd. Again, without a filter, this statement will apply to ALL records in the table. That means we would all be frankyr and have to use the abcd password to log in. The good news though is that since the age column wasn’t changed, we would all retain our youth or lack thereof.
The UPDATE statement itself is fairly simple but updating a whole table all at once is only really usefull in a fraction of the time. Lets look at how to limit which records are updated.
To add a filter, you use the WHERE statement. By putting this in, we are telling the database server that we only want to update records where the following expression matches. The nice thing about the WHERE clause is that it is pretty standard and will be very similar when you learn to DELETE and SELECT data.
For the WHERE statement to work, you need an expression. This expression is evaluated against each and every record in the table. Every record that evaluates to true is updated, all that are false are not.
UPDATE mytable SET password='abcd' WHERE user_name='bobs'
In this example, we would only change the password for rows where the user_name is bobs. This is a more appropriate SQL statement to use when letting a user change their own password. The filter (WHERE clause) keeps the database server from updating every single record. As long as you only have one user named bobs, you will only update one record.
Lets say for instance that you had columns for address, city, state, and zip. After you get two million users, you realize that the zip code 91451 is correct, but when users selected this zip code, it entered the state incorrectly (it put CA instead of OR). You could use the following UPDATE statement to correct the problem on all records that were incorrect.
UPDATE mytable SET state='OR' WHERE zip_code='91451'
You could do a mass update very easily. You of course want to be carefull doing mass updates like this as you may inadvertantly change more than you intend, but it is great to have this kind of power. Imagine bragging to all your friends that you changed 1 million records today with 100 percent accuracy. Not exactly a good pickup line, but some of your tech friends would be less then impressed.
I hope you are starting to see that SQL really isn’t that bad. The only one that starts to get really ugly is the SELECT statement, and then only when you start getting into joins and subqueries. If you break it down though, its really not so bad.
Your assignment is to write some update statements to make changes to your current data. Practice this some as UPDATE statements are used all the time in your programming code and you need to have a good grasp on this before trying to put SQL statements into PHP. The hard part about PHP is generally not so much the language, but the fact that you toss in a little SQL, and a little HTML, and the water gets muddy real fast (and don’t forget about Javascript/Ajax, CSS, XML, etc.. etc.. etc..). The better you understand this one component, the easier it will be for you later.
Ray Pulsipher
Owner
Computer Magic And Software Design
Posted in MySQL, PHP Class | Comments Off on PHP Tutorial – Lesson 10 Ooops… I messed up, can I start over? – Using the UPDATE statement
January 24th, 2006
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
Posted in MySQL, PHP Class | Comments Off on PHP Tutorial – Lesson 9 Putting data in the database – Using the INSERT statement
January 13th, 2006
Computer Magic And Software Design specializes in writing custom database driven applications in both standalone and web environments. Part of the excellent service we offer to our clients is a complete web hosting package. Now, we are offering the same great quality, reliability, and service to everyone.
Let us free you from all the technical jargon involved in getting your own web site. Our personalized service includes an actual phone number to call when you need help (no more anonymous email support!) and an email address that actually goes to a real person (none of that anonymous support ticket mumbo jumbo here!). We can offer you a basic hosting package and let you create content to your hearts content (perfect for the tech who needs his or her own web space) or we can offer you a more complete package and hold your hand through the whole process allowing you to get back to what you do best… Run your business! Call us to see how we can help enhance your business.
All plans include:
- Fast Linux servers! – Duel or Quad Intel Xeon 3.0 GHZ + processors!
- Unlimited FTP accounts
- Unlimited Email accounts
- Unlimited Email lists
- SMTP, POP3, and IMAP support
- Spam Assassin Spam Filtering Software
- Customizable Email filtering
- Unlimited MySQL v4.1 and PostgressSQL v7.3 databases
- Unlimited Sub Domains
- Front Page 2002 Extensions
- CPanel management Interface
- Password protected directories (.htaccess)
- Perl 5.8/CGI Access
- PHP 4.3.x with Zend Optimizer
- Server Side Includes (SSI)
- Web based file manager
- Custom Error Pages
- RAW access logs, AWStats, Analog, and Webalizer stats
Currently we offer the following packages:
Value Plan – Just $5.00 a month (only $60 a year!)
This plan is perfect for individuals or small businesses on a budget. You get plenty of space to get going. Write your own blog, put up your own forums, post pictures of your family. You can’t go wrong at this price!
Storage Space: 500 Meg
Available Bandwidth (per month): 5,000 Meg
| Value Plan for 1 year – $60 |
Value Plan + Domain Name for 1 year – $75 |
Value Plan + Domain Name + SSL Certificate for 1 year – $175 |
 |
|
|
Business Plan – Just $25 a month (only $300 a year!)
This plan is geared toward business clients with higher traffic web sites. The extra space and bandwidth allow for a greater number of visitors and the extra storage space gives you more freedom to grow. Our excellent service allow you to keep your sanity.
Storage Space: 1,000 Meg
Available Bandwidth (per month): 10,000 Meg
| Business Plan for 1 year – $300 |
Business Plan + Domain Name for 1 year – $315 |
Business Plan + Domain Name + SSL Certificate for 1 year – $415 |
|
|
|
Feel free to call (360-417-6844) or email us (computermagic@hotmail.com) for further assistance. All orders will be processed and setup within one business day.
2CheckOut.com Inc. (Ohio, USA) is an authorized retailer for goods and services provided by Computer Magic And Software Design.
Posted in Uncategorized | Comments Off on Web Hosting at Computer Magic!
January 13th, 2006
Today we take a short break from learning PHP. I would think that the bulk of the people reading these tutorials would like to learn about writing their own database driven web applications using PHP (that would be online shopping carts, message boards, and most any other use you would have for PHP scripts on the internet). There are definatly more concepts in PHP that we need to cover (looping, arrays, etc…) but I thought we could get a little background on a database server and then use a real world example to introduce looping in PHP later.
Today, we will talk ONLY about the database. Specifically, we will use the MySQL database server. This is a great open source database system that you can download from www.mysql.com. Next, things are MUCH easier if you have a nice interface to work with. By default, MySQL doesn’t come with a GUI interface. You can download their inteface, but last I checked, you needed two programs to do everything, and they were really more in the beta phase. Don’t judge the quality of the database server by the quality of these tools, they are new and I suspect written by a different programmer. In any case, SQL Yog is a great one. Get it at www.webyog.com. Download the free version.
If you already have a web server through a web hosting service, chances are that you already have MySQL available. You may need to look through the management interface that they provide to create a database or to set your passwords. Also, you may want to look at the option that allows you to specify what computers users can connect from. By default, many are set to local host. This is more secure, but it means that connections can only occur from software running on that server. If you want to use SQLYog, you will need to put in your IP, or put in the wildcard % to allow any IP to connect. Be aware that this means that you are loosening your security by doing so, so be careful. You can’t hold me responsible for messing up your own system 🙂
If you already have MySQL provided by your web hosting company, they may also have a management interface installed for you. PHPMyAdmin is a popular web based interface and will work great for our examples. In either case, you may have to log in with your username and password. The server (if needed) will be the name of the computer where the database server is located (localhost works if it is on the current computer, or you can use the IP address also). Real database servers have security built in from the start, not as an after thought like in Access or Excell. This is great for allowing multiple users or keeping hackers out.
Now for a little background. A database server is different than Excell and is also different from Access. It is BETTER! Of course, there is no need for a full featured database server for some tasks, but make no mistake, Access can’t hold a candle to a real database server like MySQL, MSSQL, or Oracle. The point of a database server isn’t to look pretty, it is super optimized to find and crunch data. Period.
At first, it will seem limited, especially for those who have worked with Excell or Access (“How do I format my page?”, “Why can’t I put an equation in this field?”, “Why can’t I create forms like in Access?”). Let me just say that a database was made to do one thing… Crunch DATA!! DATA, DATA, DATA!!!! All those other features present in other apps are nice, but they would interfere with the insane speed of the data processing in a real server. Try putting 1 million records into an Access database, then do the same thing in a MySQL server. You would be amazed at the difference in speed. You wouldn’t even dream of creating an Excell file that big.
A database server is the software that does the work. Internally it may hold several databases. Each database can be thought of as an Excel workbook (Excel file). It doesn’t actually hold data, it is just a container for tables which do hold data. By breaking things up into several databases, you can segregate the information and apply different security settings for each database. This means that I can have a database full of my companies information on the same server as your companies information and not have to worry about you getting into my stuff (try sharing an Access database and keeping your users limited to only certain data, or sharing an Excel file and only allow users to view certain sheets!). In these databases, you can put tables. A table is similar to an Excel work sheet in that you put your data there (it has columns and rows).
So how do I let my users enter data? You don’t give them direct access to the database. Instead, you use some language such as PHP to work with and manipulate the data via SQL statements. Then, in the case of PHP, you use HTML tags to beautify the information and make it appealing and easy to work with. The presentation layer (integrated in Access as forms) is a seperate function and is not included directly in the database server itself. This allows you to have a C++ application that connect to your database at the same time that your PHP web application is showing your products in real time online. You have many choices. With Access, you are stuck doing things the Access way and any one who wants to use the Access database has to have purchased the Access software and installed it on their machine to use it (you can’t open an Access MDB file without the Access software). With a database server, you can pick your language and development environment and make your front end look and act any way you want.
The primary method of communicating with a database server (There is a published standard even!) is via the SQL language. It isn’t a full featured language like a programming language, but it is decriptive enough to allow you to describe what you want the database to do. You can do just about anything on the database by using SQL statements (even manage it) but that is a bit overkill. It is much easier to use a GUI interface (PHPMyAdmin or SQLYog) for many of the menial tasks such as creating users.
The first task is to create a new database. A database server can host many databases. The security measures in place allow you to sandbox users in their own database and have full control over their own area. For those of you with a management interface on your web server, you may need to use the create new database function. Your user account itself doesn’t have permissions to creeate a database directly. Look around (not in PHPMyAdmin!). For the rest of you, there is probly an area to create a new database by entering the name. These interfaces simply take the name you specify and run the CREATE DATABASE command which would look something like this:
CREATE DATABASE mydatabase
I generally use CAPITALS for all SQL keywords as this helps your eye distinguish between the different elements. This is not a requirement of SQL, but I recommend it.
This SQL statement can be entered into your SQL GUI (SQLYog or PHPMyAdmin). In some environments, as mentioned above, you may not have access to run this command directly (use your web server management software to create the database or contact your hosting company to get setup). If it works, you should see your database listed.
Next, we need to create a table. The Database itself (the thing you just created, not the server) is just a container that things can go in. The tables are where the actual data is stored. Think of it as an Excell work sheet. The Excell file (generally called a work book) can hold many work sheets (sheet1, sheet2, etc…). The Excell file corresponds to the Database while the individual sheets correspond to a table. Now that I have used that analogy, don’t make assumptions about the capabilities of a database table based on the cool calculations you created in Excell, that is where the similarities end.
To creat a table, the easiest way is to use the GUI interface and just punch in the fields. We will do a simple table the hard way (using SQL statements) to show you how its done. Each table is made up of columns (think of the spread sheet). A big difference between Excell and a database is that Excell has what appears to be infinite columns and rows already available. In each of these columns and rows, you can place ANY type of data (1, 5, 2.7, “howdy”, etc..). There is no constraint. In a database table, you have to define each column including its data type. When the data type is set, ONLY that type of information may be stored there (attempts to put incorrectly formatted information will either result in an error or result in that field value being thrown away!).
Ok, I know, it sounds very limiting. Why would you impose these rules? One word, Speed! By imposing these limits, the database server can take advantage of the characteristics of computing systems. The way the information is stored on the physical medium (hard disks!) is now predictable! We can now predict that each row will be exactly XXX number of bytes long and it makes it easy to figure out where row 100 is on the hard drive without having to wade through every record to find what we are looking for. Additionally, databases use indexing as another way to pre-calculate those jump points. For a small Excell file, this could be overkil, but for a multi million record database (think of Hotmails list of users!) this is absolutely necessary. Could you imagine waiting 5 minutes for hotmail to look up your user information when you were logging in?
Again, remember that the presentation layer (PHP/HTML) allows you to manipulate and beautify this information prior to presenting it to your users, so it isn’t quite as limiting as it sounds. And for those Access junkies, our presentation layer is more versatile and powerfull than yours (sorry, not an opinion, it is a fact, we can plug in the language/tools of choice).
The last thing to note before moving on. To take advantage of the databases strenghts, we need to have at least one indexed column. The first one is generally called the Primary Key. This column identifies each record uniquely. Usually just a numeric column is adequate and most database servers support an auto increment feature which means that a new record will just get the next available number (you don’t have to try and figure it out). Oracle likes sequences, which is a little bit hokey, but gives you more flexibility (they like things to be more powerfull and more difficult 🙂 ).
Ok, here it is at last, the SQL statement to create a table…
CREATE TABLE `mytable` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`user_name` VARCHAR( 200 ) NOT NULL ,
`password` VARCHAR( 200 ) NOT NULL ,
`age` INT NOT NULL ,
PRIMARY KEY ( `id` )
) ;
The create table statement will cause the database to make a new table with the specified columns and options. First, there is the name `mytable`. This helps you to identify which data you want (think of sheet 1, sheet 2 in Excell) and will be used in later queries.
Note that the ` symbol is used to surround our table and column names. This isn’t needed most of the time, but the ` symbol works kind of like a quote. This helps keep your column name from being mistakenly interperated as a key word. For example, if you wanted a table named bigint (look at the ID column), this could confuse the server, so you could put ` symbols around it to give the server a clue that you want that as a name not a data type.
The table we created will have 4 columns. The first column is a numeric column called ID. The data type in this case is a bigint. Big ints in MySQL are I believe 64 bits. A 32 bit integer can have a range of about -2 billion to postive 2 billion (you can look up the exact number) so a 64 bit integer can be REALLY big! If you make a 32 bit number unsigned, then you can have a range of zero to 4 billion (signed numbers use 1 bit to indicate a positive or negative, therefore the difference in ranges).
The reason for our first column is so that we can have an indexed primary key. The indexing process is what makes the searches REALLY fast (familliar with google desktop search? it finds stuff really fast on your computer because it has previously indexed it for you). Also, this column is set to Auto Increment which means we don’t have to specify the number, it will fill one in for us (the next number in line). With this we can uniquely identify each record (we could theoretically have two users named bobs, though in practice this isn’t a good idea). Really this column is a maintenance type column and doesn’t ever have to be exposed to your database users (know what your unique identifier is for your hotmail account? not including your email address ). This column can ONLY hold whole numbers (integers).
The next column (user_name) is defined as a varchar. Varchar columns can be zero to 255 characters long in MySQL. The number (varchar(200)) defines the max length of the values held in this column. This means that some one can enter a user name of 1 to 200 characters in length. If the user name is over 200 characters, it is cut off at the 200th character. 200 characters is a bit over kill for a user name, so there ends up being some wasted space in each record. The idea of a varchar versus a char field is that the record will shrink (take some of the air out and not waste the space) where a char field would allocate all 200 characters even if you only used 1. Char fields can be searched faster, but it can waste space. I generally just use varchar for most purposes. If you needed to hold a LARGE ammount of information (a whole document, something more than 255 characters) you could use a TEXT field.
The password field is similar, and we have an insane number of characters specified here also (200). You can have a whole poem as your password according to this. One thing that we programmers like to do is obfuscate the actual data when it comes to sensitive information such as passwords. We would rather that if some one did happen to look at the contents of the table, that every system on the network wouldn’t be compromised (most people use the same password in many different places). One way to mask the password is to use a hashing algorithm. It is an encryption that can’t be reversed. Also, most hashes (if not all?) create a hash of the same number of characters EVERY TIME regardless of the ammount of data you feed it. For instance, you could give the hashing algorithm a two gigabyte file and it would would come out with a 40 character hash. If you hand the hashing algorithm 1 character, it would still come out with a 40 character hash. In the case of the two gigabyte file, if you change just one bit in the file, then you would get a completely different hash. This is also a good way to ensure that the file is in tact after a download (notice any MD5 hashes listed next to a download link?). The fact that these hashes are not reversable means that the only way to crack them is to start with the correct password, hash the password, then compare the two hashes to see if they match (this is exactly how some authentication mechanisms work). When you are trying to crack a hash, you would have to make every possibl guess until the hashes match (this could take weeks, month, or even years!).
Moving on, we have a column called age. This column we made a integer. This is actually over kill since most people don’t live past 100 (int ranges from -2 billion to +2 billion). Since it is an integer, we can’t have 2.5 years, only 2 or 3 (which is fine for what we want).
Lastly, we included the NOT NULL syntax on most columns. This tells the database that each column has to be set. A NULL value indicates an empty field (meaning NOT SET). This is different from 0, or from an emptry string (those are actually values). NULL is a complete lack of value. It is a nice feature in some cases, but unless there is a reason to use it it simplifies things to not use them. By using the NOT NULL option, we specify that the column has to have a value, even if it is a default value (like 0 or an empty string).
The final option specifies which column will be the primary key. The primary key just adds an index on that column, but in this case, it is the primary index.
Most SQL statements have a very similar logical layout. We start with the command (CREATE TABLE) then follow with the name of the table (mytable) then define the columns. Notice that the columns have the ( and ) symbols wrapped around them. This gives the db a clue that we are starting and ending the list of columns in the table. The parenthesis will be used more in future SQL statements.
Now that we have a table, we can start putting data in it. When you work with SQL as a programmer, you generally use a GUI (like SQLYog or PHPMyAdmin) to do your administrative tasks (creating users, tables, databases). When you write your software though, you need to know how to manipulate the data from your program. This is where you use SQL exclusivly. The bulk of your usage is constrained to four different types of access which include getting data (SELECT), changing data (UPDATE), adding data (INSERT), and removing data (DELETE). These are the four main types of SQL queries that you need to master to write database driven applications.
This article is getting long, so I will show an example of each query here, but I will break each one down in later articles. Notice the similarities between the four statements.
INSERT INTO mytable (user_name, password, age) VALUES ('bobs', 'fish', 15)
UPDATE mytable SET password='abcd' WHERE user_name='bobs'
SELECT * FROM mytable WHERE user_name='bobs'
DELETE FROM mytable WHERE user_name='bobs'
Ray Pulsipher
Owner
Computer Magic And Software Design
Posted in MySQL, PHP Class | Comments Off on PHP Tutorial – Lesson 8 Data Data Everywhere!
January 6th, 2006
When learning to write software, one of the most fundamental things is to make decisions. Usefull software requires input. Input is simply a clue to the program as to what to do next. For instance, when you are playing your favorite FPS (First Person Shooter), and you are running around shooting up all the rabid sheep and leaving bloody wool all over the place, the input you give to the game is via the keyboard and mouse. When you push the forward key, your guy moves forward, when you push the back key, your guy moves back. Input. Output in this case would be the visual representation of the world, which changes based on your movement (input). Input and Output comes in many forms (command line programs, point and click GUI, 3D games, etc…) but it is all conceptually the same, the input informs the application what the user intends.
Once you have that input, you need to decide what to do with it. Now I know the user pushed the OK button, or the user pushed the up arrow, what should I do now? In the case of an FPS game, you might want to only move forward if they player is alive and kicking. This would require a choice, is the players health above 0? If it is, then we move forward, if not, we are a bloody mess on the pavement, so make like a dead guy and stay. Here is some pseudo code (pseudo code is code that captures the logic, but isn’t really a specific language and wouldn’t actually work).
if (player_health > 0) then
Move(FORWARD)
else
return 'Do nothing!'
end if
Ok, now, this pseudo code looks more like Basic than PHP! Why would I do that in a PHP tutorial? For those seasoned programmers out there, this is dumb, but for newbies, this is more readable. Don’t worry, I will show the PHP version in a minute.
Also worth noting is the concept of code blocks. A chunk of code that logically goes together is called a block. In the previous example, the blocks were one line each (e.g. Move(FORWARD)). Using the concept of blocks, you can have one or 100 lines in a code block, it doesn’t matter. The kewords THEN, ELSE, and END IF signal that a block is starting (THEN), another new block is starting and the previous block is ending (ELSE), and that the last block is ending (END IF).
Lets break this down now. We make choices by using the IF statement. The IF statement lets us tell the computer to execute a certain block of code only if the expression is true. This is important. The IF statement doesn’t care what you are comparing or what is happening inside the expression, it only cares if the end result is True or not.
if (expression) then...
DO THIS
end if
The portion of this code “DO THIS” will ONLY be executed if the expression is True. Is 5 = to 5? It sure is, so if the expression was…
if (5 == 5) then
THIS WOULD EXECUTE
end if
Notice that a double equals was used for comparison. Most languages do this so that the language can tell if you are trying to assign the value of 5 to five (5=5) or trying to compare the two (5 == 5). In the expression, you can put any valid components as long as they result in a true or false answer (>, < , <=, >=, ==, !=, etc..). The “DO THIS” section of the code ONLY executes if the expression evaluates to true. Since 5 always equals 5, then that expression is always true. If you were to try (6 == 5), then the expression would ALWAYS be false and the “DO THIS” section of code would be skipped. The trailing END IF key words tell where the True block of code ends so the language knows how far down to skip if the expression is False.
Some times you want to know when the expression is false. To do this, you can use the NOT key word (the ! symbol in PHP represents the NOT keyword). Here is an example.
if (6 != 5) then
THIS WILL EXECUTE!
end if
By throwing in the != instead of the ==, we are flipping the test. The “DO THIS” section still only executes if the expression is true. Normally, 6 == 5 would be false, but when you put the NOT symbol in there, you are saying “does 6 not equal 5?”. This statement is true. Read that a few times to get your head wrapped around it.
Ok, now for another example. What if you want to take specific action based on a true result, and a different action based on a false result (like with our first example). You can use the ELSE keyword to create a false code block.
if (5 == 5) then
TRUE BLOCK
else
FALSE BLOCK
end if
The first block is ALWAYS the True block (even if you use the NOT symbol in your expression, the True block only executes if the expression is true). The block after the ELSE keyword is a catch all. It basically picks up what is left over. If the True block executes, then it will skip over the False block. If the True block does not execute, it will execute the False block. The else (False block) is optional as you may have noticed in some of the previous examples.
Now that we have looked at some examples of pseudo code, lets look at an example of PHP code that really works.
<?php
$name = $_GET["name"];
$age = $_GET["age"];
if ($age < 12) {
print "$name whats up kid?";
} elseif ($age > 11 && $age < 30) {
print "$name dude, would you like some fries with that?";
} else {
print "$name sir, yes sir!";
}
?>
Notice that in PHP you replace the If/THEN with an IF {. The { symbol indicates the start of a block of code. The } symbol indicates the end of a block of code. The else keyword is still used, but notice that it is preceded by a } to close the previous block of code, and followed by a { to indicate the starting of a new block of code. All programming languages are the same in the way the IF statements work, the only real difference is what symbol or keyword they use to indicate the start and end of a block of code.
The tricky part here is that the ELSEIF keyword is used in the second expression. This allows me to have two or more True blocks, each with different conditions. One thing to note here is that once any one of the blocks (True or False) is executed, execution jumps down to the END IF statement (or final } symbol). This means that if the first expression is True and that block executes, the second block will NEVER execute even if the second expression is true also. In this can, only one of the three blocks will ever execute.
And lastly, I got even more tricky by putting more than one expression after the ELSEIF keyword. This allows me to check more than one thing at a time. In this case, I could check to make sure the age specified was in a particular range by using the && symbol (which stands for AND). By using the AND method to combine two expressions, this means that BOTH expressions have to be true for the final response to be True. Should either expression be False, the entire final expression will result in a False answer. If you wanted to execute the current block if either expression were true, you could use the symbol for OR which is ||. This would mean that if the first expression is true, OR the second expression is true, then the result is True. This can complicate things greatly and further discussion will be saved for a future article. Feel free to tare out your hair trying to figure it out on your own though 🙂
To see how this script works, try uploading it to your server and requesting the file. If you put nothing on the address line (except the URL to the script) you will get something like this (you will probly also get a couple warnings):
Since you didn’t specify an age, it use the catch all (ELSE clause) and will print the message without your name. Try adding this to the end of the URL.
You should now get a different message. Try changing the age around to get different messages and see how the IF statements work.
Ray Pulsipher
Owner
Computer Magic And Software Design
Posted in PHP Class | Comments Off on PHP Tutorial – Lesson 7 You must choose!
|
|
