How to update and delete data from a database.

Scooby_Doo

I am trying to make a CMS (Content Management Script) and would like to know how to update and delete data from a database. I have made a form that allows my Mom to enter the dogs info, and a page that I can pull the data out of a database. Here's the page's code to pull the data out of the database.

Getdogs.php

<html>
<head>
<title>Your Dogs</title>

</head>
<body>

<a href="listprice.php">List by Price</a>
<a href="listbreed.php">List by Breed</a>

<?php
$con = mysql_connect("host","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("database", $con);

$result = mysql_query("SELECT * FROM addpup");

echo "<table border='1'>
<tr>
<th>Name</th>
<th>Breed</th>
<th>Price</th>
<th>Description</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['breed'] . "</td>";
  echo "<td>" . $row['price'] . "</td>";
  echo "<td>" . $row['description'] . "</td>"; 
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>
</body>
</html>

In the database I have included the id section, but I don't know how to
select the right id
make it a link to edit that dog or delete that dog.

Thanks for taking the time to read my post.

I have looked at every site I could find on how to update or to delete data in a database.

chasiv

I'm assuming you have an 'id' field as you stated... so change your code that displays the data to this:

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr>"; 
  echo "<td><input type='radio' id='id' name='id' value=" . $row['id_field_name'] . "></td>";
  echo "<td>" . $row['name'] . "</td>"; 
  echo "<td>" . $row['breed'] . "</td>"; 
  echo "<td>" . $row['price'] . "</td>"; 
  echo "<td>" . $row['description'] . "</td>"; 
  echo "</tr>"; 
  } 
echo "</table>";

This should put a radio button in front of each line of dogs... When selected, the $_POST['id'] value will be your id value from your database. If you id is not numeric you'll need to change that line I input to be:

  echo "<td><input type='radio' id='id' name='id' value='" . $row['id_field_name'] . "'></td>";

chasiv

Of course, then you'll add buttons to the form so you can process the selection. If you need help with that ... just say so.

Scooby_Doo

WOW thank you so much I never would have done that. What do you mean by add buttons to the form to process the form, like add

<input type="submit" value="Submit">

Thanks so much.

Oh I forgot I do have it set up with numeric value.

chasiv

Yes... Based on what you said you were trying to do, you'd end up with:

<input type="submit" id='btnSubmit' name='btnSubmit' value="Edit Record">
<input type="submit" id='btnSubmit' name='btnSubmit' value="Delete Record">

Then, once one of the buttons is clicked you'll have an action to the form (let's call your action page 'process.php').

In process.php, you'll handle the action based on which button was clicked:

<?php
If($_POST['btnSubmit'] == "Edit Record") {
     <your update SQL statement & processing here>
} else if ($_POST['btnSubmit'] == "Delete Record") {
    <your delete SQL statement & processing here>
}

That is just the basics... You'll want to do validation and escape out any special characters and stuff to avoid someone from hacking your database. You could also, although a little more complex, have the form page as a PHP Page and just submit to itself as well.... based on the button value.

// Set stage level for form
If(!$_POST['btnSubmit']) { $stage=1; }
If($_POST['btnSubmit'] == 'Edit Record') { $stage=2; }
If($_POST['btnSubmit'] == 'Delete Record') { $stage=3; }

Switch ($stage) {
     Case 1 :
           <display form code>;
           break;
     Case 2 :
           <Run code to Edit Record>;
           break;
     Case 3 :
           <Run code to Delete Record>;
           break;
}

It's all a matter of what you are comfortable with....

Scooby_Doo

ok, I started working with what you said and I don't know if this is the correct way to do it, but if it is then, I need help getting the pid to show up in the correct place.

Here's the page that pulls the data in the database and what I want it to do is when you click EDIT OR DELETE it takes to to the next page (below this page) where it displays the form with all the information that was already in for that dog. and if you select EDIT PUPPY it uses CASE 2 and if you push DELETE it deletes the puppy using Case 3. I don't know if this works or not, because I was going to wait and see what you think I should do different.

Here is the page that takes all the information from the database. (getdogs.php)

<html>
<head>
<title>Your Dogs</title>

</head>
<body>

<a href="listprice.php">List by Price</a>
<a href="listbreed.php">List by Breed</a>

<?php
$con = mysql_connect("HOST","USERNAME","PASSWORD");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("tttbd", $con);

$result = mysql_query("SELECT * FROM addpup");

echo "<table border='1'>
<tr>
<th>&nbsp;</th>
<th>Name</th>
<th>Breed</th>
<th>Price</th>
<th>Description</th>
<th>Edit or Delete this dog</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td><input type='radio' id='id' name='id' value=" . $row['pid'] . "></td>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['breed'] . "</td>";
  echo "<td>" . $row['price'] . "</td>";
  echo "<td width=450>" . $row['description'] . "</td>";
  echo "<td><a href=\"editdog.php?id=\"" . $row['pid'] . "\">EDIT OR DELETE</a></td>";  

  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>
