building dynamic SQL sintax. ????

[aron]

i need to run this code first then be able to call it from my sql.

do i need to assign this a variable. eg $sql_1
$size = count($suburbs);
$count = 1;
foreach($suburbs as $suburbsElement) {
echo "'$suburbsElement'";
if ($count < $size) {
echo ' OR suburb= ';
}
$count++;
}

one the above finishes its loop i then need to call it into here.

$sql="SELECT * FROM Listing WHERE suburb=$sql_1 "

its basicly a big long search query.

the above build all the fields to search from within the database.

anyone able to help please.
thanks.

[bretticus]

foreach($suburbs as $suburbsElement) 
{ 
   $sql_1 .= "'$suburbsElement'"; 
   if ($count < $size) 
  { 
      $sql_1 .= ' OR suburb= '; 
   } 
$count++; 
}

[aron]

it prints like this.

'BARELLAN POINT''BELLBOWRIE'

its ment to use this part as a check to see how many querys it finds then put the OR suburb= in
{

$sql_1 .= ' OR suburb= ';

}

$count++;

}

so in the above its 2 so it should look like this.

'BARELLAN POINT' OR suburb='BELLBOWRIE'

any way to fix it.
cheers.
aron

[aron]

this is what i have done.

if($nav == 'search'){
$size = count($suburbs);
$count = 1;
foreach($suburbs as $suburbsElement)

{

$sql_1 .= "'$suburbsElement'";

if ($count < $size)

{

$sql_1 .= ' OR suburb= ';

}

$count++;

}
$sql = "SELECT * FROM Listing WHERE suburb=$sql_1 HAVING price BETWEEN $pricefrom AND $priceto ORDER BY $order";
//echo $sql;
$sql_result = mysql_query($sql,$connection) or die ("Could not connect to database check");
$rows = mysql_num_rows($sql_result);
}

it comes up with this error.

Notice: Undefined variable: sql_1 in D:\nuvocreative\test\clients\bre\house.php on line 386

and this is the sql when you do a echo $sql.

SELECT * FROM Listing WHERE suburb='ANSTEAD' OR suburb= 'BARDON' OR suburb= 'BARELLAN POINT' OR suburb= 'BELLBOWRIE' OR suburb= 'BRISBANE' HAVING price BETWEEN 150000 AND 600000 ORDER BY priceCould not connect to database check

any ideas.

[bretticus]

Looking at your code, if you have two element values ('BARELLAN POINT' & 'BELLBOWRIE' ), then in the first loop $size should be 2 and $count should be 1. the if statement should be true and thus ' OR suburb= ' should be printed. Maybe you can show more of your code?

[bretticus]

try putting

$sql_1 = "";

at the top I suppose.

[aron]

http://my website.../house.php?pricefrom=150000&priceto=450000&property=Apartments&bedfrom=4&bedto=5&suburbs%5B%5D=ANSTEAD&suburbs%5B%5D=BARDON&suburbs%5B%5D=BARELLAN+POINT&suburbs%5B%5D=BELLBOWRIE&suburbs%5B%5D=BRISBANE&select8=Any&nav=search&order=price&select7=Any&select7=from&select18=to&select9=No&select10=No&select11=No&select12=No&select13=No&select14=No&select15=No&select16=No&select17=No&Submit=Submit

ignor or the from selects and stuff. just trying to get the suburbs working.

thanks.

[aron]

i found the problem.

i was missing a "s" of listings in my select. was listing is ment to be listings.

all works now except i get this error at the top of my page.

Notice: Undefined variable: sql_1 in D:\my website...\bre\house.php on line 386

can i not use sql_1.
or how do i define it.

thanks
aron.

[bretticus]

try putting

$sql_1 = "";

at the top I suppose.

[aron]

thanks.