Tutorials

PHP/MySQL Tutorial (Add, View, Edit & Delete from to MySQL database) Live Example (News System)

Last Update 29-09-2007

Introduction
- While I was learning PHP I searched the net and found a lot of PHP/MySQL tutorials, I went through each, but most of them didn't work as I need to. After weeks I managed to do all about PHP/MySQL. Now I am writing this Tutorial to help you learn how to use PHP and the MySQL database.

- Before you read this tutorial you should have at least a basic knowledge of how to use PHP. If you do not yet know PHP, I suggest that you read this PHP Tutorial http://www.freewebmasterhelp.com/tutorials/php/

During this tutorial we will use live example (News system) and cover these parts:
- Create table called "news" to store the content. (News_sql.sql)
- Setting up the database name and connection. (config.php)
- Inserting news into the database. (add_news.php).
- Viewing home news (index.php).

- Viewing the full news (read_more.php)

- Edit news (edit_news.php)

- Delete news (delete_news.php)

So, after you go through this tutorial, you will be able to (Add, View, Edit and Delete from to MySQL databse).

In this news system we are going to use the following fields:

1- News title (title).

2- Date & Time (dtime).

3- Home Text (text1).

4- Body Text (text2)

:: download the script files from here

:: Live example available here

* Part 1 :: Creating database and table (News_sql.sql)

Code: "insert this code into your database using phpMyAdmin"