</body>
</html>

And here is the page that the edit form is on (editdog.php)

<?php
include("connect.php");

// Set stage level for form
If(!$_POST['btnSubmit']) { $stage=1; }
If($_POST['btnSubmit'] == 'Edit Record') { $stage=2; }
If($_POST['btnSubmit'] == 'Delete Record') { $stage=3; }

Switch ($stage) {
     Case 1 :
           <form action="$PHP_SELF" method="POST">
				<input type="text" name="name">
				<input type="text" name="price">
				<textarea cols="30" rows="6" name="description"></textarea>
					<input type="submit" id='btnSubmit' name='btnSubmit' value="Edit Puppy">
                    <input type="submit" id='btnSubmit' name='btnSubmit' value="Delete Puppy">;
           break;
     Case 2 :
           mysql_query("UPDATE addpup SET name = '$_POST['name']' price = '$_POST['price']' description = '$_POST['description']'
            WHERE pid = '$_POST['pid']");
           break;
     Case 3 :
           mysql_query("DELETE FROM addpup 
		                WHERE pid='$_POST['pid']'");;
           break;
}
mysql_close($con);
?>

pics

scoobydoo, Heads up... you have your database info in your post above.

Scooby_Doo

pics wrote:
scoobydoo, Heads up... you have your database info in your post above.

Thanks so much, I didn't even notice.:eek:

chasiv

a few notes...

Since you are using a editdog.php page to handle the results.. you want case 1 to be an error.. Noone should be getting to editdog.php unless they have gone through the form to get there. So:

Case 1:
     exit ("You have reached this page in error.  Please contact the system administrator");  // or something of that nature.
     break;

If you don't have an appropriate $_POST['btnSubmit'] value, then they didn't come from the form ;-)

What you've outlined is a 2 phase process (which is very good and simple). Thus, you want the <form> attribute to be on Page 1.. Let me outline with some corrections:

Your Form to select the record for Editing/Deleting would be like so (getdogs.php):

<form action='editdogs.php' method='post' id='form' name='form'>

<?php
mysql_select_db("tttbd", $con); 

$result = mysql_query("SELECT * FROM addpup"); 

echo "<table border='1'> 
<tr> 
<th>&nbsp;</th> 
<th>Name</th> 
<th>Breed</th> 
<th>Price</th> 
<th>Description</th> 
</tr>"; 

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr>"; 
  echo "<td><input type='radio' id='pid' name='pid' value=" . $row['pid'] . "></td>"; 
  echo "<td><input type='text' id='name' name='name' />" . $row['name'] . "</td>"; // this must be a text box if the user is going to edit the record
  echo "<td><input type='text' id='breed' name='breed' />" . $row['breed'] . "</td>"; 
  echo "<td><input type='text' id='price' name='price' />" . $row['price'] . "</td>"; 
  echo "<td width=450><input type='textarea' id='description' name='description' />" . $row['description'] . "</td>"; 
  echo "</tr>"; 
  } 
echo "</table>"; 
echo "<input type='submit' name='btnSubmit' id='btnSubmit' Value='Edit Selected Record' />;"
echo "<input type='submit' name='btnSubmit' id='btnSubmit' Value='Delete Selected Record' />;"
mysql_close($con); 
?> 
</form>

Then.. your editdogs.php will be like so:


<?php 
include("connect.php"); 

// Set stage level for form 
If(!$_POST['btnSubmit']) { $stage=1; } 
If($_POST['btnSubmit'] == 'Edit Selected Record') { $stage=2; } 
If($_POST['btnSubmit'] == 'Delete Selected Record') { $stage=3; } 

