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. :-)
