Deleting rows from MySQL using php

[garrickplaisted]

Ok I am trying to delete some rows from a database. I have done this before with success so I thought I could use the same method just modify it a little and it would work. Man was I wrong. Any help on this is much appreciated below is my code.

 

Code for the Query to get the data from the database and for displaying the results in a table:

//GET DATA
$sql = "SELECT * FROM `".$tblname."` ORDER BY $orderby $sort LIMIT $startrow,$limit";
$result = mysql_query($sql) or die(mysql_error());
$result2 = mysql_query($sql);

//START TABLE AND TABLE HEADER
echo "<form name='form1' method='post' action=''><table style='font-size:9.5px;'>\n<tr><th>Delete</th>";
$array = mysql_fetch_assoc($result);
foreach ($array as $key=>$value) {
if($config['nicefields']){
$field = str_replace("_"," ",$key);
$field = ucwords($field);
}

$field = columnSortArrows($key,$field,$orderby,$sort);
echo "<th>" . $field . "</th>\n";
}
echo "</tr>\n";

//reset result pointer
mysql_data_seek($result,0);

//start first row style
$tr_class = "class='odd'";

//LOOP TABLE ROWS
while($row = mysql_fetch_assoc($result)){


echo "<tr ".$tr_class.">\n<td><a
href='remove_rec.php?id=". $rows['ID'] . "'><p>Delete</p></a>";
echo "</td>";
foreach ($row as $field=>$value) {	
	echo "<td>" . $value . "</td>\n";
	}
echo "</tr>\n";
//switch row style
if($tr_class == "class='odd'"){
	$tr_class = "class='even'";
}else{
	$tr_class = "class='odd'";
}

}

//END TABLE
echo "</table>\n</form>";

 

Here is the code that should delete the row:

$tblname = 'tablename';
// get value of id that sent from address bar
$id=$_GET['ID'];

// Delete data in mysql from row that has this id
$sql="DELETE FROM $tblname WHERE ID='$id'";
$result=mysql_query($sql);

// if successfully deleted
if($result){
echo "Deleted Successfully";
echo "<BR>";
echo "<a href='leads3.php'>Back to Results</a>";
}

else {
echo "ERROR";
}

 

When I click on the link to delete a record it redirects me to the appropriate page with the "Deleted Successfully" message but when I go to view the results the row was not deleted. Any help on this would, again, be greatly appreciated.

 

[cpd]

Echo out your query and ensure everything is being input correctly. If so echo out your result to see if a resource ID is returned and if that's true see how many rows were affected using the mysql_affected_rows or num_rows function; at this stage I would assume our result will be 0.

 

 

[garrickplaisted]

Echo out your query and ensure everything is being input correctly. If so echo out your result to see if a resource ID is returned and if that's true see how many rows were affected using the mysql_affected_rows or num_rows function; at this stage I would assume our result will be 0.

 

First off thanks for the reply.

 

Here is what I did. I echoed $sql ( the Query ) and recieved the following message:

DELETE FROM ContactFormLog WHERE ID=''

 

I assume that it's not pulling the ID number as I hoped it would, any ideas?

[garrickplaisted]

Echo out your query and ensure everything is being input correctly. If so echo out your result to see if a resource ID is returned and if that's true see how many rows were affected using the mysql_affected_rows or num_rows function; at this stage I would assume our result will be 0.

When I echo $result I receive the following message:

DELETE FROM ContactFormLog WHERE ID=''1

 

So it is getting the right ID number but why is the other " not there?

 

I'm so cunfused, lol

[garrickplaisted]

Echo out your query and ensure everything is being input correctly. If so echo out your result to see if a resource ID is returned and if that's true see how many rows were affected using the mysql_affected_rows or num_rows function; at this stage I would assume our result will be 0.

When I echo $result I receive the following message:

DELETE FROM ContactFormLog WHERE ID=''1

 

So it is getting the right ID number but why is the other " not there?

 

I'm so cunfused, lol

 

Ok regarless of which one I choose to delete is gives me the same ID="1. I also used the following for the query thinking that it would help

:

