Delete certain records in a database

bddotnet · 6-14-06, 06:02 PM

I have a form that allows the user to specify which record in the table that they want to delete. When they enter a number, it deletes ALL records in the table. I only want to delete the ID# that the user enters on the form.

Why doesn't this work?

PHP Code:


			
DELETE FROM workorders WHERE ID = '$ID

mitchind · 6-14-06, 06:19 PM

It's likely that your PHP code is not concatenating the SQL command string the way you assume it is. Without your actual code it's hard to tell, but I know I've falled into the trap before where I copy/paste a string command - then forget to insert the concatenate operator on the same string to make it join both lines.

bddotnet · 6-14-06, 06:29 PM

OK. Here is the form:

HTML Code:

<form name="form3" method="post" action="">

PHP Code:


			
          <?
        $host = '';
        $user = '';
        $pwd = '';
        $db = '';
        
$connect=mysql_connect('mysqlxx.powweb.com',$user,$pwd);
$result=mysql_select_db($db) or die( "This database is not working");

$user = $_SERVER['REMOTE_USER'];
$today = date("Y-m-d");

$list_query = mysql_query("SELECT * FROM tablename"); 

echo "Delete the selected job posting below.<br><br>";

while ( $data = mysql_fetch_array($list_query) ) { 

//recognize the month and post each work order
echo "ID: $data[ID]<br>";
"Date: $data[today]<br>";
"Job Title: $data[fieldname2]
<br>
Job Description: $data[fieldname3]
<hr><p>
";
            
echo "<p>Delete ID: 
            <input name=\"id\" type=\"text\" id=\"id\">
          </p>
          <p>
            <input type=\"submit\" name=\"ID\" value=\"Delete\">
          </p>";
}

?>

HTML Code:

        </form>

Here is the action of the form:

HTML Code:

<head>
<link href="../style.css" rel="stylesheet" type="text/css">
<META http-equiv="refresh" content="2;URL=">
</head>

PHP Code:


			
  <?
//connection information
$host = "";
$user = "";
$pwd = "";
$db = "";
$conn = mysql_connect('',$user,$pwd);

if(!$conn) echo "connection failed<br>";

//select database
$select = mysql_select_db("$db",$conn) or die ("This database is not working.");

function clean ( $val , $ws = 1) {
        
    if ($val == "") {
            
        return "";
        
    }
    
    $val = str_replace  ( " "            , " "            , $val ) ;        
    $val = str_replace  ( chr(0xCA)           , ""             , $val ) ;  
//Remove sneaky spaces
    $val = str_replace  ( "&"                  , "&amp;"           , $val ) ;
    $val = str_replace  ( "<!--"              , "<!--" , $val ) ;
    $val = str_replace  ( "-->"                  , "-->"       , $val ) ;
    $val = preg_replace ( "/<script/i"        , "<script"  , $val ) ;
    $val = str_replace  ( ">"                  , "&gt;"           , $val ) ;
    $val = str_replace  ( "<"                  , "&lt;"           , $val ) ;
    $val = str_replace  ( "\""                  , "&quot;"       , $val ) ;
    $val = preg_replace ( "/\n/"              , "<br />"       , $val ) ;
// Convert literal newlines
    $val = preg_replace ( "/\\\$/"              , "$"       , $val ) ;
    $val = preg_replace ( "/\r/"              , ""               , $val ) ;
// Remove literal carriage returns
    $val = str_replace  ( "!"                  , "!"           , $val ) ;
    $val = str_replace  ( "'"                  , "'"           , $val ) ;
// IMPORTANT: It helps to increase sql query safety.        
    $val = preg_replace ("/&amp;#([0-9]+);/s" , "&#\\1;"       , $val ) ;
    $val = ($ws == 1) ? preg_replace ( "/\s\s+/" , " "         , $val ) : $val ;
// Remove white space

    // Strip slashes if not already done so.
    
    if ( get_magic_quotes_gpc ( ) )
    {
        $val = stripslashes($val);
    }
    
//code correction provided by satis - see below
// Swap user inputted backslashes
     
    $val = preg_replace( "/\\\(?!&amp;#|\?#)/", "\"", $val );
     
    return $val;
     
}

