garrickplaisted Posted March 12, 2012 Share Posted March 12, 2012 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. Quote Link to comment Share on other sites More sharing options...
cpd Posted March 12, 2012 Share Posted March 12, 2012 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. Quote Link to comment Share on other sites More sharing options...
garrickplaisted Posted March 12, 2012 Author Share Posted March 12, 2012 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? Quote Link to comment Share on other sites More sharing options...
garrickplaisted Posted March 12, 2012 Author Share Posted March 12, 2012 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 Quote Link to comment Share on other sites More sharing options...
garrickplaisted Posted March 12, 2012 Author Share Posted March 12, 2012 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'"); Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted March 13, 2012 Share Posted March 13, 2012 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']; Quote Link to comment Share on other sites More sharing options...
garrickplaisted Posted March 13, 2012 Author Share Posted March 13, 2012 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. Quote Link to comment Share on other sites More sharing options...
sunfighter Posted March 13, 2012 Share Posted March 13, 2012 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. Quote Link to comment Share on other sites More sharing options...
garrickplaisted Posted March 13, 2012 Author Share Posted March 13, 2012 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. Quote Link to comment Share on other sites More sharing options...
garrickplaisted Posted March 13, 2012 Author Share Posted March 13, 2012 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"; } Quote Link to comment Share on other sites More sharing options...
mikosiko Posted March 13, 2012 Share Posted March 13, 2012 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 Quote Link to comment Share on other sites More sharing options...
garrickplaisted Posted March 13, 2012 Author Share Posted March 13, 2012 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. Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted March 14, 2012 Share Posted March 14, 2012 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 Quote Link to comment Share on other sites More sharing options...
garrickplaisted Posted March 14, 2012 Author Share Posted March 14, 2012 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? Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted March 14, 2012 Share Posted March 14, 2012 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.) Quote Link to comment Share on other sites More sharing options...
garrickplaisted Posted March 14, 2012 Author Share Posted March 14, 2012 Thanks again. Quote Link to comment Share on other sites More sharing options...
garrickplaisted Posted March 14, 2012 Author Share Posted March 14, 2012 that would look something like this right $id=$_GET['id']; if(!ctype_digit($id)){ echo "It's not a digit"; } Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted March 15, 2012 Share Posted March 15, 2012 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.