$sql=sprintf("DELETE FROM $tblname WHERE ID='$id'");

[cyberRobot]

The problem is probably caused be the following:

 

$id=$_GET['ID'];

 

 

Your anchor tag creates a GET variable which is lower case:

 

<a href='remove_rec.php?id=

 

 

Try

 

$id=$_GET['id'];

[garrickplaisted]

The problem is probably caused be the following:

 

$id=$_GET['ID'];

 

 

Your anchor tag creates a GET variable which is lower case:

 

<a href='remove_rec.php?id=

 

 

Try

 

$id=$_GET['id'];

 

Thank you so much for your time on this.

Ok so I did as you suggessted and it still didn't work. On the database I made the "id field" with lowercase letters. Below is my current code with the changes I made.

Code for the Query to get the data from the database and for displaying the results in a table:

//GET DATA
$sql = "SELECT * FROM `".$tblname."` ORDER BY $orderby $sort LIMIT $startrow,$limit";
$result = mysql_query($sql) or die(mysql_error());
$count=mysql_num_rows($result);

//START TABLE AND TABLE HEADER
echo "<table style='font-size:9.5px;'>\n<tr><th>Delete</th>";
$array = mysql_fetch_assoc($result);
foreach ($array as $key=>$value) {
if($config['nicefields']){
$field = str_replace("_"," ",$key);
$field = ucwords($field);
}

$field = columnSortArrows($key,$field,$orderby,$sort);
echo "<th>" . $field . "</th>\n";
}
echo "</tr>\n";

//reset result pointer
mysql_data_seek($result,0);

//start first row style
$tr_class = "class='odd'";

//LOOP TABLE ROWS
while($row = mysql_fetch_assoc($result)){
$id = $row['id'];



echo "<tr ".$tr_class.">\n<td><a
href='remove_rec2.php?id=". $rows['id'] . "'><p>Delete</p></a>";
echo "</td>";

foreach ($row as $field=>$value) {	
	echo "<td>" . $value . "</td>\n";
	}
echo "</tr>\n";
//switch row style
if($tr_class == "class='odd'"){
	$tr_class = "class='even'";
}else{
	$tr_class = "class='odd'";
}

}
//END TABLE
echo "</table>\n";

Here is the code that should delete the row:

$tbl_name="ContactFormLog"; // Table name


// get value of id that sent from address bar
$id=$_GET['id'];

// Delete data in mysql from row that has this id
$sql=sprintf("DELETE FROM $tbl_name WHERE id='$id'");
$result=mysql_query($sql);

// if successfully deleted
if($result){
echo "Deleted Successfully";
echo "<BR>";
echo "<a href='leads3.php'>Back to Results</a>";
}

else {
echo "ERROR";
}

 

I am not sure if having to echo it out is what is causing the issue or not. Again I really appreciate anyone and everyone's help on this.

[sunfighter]

Really don't need to use sprintf in $sql=sprintf("DELETE FROM $tbl_name WHERE id='$id'");

$sql="DELETE FROM $tbl_name WHERE id='$id' "; Works just fine.

 

BEFORE ever doing a DELETE do a SELECT to make sure you have the correct information. Do this INSTEAD of the delete not in addition too.

$query = "SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($query);
$row = mysql_fetch_array($result);
print_r($row);
die;

 

If you get the correct answer(row) then you can delete it.

If this doesn't give you the row that you want to delete try removing the single quotes around the $id and try again.

[garrickplaisted]

Really don't need to use sprintf in $sql=sprintf("DELETE FROM $tbl_name WHERE id='$id'");

$sql="DELETE FROM $tbl_name WHERE id='$id' "; Works just fine.

 

BEFORE ever doing a DELETE do a SELECT to make sure you have the correct information. Do this INSTEAD of the delete not in addition too.

$query = "SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($query);
$row = mysql_fetch_array($result);
print_r($row);
die;

 

If you get the correct answer(row) then you can delete it.

If this doesn't give you the row that you want to delete try removing the single quotes around the $id and try again.

 

I tried the both suggestions and on the first suggestion on the first one I used the following code:

$tbl_name="ContactFormLog"; // Table name


