...

View Full Version : Help with using forms to delete records from a database



frankmoss
01-26-2007, 06:15 AM
I am trying to make a PHP script that displays all of the records from a database, then allows a user to edit or delete the records via a form. I need a checkbox beside the names and a text field with a number beside each name. Is there a way to make it delete all names with a check beside them and update all number that have been changed when the user clicks submit? (this is what i currently have: http://www.cookevilleib.com/creditsedit.php)

koyama
01-26-2007, 11:02 AM
Hi frankmoss

Do you have an 'id' for each record in your database. This would probably be best. You could then name each checkbox e.g. like this:


echo '<input type="checkbox" name=delete['.$id.'] value="on">';

This would give you an array on server side after form submission, e.g.



$_POST['delete'][5] = 'on'
$_POST['delete'][8] = 'on'


for the checkboxes that were checked. Then loop through the $_POST['delete'] array to find out which records to delete.

Now you will need to make an extra submit button for deletion nest to the 'update' button. Give the submit buttons different name attribute to detect whether the user wants to delete or update.



<input type="submit" name="opDelete" value="Delete" />
<input type="submit" name="opUpdate" value="Update" />


Then do something like this:


if($_POST['opDelete']) {
// delete stuff
}
if($_POST['opUpdate']) {
// update stuff
}

frankmoss
01-26-2007, 03:38 PM
Thanks!

I am very new to PHP and I still do not completely understand.


$query = "SELECT * FROM $usertable";
$result = mysql_query($query);
if($result) {
echo "<html>
<head>
<link href=beta.css rel=stylesheet type=text/css />
</head>
<body>
<table width=750px align=center>
<form method=post action=creditsedit.php name=creditedit>";
while($row = mysql_fetch_array($result)){
$name = $row["Name"];
$grade = $row["Grade"];
$credits = $row["Credits"];
echo "<tr>
<td class=left><input type=checkbox name=deletename value=$name> $name
</td>
<td class=left><input type=text name=gradeedit size=3 value=$grade>
</td>
<td class=left><input type=text name=creditedit size=5 value=$credits>
</td>
</tr>";
}
echo "<tr><td>&nbsp;</td><td class=left><input type=submit name=opDelete value=Delete></td><td class=left><br><input type=submit name=opUpdate value=Update></tr></td></form></table></body></html>;
}

I have this code that echos all of the records from a database table. I dont understand how it would delete multiple records.

Right now my sql statement looks like this:

if (isset($_POST['opDelete']))
{
$deletename = $_POST['deletename'];
mysql_query("Delete FROM $usertable WHERE 'Name' = $deletename");}

Is this right?

And how do I use the [$id] you were talking about?

koyama
01-26-2007, 07:19 PM
Try to use this loop for echoing your table. I changed the name attribute for the checkbox.


while($row = mysql_fetch_array($result)){
$name = $row["Name"];
$grade = $row["Grade"];
$credits = $row["Credits"];

echo "<tr><td class=left><input type=\"checkbox\" name=\"delete[$name]\" />$name</td>\n";
echo "<td class=left><input type=text name=gradeedit size=3 value=$grade></td>\n";
echo "<td class=left><input type=text name=creditedit size=5 value=$credits></td>\n";
echo "</tr>\n";
}

To delete the multiple records that were checked, try like this


if(isset($_POST['opDelete']) and isset($_POST['delete']))
{
foreach($_POST['delete'] as $name => $dummy) {
$sql = "Delete FROM $usertable WHERE 'Name' = '$name'";
mysql_query($sql);
}
}
What I meant with the $id was that it would probably be better to create a column in your table with an integer to identify each record. It is smoother if one references just the id. You would then just send the 'id's instead of the 'name's to delete.

Tips. Put in some newlines "\n" like I did when you echo html. This makes it easier when you have to look at the source in your browser.

frankmoss
01-26-2007, 07:30 PM
What should the action on the form be?

koyama
01-26-2007, 07:41 PM
For now, let the form point to itself as it is now (so everything is collected in one file, would that be creditsedit.php ?) and put the snippet for deleting in the very top of your script right after the db connection part.


<?php
// file: creditsedit.php

// db connection stuff
if(isset($_POST['opDelete']) and isset($_POST['delete']))
{
...
}
$query = "SELECT * FROM $usertable";
$result = mysql_query($query);
if($result) {
echo "<html>
<form method=\"post\" action=\"creditsedit.php\">";
...
?>

frankmoss
01-26-2007, 07:52 PM
I have to be doing something wrong...it still won't work. :confused:


<?php
$hostname="***********";
$username="*********";
$password="**********";
$dbname="Betaclubwebsite";
$usertable="Credits";

mysql_connect($hostname,$username, $password) OR DIE ("Unable to connect to database! Please try again later.");
mysql_select_db($dbname);

if(isset($_POST['opDelete']) and isset($_POST['delete']))
{
foreach($_POST['delete'] as $name => $dummy) {
$sql = "DELETE FROM 'Credits' WHERE 'Name' = '$name'";
mysql_query($sql);
}
}

$query = "SELECT * FROM $usertable";
$result = mysql_query($query);
if($result) {
echo "<html>
<body>
<table width=500 align=center>
<tr>
<td class=left><h3>Name</h3></td>
<td class=left><h3>Grade</h3></td>
<td class=left><h3>Credits</h3></td>
</tr>
<form method=\"post\" action=\"creditsedit.php\">";
while($row = mysql_fetch_array($result)){
$name = $row["Name"];
$grade = $row["Grade"];
$credits = $row["Credits"];

echo "<tr><td class=left><input type=\"checkbox\" name=\"delete[$name]\" />$name</td>\n";
echo "<td class=left><input type=text name=gradeedit size=3 value=$grade></td>\n";
echo "<td class=left><input type=text name=creditedit size=5 value=$credits></td>\n";
echo "</tr>\n";
}
echo "<tr><td>&nbsp;</td><td class=left><br><input type=submit name=opDelete value=Delete>";
echo "</td><td class=left><br><input type=submit name=opUpdate value=Update></tr></td>";
echo "</form></table></body></html>";
}
?>

That's what I have. Do you see any problems? It's at http://www.cookevilleib.com/creditsedit.php if you want to see what it does.

koyama
01-26-2007, 08:08 PM
Try removing your quotes around the table name (or use backticks `Credits`)


$sql = "DELETE FROM Credits WHERE 'Name' = '$name'";

Generally it is a very good idea to put in die statements while testing at least Because php (deliberately) will not show database related errors


mysql_query($sql) or die('Bad query: '.$sql);

frankmoss
01-26-2007, 08:20 PM
The back ticks did it! Thanks so much!

Would I use a similar sql statement to update grade and credit numbers?

And how do I make it ask the user if he is sure that he wants to delete the names?

koyama
01-26-2007, 08:47 PM
yeah, it would be quite similar. You would have to add another if-block next to the if-block for deleting--just with update queries in a loop.

As for asking the user if he's sure he wants to delete you have to ask yourself if you want to rely on client-side scripting. You could then just add a few lines of javascript. You could also do it server-side with php usings sessions. For now, I would just choose the javascript solution.

One more thing: The next thing you'll be asking. Now that your script is firing try to hit the refresh button after deleting or updating and you'll see a problem. Your requests contains post data. To avoid this problem, modify to this:


if(isset($_POST['opDelete']) and isset($_POST['delete']))
{
foreach($_POST['delete'] as $name => $dummy) {
$sql = "DELETE FROM Credits WHERE 'Name' = '$name'";
mysql_query($sql);
}
header('Location: http://'.$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI']);
exit();
}

frankmoss
01-26-2007, 10:50 PM
The deleting part of the script works but I can't get the updating part to work.

This is what i have:

if(isset($_POST['opUpdate']) and isset($_POST['gradeedit']))
{
$gradeedit = $_POST['gradeedit'];
$creditedit = $_POST['creditedit'];
foreach($_POST['gradeedit'] as $namee => $dummy) {
$sql = "UPDATE `Credits` SET `Grade` = '$gradeedit', `Credit` = '$creditedit' WHERE `Name` = '$name' LIMIT 1";
mysql_query($sql) or die('Bad query: '.$sql);
}
header('Location: http://'.$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI']);
exit();
}

...

<form method=\"post\" action=\"creditsedit.php\">";
while($row = mysql_fetch_array($result)){
$name = $row["Name"];
$grade = $row["Grade"];
$credits = $row["Credit"];

echo "<tr><td class=left><input type=\"checkbox\" name=\"delete[$name]\" /> $name</td>\n";
echo "<input type=hidden name=name value=$name />";
echo "<td class=left><input type=\"text\" name=\"gradeedit[$name]\" size=\"3\" value=\"$grade\" /></td>\n";
echo "<td class=left><input type=\"text\" name=\"creditedit[name]\" size=\"5\" value=\"$credits\" /></td>\n";
echo "</tr>\n";
}
echo "<tr><td class=left><br><input type=submit name=opDelete value=Delete />\n";
echo "</td><td>&nbsp;</td><td class=left><br><input type=submit name=opUpdate value=Update /></td></tr>\n";

It gives me an error about an invalid "foreach"
I also think that the variables arent right because it puts "Array" in the fields.

koyama
01-26-2007, 11:18 PM
You are a fast learner frankmoss...
but you have been typing too fast ;)

echo "<td class=left><input type=\"text\" name=\"creditedit[$name]\" size=\"5\" value=\"$credits\" /></td>\n";

Your update loop should also be slightly modified (notice you wrote $namee)


if(isset($_POST['opUpdate']) and isset($_POST['gradeedit']))
{
foreach($_POST['gradeedit'] as $name => $dummy) {
$gradeedit = $_POST['gradeedit'][$name];
$creditedit = $_POST['creditedit'][$name];
$sql = "UPDATE `Credits` SET `Grade` = '$gradeedit', `Credit` = '$creditedit' WHERE `Name` = '$name'";
mysql_query($sql) or die('Bad query: '.$sql);
}
header('Location: http://'.$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI']);
exit();
}

I'm affraid I gave you some misleading advice. These 2 lines: wait inserting them until your script is firing. Otherwise the redirect prevents you from seeing any warning messages that were displayed.


header('Location: http://'.$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI']);
exit();

Still I recommend when you got things working, to add an `id` field in your table `Clients`

frankmoss
01-26-2007, 11:32 PM
Haha...I fixed those spelling errors, but it keeps putting "array" in the database instead of numbers. Do you know why?

Also I am planning on using an id when I get it working. Should I set it to auto_increment?

koyama
01-26-2007, 11:37 PM
I had slightly modified this part: which is why it inserts 'Array'

$gradeedit = $_POST['gradeedit'][$name];
$creditedit = $_POST['creditedit'][$name];

Yes, just use auto_increment.

frankmoss
01-26-2007, 11:41 PM
I have this:

if(isset($_POST['opUpdate']) and isset($_POST['gradeedit']))
{
$gradeedit = $_POST['gradeedit'][$name];
$creditedit = $_POST['creditedit'][$name];
foreach($_POST['creditedit'] as $name => $dummy) {
$sql = "UPDATE `Credits` SET `Grade` = '$gradeedit', `Credit` = '$creditedit' WHERE `Name` = '$name' LIMIT 1";
mysql_query($sql) or die('Bad query: '.$sql);
}
}

And it still inserts array...

koyama
01-26-2007, 11:44 PM
You have still not put it within the foreach-loop. This should fix it.

frankmoss
01-26-2007, 11:59 PM
Thanks! It works perfectly. I think I can do the rest of the site with this page (unless I mispell something ;) ).

What are the advantages to using an id? And would I use it like we used the $name?

koyama
01-27-2007, 12:21 AM
There are several reasons for having a separate id field. From a database viewpoint you would always want a primary key. Relying on the name is 'dangerous' since in theory (and in real world) two people could have the same name. Then your script would be deleting both persons!

Also, it is desirable to send as little data back and forth. Using an id would mean sending only a couple of bytes, but using the name you would send many more bytes. Moreover what happens when people have names containing special characters? Many things could go wrong having to do with encoding.

Also, suppose later you want to setup a special page to view details for a single person. You would then have a url like http://somedomain.com/detail.php?id=55 for person 55 in your table. If you didn't use id you would have to have a url like http://somedomain.com/detail.php?name=John%20Smith because you have to uri encode spaces etc. This only gives extra trouble.

As a last remark. You should consider what happens when you press 'enter' while the cursor is in a text field. Browsers usually submit the form, but since you have two submit buttons it is unclear which submit button is 'pressed' and you cannot rely on what browsers do. So consider using javascript to handle this situation.

frankmoss
01-27-2007, 01:11 AM
How would javascript handle that? What would the script looke like and how do I put javascript in PHP?

koyama
01-27-2007, 01:41 AM
php doesn't care the slightest whether you are echoing html or javascript or whatever. Now I am not a javascript expert but I would be doing something like this:

Add the same class name to all input text fields. For example:


<input class="update_field" type=\"text\" name=\"gradeedit[$name]\" size=\"3\" value=\"$grade\" />

Add an id to your update submit button


<input id="update_submit" type=\"submit\" name=\"opUpdate\" value=\"Update\" />


And for the script: echo this before your closing </body> tag


...
<script type="text/javascript">
// <![CDATA[

var inputs = [];
inputs = document.getElementsByTagName('input');

for(var j=0; j<inputs.length; j++) {
if(inputs[j].className == 'update_field') {
inputs[j].onkeypress = update_on_enter;
}
}

function update_on_enter(evt) {
e = evt || event;
if(e.keyCode == 13) {
document.getElementById('update_submit').click();
return false;
}
return true;
}
// ]]>
</script>
</body>
</html>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum