So I've got a relatively long query as follows:
SELECT (
(CASE Methanethiol
WHEN -1 THEN 0
ELSE Methanethiol
END)
+
...
+
(CASE nHeptanethiol
WHEN -1 THEN 0
ELSE nHeptanethiol
END)
)
FROM condensates.mercaptans
WHERE (
(CASE Methanethiol
WHEN -1 THEN 0
ELSE Methanethiol
END)
+
...
+
(CASE nHeptanethiol
WHEN -1 THEN 0
ELSE nHeptanethiol
END)
) IS NOT NULL
The problem is that the query works perfectly fine in MySQL admin, but PHP seems to choke on it when I add more then 4 columns and gives me a NULL result. Any tips? Also, am I missing some easy way to simply set the NOT NULL condition for the entire SELECT parameter rather than copying it out again?
EDIT: As requested, the PHP that calls this query is as follows...
First function call is:
$mr = avg(query($property, 'mercaptans', $dates['mostRec']), $property);
Where query and avg are defined as:
function avg($query, $colName){
$iter=0;
$sum=0;
while($row = mysql_fetch_array($query)) {
if($row[0]!==NULL){
$iter++;
$sum += ($row[$colName]==-1) ? 0 : $row[$colName];
}
}
mysql_free_result($query);
if ($iter==0)
return '-';
else {
$avg = ($sum / $iter);
if(lessThanMDL($avg, $colName))
return 'ND';
else
return $avg;
}
}
function query($selectWhat, $fromTable, $sampleIDs,$orderBySampIDAsc='false'){
$query = "SELECT ";
$query .= mysql_real_escape_string($selectWhat);
$query .= " FROM ";
$query .= mysql_real_escape_string($fromTable);
if(count($sampleIDs) >= 1) {
$query .= " WHERE (";
$iter=0;
while($iter < count($sampleIDs)-1){
$query .= "(SampleID=" . >mysql_real_escape_string($sampleIDs[$iter]) . ") OR ";
$iter++;
}
$query .= "(SampleID=" . >mysql_real_escape_string($sampleIDs[$iter]) . "))";
$query .= " AND " . mysql_real_escape_string($selectWhat) . " IS NOT NULL";
} else {
$query .= " WHERE SampleID=0"; # always returns nothing
}
if($orderBySampIDAsc=='true')
$query .= " ORDER BY SampleID ASC";
global $condensatesdb;
return mysql_query($query, $condensatesdb);
}
Sorry it's so spaced out - I can't seem to get it formatted otherwise. Anyway, this code works in a probably close to 30 other queries on the page, but fails just for this one.