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
# (property_setting = '$property_setting') AND
(property_type = '$property_type'))";
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";After interpolating $name into the $sql string it will look like this:
$sql = "SELECT * FROM table WHERE cd='$name'"
$sql = "SELECT * FROM table WHERE cd='Aaron's Party'"This will produce an error.
Andreas
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.
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
$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
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
As for the "No Preferences" problem just use an SQL statement without any conditions regarding preferences in the where clause.
Andreas
