homepage Welcome to WebmasterWorld Guest from 54.237.98.229
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

    
Help with MySQL/Perl Comparison
Coding Help
leslie

5+ Year Member



 
Msg#: 1612 posted 2:13 am on Nov 8, 2002 (gmt 0)

Hi -

I'm trying to select rows of data from a mySQL database that I have created. I have tried altering the data types of the fields in the database, as well as writing and re-writing the select statement.

I'm trying to compare string data in variables parsed from an HTML form to entries in a database to select specific records from the database. I can get numeric comparison operators (=, >=, <=) to work on varchar and bool type variables, but I'm having trouble getting the script to run when I try to match data strings. For example, I want to select from a table where the value in $property_setting = the value in the field property_setting in the database, i.e. $property_setting from the html form is "acreage", and I want to match it with all records in the database where the value in field property_setting = "acreage".

Here's my SELECT statement ...

$sql = "SELECT * FROM properties
WHERE ((rent_amount <= $rent_amount)AND **works
(num_baths >= $num_baths) AND **works
(num_beds >= $num_beds) AND **works
(pets >= $pets) AND **works
(handicap >= $handicap)AND **works
(fireplace >= $fireplace)AND **works
(deck >= $deck_patio)AND **works
(fence >= $fence)AND **works
(sec_8 >= $sec_8)AND **works
(pool >= $pool)AND **works
#Below is where the script bombs if property_setting
# is a varchar or enum with a short string and the value
# coming from the html form is a string. I can make the
# script run if I create the property_setting field in the
# database as an enum type and set the value coming from
# the html form as a number, but not the way I would like
# it to ...

# (property_setting = $property_setting) AND
(property_type = $property_type))";

I hope this makes some sense, and I appreciate any help. Thanks!

Leslie

 

Air

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 1612 posted 3:05 am on Nov 8, 2002 (gmt 0)

Try using single quotes around the character variables, i.e.

# (property_setting = '$property_setting') AND
(property_type = '$property_type'))";

andreasfriedrich

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 1612 posted 3:17 am on Nov 8, 2002 (gmt 0)

I just saw that Air was faster and suggested the same, but here goes...

You need to have slashes around your string values. And you need to ensure that slashes within the strings are escaped.

$property_setting = addslashes($property_setting); 
$property_type = addslashes($property_type);

#beginning of $sql statement
property_setting = '$property_setting' AND
property_type = '$property_type')";

Otherwise a string like Aaron's Party would break your sql statement since MySQL would think the string ends with the single quote within the string.

Imagine the following code:

$name = "Aaron's Party"; 
$sql = "SELECT * FROM table WHERE cd='$name'"
After interpolating $name into the $sql string it will look like this:
$sql = "SELECT * FROM table WHERE cd='Aaron's Party'"
This will produce an error.

Andreas

amoore

10+ Year Member



 
Msg#: 1612 posted 5:37 am on Nov 8, 2002 (gmt 0)

It looks like the answer for which you were looking is to surround your variables in single quotes, but as long as we're on the topic, I'm going to go further than you wanted.

It's perl, so make use of the placeholder features of the DBI package for something like this:

my $dbh = # a DBI object
my $statement = "select foo from properties where property_type = '?'"; # that's a placeholder
my $sth = $dbh->prepare( $statement ) ¦¦ die "can't prepare sth";
my $ret = $sth->execute( $property_type ) ¦¦ die "can't execute";
# now use some fetch methods on $sth

That way, you don't have to worry about the content of your $property_type. Single quotes may be the least of your worries.

Also, you may want to look into an even higher database package like Class::DBI or something.

Hope it helps.

andreasfriedrich

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 1612 posted 7:00 am on Nov 8, 2002 (gmt 0)

It's perl

Did I miss something?

Anyway, in amoore´s Perl DBI example you won´t even need the single quotes around the quotation mark.

This is just another example where Perl is more elegant than PHP. ;)

Andreas

seindal

10+ Year Member



 
Msg#: 1612 posted 10:54 am on Nov 8, 2002 (gmt 0)

The example below can even be boiled down to this, making it even more programming-efficient:

$data = $dbh->selectall_arrayref("select foo from properties where property_type =?", undef, $property_type);
die "can't execute query" unless ($data);

Placeholders is definitely the way to go when avoiding quoting problems in SQL.

René

amoore writes:

It's perl, so make use of the placeholder features of the DBI package for something like this:

my $dbh = # a DBI object
my $statement = "select foo from properties where property_type = '?'"; # that's a placeholder
my $sth = $dbh->prepare( $statement ) ¦¦ die "can't prepare sth";
my $ret = $sth->execute( $property_type ) ¦¦ die "can't execute";
# now use some fetch methods on $sth

leslie

5+ Year Member



 
Msg#: 1612 posted 1:04 pm on Nov 10, 2002 (gmt 0)

Wow! Thanks for all of the great input! I tried the single quotes, and I got the script working, but now I have a logic problem. As long as my user selects an individual option for each of these inputs, I get results to print out, but my form also allows a user to select "No preference". If the user selects "No preference", I get no results (script runs, just no results printed out).

Of course if they choose that option, I want to select ALL of the records. So my question is, how do I tell the select to choose all of the records if I get a no preference option? Presently there are three fields that would have more than one string option, PLUS the No preference option. I had thought about writing different select statements, but it appears to be pretty cumbersome, and I'm not sure it would do what I want it to do. I also thought about somehow changing the value of the variable to a wildcard value, but not sure how to do this or if it would work.

Regarding the placeholder concept: I'm REALLY a novice at this stuff, and have mostly looked at other scripts to design my script. I don't understand the concept of the placeholder. Here's the functional part of my script:

#connect to the database
# set the datasource name
$dbHandle = DBI->connect("DBI:mysql:XXXXXXXX:localhost","user_id","password");

#create the sql statement
$sql = "SELECT * FROM properties
WHERE (
(rent_amount <= $rent_amount)AND
(num_baths >= $num_baths) AND
(num_beds >= $num_beds) AND
(pets >= $pets) AND
(handicap >= $handicap)AND
(fireplace >= $fireplace)AND
(deck >= $deck_patio)AND
(fence >= $fence)AND
(sec_8 >= $sec_8)AND
(pool >= $pool)AND
(property_type = '$property_type') AND
(property_setting = '$property_setting'))";

$statementHandle = $dbHandle->prepare($sql);
$statementHandle->execute() ¦¦ die $statementHandle->errstr;

What does the placeholder do? How does it make the code more efficient? Sigh. I guess it's time to invest in a real book ... :-)

Anyway, thanks for all the help!

Leslie

andreasfriedrich

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 1612 posted 1:21 pm on Nov 10, 2002 (gmt 0)

Have a look at the documentation for the DBI [search.cpan.org] module. It tells you about placeholders and why their use makes your code more efficient.

As for the "No Preferences" problem just use an SQL statement without any conditions regarding preferences in the where clause.

Andreas

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
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