php / mysql delete help

tom11011 · May 5, 2004

Hi all, I am a newbie and have been tinkering with a php script that shows 3 fields on a webpage. Also, I have a form built in that allows you to add a row. It's working great. Can someone hand-hold me to create a process to delete a row? It could be a fourth column with a delete link or a seperate form where they type in the asset number and click a submit button. Thanks.

Here is the script:
--------------------

<html>
<body>
<table>
<tr>
<td width=300>
<?php
//make a connection with your details
$connection = mysql_connect("10.2.1.18" , "pcinv" , "pcinv")
or die ("Cannot make the connection");
//connect to the pcinv database with our connection details
$db = mysql_selectdb("pcinv" , $connection)
or die ("Cannot connect to the database");
//select all from sample table and store in $sql_query
$sql_query = "SELECT * FROM Inventory";
//store this in $result
$result = mysql_query($sql_query);
//if an entry exists
if(mysql_num_rows($result))
{
echo ("<table bgcolor=bbbbbb>");
echo ("<tr><td bgcolor=green><font size=-6 face=verdana>NC Asset</font></td><td bgcolor=green><font size=-6 face=verdana>User/Location</font></td><td bgcolor=green><font size=-6 face=verdana>Service Tag</font></td></tr>");
//loop through the fields
while($row = mysql_fetch_row($result))
{
//display the links $row[1] is the link , $row[0]
//is the id value
echo("<tr><td bgcolor=ffffff><font size=-6 face=verdana>$row[0]</font></td><td bgcolor=ffffff><font size=-6 face=verdana>$row[1]</font></td><td bgcolor=ffffff><font size=-6 face=verdana>$row[2]</font></td> </tr> ");
}
echo ("</table>");
}
//if no entry exists print a message
else
{
echo "no values in the database";
}
?>
</td>
<td width=250 valign=top>
<center><img src="logo.gif"></center><br><br>
<?php

if (isset($_POST['submit'])):
// A new entry has been entered
// using the form below
$asset = $_POST['Asset'];
$user = $_POST['User'];
$svctag = $_POST['Svctag'];
$sqladd = "INSERT INTO Inventory SET
Asset='$asset',
User='$user',
Svctag='$svctag'";
if (mysql_query($sqladd)) {
echo('<p><table><tr><td bgcolor=green>* New record added! *</td></tr></table></p>');
} else {
echo('<p>Error adding new record: ' .
mysql_error() . '</p>');
}

?>
<p><a href="<?=$_SERVER['PHP_SELF']?>">Return</a></p>

<?php
else: //
?>

<fieldset>
<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
<p> <b>Enter new record:</b><br /><br />
 NC Asset:    <input type="text" name="Asset" size="20" maxlength="9" /><br />
 User / Loc:   <input type="text" name="User" size="20" maxlength="50" /><br />
 Service Tag: <input type="text" name="Svctag" size="20" maxlength="200" /><br /><br />

 <input type="submit" name="submit" value="SUBMIT" /></p>
</form>
</fieldset>
<?php endif; ?>
</td>
</tr>
</table>
</body>
</html>

DRJ478 · May 5, 2004

Suggestions:

1. Print out a hyperlink next to the displayed items

Code:

echo('<a href="'.$_SERVER['PHP_SELF'].'?action=delete&id='.$row[0]..'">delete</a>');

2. Make your code check for $_GET['action'] and if it is 'delete' trigger the SQL query to remove the record by id

Code:

$removeSQL = "DELETE from inventory WHERE id=".$_GET['id'];

Also:
3. Use mysql_fetch_assoc($result) then you can refer to the column names as keys in the associative array.
$row['id'] says much more than $row[0]. What the he.. is $row[11]?

Warning:
This is primitive code. Anyone can delete things with this by appending an id parameter to the URL. It's just to get you started or for use in a safe environment where admin users are authenticated.

Even if you add client side scripting and change to POST it's just a bit harder, but still quite easily possible to post to that URL.
I'd introduce a check if the page was referred from itself, and, of course user authentication.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

php / mysql delete help

tom11011

MIS

DRJ478

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor