TheCodingTutorials: Interacting with a MySQL Database Table in PHP

Now that you can create HTML forms and receive HTTP POST data and properly set up a table in a MySQL database, you are ready for the next step: interacting with this MySQL database through the use of an HTML form. All that's left for you to know and do is run MySQL queries from PHP code. Here's the script we're going to be looking at in this tutorial:

http://thecodingwebsite.com/tutorials/demos/phpmysql

It is a rather big one... It allows for the following functionality (in this order):

Show an individual column that's in the table.
Show all of the columns in the table.
Add a new column to the table.
Modify an existing column in the table.
Remove an individual column from the table.
Remove all of the columns from the table.

Get familiar with how this script works, what it does, etc. We're going to be taking a look at it in its entirety.

Just as a quick reminder of what we're currently dealing with, we've had a database set up (along with a table inside it) ready for this script. The database itself needed the following information:

The MySQL host - this might be something like "mysql12345.mywebsite.com".
The database - this might be something like "asgsd34g34_database".
The user - this might be something like "asgsd34g34_user".
The password - this is probably going to be set by you.

The table inside it had 7 rows of data:

Account ID number (for indexing purposes)
Username
Password
Real name
Register date
Last login date
A small piece of HTML code for every users' introduction of themselves

and these were the row names used in the database:

id
username
password
name
registerDate
lastLoginDate
introductionCode

With all of that established, let's start looking at the code... I'm providing you with this code both as a text file and embedded within the tutorial itself, as it is relatively large. Here's a download link for the script (unzip it):

www.thecodingwebsite.com/tutorials/demos/phpmysql/index.zip

<html>

<head>

</head>

<body>

<a href="index.php">Click here to refresh the page.</a>[br/][br/]

<?php

function receivePOST($variableName)
{
 return htmlspecialchars($_POST[$variableName]);
}

$action = receivePOST("action");

if (strlen($action) && ($action == "Show" || $action == "Show all" || $action == "Add" || $action == "Modify" || $action == "Remove" || $action == "Remove all"))
{
 $validAction = true;
}
else
{
 $validAction = false;
}

if ($validAction)
{
 mysql_connect("mysql12345.mywebsite.com", "asgsd34g34_user", "nottheactualpassword") or die(mysql_error());
 mysql_select_db("asgsd34g34_database") or die(mysql_error());
 
 switch($action)
 {
  case "Show":
   $id = receivePOST("id");
   
   if (strlen($id))
   {
    $account = mysql_query("SELECT * FROM accounts WHERE id = $id LIMIT 1") or die(mysql_error());
    
    if ($row = mysql_fetch_assoc($account))
    {
     print_r($row);
     echo "[br/]";
    }
    else
    {
     die("ERROR: no column with this ID ($id) exists!");
    }
   }
   else
   {
    die("ERROR: missing information for displaying!");
   }
   break;
  case "Show all":
   $accounts = mysql_query("SELECT * FROM accounts") or die(mysql_error());
   
   while ($row = mysql_fetch_assoc($accounts))
   {
    print_r($row);
    echo "[br/]";
   }
   break;
  case "Add":
   $id = receivePOST("id");
   $username = receivePOST("username");
   $password = receivePOST("password");
   $name = receivePOST("name");
   $introCode = receivePOST("introCode");
   
   if (strlen($id) && strlen($username) && strlen($password) && strlen($name) && strlen($introCode))
   {
    mysql_query("INSERT INTO accounts SET id = $id, username = \"$username\", password = \"$password\", name = \"$name\", registerDate = now(), lastLoginDate = now(), introductionCode = \"$introCode\"") or die(mysql_error());
    
    echo "New column successfully added![br/]";
   }
   else
   {
    die("ERROR: missing information for adding!");
   }
   break;
  case "Modify":
   $id = receivePOST("id");
   $name = receivePOST("name");
   $introCode = receivePOST("introCode");
   
   if (strlen($id) && strlen($name) && strlen($introCode))
   {
    $account = mysql_query("SELECT * FROM accounts WHERE id = $id LIMIT 1") or die(mysql_error());
    
    if ($row = mysql_fetch_assoc($account))
    {
     mysql_query("UPDATE accounts SET name = \"$name\", lastLoginDate = now(), introductionCode = \"$introCode\" WHERE id = $id LIMIT 1") or die(mysql_error());
     echo "Column successfully updated![br/]";
    }
    else
    {
     die("ERROR: no column with this ID ($id) exists!");
    }
   }
   else
   {
    die("ERROR: missing information for modification!");
   }
   break;
  case "Remove":
   $id = receivePOST("id");
   
   if (strlen($id))
   {
    $account = mysql_query("SELECT * FROM accounts WHERE id = $id LIMIT 1") or die(mysql_error());
    
    if ($row = mysql_fetch_assoc($account))
    {
     mysql_query("DELETE FROM accounts WHERE id = $id LIMIT 1") or die(mysql_error());
     echo "[br/]";
    }
    else
    {
     die("ERROR: no column with this ID ($id) exists!");
    }
   }
   else
   {
    die("ERROR: missing information for removal!");
   }
   break;
  case "Remove all":
   mysql_query("DELETE FROM accounts") or die(mysql_error());
   echo "All columns removed successfully![br/]";
   break;
 }
}
else
{
 echo "No (valid) action submitted![br/][br/]";
}

