Delete MySQL database record row by link?

The following code displays the current results in the database for the table 'orders'.

How can I make the link "Delete" delete a current table row from the database?

<table>
	<thead>
	<tr>
		<th scope="col">ID</th>
		<th scope="col">Date</th>	
		<th scope="col">Name</th>
		<th scope="col">Amount</th>
                <th scope="col">Notes</th>
                <th scope="col">Delete</th>
	</tr>	
	</thead>
	<tbody>
<?php
require('db.php');
 
$result = mysql_query("SELECT idnumber, date, name, amount, notes FROM orders") 
or die(mysql_error());  
 
while($row = mysql_fetch_array( $result )) {
?>
 	<tr>
		<td><?php echo "".$row['idnumber']; ?></td>
                <td><?php echo "".$row['date']; ?></td>
		<td><?php echo "".$row['name']; ?></td>
		<td><?php echo "".$row['amount']; ?></td>
		<td><?php echo "".$row['notes']; ?></td>
                <td><a href="<?php //delete this row ?>">delete</a></td>
	</tr>
<?php } ?>		
	</tbody>
 
</table>

Open in new window

fcruz5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mankowitzCommented:
I assume that idnumber is unique.
Below is a fairly insecure way of doing it. You will have to trust your users quite a bit
Your delete link would look like this:
 <td><a href="deleteme.php?deleterow=><?php echo $row['idnumber']; ?> >delete</a></td>

Then... in deleteme.php,
require('db.php');
 $result = mysql_query("DELETE FROM ORDERS WHERE idnumber =" . $_GET['deleterow'])
or die(mysql_error());
mankowitzCommented:
It's exploitable, but servicable
hieloCommented:
Refer to attached code
<table>
      <thead>
      <tr>
            <th scope="col">ID</th>
            <th scope="col">Date</th>      
            <th scope="col">Name</th>
            <th scope="col">Amount</th>
                <th scope="col">Notes</th>
                <th scope="col">Delete</th>
      </tr>      
      </thead>
      <tbody>
<?php
require('db.php');
$query="SELECT idnumber, date, name, amount, notes FROM orders";
if("d"==strval($_REQUEST['op']) && is_numeric($_REQUEST['id']) )
{
	$query="DELETE FROM `orders` WHERE `idnumber`=" . $_REQUEST['id'];
}
$result = mysql_query($query) 
or die(mysql_error());  
 
while($row = mysql_fetch_array( $result )) {
?>
       <tr>
            <td><?php echo "".$row['idnumber']; ?></td>
                <td><?php echo "".$row['date']; ?></td>
            <td><?php echo "".$row['name']; ?></td>
            <td><?php echo "".$row['amount']; ?></td>
            <td><?php echo "".$row['notes']; ?></td>
                <td><a href="<?php echo $_SERVER['PHP_SELF'];?>?op=d&id=<?php echo $row['idnumber'];?>">delete</a></td>
      </tr>
<?php } ?>            
      </tbody>
 
</table>

Open in new window

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

fcruz5Author Commented:
@hielo

on the page that displays the results, the url string looks something like this:

test.php?currentrow=123

With your code, I get the following string

test.php??op=d&id=123

and I am getting an error when clicking on the delete link:

"Notice: Undefined variable: currentrow"

Is there a way to make it stay on ?currentrow='idnumber' when clicking on the 'delete' link?

hieloCommented:
This is what you posted for the link:
href="<?php //delete this row ?>">

You gave me no indication as to what you are calling the querystring variable. I just took a guess and called it id. you are calling it idnumber. So make the necessary changes or post your actual code.
fcruz5Author Commented:
Sorry about. The code that you posted does delete the current record row but gives me this error:

"Notice: Undefined variable: currentrow"

There is another query outside of the one that displays the results. Its allows you to navigate between records which uses ex: ?currentrow=123.

I guess the other query is causing the problem.

<?php
include("header.php");
require('db.php');
 
if (isset($_REQUEST['id']))
    $id = $_REQUEST['id'];
 
$sql2 = "SELECT
	company.id AS current,
    (SELECT MIN(company.id) AS nextRecord FROM company WHERE company.id > '$currentrow') AS next,
    (SELECT MAX(company.id) AS prevRecord FROM company WHERE company.id < '$currentrow') AS prev
FROM
  company
WHERE
  company.id = '$currentrow'";
 
$result2 = mysql_query($sql2) or die("Invalid query: " . mysql_error());
 
while ($row2 = mysql_fetch_assoc($result2))
{
?>
 
<table>
      <thead>
      <tr>
            <th scope="col">ID</th>
            <th scope="col">Date</th>      
            <th scope="col">Name</th>
            <th scope="col">Amount</th>
            <th scope="col">Notes</th>
            <th scope="col">Delete</th>
      </tr>      
      </thead>
      <tbody>
<?php
$query="SELECT idnumber, date, name, amount, notes FROM orders";
if("d"==strval($_REQUEST['op']) && is_numeric($_REQUEST['id']) )
{
        $query="DELETE FROM `orders` WHERE `idnumber`=" . $_REQUEST['id'];
}
$result = mysql_query($query) 
or die(mysql_error());  
 
while($row = mysql_fetch_array( $result )) {
?>
       <tr>
            <td><?php echo "".$row['idnumber']; ?></td>
            <td><?php echo "".$row['date']; ?></td>
            <td><?php echo "".$row['name']; ?></td>
            <td><?php echo "".$row['amount']; ?></td>
            <td><?php echo "".$row['notes']; ?></td>
                <td><a href="<?php echo $_SERVER['PHP_SELF'];?>?op=d&id=<?php echo $row['idnumber'];?>">delete</a></td>
      </tr>
<?php } ?>            
      </tbody>
 
</table>
 
include("footer.php");
}
?>

Open in new window

hieloCommented:
It's not clear if you are setting the value of $currentrow in header.php. At any rate, I am initializing it on the code below and passing the value along on the delete link.
<?php
include("header.php");
require('db.php');
if( isset($_REQUEST['currentrow']) && is_numeric($_REQUEST['currentrow']) )
	$currentrow=$_REQUEST['currentrow'];
 
if (isset($_REQUEST['id']) && is_numeric($_REQUEST['id']) )
    $id = $_REQUEST['id'];
 
$sql2 = "SELECT
	company.id AS current,
    (SELECT MIN(company.id) AS nextRecord FROM company WHERE company.id > '$currentrow') AS next,
    (SELECT MAX(company.id) AS prevRecord FROM company WHERE company.id < '$currentrow') AS prev
FROM
  company
WHERE
  company.id = '$currentrow'";
 
$result2 = mysql_query($sql2) or die("Invalid query: " . mysql_error());
 
while ($row2 = mysql_fetch_assoc($result2))
{
?>
 
<table>
      <thead>
      <tr>
            <th scope="col">ID</th>
            <th scope="col">Date</th>      
            <th scope="col">Name</th>
            <th scope="col">Amount</th>
            <th scope="col">Notes</th>
            <th scope="col">Delete</th>
      </tr>      
      </thead>
      <tbody>
<?php
$query="SELECT idnumber, date, name, amount, notes FROM orders";
if("d"==strval($_REQUEST['op']) && is_numeric($_REQUEST['id']) )
{
        $query="DELETE FROM `orders` WHERE `idnumber`=" . $_REQUEST['id'];
}
$result = mysql_query($query) 
or die(mysql_error());  
 
while($row = mysql_fetch_array( $result )) {
?>
       <tr>
            <td><?php echo "".$row['idnumber']; ?></td>
            <td><?php echo "".$row['date']; ?></td>
            <td><?php echo "".$row['name']; ?></td>
            <td><?php echo "".$row['amount']; ?></td>
            <td><?php echo "".$row['notes']; ?></td>
                <td><a href="<?=$_SERVER['PHP_SELF']?>?op=d&id=<?=$row['idnumber']?>&currentrow=<?=$currentrow?>">delete</a></td>
      </tr>
<?php } ?>            
      </tbody>
 
</table>
 
include("footer.php");
}
?>

Open in new window

fcruz5Author Commented:
I am getting this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on this line:
while($row = mysql_fetch_array( $result )) {

Also with the error, the results are not displayed.

The query string looks like this after clicking the link:
?op=d&id=123&currentrow=1001
If I take out this "op=d&id=123&" and just leave "?currentrow=1001", it will display the results correctly.

When the query string looks like this: ?currentrow=1001 even though the results display correctly, this error appears above:

Notice: Undefined index: op

hieloCommented:
>>Warning:...
The problem is that the script is posted will construct either a DELETE or a SELECT query, but the while at the bottom of the page requires a SELECT query all the time. So the correct approach is to delete the record if op=d is detected, otherwiser just execute a select.
<?php
include("header.php");
require('db.php');
if( isset($_REQUEST['currentrow']) && is_numeric($_REQUEST['currentrow']) )
	$currentrow=$_REQUEST['currentrow'];
 
if (isset($_REQUEST['id']) && is_numeric($_REQUEST['id']) )
    $id = $_REQUEST['id'];
 
$sql2 = "SELECT
	company.id AS current,
    (SELECT MIN(company.id) AS nextRecord FROM company WHERE company.id > '$currentrow') AS next,
    (SELECT MAX(company.id) AS prevRecord FROM company WHERE company.id < '$currentrow') AS prev
FROM
  company
WHERE
  company.id = '$currentrow'";
 
$result2 = mysql_query($sql2) or die("Invalid query: " . mysql_error());
 
while ($row2 = mysql_fetch_assoc($result2))
{
?>
 
<table>
      <thead>
      <tr>
            <th scope="col">ID</th>
            <th scope="col">Date</th>      
            <th scope="col">Name</th>
            <th scope="col">Amount</th>
            <th scope="col">Notes</th>
            <th scope="col">Delete</th>
      </tr>      
      </thead>
      <tbody>
<?php
if("d"==strval($_REQUEST['op']) && is_numeric($_REQUEST['id']) )
{
        $query="DELETE FROM `orders` WHERE `idnumber`=" . $_REQUEST['id'];
		mysql_query($query) or die(mysql_error());
}
$query="SELECT idnumber, date, name, amount, notes FROM orders";
$result = mysql_query($query) or die(mysql_error());  
 
while($row = mysql_fetch_array( $result )) {
?>
       <tr>
            <td><?php echo "".$row['idnumber']; ?></td>
            <td><?php echo "".$row['date']; ?></td>
            <td><?php echo "".$row['name']; ?></td>
            <td><?php echo "".$row['amount']; ?></td>
            <td><?php echo "".$row['notes']; ?></td>
                <td><a href="<?=$_SERVER['PHP_SELF']?>?op=d&id=<?=$row['idnumber']?>&currentrow=<?=$currentrow?>">delete</a></td>
      </tr>
<?php } ?>            
      </tbody>
 
</table>
 
include("footer.php");
}
?>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fcruz5Author Commented:
Okay, I am not getting the errors anymore except that when move between records it will give this error:

"Notice: Undefined index: op"

When navigating between records the query string looks like this:

?currentrow=1001

And the error doesn't appear when the query strings looks like this, which is generated when you delete a record:

?op=d&id=123&currentrow=1001

Is there a way to get rid of that error when navigating through records?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.