// get value of id that sent from address bar
$id=$_GET['id'];

// Delete data in mysql from row that has this id
$query="SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($query);
$row = mysql_fetch_array($result);
print_r($row);
die;
// if successfully deleted
if($result){
echo "Deleted Successfully";
echo "<BR>";
echo "<a href='leads3.php'>Back to Results</a>";
}

else {
echo "ERROR";
}

and received received no error or successfully deleted message and the rows were not deleted.

 

On the second suggestion I used the following code:

$tbl_name="ContactFormLog"; // Table name


// get value of id that sent from address bar
$id=$_GET['id'];

// Delete data in mysql from row that has this id
$query="SELECT * FROM $tbl_name WHERE id=$id";
$result=mysql_query($query);
$row = mysql_fetch_array($result);
print_r($row);
die;
// if successfully deleted
if($result){
echo "Deleted Successfully";
echo "<BR>";
echo "<a href='leads3.php'>Back to Results</a>";
}

else {
echo "ERROR";
}

 

and I received the following message: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /homepages/2/d208592347/htdocs/websitemorph/LGF/admin/remove_rec2.php on line 64.

 

Again I appreciate everyone's help on this, it's driving me crazy trying to figure this out.

[garrickplaisted]

I tried removing the

die;

code and received the following message:

Deleted Successfully

Back to Results

SELECT * FROM ContactFormLog WHERE id=''

Resource id #3

I used the following code:

$tbl_name="ContactFormLog"; // Table name


// get value of id that sent from address bar
$id=$_GET['id'];

// Delete data in mysql from row that has this id
$query="SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($query);
$row = mysql_fetch_array($result);
print_r($row);
// if successfully deleted
if($result){
echo "Deleted Successfully";
echo "<BR>";
echo "<a href='leads3.php'>Back to Results</a><br />";
echo $query . "<br />";
echo $result . "<br />";
echo $row;
}

else {
echo "ERROR";
}

[mikosiko]

not sure which is your last code... but looking this part or the code in your first post....

 

....
//LOOP TABLE ROWS
while($row = mysql_fetch_assoc($result)){
echo "<tr ".$tr_class.">\n<td><a href='remove_rec.php?id=". $rows['ID'] . "'><p>Delete</p></a>";
echo "</td>";
....

 

don't you see the evident error? .... your resultset name is $row not $rows , hence $rows['ID'] doesn't have any value

 

 

[garrickplaisted]

I see it now, it wasn't so evident to me before that is why I posted the questions. Thank you all very much for your help especially mikosiko. The delete function is now working for me.

[cyberRobot]

For what it's worth, you'll want to validate and sanitize any data that could be tampered with by visitors. In this example, the ID being passed could be modified to inject code into the SQL query. To prevent that, make sure the value contains an expected value.

 

It sounds like the ID is supposed to be a number. You could use something like ctype_digit() to check that it is before querying the database:

http://php.net/manual/en/function.ctype-digit.php

 

If the ID contains text, do what you can to sanitize that value. Then use mysql_real_escape_string() on the variable before running the query:

http://php.net/manual/en/function.mysql-real-escape-string.php

[garrickplaisted]

Thanks for that helpful tip. I guess I should validate it even though there is no input box, they could use the browser it self to inject malicious code right?

[cyberRobot]

Thanks for that helpful tip. I guess I should validate it even though there is no input box, they could use the browser it self to inject malicious code right?

 

Yep, anything that could potentially be tampered with by the user should be treated with caution. (GET / POST variables, cookies, etc.)

[garrickplaisted]

Thanks again.

[garrickplaisted]

that would look something like this right

$id=$_GET['id'];
if(!ctype_digit($id)){
  echo "It's not a digit";
}

[cyberRobot]

Yep, that looks correct.

 

Note that you'll still get numbers which are technically invalid, such as 0 or an ID that hasn't been used yet. That's where a solution like the one suggested by sunfighter in Reply 7 will be useful. Basically, you would

[*]Make sure the ID is a number

[*]If it's a number, make sure the ID is found in the database

[*]If the ID was found, delete the record