Hi all,
As before, I'm still trying to work a conditional statement into my search script. I've more or less gotten it to work, except for one little bug.
I'm not sure what I'm doing wrong here, but for some reason my database returns the column I'm trying to search rather than it's contents in the database. So, it's returning the field to be searched rather than it's value. Something is going wrong around line 65, and the variable isn't being put into the sql string but I can't figure out what...
<?php
//ini_set('display_errors',1);
//error_reporting(E_ALL | E_STRICT);
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
// take chronology and get corresponding interpretation
switch($chronology){
case "MAXOFCL":
$interpretation = "c.INTERPCL";
break;
case "MAXOFEH":
$interpretation = "c.INTERPEH";
break;
case "MAXOFH":
$interpretation = "c.INTERPH";
break;
case "MAXOFER":
$interpretation = "c.interper";
case "MAXOFMR":
$interpretation = "c.interpmr";
break;
case "MAXOFLA1":
$interpretation = "c.interpla1";
break;
case"MAXOFLA2":
$interpreation ="c.interpla2";
break;
case "MAXOFEM":
$interpretation ="c.interpem";
break;
case "MAXOFMM":
$interpretation ="c.interpmm";
break;
case "MAXOFLM":
$interpreation = "c.interplm";
break;
case "MAXOFEMOD1":
$interpretation ="c.interpmod1";
break;
case "MAXOFEMOD2":
$interpretation = "c.interpmod2";
break;
}
//add interp-field to sql statement
if($interpretation){
$interpretation = ''.$interpretation.'';
}
if($chronology){
$phase_certainty = $chronology;
}
$phase_certainty = "c.";
if($phase_certainty){
$phase_certainty .= " '".$phase_certainty."' ";
}
if($interpretation){
$sql .= $interpretation;
}
//if($phase_certainty){
//$sql .= $phase_certainty;
//}
// remaining portion of sql statement
$fields=" '".$interpretation."', '".$phase_certainty."', 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__';
//append remainder to sql
if($fields){
$sql .= $fields;
}
if($q){
$where = "c.interpcl like '".$q."'
OR c.interpeh like '".$q."'
OR c.interph like '".$q."'
OR c.interper like '".$q."'
OR c.interpmr like '".$q."'
OR c.INTERPLA1 like '".$q."'
OR c.INTERPLA2 like '".$q."'
OR c.interpem like '".$q."'
OR c.interpmm like '".$q."'
OR c.interplm like '".$q."'
OR c.INTERPMOD1 like '".$q."'
OR c.INTERPMOD2 like '".$q."'
OR c.site_ like '".$q."'
OR c.material like '".$q."'
OR chronology.CLASS like '".$q."'"
;
}
// $where = '';
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, c.M2, 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 like '".$q."'
OR c.interpeh like '".$q."'
OR c.interph like '".$q."'
OR c.interper like '".$q."'
OR c.interpmr like '".$q."'
OR c.INTERPLA1 like '".$q."'
OR c.INTERPLA2 like '".$q."'
OR c.interpem like '".$q."'
OR c.interpmm like '".$q."'
OR c.interplm like '".$q."'
OR c.INTERPMOD1 like '".$q."'
OR c.INTERPMOD2 like '".$q."'
OR c.site_ like '".$q."'
OR c.material like '".$q."'
OR chronology.CLASS like '".$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;
}
?>
