Databases
StackOverflow2013
Postsdbo
POPHP/MySQL query character limit?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP/MySQL query character limit?
    text
    copied!<p>So I've got a relatively long query as follows:</p> <pre><code>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 </code></pre> <p>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?</p> <p>EDIT: As requested, the PHP that calls this query is as follows...</p> <p>First function call is:</p> <blockquote> <p>$mr = avg(query($property, 'mercaptans', $dates['mostRec']), $property);</p> </blockquote> <p>Where query and avg are defined as:</p> <pre><code>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) &gt;= 1) { $query .= " WHERE ("; $iter=0; while($iter &lt; count($sampleIDs)-1){ $query .= "(SampleID=" . &gt;mysql_real_escape_string($sampleIDs[$iter]) . ") OR "; $iter++; } $query .= "(SampleID=" . &gt;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); } </code></pre> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload