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@xxxxxxxxxxxxxxxxxx]
> Sent: Monday, April 23, 2007 11:31 PM
> To: PHP DB
> Subject: 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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