?>

[br/][br/]

<form method="POST" action="#">
ID: <input type="text" name="id" value="0"/>
<input type="submit" name="action" value="Show"/>
</form>

[br/][br/]

<form method="POST" action="#">
<input type="submit" name="action" value="Show all"/>
</form>

[br/][br/]

<form method="POST" action="#">
ID: <input type="text" name="id" value="0"/>[br/]
Username: <input type="text" name="username" value="user123"/>[br/]
Password: <input type="text" name="password" value="password123"/>[br/]
Name: <input type="text" name="name" value="Bill"/>[br/]
(Register date and last login date will be set to "now()".)[br/]
Introduction code: <input type="text" name="introCode" value="<b>This is a very, very lame introduction</b>"/>[br/]
<input type="submit" name="action" value="Add"/>
</form>

[br/][br/]

<form method="POST" action="#">
ID of existing column of data: <input type="text" name="id" value="0"/>[br/]
New name: <input type="text" name="name" value="Joe"/>[br/]
(Last login date will be set to "now()".)[br/]
<input type="hidden" name="introCode" value="<h3>This introduction has been modified... A hidden input was used so that normal users wouldn't see it! :)</h3>"/>[br/]
<input type="submit" name="action" value="Modify"/>
</form>

[br/][br/]

<form method="POST" action="#">
ID of existing column of data: <input type="text" name="id" value="0"/>[br/]
<input type="submit" name="action" value="Remove"/>
</form>

[br/][br/]

<form method="POST" action="#">
<input type="submit" name="action" value="Remove all"/>
</form>

</body>

</html>

You'll notice that in the code above I have a bunch of "[br/]"'s everywhere. This is because if I put "<br/>" instead, it messes up the color coding.

