Hackosis

PHP and MySQL are one of the most used technologies on the web today. By using about 7 different script files we can add, update, and delete rows from a MySQL table.

Example of completed project:

Download the Simple PHP MySQL Table Editor.

Lets get started. I will explain each section of the script files so you have an understanding of how it works. You can download the project in a zip file above. The download also includes the example MySQL schema structure so you can test the script right away.

All examples in this post assume that you have already typed the php opening and closing tags. I cannot inlcude them on my page due to Wordpress plugins that I am running.

ALSO, because my PHP color coder sometimes runs off the page, you may have to click “show plain code” to view all typed PHP code.

HEADER.PHP:

Header.php stores the HTML header that include the title, etc. I am not going into detail since this is not a HTML tutorial.

I always start my PHP files with comments to indicate the purpose of the script and give contact details in case anyone has questions. Comments in PHP are indicated with ‘//’ for single lines and ‘/*’, ‘*/’ for multiple line comments:

/**************************************************************************************
 *Header
 * Author: Shane 
 * use this to learn, share, or what ever else floats your boat and finds your lost remote
 **************************************************************************************/

Now we print the HTML headers inside of the echo command. The ‘\n’ sends a line break to the browser so the source is readable. If we didn’t do that, it would be all one one line and look like doo doo:

echo "\n";
echo "\n";
echo "\n";
echo "
\n";
echo "\n";
echo "\n";

CONFIG.PHP:

Config.php stores our database connection details. Match this to what you have setup in your MySQL database. You may have to contact your systems administrator to get the details. Fill in the appropriate values for $dbhost, $username, and $password:

$dbhost='127.0.0.1';
$dbusername='username';
$dbuserpass='password';
$dbname = 'db1';

INDEX.PHP:

Add the comments:

/**************************************************************************************
 * Simple MySQL Table Editor index script - index.php
 * Author: Shane - shane *at* hackosis *dot* com>
 * use this to learn, share, or what ever else floats your boat and finds your lost remote
 **************************************************************************************/

The next part of the index.php script is going to require that the config.php be ran so we have the details to connect to the MySQL database:

//Get database credentials
require 'config.php';

Now we can connect to the MySQL database. The mysql_connect and mysql_select_db functions are used for this:

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
//select the database
mysql_select_db($dbname) or die('Cannot select database');

Next, we are going to require a header.php script that will store our HTML header so we have (some what) valid code:

require 'header.php';

After the initialization of the database connection and spitting out our HTMl header we define the MySQL query to display the data from our table. We are using a PHP variable to store the query string.

It is best to define the columns we want to SELECT, but for instructional purposes we will just use ‘*’ to select all columns:

$query = "SELECT * FROM table1;";

Now we acually run the query on the database and store the results in an array. We use the mysql_query function to accomplish this:

$result = mysql_query($query) or die(mysql_error());

We need some HTML to output or results in a way that will look nice in the browser window. I know tables are out of date and can be defined through CSS, but this is a PHP tutorial not HTML or CSS.

This snippit is using the echo command to output the true HTML code to the browser. You always have to use ‘\’ if you want to use double quote inside of the HTML code or we will get errors:

Spit out the header HTML for our editing table:

//Table header
echo "

\n";
echo "
\n";
echo "


\n";
echo "


\n";
echo "


\n";
echo "


\n";
echo "


\n";
echo "
";
echo "
 Column 1:
 Column 2:
 Column 3:
 Column 4:
 Column 5:



";

Now we are going to count the number of rows returned from the MySQL query. The reason we do this is that we want to display a message stating there are no rows if that is the case:

//Count the number of rows returned
$count = mysql_num_rows($result);

Now comes the if statement to display the data IF there are rows, and if there are no rows we print “NO DATA” using an else statement.

Pay close attention to where the ‘{’ and the ‘}’ are because this defines were the if and else statements begin and end. This is really where we should start to indent, but you can see that in the download.

The while statement is REALLY important. What it is saying is that while there are rows print them to the browser. We use the mysql_fetch_array to achieve this.

This code also involves a form to update the data using the update.php script if we want to edit any of the data in the database. Also there is a delete button to delete rows that uses the delete.php script if we choose to do so:

if ($count !== 0) {
while($row = mysql_fetch_array($result)) {
echo "

\n";
echo "
\n";
echo "
\n";
echo "
\n";
echo "
\n";
echo "
\n";
echo "
\n";
echo "


\n";
}
echo "

\n";
} else {
echo "NO DATA\n";
}

After printing the data, we have a form to add new records to the database. This is similar to the previous form, but it calls the add.php script to add new rows into the database:

echo "

\n".
"
\n".
"
\n".
"
\n".
"
\n".
"
\n".
"
\n".
"


";

That pretty much completes the index.php file except for a legend to idiot proof the icons for adding, deleting, and updating records in the database. I have now escaped the PHP code and am using standard HTML:

Legend:

 Add a row after entering the correct information.
 Update a row after editing it.
 Delete a row.

UPDATE.PHP:

The update.php script will be called from the form on the index.php when we click the update button or hit enter on our keyboard when editing data already in the MySQL table.

Again, we are adding comments to the beginning of the script (click show plain code to view all):

/**************************************************************************************
 * update row in database - update.php
 * Author: Shane  - 
 * use this to learn, share, or what ever else floats your boat and finds your lost remote
 **************************************************************************************/

Also, we are again going to require the config.php to get the credentials to connect to the database. It is easier to keep the information in one file in case we have to update the credentials we only have to do it once:

//Get database credentials
require 'config.php';

Now we are going to use the predefined $_GET variable to pull the data from the URL that is stored when we edit data on the index.php script. This will store the value that we have inputted so we can update the database. We do this for each column we are going to update:

$column1 = $_GET['column1'];
$column2 = $_GET['column2'];
$column3 = $_GET['column3'];
$column4 = $_GET['column4'];
$column5 = $_GET['column5'];

Then again, we connect to the MySQL server and select the database:

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
//select the database
mysql_select_db($dbname) or die('Cannot select database');

Now that we are connected to the database we can store our MySQL query in a variable. This is where we use the variables pulled from the $_GET. This assumes that column1 is distinct and our primary key. It might be best if it was an auto increment column or some type of ID column. If it is not distinct then we might be updating rows we don’t want to. We use an UPDATE query to achieve this:

$query = "UPDATE table1" .
" SET column2 = '".$column2."'," .
" column3 = '".$column3."'," .
" column4 = '".$column4."'," .
" column5 = '".$column5."'" .
" WHERE column1 = '".$column1."';";

And we run the query that updates the database:

//Run the query
$result = mysql_query($query) or die(mysql_error());

Now is where we redirect back to the index.php page because we don’t need to see anything happen. We use the header function to achieve this:

//link variable is equal to the referring page
$link = $_SERVER['HTTP_REFERER'];
//sends a header directly to the browser telling it to redirect the user to the referring page
header("Location: $link");

ADD.PHP

The add.php script file will use the add form we have on the index.php page to add rows into the database. This is very similar to the update.php, but we use a slightly different query called INSERT.

Again, we are adding comments:

/**************************************************************************************
 * add row to database - add.php
 * Author: Shane 
 * use this to learn, share, or what ever else floats your boat and finds your lost remote
 **************************************************************************************/

Again, we are requiring config.php:

//Get database credentials
require 'config.php';

Again, we are using the predefined $_GET variable to store data from the URL inputted from the add form on the index.php page:

$column1 = $_GET['column1'];
$column1 = $_GET['column1'];
$column2 = $_GET['column2'];
$column3 = $_GET['column3'];
$column4 = $_GET['column4'];
$column5 = $_GET['column5'];

Again, we are connecting and selecting the database:

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
//select the database
mysql_select_db($dbname) or die('Cannot select database');

Now we are ready to define our MySQL query. Remember that we are using an INSERT query to add a row to the database. This is pulling the variable that we stored with the $_GET command and inserting them into the database:

$query = "INSERT INTO `table1` ( `column1` , `column2` , `column3` , `column4` , `column5` )" .
"VALUES ('".$column1."', '".$column2."', '".$column3."', '".$column4."', '".$column5."');";

Now we run the MySQL query:

//Run the queries
$result = mysql_query($query) or die(mysql_error());

Again, we are redirecting back the the index.php page because we only need to execute the query, not view anything. We are also using the predefined $_SERVER variable to achieve this:

//link variable is equal to the referring page
$link = $_SERVER['HTTP_REFERER'];
//sends a header directly to the browser telling it to redirect the user to the referring page
header("Location: $link");

DELETE.PHP

The delete.php script delete rows from the database. This is called when you click the the delete button on the index.php page. This is very similar to the previous two scripts except we are using a DELETE MySQL query.

Again, we are adding comments:

/**************************************************************************************
 * delete row from database- delete.php
 * Author: Shane  
 * use this to learn, share, or what ever else floats your boat and finds your lost remote
 **************************************************************************************/

Again, we are requiring the execution of config.php to pull the MySQL credentials:

//Get database credentials
require 'config.php';

Now since we have a primiary index and unique column of ‘column1′ it is safe to delete by this index. If it is not unique we might be deleting more then desired. We again use the predefined $_GET variable to pull the value from the URL:

//Get the row ID to delete!
$column1 = $_GET['column1'];

Again, we are connecting and selecting the MySQL database:

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
//select the database
mysql_select_db($dbname) or die('Cannot select database');

After that we set the MySQL query and store it in a variable:

//Set the query to return names of all employees
$query = "DELETE FROM table1 WHERE column1 = '".$column1."';";

Then again, we run the query to delete the row in the database:

//Run the query
$result = mysql_query($query) or die(mysql_error());

Now we redirect, again:

//link variable is equal to the referring page
$link = $_SERVER['HTTP_REFERER'];
//sends a header directly to the browser telling it to redirect the user to the referring page
header("Location: $link");

STYLE.CSS

There is a style.css style sheet also required by the header.php script. I am not including this here since this is not a CSS tutorial. You can find it in the download.

Thanks for stopping and I hope this PHP tutorial was useful to you. If you have any requests for PHP tutorials please leave a comment or contact me.

Related Posts

• HOWTO: Simple Search Engine with PHP and MySQL
• How To Add Gravatars To Your Wordpress Theme
• PHP Tutorial: Brute Force Calculator
• PHP: Brute Force Calculator v1.1
• Hacker Proof Your Web Application with PHPIDS