Re: Select with dbi perl??? Help please!
Sascha Kettner wrote:
> Hi!
>
> I have the following script to be executed via post from a web-form; the
> var. Pin, msisd and knd are given by the form but however, the script is
> not working. I always get no results as if there are no matches, but
> this isnt right! This is regardless which entries i submit with the
> form!
> Any ideas to fix the problem?
>
> Thanks a lot in advance
> Regards
>
> Sascha Kettner
>
> #!/usr/bin/perl
>
> use DBI();
> #
> # Get form Data #
> #
>
> &parse_form;
>
> # Script Variables #
>
> $input{knd} = "";
> $input{pin} = "";
> $input{msisdn} = "";
>
>
> # What to do on submit #
>
>
> &dojob;
>
> ##
> # Lets have a look at the db #
> ##
>
> sub dojob {
>
> # Now retrieve data from the table.
> my $dbh = DBI->connect("DBI:mysql:database=prepaid;host=localhost",
> "root", "sascha28",
> {'RaiseError' => 1});
> my $sth = $dbh->prepare("SELECT * FROM pins WHERE pin LIKE '$input{pin}'
> OR msisdn LIKE '$input{msisdn}' OR knd LIKE '$input{knd}'");
> print < Content-type: text/html
'$input(pin)' is a literal string.
The single quote will prevent it from being evaluated.
Use the $dhb->quote() function to quote and assign your variable to
another, and
then use that variable in your query without the single quotes.
my $pin=$dbh->quote($input{pin};
my $sth = $dbh->prepare("SELECT * FROM pins WHERE pin LIKE $pin ...
>
>
>
>
>
>
> MAKEPINS
>
>
>
> Suche Ausgeführt
>
>
>
> EOF
> $sth->execute();
> while (my $ref = $sth->fetchrow_hashref()) {
> print "Eintrag gefunden: pin = $ref->{'pin'}, msisdn =
> $ref->{'msisdn'}\n, knd = $ref->{'knd'}\n\n";
> }
> $sth->finish();
> # Disconnect from the database.
> $dbh->disconnect();
> exit;
> }
> ##
> # Get form data function #
> ##
>
> sub parse_form {
>
>read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
>if (length($buffer) < 5) {
> $buffer = $ENV{QUERY_STRING};
> }
>@pairs = split(/&/, $buffer);
>foreach $pair (@pairs) {
> ($name, $value) = split(/=/, $pair);
>
> $value =~ tr/+/ /;
> $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
>
> $input{$name} = $value;
>}
> }
>
> ###
> # The end #
> ###
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select with dbi perl??? Help please!
I would suggest turning logging on ('log' entry in my.cnf under mysqld)
do a search and look to see what the actual SELECT parsed by the mysqld
server was. My hunch is that it is only seeing '%' and not '123456789123%'
as expected.
OK, so I now look at your code. It will be looking for '$input{pin}%'
and NOT veriable substituted since it has SINGLE quotes (perl treats things
in single quotes as literals).
A better way is:
my $sql = qq{
SELECT *
FROM pins
WHERE
pin LIKE ? OR
msisdn LIKE ? OR
knd LIKE ?
};
my $sql=$dbh->prepare($sql);
$sql->execute( "%".$input{pin}."%", "%".$input{msisdn}."%",
"%".$input{knd}."%");
If you want exect matches, use:
$sql->execute( $input{pin}, $input{msisdn}, $input{knd});
This makes quoting problems go away. (Imagine if a single or double quote
was
in one of the $input variables - what would the SELECT look like... OK,
so
you could get round the problem with $input{pin}=$dbh->quote($input{pin})
but
much easier to bind variables as above.
Using '?' makes your code much easier to read.
[Note, the '%' in a LIKE expression matches any zero or more characters.]
Regards
M
-----Original Message-
From: Sascha Kettner [mailto:[EMAIL PROTECTED]]
Sent: 07 January 2002 07:38
To: [EMAIL PROTECTED]
Subject: Select with dbi perl??? Help please!
HI!
Thanks for your Help! I´ve tried this, but when inserting the % now all
my selections seem to be valid or successful, even if there is no match!
What is wrong? My table for testing has the following entries:
Pin: 123456789123 msisdn:+491231234567knd:test
PIN: 123456789012 msisdn:0knd:test
So when i insert into my form the search for test, f.e. i get both
entries as a result, but even when i insert the search for 123456789123
i get both results as before but when you look at my table, you see,
that only one entry should be displayed! So any other ideas?
Regards
Sascha Kettner
> -Ursprüngliche Nachricht-----
> Von: Jesse Shy [mailto:[EMAIL PROTECTED]]
> Gesendet: Sonntag, 6. Januar 2002 23:54
> An: [EMAIL PROTECTED]
> Betreff: RE: Select with dbi perl??? Help please!
>
>
> I have found that unless I put a % sign either before, after
> or both, to denote the match part in question, that I don't
> get anything. Try this: LIKE '$input{pin}%'
>
> that should do it. If not, copy the SQL statement from your
> script to the mysql client to test. I wouldn't retypr it, I
> would copy it so you try exactly what is in your script.
>
> Good luck!
>
> -Original Message-
> From: Sascha Kettner [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, January 06, 2002 4:14 PM
> To: [EMAIL PROTECTED]
> Subject: Select with dbi perl??? Help please!
>
>
> Hi!
>
> I have the following script to be executed via post from a
> web-form; the var. Pin, msisd and knd are given by the form
> but however, the script is not working. I always get no
> results as if there are no matches, but this isnt right! This
> is regardless which entries i submit with the form! Any ideas
> to fix the problem?
>
> Thanks a lot in advance
> Regards
>
> Sascha Kettner
>
> #!/usr/bin/perl
>
> use DBI();
> #
> # Get form Data #
> #
>
> &parse_form;
>
> # Script Variables #
>
> $input{knd} = "";
> $input{pin} = "";
> $input{msisdn} = "";
>
>
> # What to do on submit #
>
>
> &dojob;
>
> ##
> # Lets have a look at the db #
> ##
>
> sub dojob {
>
> # Now retrieve data from the table.
> my $dbh = DBI->connect("DBI:mysql:database=prepaid;host=localhost",
> "root", "sascha28",
> {'RaiseError' => 1});
> my $sth = $dbh->prepare("SELECT * FROM pins WHERE pin LIKE
> '$input{pin}' OR msisdn LIKE '$input{msisdn}' OR knd LIKE
> '$input{knd}'");
> print < Content-type: text/html
>
>
>
>
>
> MAKEPINS
>
>
>
> Suche Ausgeführt
>
>
>
> EOF
> $sth->execute();
> while (my $ref = $sth->fetchrow_hashref()) {
> print "Eintrag gefunden: pin = $ref->{'pin'}, msisdn =
> $ref->{'msisdn'}\n, knd = $ref->{'knd'}\n\n";
> }
> $sth->finish();
> # Disconnect from the database.
> $dbh->disconnect();
> exit;
> }
> ##
> # Get form data fun
Select with dbi perl??? Help please!
HI!
Thanks for your Help! I´ve tried this, but when inserting the % now all
my selections seem to be valid or successful, even if there is no match!
What is wrong? My table for testing has the following entries:
Pin: 123456789123 msisdn:+491231234567knd:test
PIN: 123456789012 msisdn:0knd:test
So when i insert into my form the search for test, f.e. i get both
entries as a result, but even when i insert the search for 123456789123
i get both results as before but when you look at my table, you see,
that only one entry should be displayed! So any other ideas?
Regards
Sascha Kettner
> -Ursprüngliche Nachricht-
> Von: Jesse Shy [mailto:[EMAIL PROTECTED]]
> Gesendet: Sonntag, 6. Januar 2002 23:54
> An: [EMAIL PROTECTED]
> Betreff: RE: Select with dbi perl??? Help please!
>
>
> I have found that unless I put a % sign either before, after
> or both, to denote the match part in question, that I don't
> get anything. Try this: LIKE '$input{pin}%'
>
> that should do it. If not, copy the SQL statement from your
> script to the mysql client to test. I wouldn't retypr it, I
> would copy it so you try exactly what is in your script.
>
> Good luck!
>
> -Original Message-
> From: Sascha Kettner [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, January 06, 2002 4:14 PM
> To: [EMAIL PROTECTED]
> Subject: Select with dbi perl??? Help please!
>
>
> Hi!
>
> I have the following script to be executed via post from a
> web-form; the var. Pin, msisd and knd are given by the form
> but however, the script is not working. I always get no
> results as if there are no matches, but this isnt right! This
> is regardless which entries i submit with the form! Any ideas
> to fix the problem?
>
> Thanks a lot in advance
> Regards
>
> Sascha Kettner
>
> #!/usr/bin/perl
>
> use DBI();
> #
> # Get form Data #
> #
>
> &parse_form;
>
> # Script Variables #
>
> $input{knd} = "";
> $input{pin} = "";
> $input{msisdn} = "";
>
>
> # What to do on submit #
>
>
> &dojob;
>
> ##
> # Lets have a look at the db #
> ##
>
> sub dojob {
>
> # Now retrieve data from the table.
> my $dbh = DBI->connect("DBI:mysql:database=prepaid;host=localhost",
> "root", "sascha28",
> {'RaiseError' => 1});
> my $sth = $dbh->prepare("SELECT * FROM pins WHERE pin LIKE
> '$input{pin}' OR msisdn LIKE '$input{msisdn}' OR knd LIKE
> '$input{knd}'");
> print < Content-type: text/html
>
>
>
>
>
> MAKEPINS
>
>
>
> Suche Ausgeführt
>
>
>
> EOF
> $sth->execute();
> while (my $ref = $sth->fetchrow_hashref()) {
> print "Eintrag gefunden: pin = $ref->{'pin'}, msisdn =
> $ref->{'msisdn'}\n, knd = $ref->{'knd'}\n\n";
> }
> $sth->finish();
> # Disconnect from the database.
> $dbh->disconnect();
> exit;
> }
> ##
> # Get form data function #
> ##
>
> sub parse_form {
>
>read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
>if (length($buffer) < 5) {
> $buffer = $ENV{QUERY_STRING};
> }
>@pairs = split(/&/, $buffer);
>foreach $pair (@pairs) {
> ($name, $value) = split(/=/, $pair);
>
> $value =~ tr/+/ /;
> $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
>
> $input{$name} = $value;
>}
> }
>
> ###
> # The end #
> ###
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select with dbi perl??? Help please!
Hi!
I have the following script to be executed via post from a web-form; the
var. Pin, msisd and knd are given by the form but however, the script is
not working. I always get no results as if there are no matches, but
this isnt right! This is regardless which entries i submit with the
form!
Any ideas to fix the problem?
Thanks a lot in advance
Regards
Sascha Kettner
#!/usr/bin/perl
use DBI();
#
# Get form Data #
#
&parse_form;
# Script Variables #
$input{knd} = "";
$input{pin} = "";
$input{msisdn} = "";
# What to do on submit #
&dojob;
##
# Lets have a look at the db #
##
sub dojob {
# Now retrieve data from the table.
my $dbh = DBI->connect("DBI:mysql:database=prepaid;host=localhost",
"root", "sascha28",
{'RaiseError' => 1});
my $sth = $dbh->prepare("SELECT * FROM pins WHERE pin LIKE '$input{pin}'
OR msisdn LIKE '$input{msisdn}' OR knd LIKE '$input{knd}'");
print <
MAKEPINS
Suche Ausgeführt
EOF
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
print "Eintrag gefunden: pin = $ref->{'pin'}, msisdn =
$ref->{'msisdn'}\n, knd = $ref->{'knd'}\n\n";
}
$sth->finish();
# Disconnect from the database.
$dbh->disconnect();
exit;
}
##
# Get form data function #
##
sub parse_form {
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
if (length($buffer) < 5) {
$buffer = $ENV{QUERY_STRING};
}
@pairs = split(/&/, $buffer);
foreach $pair (@pairs) {
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$input{$name} = $value;
}
}
###
# The end #
###
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
