homepage Welcome to WebmasterWorld Guest from 54.167.173.250
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Can't Delete A Row
Can't Delete A Row
typomaniac




msg:4471898
 7:24 pm on Jul 2, 2012 (gmt 0)

Not sure exactly what the problem is here but I'm unable to delete a row in a mysql table.
The query is:
my $sth=$dbh->prepare("delete from table WHERE where column='$_[0]'");
Table & column name changed for posting purposes but the value compared against column is what it should be. One thing I'm curious about is the Collation setting. It is
currently set to latin1_swedish_ci .A meta tag in the form is calling for charset=UTF-8. The reason for this is a javascript which encrypts passwords before sending them through the net. I see in phpadmin where charsets can be set per column, etc., and I tried different settings for the UTF-8 but still the delete isn't happening. The column which holds the passwords and also is not the one being matched for deletion. ?

 

typomaniac




msg:4472016
 4:14 am on Jul 3, 2012 (gmt 0)

Got it fixed.. Kept searching and eventually found an answer. Not sure why the syntax I have been using didn't work in this case but I found one:

my $sql = qq`DELETE FROM table WHERE column='$_[0]'`;
my $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr()
$sth->execute() or die "Cannot execute: " . $sth->errstr();
$sth->finish();


Still have a question however and that is in regards to the backticks. I have seen somewhere that backticks should be avoided. Any comments on that.

rocknbil




msg:4472196
 4:14 pm on Jul 3, 2012 (gmt 0)

Good to see someone is still using Perl. :-) Either statement should work,unless your table is really named "table" and column really named "column", see below.

A few things:

1. Whoever told you to avoid backticks was probably referring to some other condition and the statement is out of context. In a mySQL context, backticks are mysql's specific "escape" for reserved words or table/field names. This also allows you to create and use tables and columns with spaces in their names (weird, but works.) The problem is, in this example you're using them as in a Perl context as a qq delimiter, not specific to mySQL. Details below.

2. I wouldn't use the anonymous reference $_[0] (personal preference).

3. I'd also do a more thorough error trap. You have one there on the specific select ("or die") but you should check all variables before using them anyway.


Details:
1. You have
$sql = qq`DELETE FROM table WHERE column='$_[0]'`;

The backticks in this case are delimiters for the qq. You could use any delimiter here, so long as the delimiter is not part of the string or if it is, is escaped:

$sql = qq|DELETE FROM table WHERE column='$_[0]'|;
$sql = qq/DELETE FROM table WHERE column='$_[0]'/;

An example of escaping comes below, after a little explaining. :-) One of the reasons someone might have told you not to use backticks is that in perl, it's an execution operator. The below means "execute this command (run perl to execute myscript.cgi) and store any result it returns in $res"

$res = `usr/bin/perl /mydirectory/myscript.cgi`;

That's in a Perl context. In a mySQL context, it has an entirely different meaning. It's used to "escape" reserved mySQL words or table names and fields. Is your table actually named "table"? If it is, that's a reserved word in mySQL. Another common field name is "date"; if you create a table with a field name of "date", it will error when you try to select from it UNLESS you use backticks.

$sql = qq`DELETE FROM `table` WHERE `column`='$_[0]'`;

That in itself will ERROR. Can you see it? From the previous statement above, the QQ is delimited by backticks, so it will find the second backtick and end the qq, not knowing how to parse the rest. The internal backticks need to be escaped.

$sql = qq`DELETE FROM \`table\` WHERE \`column\`='$_[0]'`;

As you can see that gets really messy and error prone. So use another delimiter:

$sql = qq|DELETE FROM `table` WHERE `column`='$_[0]'|;

... and you're golden.

2. Using $_[0]: This is just my preference. The special variables $_ and @_ are often useful but confusing. I generally like to store them in named variables.

3. Better error trapping: you should always, always, always initialize all variables and check their values before using them, and add appropriate responses if the values are not what you expect. This may take a little longer to set up, but it will save you hours in debugging.

All together, your new code would look like this:


# if this is in a sub or function, you could do this instead of $id = $_[0];
#call the sub like this:
delete_record($myID);
# then use it like this.
#
sub delete_record {
# initialize
my ($sql,$sth,$id);
$id = shift(@_); # identical result $id=$_[0];
#
# make sure it's numeric and > 0
if (($id =~ /^\d$/) && ($id > 0)) {
# note NUMERIC values do not need quoting.
$sql = qq|delete from `table` where `column`=$id|;
$sth = $dbh->prepare($sql) or die "Cannot prepare delete statement: " . $dbh->errstr()
$sth->execute() or die "Cannot execute delete statement: " . $sth->errstr();
$sth->finish();
}
else {
# and if it's not numeric, you know there's something wrong with your value
print "<p><strong> The input value in delete is not numeric</strong></p>";
}
} # end sub


A last note, mysql functions do not need to be in capitals - I like the ee cummings style. :-)

typomaniac




msg:4472365
 1:29 am on Jul 4, 2012 (gmt 0)

Thanks Rocknbil, Perl forever--I started out late in the game and its the only thing I know. Thanks for the input--I'll give all a try.
btw.....table was not really the name of the table and column was not really the name of the column and $_[0] was a value coming into the sub routine. Its the only value coming in but would still be easy to give it a different name. Been pretty lucky on the script because in all the subs the variables mean the same in each and are in the same order.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved