Lesson 22 - Starting off with Databases

Now is the time you have all been waiting for! Lets get into Databases!
However, before we can start programming - I want to teach you how to think in databases.

A database (or 'DB' as they're sometimes called) is basically like a large file cabinet for different folders. Here is what a DB would look like as a picture:




The Database is called database_1 and inside the database are two tables "contacts" and "orders". Both of those tables have 3 columns in them:
The table "orders" has the columns "id", "date", and "item".
The table "contacts" has the columns "name", "phone", and "address".

In the end, all of the information in a database is stored in one column or another; the whole point of tables is just to help group columns that have similar or related information.

After each of the columns that will be in a table are made, you start getting into rows. (Think of an Excel Spreadsheet made up of rows and columns, or for those of you who have done HTML; think of ). Each row of a table will have the information for that tables columns. For example, row 1 has the information "Bob" for the column "name", "#" for the column "phone", and "TX" for the column "address". Each new record (row) in the table will have the information for that tables columns.

Note: Database's can have hundreds of tables or just one. Likewise, a table can have hundreds of columns or just one. Also, all of the different tables in a DB can have different numbers of columns; just because both of the tables in this example have 3 columns doesn't mean that they have to have the same number.




All of the interaction with a MySQL database will be done through something called a "query". A query is just another way of saying a request for something. So next time you hear someone say "run a query on your database" you will know that they just mean give your DB a request (ask for some information from it).




Now then, let me explain the different commands you can use on a database:

-----------------------------------------------------------
-- Selecting Data:
-----------------------------------------------------------
The SELECT command selects the data that you want from a table or column. It is the most used MySQL command there is. You can optionally add a 'WHERE' command that tells the computer that you only want the data from where you say.

SELECT [columns] FROM [table] WHERE [specification];

For example:
SELECT * FROM `orders` WHERE `ID` = 1;

In English this would be:
Select everything FROM a table named "orders" WHERE A Column named "ID" is equal to '1';

On the example DB above this would return the values "1", "6/06", and "car". Because this is all of the information in the row of the table "orders" that has an ID equal to "1".


Another example is:
SELECT `phone`,`name`,`address` FROM `contacts`;

This would select all of the information in the columns named "phone", "name", and "address" from the table named "contacts". It would return all of the data based on the rows that the data was in. For example, the result of this command on the above database would return an array with each element a row from the table:

PHP-Code:

$result['1']['name']; would be equal to "Bob" $result['1']['phone']; would be equal to "#" $result['1']['address']; would be equal to "TX" $result['2']['name']; would be equal to "Sam" $result['2']['phone']; would be equal to "#" $result['2']['address']; would be equal to "CA" $result['3']['name']; would be equal to "Joe" $result['3']['phone']; would be equal to "#" $result['3']['address']; would be equal to "FL" ?>

Lesson 21 - Making your own Functions

I want to teach you how to make your own functions now. We have been using lots of PHP's built-in functions, but did you know that you could make your own?

A function is basically a command that tells the computer to carry out a certain set of instructions for you. For example:

trim() - Strips white spaces from the begging and end of a string.
mysql_query() - sends a MySQL queary.
count() -- Counts the elements in an array, or properties in an object
crypt() -- One-way string encryption (hashing)
file() -- Reads an entire file into an array

These are all functions that people wrote to do these tasks for you rather than you having to build a bunch of loops or classes every time you want to do these things. With these functions all you do is:

PHP-Code:

$newstring = trim($string); ?>

As a pose to trying to write all of the code that would trim the $string:

PHP-Code:

foreach(count($string) as $var) { blah... blah... blah ..... blah... blah... blah ..... blah... blah... blah ..... while ($x > $something) { blah... blah... blah ..... blah... blah... blah ..... blah... blah... blah ..... blah... blah... blah ..... } .... more code.... .... blah... blah... blah ..... blah... blah... blah ..... } ?>

Well, if other people can make functions why can't you? Well...You can! So lets get to it!


Here is the syntax for making a function:

PHP-Code:

function fakefunction($argument_1, $argument_2, /* ...more arguments..., */ $argument_n) { echo "Example function.\n"; return $returnvaliable; } ?>

The first step is to type "function", this lets the computer know that you are going to make a function. Next you create a name for the function, we choose "fakefunction".

NOTE: You want to be careful when you choose the name because there might already be a function by that name. If you want to find out if the name already exists visit the function index at php.net.

After you choose a name you need to decide if there are going to be any arguments passes to the function. An argument would be like a variable:

PHP-Code:

function does_user_exits($username) { run code to see if "username" exists } ?>

If you didn't pass the function does_user_exits() the variable "$username" how would you be able to see if that user existed?

A real-life example is trim():

PHP-Code:

$newstring = trim($string); ?>

If you didn't pass the function the name of the string too trim, what good would it do?

Finally, you add all of the code that will make up the function and then when your done you let PHP know your finished by typing a closing " } ". So now lets get to it!


##########################################################
## PART 2: Making a function
##########################################################

Open up SciTE or Notpad and type this into it:

PHP-Code:

function print_my_name() { echo "My name is David"; } print_my_name(); ?>

Note: I hope you changed David to your name...

Save the file as "printname.php" and upload it to your server. When you run "printname.php" you will see:

Code:

My name is David

Now then, you have just made your first function! Give yourself a pat on the back.
When the function print_my_name() is called it will run the code inside it. However, the only code is one line that prints out "My name is David".

Also, realize that after you make the function you have to call it! Just because you wrote the function doesn't mean that it will run. You have to now tell PHP when and where you want that function to run. In this case we told the computer to run the function right away:

PHP-Code:

print_my_name();

However, we could have waited until later in the script or even not run it at all!

##########################################################
## PART 3: Making a USEFUL function
##########################################################

Open up SciTE or Notepad and type the following into it:

PHP-Code:

function print_out_file($filename) { // Read file into array called $contents $contents = @file($filename); //if the array isn't empty (a.k.a. there was something in the file!) if (count($contents) > 1) { // Loop through the array and print out the line (with a line-break ( )) foreach ($contents as $row) { echo "$row "; } } else { // if $contents is equal to '0' or there was an error opening the file print: echo "File is empty or doesn't exist!"; } }

You can now save it as "file_functions.php" and upload it to your server. Next, start a new document and type this into it:

PHP-Code:

include ("file_functions.php"); print_out_file("testfile.txt");

Then save it as "printfile.php" (or whatever you want to call it) and upload it to your web server. Open your browser and go to "printfile.php". When you do you should get a nice error message saying:

Code:

File is empty or doesn't exist!

Can you figure out why? (Hint: read the error )


That's right! There IS no file called "testfile.txt"! So start your editor one more time and type five lines of anything you want into it:

Code:

This is line One. This is line Two. This is line Three. This is line Four. This is line Five.

Save it as "testfile.txt" then upload it to your web server. Once all three are on the internet (and in the same directory) open your browser and go to "printfile.php".


This time you have passed a file that actually exists to your function and it was able to open the file. So you should see this on the screen:

Code:

This is line One. This is line Two. This is line Three. This is line Four. This is line Five.

Now then, you have just made your first useful function! Give yourself another pat on the back, lol.
I trust you understand the code inside the function. If not, you need to read the past lessons . So onto the description of what is going on here:

First we made the file "file_functions.php" and we created a function called "print_out_file()". That function is only passed one argument - the name of the file to print out.

Then we made "printfile.php" which was a simple two line script that included "file_functions.php" and then ran the function. Because the function is in it's own file we can now use print_out_file() in any script we want simply by including the file "file_functions" and then typing print_out_file().


SIDE NOTE:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

By making the function and putting it into its own file we can keep our code clean. Imagine if we had a large function in the middle of a good-sized script. If we were trying to find an error it would be a pain to search the whole document.

Note: Just for those who would like to see it - here is what the file would look like if we stuck the function into "printfile.php":

PHP-Code:

function print_out_file($filename) { // Read file into array called $contents $contents = @file($filename); //if the array isn't empty (a.k.a. there was something in the file!) if (count($contents) > 1) { // Loop through the array and print out the line (with a line-break ( )) foreach ($contents as $row) { echo "$row "; } } else { // if $contents is equal to '0' or there was an error opening the file print: echo "File is empty or doesn't exist!"; } } print_out_file("testfile.txt");

While this would work just fine; like I said, by sticking the function in it's own file it keeps the code clean for larger scripts.

Plus, by having the function in it's own file any script on our server can use the function by just including the file!

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::


Ok, that's it! Try making your own functions for things you do often! For example I put the entire HTML Header and Footer for my pages into two functions called page_header() and page_footer() then have all of my pages just include the function.php page and then I run page_header() and page_footer():

PHP-Code:

include("functions.php"); page_header(); // Main content here page_footer();

Lesson 20 - Including files with PHP's Include function

Now that we know how to wield the POWER of PHP's file API lets kill some orc's!
uh.. wait...that's not, *quite* right...

*ahem

Actually, what I was going to say is:
Now that we know how to read and write to files let me show you how to include other PHP files in your scripts.

If you have ever coded in another language I am sure your saying "it's about time!". After all, including other files will become the basis for many of your scripts. If you think about it, it makes more sense to have a several different files that you can include if you want - as appose to having ONE huge file.

There are two main functions for including files into your script:
include();
require();
Basically, the only difference is that if include can't get the file you will get an error, but the script will still run. However, if require can't get the file your script will terminate with an error. Therefore, it is better to use include if you still want your script to keep going even if the file can't be opened.


So to get you used to including files I want you to open your text editor and type the following into it:

PHP-Code:

$number = 1; if ($number == 0) { include("somedumbfile.php"); } if ($number == 1) { include("saying.php"); } echo "The included file says: "; echo $saying; ?>

Save the file as "includetest.php" and then up load it to your web server. If you run it you will get an error saying that there is no variable called "$saying", so... open back up your editor and make a new file called "saying.php". Enter this into it:

PHP-Code:

$saying = "There are ten kinds of people in the world - those that understand binary, and those that don't."; //Note: if you don't get the joke just enter something else...sheesh! ?>

Then upload this file to your web server and run "includetest.php" again. This time you will see "includetest.php" print out what you entered in "saying.php"! That is because the variable $saying was passed from the included file to "includetest.php":

Code:

The included file says: There are ten kinds of people in the world - those that understand binary, and those that don't.

Now I am sure that you are starting to get ideas from this. I know that I was amazed when I first learned you could include files in PHP.

Honestly, I have a confession to make - I learned PHP just for the include function!

Up until then, I had only been using HTML for my sites. It was a MAJOR pain to open each and every file and change the navigation bar every time I added something. However, with PHP you can just make ONE navigation file that has the nav bar and then include it into every page that needs the nav bar. That way you only have to change "nav.php" and every page on your site will have the new nav bar! Trust me, this is an AWESOME feature if you have a site with 100+ pages - as there is no way that you could open all of them up and change the nav bar!

This site is a perfect example of using include to build a web page.
There is a header, content, and footer file on my server. In order to show this page PHP runs a script that looks like this:

PHP-Code:

include("header.php"); include("content.php"); include("footer.php"); ?>

Now this is very different than what this page actually does, but the idea is the same.

Every time I open "header.php" and add a new thing to the banner area above all of the pages on my site then have the new code because they all include "header.php" before they show you the page.


So, now that you know about include lets build a simple page that is made up of different files that all come together to make what you see! Open up SciTE or Notepad and enter this into it:

PHP-Code:

echo " Here is the Header: "; include("header.php"); echo " This is the Content area: "; include("content.php"); echo " Here you could put the copyright: "; include("footer.php"); echo " "; ?>

Then save it as "webpage.php".
Now you need to create three more files; "header.php", "content.php", and "footer.php". Create each one and put anything you want into them. Then take all four files and upload them to your web server. If you need ideas look at this:

PHP-Code:

// Header.php echo "this is the header"; ?> // Content.php echo "This is the stuff that the whole page is actually about!"; ?> // Footer.php echo "&copy 2006 learnphpfree.com"; ?>

After you have uploaded website.php, header.php, content.php, and footer.php; run website.php. You will see something like this:

Code:

Here is the Header: this is the header This is the Content area: This is the stuff that the whole page is actually about! Here you could put the copyright: ? 2006 learnphpfree.com

(with a border around each part!)

Well, this is nothing special so let me show you something more useful. You could change the "website.php" code to this:

PHP-Code:

include ("header.php"); /////////////////////////////////////////////////////////////// $pagetoget = $_POST['pagename']; //Get the page that the user wants to see and put it in a var. //Now we find the page that the user wanted to see and "include" it. if ($pagetoget == "help") { include ("help.php"); } elseif ($pagetoget == "about") { include ("about.php"); } elseif ($pagetoget == "help") { include ("about.php"); } else { // If no pages match just show the home page. include ("home.php"); } /////////////////////////////////////////////////////////////// include ("footer.php"); ?>

In this example, website.php is passed a page name and the script includes the corresponding page. In this way you can actually just use ONE page to build and print all of the pages on your site! That way you don't have to worry about re-coding the WHOLE page for every page you have. Just make part of a page with the content on it and BOOM! let you main page grab the "header" and "footer" pages and put it all together.

Like always, if you want to learn more about include() or require() stop by php.net and then do a search on Google. Later!

Lesson 19 - Reading External Files into PHP - Part 4

Now that we know how to read and write to files, lets make something. Do you remember the simple "Chat System" from lesson 11? Lets turn it into a REAL chat system. (One that won't forget the messages!) So open SciTE (or Notepad) and type the following into it:
(Do NOT copy and paste the code! You need to learn it and typing it will help you to focus on it.)

PHP-Code:

///////////////////////////////////////////////////////////////////////// // Put the page layout in some strings: $filename = "chatfile.txt"; $startofpage = " "; $endofpage = " "; $form = " "; /* Now that we have a basic layout in a few strings we can move onto writing the code. */ ///////////////////////////////////////////////////////////////////////// if (isset($_POST['text'])) { /////////////////////////////////////// // Clean the text from HTML, PHP, and Cussing $text = strip_tags(trim($_POST['text'])); $badwords = array("sucks", "damn", "#!@?", "ASP"); $text = str_replace($badwords, "****", "$text"); $text .= ":END:\n"; /////////////////////////////////////// //////////////////////////////////////////////////////////// // We create the filehandle and set the mode to write ("a") $filehandle = fopen($filename, 'a') or die('Could not open the file'); if (!fwrite($filehandle, $text)) { echo "Could not add the post to the file!"; } else { echo " Added your comment! "; } fclose($filehandle); //////////////////////////////////////////////////////////// } ///////////////////////////////////////////////////////////////////////// // Now we need to open the file back up and pull out all of the comments. $contents = file($filename); if (count($contents) >= 1) { foreach ($contents as $row) { $comment = str_replace(":END:", " ", $row); $comment = str_replace("\n", " ", $comment); $usercomments .= $comment; } } ///////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////// if (!isset($usercomments)) { $usercomments = "There are no comments"; } $usercomments = " ". $usercomments. " "; echo $startofpage; echo $usercomments; echo $form; echo $endofpage; ?>

When you are done typing in that code, save it as "chatscript.php" and upload (FTP) it to your web server. Before you go on, try to just read through the code and see if you can follow the logic. Then run it a couple of times and post different things and see if you can understand most of the code.




Now here is the break-down of the script:

1) We start by putting the layout, filename, and comment form into strings so that we can keep our code clean by just using a string instead of putting all of that code into our loops...

PHP-Code:

///////////////////////////////////////////////////////////////////////// // Put the page layout in some strings: $filename = "chatfile.txt"; $startofpage = " "; $endofpage = " "; $form = " "; /* Now that we have a basic layout in a few strings we can move onto writing the code. */ ///////////////////////////////////////////////////////////////////////// ?>

2) Next we check to see if anything was POSTED to the page. This will evaluate to FALSE if nothing has been "Posted" to the page (and FALSE means that it won't run).
However, after something has BEEN posted then this will be TRUE and the loop will run:

PHP-Code:

if (isset($_POST['text'])) { /////////////////////////////////////// // Clean the text from HTML and PHP So that no one messes up the page! // trim() deletes the white spaces at the end of a string. $text = strip_tags(trim($_POST['text'])); // Make an array called $badwords that has bad words as each array element $badwords = array("sucks", "damn", "#!@?", "ASP"); // Then we replace all of the matching bad words in $text with "****" $text = str_replace($badwords, "****", "$text"); // We need to add something that we can split the text with: $text .= ":END:\n"; /* The ( . ) char before the "=" means "add to $text", and the "\n" is the text version of pressing "Enter" on the keyboard. We added the "\n" so that each comment will be on a different line in the file. */ /////////////////////////////////////// ?>

After we determined that a message was posted we then clean the message of all of the PHP, HTML, and Cussing that could ruin our conversation or point to spam sites. Finally we add ":END:\n" to the end of the text so that we can tell each comment apart.



3) Now that we have the cleaned message ready, we open the file and write the new text to it:

PHP-Code:

//////////////////////////////////////////////////////////// // We create the file handle and set the mode to write ("a") $filehandle = fopen($filename, 'a') or die('Could not open the file'); // Now we take the new comment and put it into the text file. if (!fwrite($filehandle, $text)) { echo "Could not add the post to the file!"; } else { echo " Added your comment! "; } fclose($filehandle); //Then close the file //////////////////////////////////////////////////////////// } ?>

For those of you who don't know loops (read the lessons on them) I am going to write the [if][/i] loop in English:

PHP-Code:

if (!fwrite($filehandle, $text)) { echo "Could not add the post to the file!"; } else { echo " Added your comment! "; } // Would look like this to a human: if You(CANNOT write the $text to the file) { tell the user: "Could not add the post to the file!"; } otherwise if you COULD write to the file { tell the user: " Added your comment! "; } ?>

4) Now that we just finished checking to see if a message was posted it is time to show all of the messages in the file:

PHP-Code:

///////////////////////////////////////////////////////////////////////// // Now we need to open the file back up and pull out all of the comments. // Read file into array called $contents $contents = file($filename); //if the array isn't empty (a.k.a. there was something in the file!) if (count($contents) >= 1) { // Loop through the array and after fixing each row // add the fixed array element to the $usercomments string foreach ($contents as $row) { /* First we need to separate the comments by Replacing any :END:'s with a . */ $comment = str_replace(":END:", " ", $row); /* Then we replace any "newline" chars (\n) with a so that if someone presses Return in the textarea the new line will show up right when we show the comment. */ $comment = str_replace("\n", " ", $comment); // ADD the finished array element to the $usercomments // string and start the loop over: $usercomments .= $comment; } } ///////////////////////////////////////////////////////////////////////// ?>

In English the if loop would read:

PHP-Code:

if the(number of array elements in "$contents" is greater than or equal to 1) { do this: foreach (of the elements in "$contents", put that element in a var called "$row") { ...[do the code HERE].... } Now that we are done with that array element empty "$row" and go to the next element. } ?>

5) After we have grabbed all of the comments out of the file we just need to make another "safety" loop that will print "There are no comments" if for some reason the file is empty:
(Note: This is just a nice thing to add)

PHP-Code:

/* If $usercomments has not been set then there are no comments! Because the loop was never run (it equaled FALSE) and so the variable "$usercomments" was never made! so lets make the $usercomments string and put a warning sentence in it. */ if (!isset($usercomments)) { $usercomments = "There are no comments"; } ?>

6) Finally, We add some formatting to the comments so that they stay in a box. and print everything to the screen!