CREATE TABLE `news` (

`newsid` int(11) NOT NULL auto_increment,

`dtime` datetime default NULL,

`title` varchar(255) default NULL,

`text1` text,

`text2` text,

PRIMARY KEY (`newsid`)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

The code will create a table that looks like this:

newsid	dtime	title	text1	text2

If we grab a moment and take a look at the SQL-code, and pick it apart we will see that we actually are asking MySQL to do the following:

Please CREATE a TABLE named news with the following columns and properties:

1- ( column-name newsid with datatype INT integer with max 11 digits long, it must be NOT NULL and it should be AUTO_INCREMENT,

2- column-name dtime with datatype datetime, it is NULL,

3- column-name title with datatype VARCHAR with max (255) characters, it is NULL,

4- column-name text1 with datatype text

5- column-name text2 with datatype text

The PRIMARY KEY is (newsid),

) TYPE= MyISAM MyISAM is the default storage engine as of MySQL 3.23. Each MyISAM table is stored on disk in three files. The files have names that begin with the table name AUTO_INCREMENT=1 finally, make the newsid auto inserted. End sql-statement ;

Now we're done with the creation of the database and the table, in the next part we will setup the connection and database variables (config.php).

* Part 2 :: Connecting to MySQL database (config.php)

Code:

<?php

$dbhost="localhost";

$dbusername="user";

$dbpassword="pass";

$dbname="news_system";

//Before you can perform any operation on a database you must connect to the MySQL server. The syntax for performing this operation is simple:

$connect = mysql_connect($dbhost, $dbusername, $dbpassword);

mysql_select_db($dbname,$connect) or die ("Could not select database");

What I've done here is just to set all the variables, it looks better and it's much easier to see what you are doing once the code starts to get big.

$dbhost is a variable and holds your localhost or server name.

$dbusername, is a variable and holds your database username

$dbpassword, is a variable and holds your username password

$dbname is a variable and holds your database name

$connect = is a variable and will store the connection for future use.

mysql_connect creates a connection to MySQL, it takes three arguments; "host, user and password" it returns false if it fails to connect.

mysql_select_db means; please select and open the database. The "or die()"-statements is used to display error message if it doesn't find the database.

By this we are end of the connection and selection of the database.

Next we will learn how to add news to the database.

* Part 3 :: Add News to MySQL database (add_news.php)

Code:

<?php

include("config.php");

if(isset($_POST['submit']))

{//begin of if($submit).

// Set global variables to easier names

// and pervent sql injection and apostrophe to break the db.

$title = mysql_escape_string($_POST['title']);

$text1 = mysql_escape_string($_POST['text1']);

$text2 = mysql_escape_string($_POST['text2']);

//check if (title) field is empty then print error message.

if(!$title){ //this means If the title is really empty.

echo "Error: News title is a required field. Please fill it.";

exit(); //exit the script and don't do anything else.

}// end of if

//run the query which adds the data gathered from the form into the database

$result = mysql_query("INSERT INTO news (title, dtime, text1, text2)

VALUES ('$title',NOW(),'$text1','$text2')",$connect);

//print success message.

echo "<b>Thank you! News added Successfully!<br>You'll be redirected to Home Page after (4) Seconds";

echo "<meta http-equiv=Refresh content=4;url=index.php>";

}//end of if($submit).

// If the form has not been submitted, display it!

else

{//begin of else

<br>

Title: <input name="title" size="40" maxlength="255">

<br>

Text1: <textarea name="text1" rows="7" cols="30"></textarea>

<br>

Text2: <textarea name="text2" rows="7" cols="30"></textarea>

<br>

</form>

}//end of else

Now lets describe what was that.

1- include("config.php"); is used to load the connection to use it. Remember that we put the connection and variables of database in this file as we explained before.

2- if(isset($_POST['submit'])) : it means if you Clicked the (Add News) button of the form.

Why $submit? Because it's the name of the from, look at the form's code here:

<input type="submit" name="submit" value="Add News"> you see the name of the form is (submit) and of course you can change to whatever you want.

$title = mysql_escape_string($_POST['title']); this will hold the value of title field from the form

and mysql_escape_string will prevent sql injection and apostrophe to break the db.

$text1 = mysql_escape_string($_POST['text1']); this will hold the value of the Home text from the form

and mysql_escape_string will prevent sql injection

$text2 = mysql_escape_string($_POST['text2']); this will hold the value of the Body text from the form

and mysql_escape_string will prevent sql injection

$_POST is a super global variable of PHP.

4- if(!$title){ :this means, if the title field id empty then print error message.

5- exit(); exit the script and don't do anything else or don't go to the next code lines.

6- $result = mysql_query("INSERT INTO news (title, dtime, text1, text2)

VALUES ('$title',NOW(),'$text1','$text2')",$connect);

This may look a little confusing at first, so I'll explain what it all means.

First, $result = is there because we are assigning to variable that will run the query.
"mysql_query()" takes two arguments, the query and the connection, exactly like this: "$result = mysql_query($query,$connection);".
The query is INSERT INTO news VALUES : and responsible for inserting the data into a given table's fields.
- VALUES ('$title',NOW(),'$text1','$text2
a- '$title' : returns the news title you entered in the form.
b- NOW(), this function will insert the date and time in the database using the following format: (2004-10-27 14:05:28), remember that we used (datatype datetime) when we created the table. So if we used NOW() the datatype must be datetime.
c- '$text1' : returns the news home text you entered in the form.
d- '$text2' : returns the news body text you entered in the form.
and $connect was defined before in the config.php file, you see we called it here.

* Congratulations. Every thing is okay! Now print the success message:
echo "<b>Thank you! News added Successfully!<br>You'll be redirected to Home Page after (4) Seconds";

echo "<meta http-equiv=Refresh content=4;url=index.php>";
It will print a success message and redirect you to the index.php page after 4 seconds you can change it to whatever you want. It is very simple as you see.

7- else{

what is else? As we mentioned before "if($submit){" if the form was submitted, but else do the opposite if the form wasn't submitted then show it.
The form:

<form method="post" action="<?php echo $PHP_SELF ?>"> and the rest come after this like the inputs and the textareas … etc. Setting up the HTML form is very easy.

Now then, I'm sure you are wondering what the PHP is in there for - that variable, $PHP_SELF uses the current URL as the action for the form - so you can put the form handling script in the same document as the HMTL - which is especially useful when your script isn't very long, as in this case.
was that very easy? Now lets go to the next step.

* Part 4 :: A- Viewing Home News (index.php)

Code:

<?php

// load the configuration file.

include("config.php");

//load all news from the database and then OREDER them by newsid

//you will notice that newlly added news will appeare first.

//also you can OREDER by (dtime) instaed of (news id)

$result = mysql_query("SELECT * FROM news ORDER BY newsid DESC",$connect);

//lets make a loop and get all news from the database

while($myrow = mysql_fetch_assoc($result))

{//begin of loop

//now print the results:

echo "<b>Title: ";

echo $myrow['title'];

echo "</b><br>On: <i>";

echo $myrow['dtime'];

echo "</i><hr align=left width=160>";

echo $myrow['text1'];

// Now print the options to (Read,Edit & Delete the news)

echo "<br><a href=\"read_more.php?newsid=$myrow[newsid]\">Read More...</a>

|| <a href=\"edit_news.php?newsid=$myrow[newsid]\">Edit</a>

|| <a href=\"delete_news.php?newsid=$myrow[newsid]\">Delete</a><br><hr>";

}//end of loop

Now lets describe what was that?

1- $result = mysql_query("SELECT * FROM news ORDER BY newsid DESC",$connect);
The script above is using the mysql_query function to retrieve records or rows from our news table and store them on $results, then order them by newsid. DESC will print the last inserted record first.

2- while($myrow = mysql_fetch_assoc($result))

-While: do a loop until you get the last recored.

- $myrow: a variable, will hold the records data.

- mysql_fetch_assoc : Returns an associative array that corresponds to the fetched row and it accesses the data retrieved by the previous mysql_query stored as $result. You can use (mysql_fetch_array instead of mysql_fetch_assoc).

3- echo "<b>Title: ";

echo $myrow['title'];

echo "</b><br>On: <i>";

echo $myrow['dtime'];

$myrow['title']; is the value of the row which comes from the table, and so for the rest.

print the results and prin the options like (Read More…)(Edit)(Delete).

- <a href=\"read_more.php?newsid=$myrow[newsid]\">Read More...</a>

As you click " Read More..", it will direct you to (read_more.php)page where you will see the complete news that you are reading not a random one.

- <a href=\"edit_news.php?newsid=$myrow[newsid]\">Edit</a>

As you click "Edit", it will direct you to (edit_news.php) page where you edit the news you selected.

- <a href=\"delete_news.php?newsid=$myrow[newsid]\">Delete</a>

As you click "Delete", it will direct you to (delete_news.php) page where it will delete the news you selected.

Why we used: $myrow[newsid]?

Do you remember when we created the news table we created a column-name newsid with datatype INT integer with max 11 digits long, AUTO_INCREMENT=1 , make the newsid auto inserted.

Well, each time you add news to the database the newsid field will be increased one number, so, for the first time you added news, the newsid will become (1) and if you add another it will increase to (2) and then 3 , 4 , 5, 6 and so on.

We use newsid to manage the news, so we will depend on the value of the newsid because it's an integer not a character and cannot be duplicated because it's automatic.

* Part 4 :: B- Viewing Full News (read_more.php)

Code:

<?php

include("config.php");

$newsid = $_GET['newsid'];

$result = mysql_query("SELECT * FROM news WHERE newsid='$newsid' ",$connect);

while($myrow = mysql_fetch_assoc($result))

{

echo "<b>";

echo $myrow['title'];

echo "</b><br>On: <i>";

echo $myrow['dtime'];

echo "</i><hr>";

echo $myrow['text1'];

echo " ";

echo $myrow['text2'];

echo "<br><br><a href=\"javascript:self.history.back();\"><-- Go Back</a>";

}

Description:

1- $result = mysql_query("SELECT * FROM news WHERE newsid='$newsid' ",$connect);

This query will select only the news you meant to read, not a random one, suppose you're reading a news about security and you clicked on Read More.. then it must direct you to the rest of the security article you are reading not any other one. Here newsid plays it rule. As you see SELECT * FROM news WHERE newsid='$newsid' we selected from news table only the article that matched the newsid value. Suppose the security article has the number (3) then the value of the newsid must be (3).

You will understand this after you add news to the database and view it. Before you click on read more… watch the stats bar. You will se something like (http://localhost/news/read_more.php?newsid=3) and after you click, give a look at the address bar.

2- while($myrow = mysql_fetch_assoc($result)) {

echo "<b>";

echo $myrow['title'];

echo "</b><br>On: <i>";

echo $myrow['dtime'];

echo "</i><hr>";

echo $myrow['text1'];

echo " ";

echo $myrow['text2'];

echo "<br><br><a href=\"javascript:self.history.back();\"><-- Go Back</a>";

}

This will print only the selected news. Note there is: javascript:self.history.back(); this is java. When you click on go back it will direct you to the previous page.

Was that fun?!!! I think so. Hey ;) lets move next.

* Part 5 :: Editing News (edit_news.php)

Code:

<?php

include("config.php");

if(isset($_POST['submit']))

{

// Set global variables to easier names

// and prevent sql injection and apostrophe to break the db.

$title = mysql_escape_string($_POST['title']);

$text1 = mysql_escape_string($_POST['text1']);

$text2 = mysql_escape_string($_POST['text2']);

$result = mysql_query("UPDATE news SET title='$title', text1='$text1', text2='$text2' WHERE newsid='$newsid' ",$connect);

echo "<b>Thank you! News UPDATED Successfully!<br>You'll be redirected to Home Page after (4) Seconds";

echo "<meta http-equiv=Refresh content=4;url=index.php>";

}

elseif(isset($_GET['newsid']))

{

$result = mysql_query("SELECT * FROM news WHERE newsid='$_GET[newsid]' ",$connect);

while($myrow = mysql_fetch_assoc($result))

{

$title = $myrow["title"];

$text1 = $myrow["text1"];

$text2= $myrow["text2"];

<br>

Title: <input name="title" size="40" maxlength="255" value="<? echo $title; ?>">

<br>

Text1: <textarea name="text1" rows="7" cols="30"><? echo $text1; ?></textarea>

<br>

Text2: <textarea name="text2" rows="7" cols="30"><? echo $text2; ?></textarea>

<br>

</form>

}//end of while loop

}//end else

What was that?

This script exactly the same as (add_news.php) but here we update a previously added news.

1- include("config.php"); :loads the connection.

2- if($submit) : if you clicked on (Update News) button.

3- $title = mysql_escape_string($_POST['title']);

$text1 = mysql_escape_string($_POST['text1']);

$text2 = mysql_escape_string($_POST['text2']);

Define easy variables to hold the value of the form fields and prevent sql injections.

4- $result = mysql_query("UPDATE news SET title='$title', text1='$text1', text2='$text2' WHERE newsid='$newsid' ",$connect);

This query updates a specified news depending on the newsid value.

5- elseif($newsid): if the form wasn't submitted then view the news before updating. Here we used the value of newsid to drag the data from the database in put it in the form fields.

6- <input type="hidden" name="newsid" value="<? echo $myrow['newsid']?>">

Is used to print the news in the form fields in-order to modify.

- <input type="submit" name="submit" value="Update News"></form>

The end of the form.

* Part 6 :: Deleting News (delete_news.php)

Code:

<?php

include("config.php");

$newsid = $_GET['newsid'];

$result = mysql_query("DELETE FROM news WHERE newsid='$newsid' ",$connect);

echo "<b>News Deleted!<br>You'll be redirected to Home Page after (4) Seconds";

//header("location: index.php");

echo "<meta http-equiv=Refresh content=4;url=index.php>";

The final part which needs to be created is a page to delete news. As with the edit news page we depend on the newsid and when you click on delete a URL like delete_news.php?newsid=3 will be shown to you.
The code to do this is the same as to edit news, except with a slightly different MySQL query. Instead of the UPDATE query you should use: DELETE FROM news WHERE newsid='$newsid' .

There we go, a nice simple method to create a news system!

Thank you for you time reading my tutorial. I hope you understand it and got the maximum benefit.

Author: Mohammed Ahmed
Country: Palestine, Gaza
Email/MSN: m (at) maaking.com

Web: http://www.maaking.com