Switch ($stage) { 
     Case 1 : 
           exit ("There is a problem processing your request. Page aborted !!");
           break; 
     Case 2 : 
           // your validation and character escaping here......
           mysql_query("UPDATE addpup SET name = '$_POST['name']', price = '$_POST['price']', description = '$_POST['description']' 
            WHERE pid = '$_POST['pid']'"); 
           break; 
     Case 3 : 
           mysql_query("DELETE FROM addpup 
                        WHERE pid='$_POST['pid']'");; 
           break; 
} 
mysql_close($con); 
?>

That is very basic and raw and I don't have a server to check it with here at work, but should get you pointed in the right direction...

You'll definitely want to do some testing before letting it loose. For example, you'll want to mysql_real_escape_string() amongst other things to parse the input or else someone evil could hack your database.

I also tend to create a $strSQL variable and echo to screen for testing.. I also like to look at the $_POST array during testing...

Examples of test codes I use:

$strSQL = "SELECT * FROM addpup";
echo "SQL = '" . $strSQL . "' <br />";
$result = mysql_query($strSQL);

That allows me to see the SQL statement on the screen for any errors (Notice I put single quotes around it).

This will display all the values in the $POST variable. There are other ways of doing it also... but this is what I use:

echo "<pre>";
print_r($_POST);
echo "</pre>";

Different little things I've come across during my year or so of PHP Programming...
Obviously, when you have it working the way you want it you just delete (or remark) them out.
Hope they help and feel free to keep the thread going until you get it working and I'll help as much as I can with my knowledge.

Scooby_Doo

Thank you so much, I got it to work but when I tried to change somethings to make it easier for my Mom to use it. I think I messed it up. Also how do I set up an error msg on the editdogs page? Here is my get dogs page.

<center>
<div style="overflow: auto; height: 450px; width: 744px;">

<form action='editdog.php' method='post' id='form' name='form'>

<?php
$con = mysql_connect("p41mysql91.secureserver.net","tttbd","TtT1234");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("tttbd", $con); 

$result = mysql_query("SELECT * FROM addpup"); 

echo "<table border='1'> 
<tr> 
<th>&nbsp;</th> 
<th>Name</th> 
<th>Breed</th> 
<th>Price</th> 
<th>Description</th> 
</tr>"; 

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr>"; 
  echo "<td><input type='radio' id='pid' name='pid' value=" . $row['pid'] . "></td>";
  echo "<td>Name:<input type='text' id='name' name='name' />" . $row['name'] . "</td>"; // this must be a text box if the user is going to edit the record
  echo "<td>Price: $<input type='text' id='price' name='price' />" . $row['price'] . "</td>";
  echo "<td width=450>Description:<input type='textarea' cols=\"30\" rows=\"6\" id='description' name='description' />" . $row['description'] . "</textarea></td>"; 
  echo "</tr>"; 
  } 
echo "</table>";
echo "</div>"; 
echo "<input type='submit' name='btnSubmit' id='btnSubmit' Value='Edit Selected Dog' />";
echo "<input type='submit' name='btnSubmit' id='btnSubmit' Value='Delete Selected Dog' />";
mysql_close($con); 
?>
</form>
<a href="/addpuppy.php">Add another puppy</a>
</center>

and the edit dog's page. MSG 2 is the message that shows up.

<?php 
$con = mysql_connect("p41mysql91.secureserver.net","tttbd","TtT1234");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("tttbd", $con);

// Set stage level for form 
If(!$_POST['btnSubmit']) { $stage=1; } 
If($_POST['btnSubmit'] == 'Edit Selected Record') { $stage=2; } 
If($_POST['btnSubmit'] == 'Delete Selected Record') { $stage=3; } 

Switch ($stage) { 
     Case 1 : 
           exit ("There is a problem processing your request. Page aborted !!");
           break; 
     Case 2 : 
           // your validation and character escaping here......
           mysql_query("UPDATE addpup SET name = '$_POST[name]', price = '$_POST[price]', description = '$_POST[description] ' 
            WHERE pid = '$_POST[pid]' "); 
           break; 
     Case 3 : 
           mysql_query("DELETE FROM addpup 
                        WHERE pid='$_POST[pid]' ");; 
           break;
echo "MSG 1";		   

}
echo "MSG 2";
mysql_close($con); 
?>

chasiv

You put your echo "MSG 1"; line inside the SWITCH { } process, so it will not display --EVER !!

Your MSG 2 will show any time the page is appropriately accessed. So whether the user Edited or Deleted a record -- it will show up.

Anything you want to do to execute error checking and such should be done inside the appropriate Case areas.

Scooby_Doo

hey, I changed the messages and that works correctly and I changed the buttons name on the form but not in the proccesing page. it now deletes correctly but not update. I posted a video on youtube and it isn't done proccessing yet. but by the time you read this it should be done.

http://www.youtube.com/v/zcEvTtdq2R4

here is the page that the form is on.

<html>
<head>
<title>Your Dogs</title>

</head>
<body>

<a href="listprice.php">List by Price</a>
<a href="listbreed.php">List by Breed</a>

<div style="overflow: auto; height: 425px; width: 744px;">

<form action="editdog.php" method="POST">
<?php
$con = mysql_connect("p41mysql91.secureserver.net","tttbd","TtT1234");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("tttbd", $con);

$result = mysql_query("SELECT * FROM addpup");

echo "<table border='1'>
<tr>
<th>&nbsp;</th>
<th>Breed</th>
<th>Name</th>
<th>Price</th>
<th>Description</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td><input type='radio' id='pid' name='pid' value=" . $row['pid'] . "></td>";
  echo "<td>" . $row['breed'] . "</td>";
  echo "<td><input type='text' id='name' name='name' value=" . $row['name'] . " /></td>";
  echo "<td>$<input type='text' id='price' name='price' value=" . $row['price'] .  " /></td>";
  echo "<td width=\"300\"><textarea cols=\"30\" rows=\"6\" id='description' name='description' />" . $row['description'] . "</textarea></td>";
  echo "</tr>";
  }
echo "</table>";
echo "</div>";

  echo "<td><input type='submit' name='btnSubmit' id='btnSubmit' Value='Edit Selected Dog' /></td>";
  echo "<td><input type='submit' name='btnSubmit' id='btnSubmit' Value='Delete Selected Dog' /></td>";  

echo "<a href=\"addpuppy.php\">Click Here to add another puppy</a>";
mysql_close($con);
?>
</body>
</html>

and the page that proccesses it.

<?php 
$con = mysql_connect("p41mysql91.secureserver.net","tttbd","TtT1234");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("tttbd", $con);

// Set stage level for form 
If(!$_POST['btnSubmit']) { $stage=1; } 
If($_POST['btnSubmit'] == 'Edit Selected Dog') { $stage=2; } 
If($_POST['btnSubmit'] == 'Delete Selected Dog') { $stage=3; } 

Switch ($stage) { 
     Case 1 : 
           exit ("There is a problem processing your request. Page aborted !!");
           break; 
     Case 2 : 
           // your validation and character escaping here......
           mysql_query("UPDATE addpup SET name = '$_POST[name]', price = '$_POST[price]', description = '$_POST[description] ' 
            WHERE pid = '$_POST[pid]' "); 
			echo "Your Puppy has been updated <a href=\"getdogs.php\">Please Click Here To  Manage Your Dogs</a>";
           break; 
     Case 3 : 
           mysql_query("DELETE FROM addpup 
                        WHERE pid='$_POST[pid]' ");
			echo "Your Puppy was deleted <a href=\"getdogs.php\">Please Click Here To Mangage Your Dogs</a>";
           break;
}
mysql_close($con); 
?>

chasiv

Change this:

mysql_query("UPDATE addpup SET name = '$_POST[name]', price = '$_POST[price]', description = '$_POST[description] ' WHERE pid = '$_POST[pid]' ");

to this:

$strSQL = "UPDATE addpup SET name = '" . $_POST['name'] . "'";
$strSQL .= ", price = '" . $_POST['price'] . "'";
$strSQL .= ", description = '" . $_POST['description'] . "'";
$strSQL .= " WHERE pid = '" . $_POST['pid'] . "'";
$result = mysql_query($strSQL);

I would also suggest you echo the $strSQL variable to screen in testing to assure it looks correct. (then remove it once testing is completed).

Scooby_Doo

Well, I tried that and it lets me update a dog, but if I have two it does the same thing as before, This is what my editdog.php page looks like with the updated code.

<?php 
$con = mysql_connect("HOST","USER","PASS");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("tttbd", $con);

// Set stage level for form 
If(!$_POST['btnSubmit']) { $stage=1; } 
If($_POST['btnSubmit'] == 'Edit Selected Dog') { $stage=2; } 
If($_POST['btnSubmit'] == 'Delete Selected Dog') { $stage=3; } 

Switch ($stage) { 
     Case 1 : 
           exit ("There is a problem processing your request. Page aborted !!");
           break; 
     Case 2 : 
           // your validation and character escaping here......
$strSQL = "UPDATE addpup SET name = '" . $_POST['name'] . "'";
$strSQL .= ", price = '" . $_POST['price'] . "'";
$strSQL .= ", description = '" . $_POST['description'] . "'";
$strSQL .= " WHERE pid = '" . $_POST['pid'] . "'";
$result = mysql_query($strSQL); 
			echo "Your Puppy has been updated <a href=\"getdogs.php\">Please Click Here To  Manage Your Dogs</a>";
           break; 
     Case 3 : 
           mysql_query("DELETE FROM addpup 
                        WHERE pid='$_POST[pid]' ");
			echo "Your Puppy was deleted <a href=\"getdogs.php\">Please Click Here To Mangage Your Dogs</a>";
           break;
}
mysql_close($con); 
?>

also how do I get it to post the data like you were saying earlier. I don't understand.

chasiv

you may want to remove your server info form your last post...

I'm a little confused. You have 2 dogs with the same pid?
You want to post the data to where ?

roscor

Heads up again scooby Doo database conn info!!!!!!!!!!!!!!!!!!!!