len() with dynamic sql statement

geogal

Hi all. I've just recently figured out how (with the help of you good folks) to perform a dynamic sql statement in the WHERE clause. My problem lies at the end of the generated code, when I have to dynamically delete the " AND" from the sql statement.

Here's a sample snippit:

$sql = "SELECT table1.field, table2.field from.........WHERE";

if ($builder){
$where1 = " fk_builder = ('$builder') AND";
}

if ($broker){
$where2 = " fk_broker=('$broker') AND";
}

$where3 = " price BETWEEN ('$minPrice') AND ('$maxPrice') AND";
}

$sqlquery = $sql . $where1 . $where2 . $where3 ;

$result = mysql_query($sqlquery);
$number = mysql_numrows($result);

Any ideas on how to "back out" the last AND if the variable isn't used in the sql query? I think I need some sort of len() function but I don't know how to begin.

Thanks gang!

geogal

sorry, this is the correct code after the WHERE;

if ($builder){
$where1 = " fk_builder = ('$builder') AND";
}

if ($broker){
$where2 = " fk_broker=('$broker') AND";
}

if ($model){
$where3 = " fk_model=('$model') AND";
}

fukas

$sqlquery = $sql . $where1 . $where2 . $where3 . ((isset($where1) || isset($where2) || isset($where3))?' 1=1':'');

geogal

Thanks for the reply! I have amended my code as follows, but I get an error.

$sql = "SELECT brokers.broker_name, builders.builder_name, communities.community_name, inventory2.lot_number, inventory2.price, models.model_name ".
"FROM (((inventory2 INNER JOIN brokers ON inventory2.fk_broker = brokers.brID) ".
"INNER JOIN builders ON inventory2.fk_builder = builders.buID) ".
"INNER JOIN communities ON inventory2.fk_community = communities.cID) ".
"INNER JOIN models ON inventory2.fk_model = models.mID WHERE";

// **** dynamic where clause

if ($builder){
$where1 = " fk_builder = ('$builder') AND";
}

if ($broker){
$where2 = " fk_broker=('$broker') AND";
}

if ($model){
$where3 = " fk_model=('$model') AND";
}

$sqlquery2 = $sql . $where1 . $where2 . $where3 . ((isset($where1) || isset($where2) || isset($where3)?' 1=1':' ');

Am I missing something? Thanks for any help!

geogal

Hi All,

Nevermind, I've discovered how to do this. My SQL statement was set up incorrectly, and in deeper research on these pages, I found the answer. In case anyone else is interested, here's how you dynamically create a SQL statement:

// **** dynamic where clause

if ($builder!="")
{
$sql .=" AND fk_builder = ('$builder') ";
};

if ($broker!="") {
$sql .=" AND fk_broker = ('$broker') ";
};

if ($model!="") {
$sql .=" AND fk_model = ('$model') ";
};

Then you call out your variables. There is a way to do this with arrays, but I haven't done that yet. Thanks for the help!

Geogal