The first thing you should take note of in regards to this code is that the refresh link at the top and the forms at the bottom will display no matter what (unless there's an error). The first (and possibly only) POST value retrieved is "action". This is because the 6 forms at the bottom of the page all have a submit button with a name of "action" and a value of "Show", "Show all", "Add", "Modify", "Remove", or "Remove all":

$action = receivePOST("action");

if (strlen($action) && ($action == "Show" || $action == "Show all" || $action == "Add" || $action == "Modify" || $action == "Remove" || $action == "Remove all"))
{
 $validAction = true;
}
else
{
 $validAction = false;
}

if ($validAction)
{
 //MySQL Database interaction code here.
}
else
{
 echo "No (valid) action submitted![br/][br/]";
}

During the initial page load (as well as when the refresh link is clicked and therefore no data is sent to the server then), no value for "action" will be specified. The PHP code checks to make sure that an action is actually specified and it is a valid (expected) one before doing anything involving the database. If not, it tells the user and then skips past the rest of the PHP code on to the rest of the page (the HTML forms and such).

Before looking at the MySQL queries and surrounding code, let's first glance down at the HTML forms so we get an idea of what's being submitted. Each of the 6 actions that can be performed not only have their own "action" value, but they also each have their own form:

<form method="POST" action="#">
ID: <input type="text" name="id" value="0"/>
<input type="submit" name="action" value="Show"/>
</form>

[br/][br/]

<form method="POST" action="#">
<input type="submit" name="action" value="Show all"/>
</form>

[br/][br/]

<form method="POST" action="#">
ID: <input type="text" name="id" value="0"/>[br/]
Username: <input type="text" name="username" value="user123"/>[br/]
Password: <input type="text" name="password" value="password123"/>[br/]
Name: <input type="text" name="name" value="Bill"/>[br/]
(Register date and last login date will be set to "now()".)[br/]
Introduction code: <input type="text" name="introCode" value="<b>This is a very, very lame introduction</b>"/>[br/]
<input type="submit" name="action" value="Add"/>
</form>

[br/][br/]

<form method="POST" action="#">
ID of existing column of data: <input type="text" name="id" value="0"/>[br/]
New name: <input type="text" name="name" value="Joe"/>[br/]
(Last login date will be set to "now()".)[br/]
<input type="hidden" name="introCode" value="<h3>This introduction has been modified... A hidden input was used so that normal users wouldn't see it! :)</h3>"/>[br/]
<input type="submit" name="action" value="Modify"/>
</form>

[br/][br/]

<form method="POST" action="#">
ID of existing column of data: <input type="text" name="id" value="0"/>[br/]
<input type="submit" name="action" value="Remove"/>
</form>

[br/][br/]

<form method="POST" action="#">
<input type="submit" name="action" value="Remove all"/>
</form>

The "Show all" and "Remove all" forms do not consist of anything other than the submit button itself, as that is all that the server needs to know (that it was clicked).

The "Show" and "Remove" forms only have one other input that is used to specify the column ID. Obviously since these two will be dealing with an individual column, they will need to know the ID of the column before they can do anything.

Lastly, we have the "Add" and "Modify" forms, which are slightly more complicated. I've already covered what these inputs are and how their name and value properties work in relation to the PHP code in another tutorial.

There is one interesting input I would like to point out, though: in the "Modify" form there is an input with a type of "hidden" instead of type "text" or "submit". As mentioned in the other tutorial, a hidden input is "used for passing hidden data to the receiving page that the user can only see by looking at the source code of the form's page". You can see that its value is set to "<h3>This introduction has been modified... A hidden input was used so that normal users wouldn't see it! :)</h3>", although its name is still "introCode". This means that the above text will be passed to the PHP code as if the user submitted it in a text box (an input with a type of "text") with a name of "introCode". The only difference is that the user won't see it or know about it unless they look at the source code of the page with the form on it.

The last thing I need to mention about these HTML forms is that their actions are all set to "#" (rather than something like "submitForm.php"). What this does is cause the page to refresh, except that the HTTP POST data will be sent to the page during this refresh. Basically, it's a means of simply displaying both the HTML form and the resulting data from the PHP code all on one page.

Now that we know what the PHP code should be expecting, let's take a look at how it all works:

mysql_connect("mysql12345.mywebsite.com", "asgsd34g34_user", "nottheactualpassword") or die(mysql_error());
 mysql_select_db("asgsd34g34_database") or die(mysql_error());
 
 switch($action)
 {
  case "Show":
   $id = receivePOST("id");
   
   if (strlen($id))
   {
    $account = mysql_query("SELECT * FROM accounts WHERE id = $id LIMIT 1") or die(mysql_error());
    
    if ($row = mysql_fetch_assoc($account))
    {
     print_r($row);
     echo "[br/]";
    }
    else
    {
     die("ERROR: no column with this ID ($id) exists!");
    }
   }
   else
   {
    die("ERROR: missing information for displaying!");
   }
   break;
  case "Show all":
   $accounts = mysql_query("SELECT * FROM accounts") or die(mysql_error());
   
   while ($row = mysql_fetch_assoc($accounts))
   {
    print_r($row);
    echo "[br/]";
   }
   break;
  case "Add":
   $id = receivePOST("id");
   $username = receivePOST("username");
   $password = receivePOST("password");
   $name = receivePOST("name");
   $introCode = receivePOST("introCode");
   
   if (strlen($id) && strlen($username) && strlen($password) && strlen($name) && strlen($introCode))
   {
    mysql_query("INSERT INTO accounts SET id = $id, username = \"$username\", password = \"$password\", name = \"$name\", registerDate = now(), lastLoginDate = now(), introductionCode = \"$introCode\"") or die(mysql_error());
    
    echo "New column successfully added![br/]";
   }
   else
   {
    die("ERROR: missing information for adding!");
   }
   break;
  case "Modify":
   $id = receivePOST("id");
   $name = receivePOST("name");
   $introCode = receivePOST("introCode");
   
   if (strlen($id) && strlen($name) && strlen($introCode))
   {
    $account = mysql_query("SELECT * FROM accounts WHERE id = $id LIMIT 1") or die(mysql_error());
    
    if ($row = mysql_fetch_assoc($account))
    {
     mysql_query("UPDATE accounts SET name = \"$name\", lastLoginDate = now(), introductionCode = \"$introCode\" WHERE id = $id LIMIT 1") or die(mysql_error());
     echo "Column successfully updated![br/]";
    }
    else
    {
     die("ERROR: no column with this ID ($id) exists!");
    }
   }
   else
   {
    die("ERROR: missing information for modification!");
   }
   break;
  case "Remove":
   $id = receivePOST("id");
   
   if (strlen($id))
   {
    $account = mysql_query("SELECT * FROM accounts WHERE id = $id LIMIT 1") or die(mysql_error());
    
    if ($row = mysql_fetch_assoc($account))
    {
     mysql_query("DELETE FROM accounts WHERE id = $id LIMIT 1") or die(mysql_error());
     echo "[br/]";
    }
    else
    {
     die("ERROR: no column with this ID ($id) exists!");
    }
   }
   else
   {
    die("ERROR: missing information for removal!");
   }
   break;
  case "Remove all":
   mysql_query("DELETE FROM accounts") or die(mysql_error());
   echo "All columns removed successfully![br/]";
   break;
 }

The first thing it does is connect to the MySQL server and then select the correct database. I've made up the values shown above and in the downloadable script file, of course. :)

Next there is a large switch statement with cases for each of the 6 possible actions, since each action should receive different treatment. Some of the actions require some variables that should have been passed in, so they use the "receivePOST" function to retrieve this data and then check each variables' length with "strlen" (to verify that a value was passed in for each variable) - this should all be standard practice by now (because of the other tutorials).

Lastly, we come to the MySQL queries themselves. As I stated in another tutorial, I recommend this MySQL cheat sheet for the quick learning of MySQL:

http://cse.unl.edu/~sscott/ShowFiles/SQL/CheatSheet/SQLCheatSheet.html

It's not an exhaustive list of all information regarding PHP and MySQL queries, but that's why I like it. If you need more help than that, you can use http://dev.mysql.com/doc/refman/5.0/en/apis-php.html and www.google.com. ;)

The goal of this tutorial is not really to help you with the MySQL queries themselves but with the PHP code that's calling MySQL queries and then interpreting the returned information appropriately. Here's a brief overview of the new functions I've been using in this script:

mysql_connect("server", "username", "password"): Connect to a MySQL server.
mysql_select_db("database"): Select a database to interact with.
mysql_query("query"): Call a MySQL query on the selected database (this is where the above MySQL links will come in handy).
mysql_fetch_assoc(mysql_query-return-value): Retrieve the first/next array from a returned MySQL query OR return false if there aren't any more arrays to process. If there are more than 1 arrays in a returned query (or there could be more than 1 and you want to process them all) then you should place this in a "while" loop. I'll explain this more later.
mysql_error(): Get the last MySQL error that occurred (if any).
die("Last string to display to the user"): Display a string of text to the user and then end the script immediately (it will be as if the file didn't have any more code/characters in it at all).
print_r(array): Display the contents of an array (I'll explain arrays later) to the user - this was not meant to look pretty. It is just a very convenient way of displaying an array of returned data if you're not interested in the looks of it yet.

Here's a prime example of how to use the "mysql_fetch_assoc" function properly to process 0, 1, or more arrays of returned data:

case "Show all":
   $accounts = mysql_query("SELECT * FROM accounts") or die(mysql_error());
   
   while ($row = mysql_fetch_assoc($accounts))
   {
    print_r($row);
    echo "[br/]";
   }
   break;

A while loop simply repeats a piece of code (in between the {}) until the condition inside the parentheses () is false. If the condition is originally false, then the code inside the loop is never run. This code above sets the variable "$row" to the return value of "mysql_fetch_assoc" (which is set to the result of the MySQL "SELECT * FROM accounts" query). The value being assigned to "$row" is then checked by the "while" loop before displaying the contents of the row with the "print_r" function.

This process of retrieving the next row from the returned data and then displaying its contents would go on forever if the returned data had an infinite number of rows. It doesn't, however, and eventually (when every row is displayed, in this case) the "mysql_fetch_assoc" function will return false, causing the while loop to end and the script to continue.

Another thing I should point out is that after every "mysql_connect", "mysql_select_db", and "mysql_query" function call I have placed the code "or die(mysql_error());". I should mention right away that if you are writing code for something important that needs to look pretty and be user friendly and such, then you should not do what I have done here. This code will cause the script to display whatever MySQL error has occurred and then immediately exit (no more code will be run at all - the script will output whatever pieces of the page have been generated so far, output the string inside the parentheses (), and then quit).

Obviously you never want to actually display the MySQL error to the user. I know plenty of people who would go crazy if their phone's touch screen isn't immediately responsive or their anti virus program gives them a warning. We don't want to give people heart attacks. =) You could redirect the user to an error page you've created, have the script send you an email about this problem with the "mail" function, record all of the errors you receive in a text file on the server, etc.

Also, there are many people who believe that you should never use the "die" function either. I think it really depends on who the audience of your website is going to be - for instance, this is supposed to be a simple tutorial on using MySQL in PHP code. The script I've created here is much simpler with the "die" function than it could have been, etc.

Now I will briefly explain the MySQL queries I have used in the script:

"SELECT * FROM accounts WHERE id = $id LIMIT 1": Retrieve all of the columns from the accounts table where the id property is equal to the passed in ID, except only get 1 column.
"SELECT * FROM accounts": Retrieve every column from the accounts table.
"INSERT INTO accounts SET id = $id, username = \"$username\", password = \"$password\", name = \"$name\", registerDate = now(), lastLoginDate = now(), introductionCode = \"$introCode\"": Insert a new column into the accounts table using the passed in id, username, password, name, and introCode values and the current date and time for the registerDate and the lastLoginDate.
"UPDATE accounts SET name = \"$name\", lastLoginDate = now(), introductionCode = \"$introCode\" WHERE id = $id LIMIT 1": Find the column in the accounts table where the id property is equal to the passed in ID and update its name and introCode to the passed in values as well as set the lastLoginDate to the current date and time, except only do this for 1 column.
"DELETE FROM accounts WHERE id = $id LIMIT 1": Find the column in the accounts table where the id property is equal to the passed in ID and delete it from the table, except only do this for 1 column.
"DELETE FROM accounts": Delete all of the columns from the accounts table.

The last thing I need to cover before I wrap this tutorial up is using arrays in PHP... I would explain this myself, however there are already plenty of tutorials out there on PHP arrays that I can't top anyways. Check out these 3 links for just about everything you'll ever need to know about array manipulation in PHP:

http://www.w3schools.com/php/php_arrays.asp
http://us.php.net/manual/en/book.array.php
http://us.php.net/manual/en/book.mysql.php

It's important that you realize that a returned MySQL query value is often an array of arrays (multidimensional array), and each of these arrays inside the return value is an associative array that represents a single column.

Here are some PHP array functions I have deemed likely to be helpful to you (although don't forget that there are also PHP MySQL array functions and such!):

http://us.php.net/in_array
http://us.php.net/manual/en/function.array.php
http://us.php.net/manual/en/function.array-pop.php
http://us.php.net/manual/en/function.array-shift.php
http://us.php.net/manual/en/function.array-slice.php
http://us.php.net/manual/en/function.count.php

This was a pretty exhaustive tutorial... I hope I didn't bore you along the way! If you understood everything from the beginning of this tutorial to the end, then good for you - now you know how to interact with a MySQL database using PHP code! :)

TheCodingTutorials

Pages

Friday, May 25, 2012

Interacting with a MySQL Database Table in PHP

2 comments:

Blog Views