conditional sql query based on ajax variable

kind of over-complicated approach to this form...but

[Nizzok]

Hi All,

I want to add a conditional statement to my search script, which adds a certain field to my sql query depending on the variable from a drop-down form via an Ajax request. I think the best way to do this is with an associative array matching the drop down, and a conditional within a foreach loop, which will select the appropriate field if it matches. I'm not 100% how to implement this, and at the moment my script breaks at the associative array with a T_unexpected variable. Any suggestions on how to approach this?

require_once("dbconn.php");
function getResultSearch($q, $chronology=''){
$retArr = array();


//SQL statement change this for different searches
$sql= "SELECT DISTINCT c.NEWSITE_, c.site_, c.location1,"
//get period
$period = array("MAXOFCL"=>c.interpcl, "MAXOFEH"=>"c.interpeh,", "MAXOFH"=>"c.interph,", "MAXOFER"=>"c.interper,", "MAXOFMR"=>"c.interpmr,", "MAXOFLA1"=>"c.interpla1,", "MAXOFLA2"=>"c.interpla2,", "MAXOFEM"=>"c.interpem,", "MAXOFMM"=>"c.interpmm,", "MAXOFLM"=>"c.interplm,");
// if chronology matches then add appropriate interp-field
foreach($period as $phase){
if($chronology = $period)
{
$interpretation = $$period;
}
}
//add interp-field to sql statement
if($interpretation){
$interpretation = '('.$interpretation.'),';
}
$sql .= $interpretation;
$fields="c.descriptio, c.presence_a, c.material, c.interpcl, c.interpeh, c.interph, c.interper, c.interpmr, c.INTERPLA1, c.INTERPLA2, c.interpem, c.interpmm, c.interplm, c.INTERPMOD1, c.INTERPMOD2, c.M2, chronology.CLASS
FROM compsites02 c"
.' left join `ceramic dbase july2k1` chronology ON c.NEWSITE_=chronology.NEW_SITE__'
;
if($fields){
$sql .= $fields;
}
if($q){
}
$where = "c.interpcl = '".$q."'
OR c.interpeh = '".$q."'
OR c.interph = '".$q."'
OR c.interper = '".$q."'
OR c.interpmr = '".$q."'
OR c.INTERPLA1 = '".$q."'
OR c.INTERPLA2 = '".$q."'
OR c.interpem = '".$q."'
OR c.interpmm = '".$q."'
OR c.interplm = '".$q."'
OR c.INTERPMOD1 = '".$q."'
OR c.INTERPMOD2 ='".$q."'
OR c.site_ = '".$q."'
OR c.material = '".$q."'
OR chronology.CLASS = '".$q."'"
;
}
if($chronology && $chronology != 'ALL'){
if($where){
$where = '('.$where.') and';
}
$where .= ' c.`'.$chronology.'` is not NULL';
}

if($where){
$sql .= ' where '.$where;
}
$sql .= ' group by c.NEWSITE_, c.site_, c.location1, c.descriptio, c.presence_a, c.material, c.M2, chronology.CLASS';
if( $result = clMyConnect::$mysqli->query($sql)){
while ($row = mysqli_fetch_array($result)) {
if(!is_array($retArr[$row['NEWSITE_']])){
$retArr[$row['NEWSITE_']] = $row;
}
else{
if(trim($retArr[$row['NEWSITE_']]['CLASS'])){
$retArr[$row['NEWSITE_']]['CLASS'] .= ', ';
}
$retArr[$row['NEWSITE_']]['CLASS'] .= $row['CLASS'];

}

}
}

return $retArr;
}

function getResultSearchForXml($q, $chronology=''){
$retArr = array();


//SQL statement change this for different searches
$sql= "SELECT c.site_, c.location1, c.descriptio, c.presence_a, c.material, points.Lat, points.Lon,
c.newsite_, c.utm_e, c.utm_n
FROM compsites02 c
INNER join points ON points.NEWSITE_=c.NEWSITE_"
.' left join `ceramic dbase july2k1` chronology ON c.NEWSITE_=chronology.NEW_SITE__'
;
if($q){
$where = " c.interpcl = '".$q."'
OR c.interpeh = '".$q."'
OR c.interph = '".$q."'
OR c.interper = '".$q."'
OR c.interpmr = '".$q."'
OR c.INTERPLA1 = '".$q."'
OR c.INTERPLA2 = '".$q."'
OR c.interpem = '".$q."'
OR c.interpmm = '".$q."'
OR c.interplm = '".$q."'
OR c.INTERPMOD1 = '".$q."'
OR c.INTERPMOD2 ='".$q."'
OR c.site_ = '".$q."'
OR c.material = '".$q."'
OR chronology.CLASS = '".$q."'"
;

}
if($chronology && $chronology!= 'ALL'){
if($where){
$where = '('.$where .') and';
}
$where .= ' c.`'.$chronology.'` is not NULL';
}

if($where){
$sql .= ' where '.$where;
}
$sql .= ' group by c.NEWSITE_';
//echo $sql;
if( $result = clMyConnect::$mysqli->query($sql)){
while ($row = mysqli_fetch_array($result)) {
$retArr[] = $row;
}
}

return $retArr;
}

