[PHP-DB] Re: Multiple select ?
Try using parentheses inside your sql: Select * from table_name where (col1=1 OR col2=1) and col3=0 Select * from table_name where col1=1 OR (col2=1 and col3=0) They make a big difference, also helps to indentify what you're selecting when you come back to the sql later. Adam
[PHP-DB] Re: multiple select statements
Hi there :)
You could construct seperate queries for every possible combination of
search provided you don't have a huge number of search criteria (form
elements for your search)
.
I did this just recently for a friend finder site. In the search the user
has four fields that are automatically included in the search and three more
that are optional
I built the sql queries based on every combination of the three optional
search criterias. Since there are three optional search criterias the number
of queries to be built is 8 to compensate for every combination.
This is one way to do it anyway:)
Hope this helps you out, Joe :)
?php
# stateprovince, country, and relationship are all optional therefor 8
combinations for query -- use the other form values as well
if(($country == All) ($stateprovince == All) ($relationship ==
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN '$age1'
AND '$age2' AND sexuality ='$sexuality' ORDER BY signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of . $age1
. and . $age2;
}
elseif(($country != All) ($stateprovince == All) ($relationship ==
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN '$age1'
AND '$age2' AND sexuality ='$sexuality' AND country ='$country' ORDER BY
signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of . $age1
. and . $age2 . from . $country;
}
elseif(($country != All) ($stateprovince != All) ($relationship ==
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state, country,
relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN '$age1' AND '$age2' AND
sexuality ='$sexuality' AND country ='$country' AND province_state ='$stateprovince'
ORDER BY signupdate DESC;
$searchcriteria
= $sex . . $sexuality . between the ages of . $age1 . and .
$age2 . from . $stateprovince . , . $country;
}
elseif(($country != All) ($stateprovince != All) ($relationship !=
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN '$age1'
AND '$age2' AND sexuality ='$sexuality' AND country ='$country' AND
province_state ='$stateprovince' AND relationship ='$relationship' ORDER BY
signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of . $age1
. and . $age2 . from . $stateprovince . , . $country . looking
for . $relationship . relationship.;
}
elseif(($country == All) ($stateprovince != All) ($relationship !=
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN '$age1'
AND '$age2' AND sexuality ='$sexuality' AND province_state
='$stateprovince' AND relationship ='$relationship' ORDER BY signupdate
DESC;
$searchcriteria = $sex . . $sexuality . between the ages of . $age1
. and . $age2 . from . $stateprovince . looking for .
$relationship . relationship.;
}
elseif(($country == All) ($stateprovince == All) ($relationship !=
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN '$age1'
AND '$age2' AND sexuality ='$sexuality' AND relationship ='$relationship'
ORDER BY signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of . $age1
. and . $age2 . looking for . $relationship . relationship.;
}
elseif(($country != All) ($stateprovince == All) ($relationship !=
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN '$age1'
AND '$age2' AND sexuality ='$sexuality' AND country ='$country' AND
relationship ='$relationship' ORDER BY signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of . $age1
. and . $age2 . from . $country . looking for . $relationship .
relationship.;
}
elseif(($country == All) ($stateprovince != All) ($relationship ==
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN '$age1'
AND '$age2' AND sexuality ='$sexuality' AND country ='$country' ORDER BY
signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of . $age1
. and . $age2 . from . $stateprovince;
}
#connect to db here
odbc_do($connectionToDb, $sql);
blah, blah, blah :)
Geoffrey Makstutis [EMAIL PROTECTED] wrote in message
002201c1c460$85f5fd80$8eaf7ad5@nt..">news:002201c1c460$85f5fd80$8eaf7ad5@nt..;
Hi,
I've got an HTML form which allows users to select various criteria to
search for in my database (MySQL).
The problem is that I can't seem to figure out how create the SELECT
statement, given
RE: [PHP-DB] Re: multiple select statements
I'd try building it piece by piece if you do have too many combinations -
(I'm not promising efficiency, but I do something similar to this in one of
my programs and it works).
Have vars for each type of data:
$firstName = ;
$lastName = ; etc.
Then you can set each one based on the selection boxes you have
if ($firstNameComboValue select one)
$firstName = $firstNameComboValue;
From there you can build your SQL statement:
$criteria = ;
if ($firstName )
$criteria .= '$firstName'; (I can't remember if PHP needs the
single quotes or not but perl does)
... for each variable (making sure you put in AND between each one).
Then you can do:
$sql = SELECT whatever from table WHERE $criteria;
I do my db programming in perl so this is probably a horrible mix of the
two, but at least it will give you an idea for somewhere you could start.
You could also use booleans to determine if each one has been set, but that
would probably just be an extra step that you wouldn't need unless you want
to use them somewhere else too.
Good luck!
-Natalie
-Original Message-
From: Lerp [SMTP:[EMAIL PROTECTED]]
Sent: Tuesday, March 05, 2002 11:47 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Re: multiple select statements
Hi there :)
You could construct seperate queries for every possible combination of
search provided you don't have a huge number of search criteria (form
elements for your search)
.
I did this just recently for a friend finder site. In the search the user
has four fields that are automatically included in the search and three
more
that are optional
I built the sql queries based on every combination of the three optional
search criterias. Since there are three optional search criterias the
number
of queries to be built is 8 to compensate for every combination.
This is one way to do it anyway:)
Hope this helps you out, Joe :)
?php
# stateprovince, country, and relationship are all optional therefor 8
combinations for query -- use the other form values as well
if(($country == All) ($stateprovince == All) ($relationship ==
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN
'$age1'
AND '$age2' AND sexuality ='$sexuality' ORDER BY signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of .
$age1
. and . $age2;
}
elseif(($country != All) ($stateprovince == All) ($relationship
==
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN
'$age1'
AND '$age2' AND sexuality ='$sexuality' AND country ='$country' ORDER BY
signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of .
$age1
. and . $age2 . from . $country;
}
elseif(($country != All) ($stateprovince != All) ($relationship
==
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN
'$age1' AND '$age2' AND sexuality ='$sexuality' AND country ='$country'
AND province_state ='$stateprovince' ORDER BY signupdate DESC;
$searchcriteria
= $sex . . $sexuality . between the ages of . $age1 . and .
$age2 . from . $stateprovince . , . $country;
}
elseif(($country != All) ($stateprovince != All) ($relationship
!=
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN
'$age1'
AND '$age2' AND sexuality ='$sexuality' AND country ='$country' AND
province_state ='$stateprovince' AND relationship ='$relationship' ORDER
BY
signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of .
$age1
. and . $age2 . from . $stateprovince . , . $country .
looking
for . $relationship . relationship.;
}
elseif(($country == All) ($stateprovince != All) ($relationship
!=
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN
'$age1'
AND '$age2' AND sexuality ='$sexuality' AND province_state
='$stateprovince' AND relationship ='$relationship' ORDER BY signupdate
DESC;
$searchcriteria = $sex . . $sexuality . between the ages of .
$age1
. and . $age2 . from . $stateprovince . looking for .
$relationship . relationship.;
}
elseif(($country == All) ($stateprovince == All) ($relationship
!=
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN
'$age1'
AND '$age2' AND sexuality ='$sexuality' AND relationship ='$relationship'
ORDER BY signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of .
$age1
. and . $age2 . looking for . $relationship
Re: [PHP-DB] Re: multiple select statements
check out this code:
*
function build_statement ( $col, $needle ) {
$sWhere = ;
if ( $needle!= ) {
$array = explode ( , $needle );
$sWhere = ( ;
$firsttime= True;
foreach ( $array as $str ) {
if ( !$firsttime)
$sWhere.= or ;
$sWhere.= $col like '%$str%' ;
$firsttime= False;
} // foreach
$sWhere.= );
} // if ( $needle!= )
return $sWhere;
} /* function */
$nombrearea = strtoupper ( $nombrearea );
if ( $nombreempresa ) {
$sWhere.= armar_where ( UPPER(e.nombreempresa), $nombreempresa ). ;
$sql =SELECT * FROM list WHERE $sWhere
}
*
- Original Message -
From: Leotta, Natalie (NCI/IMS) [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, March 05, 2002 8:50 AM
Subject: RE: [PHP-DB] Re: multiple select statements
I'd try building it piece by piece if you do have too many combinations -
(I'm not promising efficiency, but I do something similar to this in one
of
my programs and it works).
Have vars for each type of data:
$firstName = ;
$lastName = ; etc.
Then you can set each one based on the selection boxes you have
if ($firstNameComboValue select one)
$firstName = $firstNameComboValue;
From there you can build your SQL statement:
$criteria = ;
if ($firstName )
$criteria .= '$firstName'; (I can't remember if PHP needs the
single quotes or not but perl does)
... for each variable (making sure you put in AND between each one).
Then you can do:
$sql = SELECT whatever from table WHERE $criteria;
I do my db programming in perl so this is probably a horrible mix of the
two, but at least it will give you an idea for somewhere you could start.
You could also use booleans to determine if each one has been set, but
that
would probably just be an extra step that you wouldn't need unless you
want
to use them somewhere else too.
Good luck!
-Natalie
-Original Message-
From: Lerp [SMTP:[EMAIL PROTECTED]]
Sent: Tuesday, March 05, 2002 11:47 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Re: multiple select statements
Hi there :)
You could construct seperate queries for every possible combination of
search provided you don't have a huge number of search criteria (form
elements for your search)
.
I did this just recently for a friend finder site. In the search the
user
has four fields that are automatically included in the search and three
more
that are optional
I built the sql queries based on every combination of the three optional
search criterias. Since there are three optional search criterias the
number
of queries to be built is 8 to compensate for every combination.
This is one way to do it anyway:)
Hope this helps you out, Joe :)
?php
# stateprovince, country, and relationship are all optional therefor 8
combinations for query -- use the other form values as well
if(($country == All) ($stateprovince == All) ($relationship ==
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN
'$age1'
AND '$age2' AND sexuality ='$sexuality' ORDER BY signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of .
$age1
. and . $age2;
}
elseif(($country != All) ($stateprovince == All)
($relationship
==
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN
'$age1'
AND '$age2' AND sexuality ='$sexuality' AND country ='$country' ORDER BY
signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of .
$age1
. and . $age2 . from . $country;
}
elseif(($country != All) ($stateprovince != All)
($relationship
==
)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN
'$age1' AND '$age2' AND sexuality ='$sexuality' AND country ='$country'
AND province_state ='$stateprovince' ORDER BY signupdate DESC;
$searchcriteria
= $sex . . $sexuality . between the ages of . $age1 . and .
$age2 . from . $stateprovince . , . $country;
}
elseif(($country != All) ($stateprovince != All) ($relationship
!=
All)){
$sql = SELECT friend_id, first_name, sex, age, city, province_state,
country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN
'$age1'
AND '$age2' AND sexuality ='$sexuality' AND country ='$country' AND
province_state ='$stateprovince' AND relationship ='$relationship' ORDER
BY
signupdate DESC;
$searchcriteria = $sex . . $sexuality . between the ages of .
$age1
. and . $age2 . from . $stateprovince . , . $country .
looking
for . $relationship . relationship.;
}
elseif(($country == All) ($stateprovince != All
[PHP-DB] RE: Multiple select list box to search mysql database
I've done stuff like this in VBScript and haven't yet done it in PHP,
so this advice may be worth what you're paying for it:
1. Make sure you've got a space in your sql string.
$query .= WHERE MANUFACTURER LIKE '$manufacturername'
should be
$query .= WHERE MANUFACTURER LIKE '$manufacturername'
(note the space before the WHERE). Otherwise your sql will be
DISTINCT MANUFACTURER FROM inventoryWHERE manufacturer
LIKE 'GE' ORDER BY Manufacturer ASC
2. When your form is submitted, the value of $manufacturername will be
a comma-delimited list of all the values they selected. If your query
will be based on this selection, then it must read:
SELECT ... WHERE Manufacturer IN ('$manufacturername')
3. You can use 'size=n' to control the heigth of the form object
SELECT name=manufacturername multiple size=2
TIM
-The only domestic animal not mentioned in the Bible is the cat.
-Original Message-
From: Gisele Grenier [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 01, 2002 9:53 PM
To: [EMAIL PROTECTED]
Subject: Multiple select list box to search mysql database
Hi everyone,
This is a php and mysql question...
I've spent days trying to figure this out and can only find
help on one or
the other.. not both things that I'm trying to do.
I have a table called inventory which contains the following:
manufacturer
product
price
I have a php form that has a list box that contains
distinct values from the
inventory table as follows:
$sql = SELECT DISTINCT MANUFACTURER FROM inventory ORDER
BY MANUFACTURER
ASC;
// execute SQL query and get result
$sql_result = mysql_query($sql,$connection)
or die(Couldn't execute query.);
// put data into drop-down list box
while ($row = mysql_fetch_array($sql_result)) {
$manufacturername = $row[MANUFACTURER];
$option_block.= OPTION
value=\$manufacturername\$manufacturername/OPTION;
}
Then the list box is created:
Select a manufacturer
SELECT name=manufacturername
optionAll/option
? echo $option_block; ?
/SELECT
All this works great when selecting only one manufacturer
to display. The
data is passed to: $query .= WHERE MANUFACTURER LIKE
'$manufacturername'
Where I'm having a problem is that I want to be able to
select multiple
manufacturers to search.
I know that I need to add multple to the select as:
SELECT name=manufacturername multiple
and that's about it...I'm lost after this.
can anyone shed any light on this?
Thanks,
Gisele Grenier
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
