This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.
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?
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>
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.
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>
@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?
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?
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.
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.
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.
"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");
}
?>
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']?>¤trow=<?=$currentrow?>">delete</a></td>
</tr>
<?php } ?>
</tbody>
</table>
include("footer.php");
}
?>
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¤trow=10
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
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¤trow=10
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
>>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.
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']?>¤trow=<?=$currentrow?>">delete</a></td>
</tr>
<?php } ?>
</tbody>
</table>
include("footer.php");
}
?>
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
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¤trow=10
Is there a way to get rid of that error when navigating through records?
"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¤trow=10
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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?deleter
Then... in deleteme.php,
require('db.php');
$result = mysql_query("DELETE FROM ORDERS WHERE idnumber =" . $_GET['deleterow'])
or die(mysql_error());