Editing an entry in a MySQL database

Dev Articles Community Forums Sponsor:

November 28th, 2002, 03:29 PM

steve55

Contributing User

Join Date: Nov 2002

Location: UK

Posts: 44

Time spent in forums: < 1 sec
Reputation Power: 11

Editing an entry in a MySQL database

I have managed to set up a database and have added a piece of data to it sucessfully - I have also managed to display the entries on a html page - BUT HOW DO I EDIT THEM !?

I'm just getting to grips with php and am having a problem trying to edit the entries. I am looking for some assistance..my code to add an entry from a form is as follows:-

PHP Code:


		
			
<?php



// This page receives and handles the data from "add.html".



//Trim the incoming data.



$Array["FirstName"] = trim($Array["FirstName"]);

$Array["LastName"] = trim($Array["LastName"]);

$Array["Email"] = trim($Array["Email"]);

$Array["Comments"] = trim($Array["Comments"]);





// Set the variables for access to the database.



$Host = "localhost";

$User = "stevesims";

$Password = "simcity";

$DBName = "stevesims";

$TableName = "Feedback";



$Link = mysql_connect ($Host, $User, $Password);





$Query = "INSERT into $TableName values ('0', '$Array[FirstName]', '$Array[LastName]', '$Array[Email]', '$Array[Comments]')";



echo ("The Query is : <br>$Query<p>\n");



if (mysql_db_query ($DBName, $Query, $Link)) {



echo ("The Query was successfully executed!<br>\n");



} else {



echo ("The Query could not be executed!<br>\n");

}





mysql_close  ($Link);





?>

<a href="DisplayDB.php"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">View
all Comments</font></b></a>

-------------------------------------------

I would like to create a similar piece of code to edit (& delete an entry).

Thank you!

Last edited by FrankieShakes : November 29th, 2002 at 11:03 AM.

November 28th, 2002, 03:35 PM

Kanu

Contributing User

Join Date: Nov 2002

Posts: 91

Time spent in forums: < 1 sec
Reputation Power: 11

"UPDATE tablename SET field= '$newfieldvalue' WHERE uniquefield = '$uniquefiedid'";

Can't remember delete, it's dead obvious though... check the MySQL manual...

"DELETE FROM table WHERE uniquefield= 'uniquevalue'";

EDIT:

Also, just a hint, when posting code, use the [php] tags to highlight code, makes it easier to read...

Last edited by Kanu : November 28th, 2002 at 03:39 PM.

November 29th, 2002, 01:28 AM

steve55

Contributing User

Join Date: Nov 2002

Location: UK

Posts: 44

Time spent in forums: < 1 sec
Reputation Power: 11

ok - I'll give it a go...
Out of interest, can the same be done with images and if so how do you get the images into the database and what tag would I use to display an image etc...

- I'll use your tip for the [php] tags as well, that seems like a good idea.

November 29th, 2002, 04:19 AM

Kanu

Contributing User

Join Date: Nov 2002

Posts: 91

Time spent in forums: < 1 sec
Reputation Power: 11

Depends on how you want to do it really. You can store the URL to an image in the database, or store the image in the database itself as a BLOB. Take a look at the thread on BLOB tutorial if you want to try the latter, otherwise PM me and I'll send you a script that handles URL storing...

November 29th, 2002, 01:58 PM

Kanu

Contributing User

Join Date: Nov 2002

Posts: 91

Time spent in forums: < 1 sec
Reputation Power: 11

Ok, as per my PM, here's what I would do...

As an example, I've used some code from a search results page I made up for a friend. It basically takes a searchword, searches the database for it, then iterates the results (including URL of a relevant image) into a set template. Admittedly it was an early script, and could probably be tidied up a fair bit...

PHP Code:


		
			
<?php

include("conf/conf.php");

// open database connection

$connection = mysql_connect($host, $user, $pass) or die ('Unable to connect!');

// select database

mysql_select_db($db) or die ('Unable to select database!');

//check using non global methods to see if form has been submitted

//Uses more secure methods from PHP 4.10 upward.

if($_REQUEST["submit"])

{

//Make sure search word doesn't contain special characters

//and assign the contents of request array to a standard variable

//for use in this page.

$searchword = htmlspecialchars($_REQUEST['searchword']);

}

echo "<html><head><title>Episode Guide: Search Results</title></head><body>";

echo "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#111111\" width=\"65%\">";



//Generate Query Based on Search Word Submitted

$query = "SELECT id, epName, epSeason, epShortReview, epRating, epImage1 FROM episodes WHERE epKeyWords LIKE '%$searchword%'";

$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());



//Sort Output and loop it's insertion into the predefined formatted table

$num = mysql_num_rows($result);

if ($num)

        {

while ($row = mysql_fetch_array($result)) {

echo <<<myhtml

<table border="0" width="100%"> 

<tr><td valign="left"> 

<img src="$row[epImage1]" height="150" width="200" align="right"> 

<font size="3" face="arial"><b>$row[epName]</b></font><br> 

<font face="arial" size="2">$row[epShortReview]</font> 

</td></tr> 

<tr><td valign="left"> 

<font face="arial" size="2"><center> 

[ <a href="http://stargatesg-1.net/guides.php?id=$row[id]">Episode Guide</a> | <a href="http://stargatesg-1.net/fanreviews.php?id=$row[id]">Fan Reviews</a> | <a href="http://stargatesg-1.net/">Images</a> | <a href="http://stargatesg-1.net/">Sound Bytes</a> 

| <a href="http://www.stargatesg-1.net/stargatesg1.php?season=$row[epSeason]">Season $row[epSeason]</a> ] 

</center></font> 

</td></tr> 

</table><br>

myhtml;

}

}

else

// show error message if the term produced no results...

{

echo <<<myerror

<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="65%" id="AutoNumber1">

  <tr>

    <td width="50%"><font size="3" face="Arial">Search Error</td>

  </tr>

  <tr>

    <td width="50%"><font size="1" face="Arial">Unfortunately, the term you entered did not 

    produce any results.&nbsp; The episode search engine operates by searching 

    each episode for Key Words related to that episode.&nbsp; For obvious 

    reasons therefore, common words like the names of characters are missed out, 

    otherwise your search would produce a list of every single episode whenever 

    you entered one of these words.</font><br><br><font size="1" face="Arial">You should try 

    your search again using more specific words to find a synopsis of the 

    episode.</font><p><font size="1" face="Arial">Click 

    <a href="javascript:history.back()">here</a> to go back.</font></td>

  </tr>

</table>

myerror;

}

?>

If you show me what you are trying to do or achieve with an working template, I'd be happy to explain and give you a working set of code for your site.

Editing an entry in a MySQL database

Developer Shed Advertisers and Affiliates