searching database on emailaddress field

Hi.

I'm trying to work out how to search a database by emailaddress.
I'd like to see if an emailaddress exists in a database already.

The code looks like this:

/~~~~~~~~~~~~~~~~~~~~~~~~~~~
my $email = 'foo@bbc.com';
my $sth = $dbh->prepare("select id, address from subs_email where address=".$email);
$sth->execute();
my @array = $sth->fetchrow;
print $array[1] . "\n";
\___________________________

I can't seem to use the '@' char in the email address.

I get the following error:

DBD::Pg::st execute failed: ERROR: column "foo" does not exist at ./testTime.pl line 178.
DBD::Pg::st fetchrow failed: no statement executing at ./testTime.pl line 179.

Thanks for any help.
Kind regards.
Luke.

--
.............._..
..| .| |.|/.|_ .
..|__.|_|.|\.|_ .
:61 421 276 282:

Re: searching database on emailaddress field

Hi.

Solved after I posted.
Surround $email with 's.
As in '$email'.

Thanks.
Kr.
Luke.

31Jan2006 @ 13:52 luke@chipcity.com.au thusly spake
> Hi.
>
> I'm trying to work out how to search a database by emailaddress.
> I'd like to see if an emailaddress exists in a database already.
>
> The code looks like this:
>
> /~~~~~~~~~~~~~~~~~~~~~~~~~~~
> my $email = 'foo@bbc.com';
> my $sth = $dbh->prepare("select id, address from subs_email where address=".$email);
> $sth->execute();
> my @array = $sth->fetchrow;
> print $array[1] . "\n";
> \___________________________
>
> I can't seem to use the '@' char in the email address.
>
> I get the following error:
>
> DBD::Pg::st execute failed: ERROR: column "foo" does not exist at ./testTime.pl line 178.
> DBD::Pg::st fetchrow failed: no statement executing at ./testTime.pl line 179.
>
> Thanks for any help.
> Kind regards.
> Luke.
>
> --
> ............._..
> .| .| |.|/.|_ .
> .|__.|_|.|\.|_ .
> :61 421 276 282:

--
.............._..
..| .| |.|/.|_ .
..|__.|_|.|\.|_ .
:61 421 276 282:

Re: searching database on emailaddress field

On Tue, 31 Jan 2006 14:24:44 +1030, luke@chipcity.com.au wrote:

Hi Luke

> Solved after I posted.
> Surround $email with 's.
> As in '$email'.

Nice try, but no cigar. See below.

>> my $sth = $dbh->prepare("select id, address from subs_email where
>> address=".$email); $sth->execute(); my @array = $sth->fetchrow;

my $sth = $dbh->prepare('select id, address from subs_email where
address=?');

$sth->execute($email);

This way, the value in $email is quoted correctly by DBI, so you don't need to
call $dbh -> quote() explicitly, which you were doing, right?


--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html

AW: searching database on emailaddress field

Hi Luke,

you should have a look at the "Placeholder and Bind Values" section
in the DBI documentation:
http://search.cpan.org/~timb/DBI-1.50/DBI.pm#Placeholders_an d_Bind_Values

regards,
Renee

-----Ursprungliche Nachricht-----
Von: luke@chipcity.com.au [mailto:luke@chipcity.com.au]
Gesendet: Dienstag, 31. Januar 2006 04:23
An: dbi-users@perl.org
Betreff: searching database on emailaddress field


Hi.

I'm trying to work out how to search a database by emailaddress.
I'd like to see if an emailaddress exists in a database already.

The code looks like this:

/~~~~~~~~~~~~~~~~~~~~~~~~~~~
my $email = 'foo@bbc.com';
my $sth = $dbh->prepare("select id, address from subs_email where
address=".$email);
$sth->execute();
my @array = $sth->fetchrow;
print $array[1] . "\n";
\___________________________

I can't seem to use the '@' char in the email address.

I get the following error:

DBD::Pg::st execute failed: ERROR: column "foo" does not exist at
../testTime.pl line 178.
DBD::Pg::st fetchrow failed: no statement executing at ./testTime.pl line
179.

Thanks for any help.
Kind regards.
Luke.

--
.............._..
..| .| |.|/.|_ .
..|__.|_|.|\.|_ .
:61 421 276 282:

Re: searching database on emailaddress field

luke@chipcity.com.au wrote:

> Hi.

Hello,

> Solved after I posted.
> Surround $email with 's.
> As in '$email'.

Close, but this is a gun pointed at your head :)

You either need to $dbh->quote() it or do the bind value/placeholder
(see `perldoc DBI` for details of both)