Re: [DBI] What is wrong with select?

[Robert A. Rawlinson]

Dave Cash wrote:
On Sat, 2 Apr 2005, Robert A. Rawlinson wrote:
 

I am trying to get the index for a state and when I do:
my $sql1 = "SELECT id, name_short, name_long FROM states WHERE
name_short='PA";
$sth1 = $dbh->prepare($sql1);
#
$sth1->execute();
It works. But when I do:
$StateProvince = 'PA';
my $sql1 = "SELECT id, name_short, name_long FROM states WHERE
name_short=$StateProvince";
$sth1 = $dbh->prepare($sql1);
#
$sth1->execute();
I get a message that:
DBD::mysql::st execute failed: Unknown column 'PA' in 'where clause' at
TestDBI.pl line 14.
It seems to me this should work. I am new at this so I must have done
something wrong, but what?
Thanks for any help you can offer.
   

Bob,
This isn't really a DBI question; the problem is with your SQL.
That's why I'm responding off-list.
In your second example, $sql1 will end up being:
SELECT id, name_short, name_long FROM states WHERE name_short=PA
which is telling the mysql engine to only return rows where the
name_short column has a value equal to the PA column.  But I imagine
you don't have a PA column.  I think what you want is this:
my $sql1 = "SELECT id, name_short, name_long FROM states WHERE 
name_short='$StateProvince'";
Actually, this is a great place to use placeholders.  Here's how to
rewrite your code using placeholders:
my $StateProvince = 'PA';
my $sql1 = "SELECT id, name_short, name_long FROM states WHERE name_short = ?";
my $sth1 = $dbh->prepare( $sql1 );
$sth1->execute( $StateProvince );
This will cause the ? in the $sql1 string to get replaced with 'PA'
(including the quotes), so the query gets sent to the database like
this:
SELECT id, name_short, name_long FROM states WHERE name_short = 'PA'
And that should do the trick nicely.  Placeholders really are great,
in many, many ways.
I hope that helps.
Dave
/L\_/E\_/A\_/R\_/N\_/T\_/E\_/A\_/C\_/H\_/L\_/E\_/A\_/R\_/N\
Dave Cash                              Power to the People!
Frolicking in Fields of Garlic               Right On-Line!
[EMAIL PROTECTED]                                  Dig it all.

 

Wow thanks so much! I got so many replies but about all of you had about 
the same things.
I tried it and that fixed it. I understand better now the placeholder. I 
had read about it but
never thought about how to apply it. This is a good group.
Bob Rawlinson