PHP and MySQL are two different languages that work hand in hand very well. PHP is an excellent and versatile programming language with thousands of examples and applications while MySQL is one of the world's best solution when it comes to storing and managing your data. When you have a dynamic website it's almost crucial to use a database in order to access your data by parameters and conditions. This is the beauty of "going dynamic". Instead of uploading a new file each time you wish to publish something new, you can create a new entry in your database and access it any time you wish. With a single click of a button your information is available and ready to do whatever it's supposed. A single page (article.php for example) can display an unlimited number of articles and it only needs one parameter in order to identify and output the desired entry from the database. This type of working with data will keep your website very robust, small and easy to manage. Before anything else, I must mention that it will not be a very easy tutorial, you need some PHP knowledge to be able to follow the code examples and the tutorial itself. In order to get us started the right way we will need our database up and running and the web server to be able to run php code. XAMPP is an excellent tool to put your localhost "alive" in under 5 minutes. Many webmasters tend to work with phpMyAdmin to update their database but we will learn in this tutorial how to write code that will delete, update or insert data by working with forms and url parameters. Yes, there are situations when you don't need a form to make a new entry, delete or update something else in your db. A script can work with sessions, server variables, data passed via url and by many other ways. We will start our examples with creating a database and an empty table by working with php only and staying away from phpmyadmin. We will need 3 informations in order to access and manipulate our databases: host ("localhost" usually), username and password (in XAMPP this comes by default with username "root" and no password. I suggest you to access the "security" link and update your info). Let's write a new file called db.php and try to create a new database with it.
Since our table is empty, we will follow the natural way and start with learning how to insert data in our database using a form and some php code. Copy the following code into a newly created file (insert.php for example):
If this conditions returns TRUE we select the database we wanna work with, and define our query inside a variable ('$query' in our case). Another condition executes our query which returns the mysql_error that occurred in the unfortunate event or defines the variable '$success' for a final use. The reason why I don't echo that message out and prefer to store it in a variable is because it helps me hide that form on success since we don't need it anymore.
Depending on the situation, selecting data can have many shapes. We can imagine a page where we would like to display a specific article (we will need a parameter to help us identify it), another one to display all the article titles that we have stored (we will need a loop), we might want to show the last 10, first 10 and so on...many situations. Let's take the first one and try to display the article that has the ID = 10. We use the ID as the parameter because it's the only record that is unique so we can't be ambiguous in our select. The parameter will come via a dynamic url ( article.php?ID=10 ) and we will access it via $_GET which is similar to $_POST and $_REQUEST but with a different functionality.
Updating a mysql record is not very complicated compared to the insert. We will need to add some extra code with another query that will do a basic select in order to populate the form with the stored data that we want to modify. This isn't even required but it helps. Let's go back and modify the insert form to suit our needs.
This is the easiest part. 'DELETE' is very similar to 'SELECT', it needs to know what to delete, a specific record that comes via $_GET, $_SESSION, $_POST, $_REQUEST etc., or we can tell the code to delete all records. Here's a simple example that will delete a specific record which comes via $_GET.
<?php
// set your infomation.
$host = 'localhost';
$user = 'root';
$pass = 'password';
$database = 'roscripts';
// connect to the mysql database server.
$connect = @mysql_connect ($host, $user, $pass);
if ( $connect )
{
// create the database.
if ( ! @mysql_query ( "CREATE DATABASE `$database`" ) )
{
die ( mysql_error() );
}
else {
echo "success in database creation.";
}
}
else {
trigger_error ( mysql_error(), E_USER_ERROR );
}
?>
Let's take the above code step by step and analyze/understand it. At first, we stored our host name (localhost), username and password in variables to call and update them a little bit easier (I can use the variable '$host' in 1000 pages and update it by only modifying this step instead of modifying all those pages one by one, that's why we store the info in variables). Next we define '$connect' with the actual connection code. This is very important because it's a little different from most of the examples out there. Notice the '@' character right at the start of the 'mysql_connect' command. It's used to hide the errors that php produces if any of the variables contain informations that are not correct and the script can't connect to the host. This is a very important step in writing secure code that will not leave empty doors (a good programmer always looks both ways before crossing a one way street). The errors can output a lot of sensitive informations and we don't wanna put that kind of info in the wrong hands so we will hide the default errors and put our own messages to identify where the error is. Let's take a live example for a better understanding: Instead of 'root' as the database username, I will put 'rosot' and delete the '@' that is supposed to hide my errors. Here's the ugly output: 'Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'rosot'@'localhost' (using password: YES) in D:\xampp\htdocs\test\index.php on line 9' It gives out my username, it's not a big thing since it's the wrong one but keep in mind that the same error will be shown if we enter a wrong password so it's not safe. Let's put back the '@' character and run this code again. The output is somehow the same because I defined this way but you can change it to whatever you want since you're in full control. We managed to control the errors and display only what we need by using an if statement. If everything is ok, the script will go further and meet a new if statement created with the same purpose of controlling the messages. By default, the command 'mysql_query' executes a query the we define and store in a variable or inside the parenthesis (it's the same thing). This mysql command returns TRUE or FALSE (TRUE = I executed the query successfully, FALSE = no I didin't) and this boolean return will help us figure out if we should display an error message or success.if ( ! @mysql_query ( "CREATE DATABASE `roscripts`" ) )Another character (!) is joining the game: if ( ! //code here ) means that if the code that follows the character is not true than the condition is true. In pure english it goes this way: "If I can't execute this query, I do this...". This condition is the same with the following but a little more elegant:
if ( @mysql_query ( "CREATE DATABASE `roscripts`" ) == FALSE )So if the code can't execute the query command, it hides the default errors (thanks to '@') and displays the message that I want (in our case the code dies and displays the mysql error : 'die ( mysql_error() );'). Defining an else statement (always try to cover all possible situations: if - elseif - elseif ...... - else), in case of a successfully executed query, we throw out the "green" message: echo "success in database creation.";. Ok! We have a database, we need a table also to keep our data. Let's call it 'articles'. I will change the above code and extend our conditions to include a table creation as well. There's only one situation when we can create a new table and that's if we managed to connect to our database and also created our database (you can't have a table without a database). That situation available (in our case) only if mysql_query returns TRUE so we need to include it in the 'else' statement of that condition which is supposed to output the success message. Instead of that message we go deeper and create a new condition. The code will grow and look like this:
<?php
// set your infomation.
$host = 'localhost';
$user = 'root';
$pass = 'password';
$database = 'roscripts';
$sql = 'CREATE TABLE `articles` (
`ID` int( 11 ) NOT NULL AUTO_INCREMENT,
`article_title` VARCHAR( 255 ) NOT NULL,
`article_content` TEXT NOT NULL,
PRIMARY KEY ( `ID` )
)';
// connect to the mysql database server.
$connect = @mysql_connect ( $host, $user, $pass ) ;
if ( $connect )
{
// create the database.
if ( ! @mysql_query ( "CREATE DATABASE $database" ) )
{
die ( mysql_error() );
}
else {
mysql_select_db ( $database );
if ( @mysql_query ( $sql ) )
{
echo 'Your new table was created successfully!';
}
else {
die ( mysql_error() );
}
}
}
else {
trigger_error ( mysql_error(), E_USER_ERROR );
}
?>
Let's analyze what's new. Another variable ($sql) was defined and holds the mysql query that should be executed if the database is created. This is one of the situations where I prefer to store the query in a variable before executing it instead of putting inside the parenthesis and that's because it's easier to follow the code and looks better as well. The success message that was telling us that we created the db is gone and replaced with a new code since we're not up and running yet. If we have a database we can work with tables but we need one more thing: we need to select the database we're about to manipulate: mysql_select_db ( $database );. By using the same practices, the "level 3" statement enters the game and executes a new query in order to create the desired table. If everything went fine we can start playing with our new database and work with data. Inserting data<?php
if ( array_key_exists ( '_submit_check', $_POST ) )
{
if ( $_POST [ 'article_title' ] != '' && $_POST [ 'article_content' ] != '' )
{
mysql_select_db ( $database, $connect );
$query = "INSERT INTO
`articles`
(
`article_title`,
`article_content`
)
VALUES
(
'" . mysql_real_escape_string ( $_POST ['article_title'] ) . "',
'" . mysql_real_escape_string ( $_POST ['article_content'] ) . "'
)";
if ( @mysql_query ( $query ) )
{
$success = 'New article added';
}
else {
die ( mysql_error () );
}
}
else {
echo 'Please ensure that you have a title and some content for this article!';
}
}
if ( isset ( $success ) ) {
echo $success;
}
else {
//we need this form only if we don't have the success message
?>
<form id="insert" name="insert" method="post" action="<?=$_SERVER['PHP_SELF']?>">
<input type="hidden" name="_submit_check" value="1"/>
Article title:<br />
<input name="article_title" type="text" id="article_title" size="55" value="<?php if ( isset ( $_POST['article_title'] ) ): echo $_POST['article_title']; endif; ?>" />
<br /><br />
Article content:<br />
<textarea name="article_content" cols="55" rows="5" id="article_content"><?php if ( isset ( $_POST['article_content'] ) ): echo $_POST['article_content']; endif; ?></textarea>
<br /><br />
<input type="submit" name="Submit" value="Submit" />
</form>
<?php } ?>
The above form points to itself because it makes our life easier in the event of entering some wrong data that doesn't passes our validation. What happens when you complete a big form, hit enter and the next page tells you that you forgot to add something and also instructing you go back and start all over? I'll tell you, in 90% of the cases you'll leave. The whole point of this is to keep our data in our inputs until the validation is passed and a new entry created. One thing that we need to take care of is to make sure that the php code is executed only on form submit, otherwise skipped (if we're there for the first time). From the form, the information comes via $_POST or $_REQUEST which are arrays holding our entries. By simply printing out the $_POST variable you will get something similar to this:Array
(
[_submit_check] => 1
[article_title] => article title
[article_content] => article content
[Submit] => Submit
)
If we've hit the submit button it means that we should have the array index '_submit_check' defined as '1' so we're using this as a condition to decide if we should execute the code (if the form was submitted) or skip it.if ( array_key_exists ( '_submit_check', $_POST ) )If the above condition is TRUE we're good to go and move on validating our data. the validation is basic, it only checks if the inputs are not empty ( != '' ):
if ( $_POST [ 'article_title' ] != '' && $_POST [ 'article_content' ] != '' )
If this conditions returns TRUE we select the database we wanna work with, and define our query inside a variable ('$query' in our case). Another condition executes our query which returns the mysql_error that occurred in the unfortunate event or defines the variable '$success' for a final use. The reason why I don't echo that message out and prefer to store it in a variable is because it helps me hide that form on success since we don't need it anymore.
if ( isset ( $success ) ) {
echo $success;
}
else {
Always remember to escape any variable that you use to create sql queries, mysql_real_escape_string is a good solution. Never trust anything and anyone. Selecting data<?php
$sql = "SELECT `article_title` FROM `articles` WHERE `ID` = " . mysql_real_escape_string ( $_GET['ID'] );
mysql_select_db ( $database, $connect );
if ( @mysql_query ( $sql ) )
{
$query = mysql_query ( $sql );
$row = mysql_fetch_assoc ( $query );
echo $row['article_title'];
}
else {
die ( mysql_error () );
}
?>
Since we specified the ID of the article we want selected, if the query is executed successfully, it's time to output that data somehow. '$row' will be defined and will store the selected data in a associative array by using 'mysql_fetch_assoc'. Notice that we need the query to be executed before using this function ($query = mysql_query ( $sql )). We can now output our selection using a simple echo addressed to the key that we want out ('$row['article_title']'; 'article_title' is the key of the array that we need). Also notice that I've selected only the 'article_title' and, obviously, we can't echo out anything else (for example the ID or article_content) but we can use a wildcard (*) and select all of them. Try not to be greedy and select only what you need making your code run faster.<?php
$sql = "SELECT * FROM `articles` WHERE `ID` = " . mysql_real_escape_string ( $_GET['ID'] );
mysql_select_db ( $database, $connect );
if ( @mysql_query ( $sql ) )
{
$query = mysql_query ( $sql );
$row = mysql_fetch_assoc ( $query );
echo $row['ID'] . '<br />' . $row['article_title'] . '<br />' . $row['article_content'];
}
else {
die ( mysql_error () );
}
?>
This was a simple usage of the select statement executed from php. We can get a little more complicated considering that we don't have a parameter pointing to an exact entry but we want to display all the article titles in a list. The procedure is similar with the one above, we will add a loop to echo out the data one by one.<?php
$sql = "SELECT `article_title` FROM `articles` ORDER BY `ID` DESC" );
mysql_select_db ( $database, $connect );
if ( @mysql_query ( $sql ) )
{
$query = mysql_query ( $sql );
$row = mysql_fetch_assoc ( $query );
do {
echo $row['article_title'] . '<br />';
} while ( $row = mysql_fetch_assoc ( $query ) );
}
else {
die ( mysql_error () );
}
?>
I've ordered our list with the new entries on top of the others (ORDER BY `ID` DESC"), we can also limit them (SELECT `article_title` FROM `articles` ORDER BY `ID` DESC LIMIT 10), order in random mode (SELECT `article_title` FROM `articles` ORDER BY RAND() DESC) etc. You don't always need a dynamic url parameter to select data from mysql, $_GET can also be $_SESSION, $_POST, $_REQUEST, a predefined variable or anything else you need to use. Updating data<?php
$sql = "SELECT * FROM `articles` WHERE `ID` = " . mysql_real_escape_string ( $_GET['ID'] );
mysql_select_db ( $database, $connect );
if ( @mysql_query ( $sql ) )
{
$query = mysql_query ( $sql );
$row = mysql_fetch_assoc ( $query );
if ( array_key_exists ( '_submit_check', $_POST ) )
{
if ( $_POST [ 'article_title' ] != '' && $_POST [ 'article_content' ] != '' )
{
mysql_select_db ( $database, $connect );
$query = "UPDATE
`articles`
SET
`article_title` = " . mysql_real_escape_string ( $_POST [ 'article_title' ] ) . "
`article_content` = " . mysql_real_escape_string ( $_POST [ 'article_content' ] ) . "
WHERE
`ID` = " . mysql_real_escape_string ( $_GET['ID'] );
if ( @mysql_query ( $query ) )
{
$success = 'Article updated successfully!';
}
else {
die ( mysql_error () );
}
}
else {
echo 'Please ensure that you have a title and some content for this article!';
}
}
}
else {
die ( mysql_error () );
}
if ( isset ( $success ) ) {
echo $success;
}
else {
//we need this form only if we don't have the success message
?>
<form id="update" name="update" method="post" action="<?=$_SERVER['PHP_SELF']?>">
<input type="hidden" name="_submit_check" value="1"/>
Article title:<br />
<input name="article_title" type="text" id="article_title" size="55" value="<?php if ( isset ( $_POST['article_title'] ) ){ echo $_POST['article_title']; }else{ echo $row['article_title'];} ?>" />
<br /><br />
Article content:<br />
<textarea name="article_content" cols="55" rows="5" id="article_content"><?php if ( isset ( $_POST['article_content'] ) ){ echo $_POST['article_content']; }else{ echo $row['article_content'];} ?></textarea>
<br /><br />
<input type="submit" name="Submit" value="Submit" />
</form>
<?php } ?>
The form is slightly changed, on default it will display the stored data, on submit (in case of an error) will update itself with the posted informations. Everything else is similar with the insert step, the query changed from 'INSERT INTO ..' to 'UPDATE .. SET .. WHERE'. Deleting data<?php
$sql = "DELETE FROM `articles` WHERE `ID` = " . mysql_real_escape_string ( $_GET['ID'] );
mysql_select_db ( $database, $connect );
if ( @mysql_query ( $sql ) )
{
echo 'Article ID = ' . $_GET['ID'];
echo ' was deleted successfully';
}
else {
die ( mysql_error () );
}
?>
Let's take this step by step. We selected our data, created a list to display our records with links next to each record pointing to different actions (update, delete etc.). We want to delete the article that has the ID=10. The 'delete' link will point to delete_script.php?ID=10. The page that is supposed to delete records (delete_script.php) will take that parameter and build a query that will look like this: DELETE FROM `articles` WHERE `ID` = 10. If we want to delete all records instead of a specific one, it's even more simple:<?php
$sql = "DELETE FROM `articles`";
mysql_select_db ( $database, $connect );
if ( @mysql_query ( $sql ) )
{
echo 'All articles have been deleted successfully';
}
else {
die ( mysql_error () );
}
?>
"DELETE FROM `articles`" will delete all entries from the table 'article', this is also similar to "TRUNCATE TABLE `articles`" which has the same results. Thank you for reading this tutorial, keep in mind that we learned only 4 mysql commands (select, insert, update and delete), for a full list please consult the mysql docs.
Comments
Post a Comment