MySQL

An Introduction to Web Databases and Storing Information

IMPORTANT NOTE:
This tutorial assumes you have knowledges of HTML and PHP. MySQL is usually used along with PHP and the same goes for HTML. While MySQL has its own language, websites often combine it with PHP so that the pages can interact with the databases. so if you have not yet learned PHP ... go learn it!


Now then...What is MySQL? SQL stands for "Structured Query Language" which is the standard language used to interact with databases.

MySQL is basically an Excel spreadsheet. It's made up of tables. Tables have rows and columns. The columns in the tables specify the "properties" - if you will - of a particular table. For example, a table called "users" might have properties like "userID", "firstname", "lastname", "email" and "password."

The rows in the tables specify all the inputs - so for the table called "users" each row would be a new user who each would have the properties specified in the columns.

The columns have different types, like how there are different variable types in Java. Here are a view:

  • varchar: a bunch of characters/string with a max limit of 255
    • good for entries like "name" or "email" ... short strings
  • text: a larger bunch of characters/string with a much higher max limit
    • good for long text entries and long strings
  • int: an integer number
  • datetime: a date-time string-like variable in the form yyyy-mm-dd hh:mm:ss
    • like 1996-02-21 03:29:34
    • representing dates/time as a number allows for searching by date/time

You have to specify the size of these columns. For numbers, specify how many digits it can have. For strings, how many characters can it have? i.e. the field "email" - which is a varchar - might allow only 30 characters max.

There's something called a primary key and it allows faster searching through the database. Each entry (row) should have its own unique key value. Therefore if you want to select something from the database, you can do it quickly by just jumping to the entry with a particular key. So for this table "users" aside from the "name" and "email" columns, you should have a column called "userID" which is an integer and gives a unique user ID number to each entry. Then you can access a user's information through his/her ID number!

Along with primary key is a feature called auto-increment which will automatically increment a number everytime you have a new entry. You'd want "userID" to be auto-increment, so everytime you add a new user to your table, he/she gets the next ID number.

 Part 1: The Syntax [back to top]

The SQL syntax is very straight-forward. Here's the code to create our "user" table.

CREATE TABLE `users` (
`userID` SMALLINT( 4 ) NOT NULL AUTO_INCREMENT ,
`firstname` VARCHAR( 45 ) NOT NULL ,
`lastname` VARCHAR( 45 ) NOT NULL ,
`email` VARCHAR( 45 ) NOT NULL ,
`password` VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( `userID` )
) TYPE = MYISAM ;

This creates a table called "users" and then in the parenthesis it specifies all the columns. The first is "userID" which is a small integer with a maximum of 4 digits (so it can go up to 9,999), it can't be null and it automatically increments itself with each entry. The second is "firstname" which is a varchar of maximum size 45 and also can't be null. At the end, it specifies the primary key as "userID". Outside of the parenthesis, the "type = MYISAM" is just standard code. MYISAM is the default storage engine...but don't worry about this.

Now that we have our "users" table, we can insert a new entry by writing:

INSERT into users (name,email) values ('eric','fishere@seas.upenn.edu');

This is pretty straight forward. We're inserting into our "users" table two columns - name and email - and giving them the values of "eric" and "fishere@seas.upenn.edu."

If you want to update an entry, having a primary key comes in handy.

UPDATE users set password='mypass' where userID=1;

This is just too darn easy to have to explain. Deleting things from the database is just as easy!

DELETE from users where userID=1;

Note that deleting a user from the database won't change the other userIDs. So if you had userIDs 1,2,3,4,5,6 and you deleted where userID=3, then the next time you add an entry, that userID would still be 7. Then you'd have 1,2,4,5,6,7.

 Part 2: Querying the Database [back to top]

There are A LOT of ways to query the database. Here's a basic one:

SELECT * from users;

...which just selects everything from the "users" table.

SELECT * from users where userID = 1;

...which just selects all the column data from that one entry where the userID = 1.

SELECT 'password' from users where email='fishere@seas.upenn.edu';