[andrewsmd]

I didn't really look at the code but I have these pages that do a select statement based on user input from a text box.

Javascript
<script type="text/javascript">
var RecoverScroll=/*28432953204368616C6D657273*/
{

timer:null, x:0, y:0,cookieId:"RecoverScroll", dataCode:0, DEBUG:false,

init:function(pageName)
{
var offsetData;

if( document.documentElement )
this.dataCode=3;
else
if( document.body && typeof document.body.scrollTop!='undefined' )
this.dataCode=2;
else
if( typeof window.pageXOffset!='undefined' )
this.dataCode=1;

if(pageName)
this.cookieId=pageName;

if((offsetData=this.readCookie(this.cookieId))!=""
&& (offsetData=offsetData.split('|')).length==4
&& !isNaN(offsetData[1]) && !isNaN(offsetData[3]))
{
window.scrollTo(offsetData[1],offsetData[3]);
if(this.DEBUG)
document.title=offsetData;
}

this.record();
this.addToHandler(window,'onscroll',function(){RecoverScroll.reset()});
},

reset:function()
{
clearTimeout(this.timer);
this.timer=setTimeout("RecoverScroll.record()",500);
},

record:function()
{
var cStr;

this.getScrollData();

this.setTempCookie(this.cookieId, cStr='x|'+this.x+'|y|'+this.y);

if(this.DEBUG)
window.status=cStr;
},

setTempCookie:function(cName, cValue)
{
document.cookie=cName+"="+cValue;
},

readCookie:function(cookieName)
{
var cValue="";

if(typeof document.cookie!='undefined')
cValue=(cValue=document.cookie.match(new RegExp(cookieName+'=([^;]+);?'))) ? cValue[1] : "";

return cValue;
},

getScrollData:function()
{
switch( this.dataCode )
{
case 3 : this.x = Math.max(document.documentElement.scrollLeft, document.body.scrollLeft);
this.y = Math.max(document.documentElement.scrollTop, document.body.scrollTop);
break;

case 2 : this.x=document.body.scrollLeft;
this.y=document.body.scrollTop;
break;

case 1 : this.x = window.pageXOffset; this.y = window.pageYOffset; break;
}
},

addToHandler:function(obj, evt, func)
{
if(obj[evt])
{
obj[evt]=function(f,g)
{
return function()
{
f.apply(this,arguments);
return g.apply(this,arguments);
};
}(func, obj[evt]);
}
else
obj[evt]=func;
}
}

function checkName(value, id){

xmlHttp=GetXmlHttpObject();
if (xmlHttp==null){

alert ("Your browser does not support AJAX!");
return;

}//if xml null

var url="newNameCheck.php";
url=url+"?nameID="+value;
url=url+"&sid="+Math.random();
xmlHttp.onreadystatechange=function () {stateChanged(id);};
xmlHttp.open("GET",url,true);
xmlHttp.send(null);

}//checkName
</script>

PHP newNameCheck.php
<?php

session_start();

//require my files
require_once("connection.php");
require_once("DB.php");

//the input from the user
$newName = $_GET['nameID'];

//check to see if the name is blank
if($newName == ""){

echo("You need to give me a name.");

}//if

//see if it exists in the table
else{

$query = "select count(*) from jobs where userID = '{$_SESSION['id']}' and name = '{$newName}';";
$result = queryDB(connectDB(), $query);
while($row = $result->fetchRow(DB_FETCHMODE_ASSOC)){

if($row['count(*)'] > 0){

echo("That name exists,<br>please select another name.");

}//if
else{

echo("That name is fine.");

}//else

}//while

}//else

?>

Html
<input type = "text" name = "newJobName" onkeyup="checkName(this.value, 24)">

[Nizzok]

Hi,

Thanks for your response. This script worked fine, as does the ajax, I just wanted to add a conditional statement to get more semantic information from the database. I'd appreciate if someone had any insight as to why I'm getting an unexpected T_variable around line 12.
thanks in advance for your help