PHP-Code:

// Lets add some formatting to the comments: $usercomments = " ". $usercomments. " "; ///////////////////////////////////////////////////////////////////////// // Now just show the finished result: echo $startofpage; echo $usercomments; echo $form; echo $endofpage; ///////////////////////////////////////////////////////////////////////// ?>

Wow, good job! You finished it! (It took me ALL DAY to write this lesson!) Doesn't it feel good to have made something cool?!

Now I hope you followed the code and can now understand all of the loops and functions in it (If you are having trouble review the past lessons). If you just take code in small pieces it is a LOT easier to understand, so don't let anything intimidate you!


(Note: You can download the script at the bottom.)

##########################################################
## Another Chat Script!
##########################################################

Well, for those of you who want to make a more advanced chat system here is another script that allows you to enter your name, email, message, and the date! Now that you have finished the above script you shouldn't have very much trouble understanding this one.

Also, there are a couple of NEW functions in this script - don't panic - just stop by php.net, type the name of the function (such as "list()")in the search box, and they will show you what it is and tell you about it. Have a great day!

PHP-Code:

// lets put our file into an variable .... $file = 'chatfile2.txt'; // Max size in bytes that you want the Chat file to reach! $filesize = 400000; ////////////////////////////////////////////////////////////////// /* This is the code that checks to see if something was "POST" 'ed and that it is a real message. Also clears out any code that may have been entered by a user (strip_tags(); ). */ if ((isset($_POST["name"])) && (isset($_POST["msg"])) && (isset($_POST["email"])) && ($_POST["name"]!="") && ($_POST["msg"]!="") && ($_POST["email"]!="")) { $email = strip_tags($_POST["email"]); $msg = strip_tags($_POST["msg"]); $name = strip_tags($_POST["name"]); $email = str_replace("|", "", $email); $msg = str_replace("|", "", $msg); $name = str_replace("|", "", $name); $date = date("F j, Y, g:i a"); /* If the code is good then enter it into the file along with the other messages from other users. Remember that the variable "$file" means the file you are writing too. */ $content = "$name|$email|$msg|$date\n"; // Add a newline char ("\n") so that each comment is on a new line. if (filesize($file) < $filesize) { $handle = fopen($file, 'a'); fwrite($handle, $content); fclose($handle); echo " Your Message Has Been Added "; } else { echo "File Is to Full"; } } // End the IF posted loop... ////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////// /* This is the code that opens the file and organizes and prints out the messages that other people left. */ $handle = fopen($file, "r"); if (!(filesize($file) == 0)) { while (!feof($handle)) { $line = fgets($handle, 5096); @list($user_name, $user_email, $user_msg, $user_date) = explode("|", $line); echo ' Name: '. $user_name. ' Email: '. $user_email. ' Message: '. $user_msg. ' Date: '.$user_date. ' '; } } else { echo "File is empty"; } fclose($handle); //////////////////////////////////////////////////////////////////// /* This is the "FORM" at the bottom of the page used to "Submit" messages to this file */ echo ' Your Name: Your Email: Message: Enter Text '; ?>

You can download both scripts here:
Lesson 19 Files