...which just selects the value of the column "password" of the entry where the email = "fishere@seas.upenn.edu." Something like this is useful for if you want to make a login page where the user provides his/her email and password. You query the database for an entry that has that email, get the password and check if the password in the database matches the password the user has just typed. If it does, then you can let him/her login. Otherwise you can throw an error.

Want to select something from the database where some entry is LIKE something? i.e. selecting from the "users" table everyone whose name has the letters "jim" in it?

SELECT * from users where name like '%jim%'

...will get all the users in the database whose name has the letters 'jim' in it. The % before 'jim' cuts off any other text that comes before and the % after 'jim' cuts off anything after.

You can have multiple "where" fields.

SELECT * from messages where receiverID = 1 and rDelete = 0;

...might be how you get your incoming messages - the ones you haven't deleted. If you delete a message, then you might set "rDelete" to 1 - but the message would still exist in the database if the sender has not deleted it from his/her outbox. Hence the reason why it would be good to have two delete fields, rDelete (receiver delete) and sDelete (sender delete). Then you'd only actually delete the message from the database when both rDelete and sDelete are 1.

ORDERING search results:

SELECT * from users Order by name;

...would get all the users in the "users" table and order them by their name. So if your "users" table consisted of four entries: Eric, Mary, Phillip and John, then ordering by name would return the users in the order: Eric, John, Mary, Phillip.
You can specify if you want the order to be ASCending or DESCending.

SELECT * from users Order by name DESC;

...returns the users from Z to A: Phillip, Mary, John, Eric. Ascending is the default.

 Part 3: Connecting to the Database [back to top]

PHP offers a lot of built-in functions to connect to a MySQL database. First you connect to the server that has the database. Then you access the database. Here's a standard to connect to a sample database located on the "localhost" server. The sample username is "fishere" and the password is "mypass." The database I want to access is called "mydatabase":

<?php
$link = mysql_connect("localhost", fishere,mypass) or die(mysql_error());
mysql_select_db("mydatabase");

The first line basically either connects you to the server or prints out an error and dies. Then the second line connects you to the database (if you were able to connect to the server).

NOW we can start querying the database.