//clean vars
$ = clean($_POST['']);

unset($error);

//delete job posting
$sql  = "DELETE FROM tablename WHERE id = '$ID";

$result = mysql_query($sql,$conn);
mysql_close($conn);

echo "<center><h3>Deleting Job Posting...........................</h3>
<br>Please wait...<center>"
?>

What am I doing wrong? This deletes nothing!

mitchind · 6-14-06, 11:57 PM

Well ...
you're making progress. In your first post it was deleting EVERYTHING .. now it's NOTHING.

So that just means the record doesn't exist as you expect it to exist with the ID value provided. Echo the $sql statement before executing - do you even have a value for $ID in the SQL statement?

satis · 6-15-06, 08:20 AM

this is wrong:

"DELETE FROM tablename WHERE id = '$ID";

you have an extra single quote in there. either

"DELETE FROM tablename WHERE id = $ID";
or
"DELETE FROM tablename WHERE id = '$ID'";

I dunno if there are other problems hiding in there. BTW, for cleaning an integer (which I assume $ID is), you can do this:

PHP Code:


			
$ID = (int)$ID;

That'll convert $ID into an integer...if it's already and integer, it'll basically verify it's safe... if it's anything other than an integer, it converts it to 0. Then you can check it before actually doing your sql stuff so you don't run bogus sql queries.

PHP Code:


			
if($id == 0){

    echo 'Invalid Id';

   include('originating_page.html');

  die();

}

//onward with your query

also, I' d recommend you stick an 'echo $sql' in there before you do your mysql_query. Any time I build a new sql statement, I ALWAYS echo out the sql query before ever running it against the database. Half the time it seems I'm running against a live database nowadays and I can't afford to accidently lose all my records or insert tons of trash or anything.

bddotnet · 6-15-06, 04:20 PM

Thanks guys!

6-14-06, 06:02 PM	#1
bddotnet Web Designer Join Date: Nov 2004 Location: Oklahoma City, Oklahoma Posts: 743 Reputation: 28	Delete certain records in a database I have a form that allows the user to specify which record in the table that they want to delete. When they enter a number, it deletes ALL records in the table. I only want to delete the ID# that the user enters on the form. Why doesn't this work? PHP Code: `DELETE FROM workorders WHERE ID = '$ID`

