RE: [PHP-DB] SELECT string
| From: | Dwight Altman | Date: | Tue, 24 Apr 2007 15:35:05 +0000 |
| Subject: | RE: [PHP-DB] SELECT string | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-43483@lists.php.net to get a copy of this message | ||
Businesses
----------
id
businessName
isChristianBookstore
isGift
isHomeDecor
isSkinCareAndCosmetics
isThriftStore
CREATE TABLE
Businesses (
id BIGINT NOT NULL AUTO_INCREMENT ,
businessName VARCHAR( 64 ) NOT NULL ,
isChristianBookstore TINYINT( 1 ) NOT NULL ,
isGift TINYINT( 1 ) NOT NULL ,
isHomeDecor TINYINT( 1 ) NOT NULL ,
isSkinCareAndCosmetics TINYINT( 1 ) NOT NULL ,
isThriftStore TINYINT( 1 ) NOT NULL ,
PRIMARY KEY ( id )
) TYPE = MYISAM ;
<?php
$DbFieldByFormFieldMap = array(
'category_1' => 'isChristianBookstore',
'category_42' => 'isGift',
'category_44' => 'isHomeDecor',
'category_43' => 'isSkinCareAndCosmetics',
'category_17' => 'isThriftStore'
);
$sql = "SELECT id, businessName FROM Businesses WHERE
";
foreach($_POST as $formField => $formValue){
if(substr($formField, 0, 9) == "category_" && substr($formValue, -1)
== "a"){//Handle any Not Selected
$sql .= "" . $DbFieldByFormFieldMap[$formField] . " = '0'
AND ";
}
if(substr($formField, 0, 9) == "category_" && substr($formValue, -1)
== "b"){//Handle any Must Include
$sql .= "" . $DbFieldByFormFieldMap[$formField] . " = '1'
AND ";
}
}
if(substr($sql, -4) == "AND "){
$sql = substr($sql, 0, -4);
}else{
$sql .= "1";
}
echo "SQL:$sql";
?>
It was getting a tad complicated with the "Could Include" using "OR" and
testing for the end of the existing $sql string, but the "Could Include"'s
just need to be omitted from the SQL query altogether.
Regards,
Dwight
God Bless!
> -----Original Message-----
> From: Ron Piggott [mailto:ron.php@actsministries.org]
> Sent: Monday, April 23, 2007 11:31 PM
> To: PHP DB
> Subject: [PHP-DB] SELECT string
>
>
> I am looking for help to write a SELECT syntax to help me process a
> directory searching query tool I am developing.
>
> If you start at
>
> http://www.actsministrieschristianevangelism.org/ministrydirectory/
> and
> under 'Step 1:' click Business a form is displayed.
>
> My question is how would you generate the SELECT syntax for the search
> results "Could Include" a given category and "Must Include" a given
> category based on what the user has inputted through this form?
>
> Ron