PHP/MySQL query character limit?

Question

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.

Answer 1

Oh my, that code is a mess. Nothing that can't be fixed.

$query = "SELECT ";
$query .= mysql_real_escape_string($selectWhat);

This will fail if you have two things in the string, like foo, bar, and will utterly obliterate your parenthesized clauses, if you're passing those in.

$query .= " FROM ";
$query .= mysql_real_escape_string($fromTable);

Tables in the FROM clause use different quoting rules than normal data. Don't run it through string escaping.

You probably want to consider switching to a database access layer like PDO that provides prepared statements & placeholders.

if($orderBySampIDAsc=='true')

PHP has real booleans. You should not be attempting to represent a boolean state as a string.

Now, all this being said, I don't see how the query function you've written can create the SQL that you've provided at the top of your question! Can you throw in a print $query at the bottom of the function, prior to the call to mysql_query, and see what's actually being generated? Then you can copy & paste it into phpmyadmin and see how sane/insane it really ends up being.

Answer 2

Hello,

You can rewrite your above query like this using the HAVING keyword:

SELECT 
    (CASE Methanethiol 
        WHEN -1 THEN 0
        ELSE Methanethiol
    END)
    +
    ...
    +
    (CASE nHeptanethiol
        WHEN -1 THEN 0
        ELSE nHeptanethiol
    END)
AS matches
FROM condensates.mercaptans
HAVING matches > 0