6-14-06, 06:29 PM	#3
bddotnet Web Designer Join Date: Nov 2004 Location: Oklahoma City, Oklahoma Posts: 743 Reputation: 28	OK. Here is the form: HTML Code: <form name="form3" method="post" action=""> PHP Code: <? $host = ''; $user = ''; $pwd = ''; $db = ''; $connect=mysql_connect('mysqlxx.powweb.com',$user,$pwd); $result=mysql_select_db($db) or die( "This database is not working"); $user = $_SERVER['REMOTE_USER']; $today = date("Y-m-d"); $list_query = mysql_query("SELECT * FROM tablename"); echo "Delete the selected job posting below.<br><br>"; while ( $data = mysql_fetch_array($list_query) ) { //recognize the month and post each work order echo "ID: $data[ID]<br>"; "Date: $data[today]<br>"; "Job Title: $data[fieldname2] <br> Job Description: $data[fieldname3] <hr><p> "; echo "<p>Delete ID: <input name=\"id\" type=\"text\" id=\"id\"> </p> <p> <input type=\"submit\" name=\"ID\" value=\"Delete\"> </p>"; } ?> HTML Code: </form> Here is the action of the form: HTML Code: <head> <link href="../style.css" rel="stylesheet" type="text/css"> <META http-equiv="refresh" content="2;URL="> </head> PHP Code: <? //connection information $host = ""; $user = ""; $pwd = ""; $db = ""; $conn = mysql_connect('',$user,$pwd); if(!$conn) echo "connection failed<br>"; //select database $select = mysql_select_db("$db",$conn) or die ("This database is not working."); function clean ( $val , $ws = 1) { if ($val == "") { return ""; } $val = str_replace ( " " , " " , $val ) ; $val = str_replace ( chr(0xCA) , "" , $val ) ; //Remove sneaky spaces $val = str_replace ( "&" , "&" , $val ) ; $val = str_replace ( "<!--" , "<!--" , $val ) ; $val = str_replace ( "-->" , "-->" , $val ) ; $val = preg_replace ( "/<script/i" , "<script" , $val ) ; $val = str_replace ( ">" , ">" , $val ) ; $val = str_replace ( "<" , "<" , $val ) ; $val = str_replace ( "\"" , """ , $val ) ; $val = preg_replace ( "/\n/" , "<br />" , $val ) ; // Convert literal newlines $val = preg_replace ( "/\\\$/" , "$" , $val ) ; $val = preg_replace ( "/\r/" , "" , $val ) ; // Remove literal carriage returns $val = str_replace ( "!" , "!" , $val ) ; $val = str_replace ( "'" , "'" , $val ) ; // IMPORTANT: It helps to increase sql query safety. $val = preg_replace ("/&#([0-9]+);/s" , "&#\\1;" , $val ) ; $val = ($ws == 1) ? preg_replace ( "/\s\s+/" , " " , $val ) : $val ; // Remove white space // Strip slashes if not already done so. if ( get_magic_quotes_gpc ( ) ) { $val = stripslashes($val); } //code correction provided by satis - see below // Swap user inputted backslashes $val = preg_replace( "/\\\(?!&#\|\?#)/", "\"", $val ); return $val; } //clean vars $ = clean($_POST['']); unset($error); //delete job posting $sql = "DELETE FROM tablename WHERE id = '$ID"; $result = mysql_query($sql,$conn); mysql_close($conn); echo "<center><h3>Deleting Job Posting...........................</h3> <br>Please wait...<center>" ?> What am I doing wrong? This deletes nothing!

6-15-06, 08:20 AM	#5
satis Join Date: Oct 2002 Location: Dallas Posts: 2,914 Reputation: 311	this is wrong: "DELETE FROM tablename WHERE id = '$ID"; you have an extra single quote in there. either "DELETE FROM tablename WHERE id = $ID"; or "DELETE FROM tablename WHERE id = '$ID'"; I dunno if there are other problems hiding in there. BTW, for cleaning an integer (which I assume $ID is), you can do this: PHP Code: `$ID = (int)$ID;` That'll convert $ID into an integer...if it's already and integer, it'll basically verify it's safe... if it's anything other than an integer, it converts it to 0. Then you can check it before actually doing your sql stuff so you don't run bogus sql queries. PHP Code: `if($id == 0){ echo 'Invalid Id'; include('originating_page.html'); die(); } //onward with your query` also, I' d recommend you stick an 'echo $sql' in there before you do your mysql_query. Any time I build a new sql statement, I ALWAYS echo out the sql query before ever running it against the database. Half the time it seems I'm running against a live database nowadays and I can't afford to accidently lose all my records or insert tons of trash or anything. __________________ Satis Clankiller Clankiller.com Forums Clankiller.com PlasmaSky.com

6-14-06, 06:19 PM	#2
mitchind Older not wiser Join Date: Nov 2003 Location: Calgary, AB Posts: 2,472 Reputation: 205	It's likely that your PHP code is not concatenating the SQL command string the way you assume it is. Without your actual code it's hard to tell, but I know I've falled into the trap before where I copy/paste a string command - then forget to insert the concatenate operator on the same string to make it join both lines.

6-14-06, 11:57 PM	#4
mitchind Older not wiser Join Date: Nov 2003 Location: Calgary, AB Posts: 2,472 Reputation: 205	Well ... you're making progress. In your first post it was deleting EVERYTHING .. now it's NOTHING. So that just means the record doesn't exist as you expect it to exist with the ID value provided. Echo the $sql statement before executing - do you even have a value for $ID in the SQL statement?

6-15-06, 04:20 PM	#6
bddotnet Web Designer Join Date: Nov 2004 Location: Oklahoma City, Oklahoma Posts: 743 Reputation: 28	Thanks guys!