$result = mysql_query(“Select * from users where userID=1");

This will give us a variable called $result which is a MySQL result. We can now convert it into an array of all the values from the columns (i.e. name, email, password, etc.):

$row = mysql_fetch_assoc($result)

Now $row["name"] = the name of the person whose userID = 1, $row["email"] = the email of that person, $row["password"] = the password, etc.
The cool thing about mysql_fetch_assoc is that it returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead. In other words, our variable $row is equal to the first result. If we called mysql_fetch_assoc($result) again, we'd get the second result (if there was one), etc.

 Part 3A: Using PEAR to Connect to Your Database [back to top]

Let's talk about PEAR DB

...an easy way to connect to your MySQL database and retrieve information.

Now, you could navigate through the documentation and learn on your own, but I'm going to point out the important things to note. Basically, Pear DB is a DB.php page with a lot of built-in functions to connect to a database.

THE STEPS:

  • Step 1: Create a new PHP page that requires 'DB.php' and creates a new class called Connect (or whatever you want)
  • Step 2: Create instance variables for the database host, databasename, username and password. These need to have "var" before them since you are creating them as instance variables. So, an example is "var $myVariable = ". Then, initialize these to the correct values for your website. Each of these variables are going to be Strings - so USE QUOTES! AND, recall from the PHP tutorial, whenever you refer to an instance variable of a class, you have to write $this->variable as opposed to just $variable.
  • Step 3: To Connect to the database, you need to create a function -- call it connect() -- and in it, we will use the connect() function.

    So here's an example:
    function connect() {
          $dsn = "mysql://eric:13s28x@myserver.net/database1";
          $this->conn = DB :: connect($dsn,true);
    }
    This method takes two parameters: The first is the DSN, which is in the form: mysql://username:password@host/databasename (obviously replacing each of those with the correct instance variable. The second parameter is true - don't worry about this.
  • Step 4: Next you need to create a function that determines whether or not an SQL command throws an error or not -- and if there is no error, then it executes it. Basically, name the function whatever you want and give it a parameter that will be your SQL query string. Then, in the method, create a new variable and assign it to $result = $this->conn->query($sql) where $sql is your parameter variable.
    • Add an "if" statement to check if it throws an error. Use this:
      if (DB :: isError($result)) {
            trigger_error("Invalid query: ".$result->getMessage()." ---> ".$result->getDebugInfo(), E_USER_ERROR);
      }
      else return $result;

    • Note that $result is a MySQL OBJECT. The function queried the database and returned an object containing the results.

  • Now that you've got all that setup, you need to create a function that reorganizes the MySQL object into something you can use (like an array) and returns something to you. Call this method "query" or something to that extent, and give it an SQL string parameter called $sql or something. Then, within the method, create a new array variable -- call it $myArray. Then, use the method you just wrote to check and make sure there are no errors with your $sql String, and set the results of that method call to a new variable:
    $result = $this->testQuery($sql); ...for example.
  • The built-in functions include a function that returns the number of rows in your result. So, for example, if you're selecting all the users of a database whose name contains a certain String, then you would get back a certain number of results (rows). So, you need to check that you actually get something from the database -- that is, you need a conditional to check that the number of rows returned is greater than 0. To do this, it's just what you might think:
    if ($result->numRows() > 0) {
    Then, inside this conditional, create a counter variable, call it $x, initialized at 0. This will be incremented as we go through each slot in our array as we add to it. After we enter an item into our array, we want $x to increase so we go to the next slot.

    Now use a WHILE loop and mimic what was discussed above about fetching each row. The built-in "fetchRow" method takes the mode as a parameter. The mode is DB_FETCHMODE_ASSOC. Recall that from above. So our loop says "While there's a row to fetch..."
    while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
  • Use the special PHP foreach loop now. foreach ($row as $column => $value) { --that is, $column is each column in your database table and $value is the value in that column for a given row. So, for example, a column might be a user's name, and a particular row might say the name is "John." What you want to do is fill your newly-created array with this array value so your new array becomes a double array. That is, each item in the array is an array in itself, for each row.

  • So, $myArray[0] is the first result row, $myArray[1] is the second result row, etc. And in each row, there are an infinite number of column fields. $myArray[0]["name"], $myArray[0]["email"], etc. --- whatever you specified when you created the database table.

  • So in your foreach loop, you have to write:
    $data["$column"] = $value;
    $myArray[$x] = $data;
  • Then close the "foreach" loop with the curly bracket, and increment our counter variable, $x, by 1. Then close the while loop, and then close the conditional. Now, return $myArray; and you're done.

NOW that your Connect.php page is done, you can create a data-editing page to house all of your database functions, such as "getUserInfo()" or whatever. Make sure that this page includes your Connect.php page and that it has an instance variable that equals new Connect(), referring to a new Connect object (because, if you recall, our Connect.php page constructs a Connect object).

And there you go. Now you can do everything.

 Part 4: MySQL and Forms [back to top]

You already know about HTML forms and how to deal with them using PHP. Involving the database just adds a step when you check if you've submitted the form:

<?php if (isset($_POST["trySave"]) && ($_POST["trySave"]==1)) {
   @ extract($_POST);
   if ($name!="" || $email!="") {
      mysql_query("Insert into users (name,email) values ('$name','$email')");
      $saved=1;
   }
   else {
      $error = "Name or Email is Not Set";
   }
}

 <form method="post" action="send.php">
<input type="hidden" name="trySave" value="1">
<input type="text" name="name" />
<input type="text" name="email" />
</form>

A piece of code that (a) checks if we've submitted the form, (b) checks if we've actually filled out the fields, (c) inserts into the database our name and email.

We could add a piece of code somewhere else on this PHP page that checks if $saved == 1, and if it does, then we could have a message displayed that says "Changes Saved" or something. In the same way, we could also check if $error!="" and echo the error if there is one.