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

来源：互联网发布：林州市2013年旅游数据编辑：程序博客网时间：2024/05/16 07:47

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 themdidn'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 basicknowledge of how to use PHP. If you do not yet know PHP, I suggest that youread this PHP Tutorial http://www.freewebmasterhelp.com/tutorials/php/

During this tutorial we willuse 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 thistutorial, you will be able to (Add, View, Edit and Delete from to MySQL databse).

In this news system we aregoing 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 databaseusing 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=MyISAMAUTO_INCREMENT=1 ;

The code will create a tablethat looks like this:

newsid

dtime

title

text1

text2

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

Please CREATE a TABLEnamed news with the following columns andproperties:

1- (column-name newsidwith datatype INTinteger with max 11 digits long, it must be NOT NULLand it should be AUTO_INCREMENT,

2- column-name dtimewith datatype datetime, it is NULL,

3- column-name title with datatype VARCHAR withmax (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 beginwith the table name AUTO_INCREMENT=1finally, make the newsidauto inserted. End sql-statement ;

Now we're done with thecreation of the database and the table, in the next part we will setup theconnection 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 anyoperation on a database you must connect to the MySQLserver. 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 justto set all the variables, it looks better and it's much easier to see what youare doing once the code starts to get big.

$dbhost is a variable and holds your localhostor server name.

$dbusername, is a variable and holds your databaseusername

$dbpassword, is a variable and holds your username password

$dbname isa variable and holds your database name

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

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

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

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

Next we will learn how to addnews 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).

// Setglobal 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){ //thismeans 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 anythingelse.

}// end of if

//runthe 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 toHome Page after (4) Seconds";

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

}//end of if($submit).

// If the formhas not been submitted, display it!

else

{//begin ofelse

<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>

<inputtype="submit" name="submit" value="Add News">

</form>

}//end of else

Now letsdescribe what was that.

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

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

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

<inputtype="submit" name="submit" value="Add News"> you see the name of the form is (submit) and of course you can change towhatever 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 sqlinjection and apostrophe to break the db.

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

and mysql_escape_string will prevent sqlinjection

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

and mysql_escape_string will prevent sqlinjection

$_POST is a superglobal variable of PHP.

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

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

6- $result = mysql_query("INSERTINTO 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 thatwill 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 inthe database using the following format: (2004-10-2714:05:28), remember that weused (datatype datetime) whenwe created the table. So if we used NOW() the datatype must be datetime.
c- '$text1' : returns the news home text you entered in theform.
d- '$text2' :returns the news body text you entered in the form.
and $connect wasdefined before in the config.php file, you see wecalled it here.

* Congratulations. Every thing is okay! Now print the successmessage:
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 successmessage and redirect you to the index.php page after4 seconds you can change it to whatever you want. It is very simple as you see.

7- else{

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

<form method="post"action="<?php echo$PHP_SELF ?>"> andthe 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 arewondering what the PHP is in there for - that variable, $PHP_SELF uses the current URL as the action for the form - soyou can put the form handling script in the same document as the HMTL - whichis especially useful when your script isn't very long, as in this case.
was that very easy? Now letsgo to the next step.

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

Code:

<?php

// load theconfiguration file.

include("config.php");

//load all news from the database andthen OREDER them by newsid

//youwill notice that newlly added news willappeare first.

//alsoyou can OREDER by (dtime) instaedof (news id)

$result= mysql_query("SELECT * FROM news ORDER BY newsidDESC",$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><hralign=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]/">ReadMore...</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 letsdescribe what was that?

1- $result = mysql_query("SELECT * FROMnews ORDER BY newsid DESC",$connect);
The script above is using the mysql_query function to retrieve records or rows from ournews 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 lastrecored.

- $myrow: avariable, will hold the records data.

- mysql_fetch_assoc :Returns an associative array that corresponds to the fetched row and itaccesses the data retrieved by the previous mysql_querystored 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 thevalue of the row which comes from the table, and so for the rest.

print the results and prin theoptions 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)pagewhere 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 whereyou 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) pagewhere it will delete the news you selected.

Why weused:$myrow[newsid]?

Do you remember when wecreated the news table we created a column-namenewsid with datatype INT integerwith max 11 digits long, AUTO_INCREMENT=1 ,make the newsidauto inserted.

Well, each time you add newsto the database the newsidfield will be increased one number, so, for the first time you added news, the newsid will become (1) andif 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 dependon the value of the newsidbecause it's an integer not a character and cannot be duplicated because it'sautomatic.

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

Code:

<?php

include("config.php");

$newsid = $_GET['newsid'];

$result = mysql_query("SELECT * FROMnews 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();/"><-- GoBack</a>";

}

Description:

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

This query will select only thenews you meant to read, not a random one, suppose you're reading a news aboutsecurity and you clicked on Read More.. then it must direct you to the rest of the security articleyou 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 articlethat matched the newsidvalue. Suppose the security article has the number (3) then the value of the newsid must be (3).

You will understand thisafter 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 lookat 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();/"><-- GoBack</a>";

}

This will print only theselected 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']))

{

// Setglobal variables to easier names

// and prevent sqlinjection 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 toHome 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>

<h3>::EditNews</h3>

Title: <inputname="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 holdthe value of the form fields and prevent sql injections.

4- $result = mysql_query("UPDATE news SETtitle='$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 thenews before updating. Here we used the value of newsid to drag the data from the database input it in the form fields.

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

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

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

The end ofthe form.

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

Code:

<?php

include("config.php");

$newsid = $_GET['newsid'];

$result = mysql_query("DELETE FROM newsWHERE 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 becreated is a page to delete news. As with the edit news page we depend on the newsid and when you clickon delete a URL like delete_news.php?newsid=3 willbe shown to you.
The code to do this is the same as to edit news, except with a slightlydifferent MySQL query. Instead of the UPDATE queryyou should use: DELETEFROM 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 andgot the maximum benefit.

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

Web: http://www.maaking.com