[SOLVED] Syntax error?

[Skipjackrick]

I can't figure this one out?  Does anything scream out at you guys?

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(IF(species_id=1,1,0)) AS speciesA, SUM(IF(angler=1' at line 3

 

 

$query_speciestotals = "SELECT
                         angler, species_id, yyyy
                         SUM(IF(species_id=1,1,0)) AS speciesA,
                         SUM(IF(angler=1,1,0)) AS species_total
                         FROM submit
                         WHERE angler=1
                         GROUP BY species_id AND yyyy
                         ORDER BY yyyy";

$speciestotals = mysql_query($query_speciestotals) or die(mysql_error());

while($row = mysql_fetch_array($speciestotals))
{
$yyyy = $row['yyyy'];
$speciesA = $row['speciesA'];
$species_total = $row['species_total'];

$species_totals_row .=<<<EOD
<tr>
	<td align='center'>$yyyy</td>
	<td align='center'>$speciesA</td>
	<td align='center'>$species_total</td>
</tr>

EOD;
}

[Skipjackrick]

I know this is against the rules to put a post to the top but i can't edit the post.

 

i forgot to add that the above code is inside a switch statement.  i think that's part of it.

 

$queryspecies = "SELECT 
         *
         FROM submit
         WHERE angler=$angler_id
         GROUP BY species_id";
$resultspecies = mysql_query($queryspecies) or die(mysql_error());

// get count of how many rows
$rowCount = mysql_num_rows($resultspecies);

// let's get the results into an array
while($row = mysql_fetch_assoc($resultspecies))
   $results[] = $row;

switch($rowCount) {
   case 1:
$query_speciestotals = "SELECT
                         angler, species_id, yyyy
                         SUM(IF(species_id=1,1,0)) AS speciesA,
                         SUM(IF(angler=1,1,0)) AS species_total
                         FROM submit
                         WHERE angler=1
                         GROUP BY species_id AND yyyy
                         ORDER BY yyyy";

$speciestotals = mysql_query($query_speciestotals) or die(mysql_error());

while($row = mysql_fetch_array($speciestotals))
{



$yyyy = $row['yyyy'];



$speciesA = $row['speciesA'];



$species_total = $row['species_total'];



$species_totals_row .=<<<EOD



<tr>





<td align='center'>$yyyy</td>





<td align='center'>$speciesA</td>





<td align='center'>$species_total</td>



</tr>




EOD;
}
more code past this irrelevant

[keeps21]

Oops - misread your question, sorry.

 

ignore the below.

 

You could try adding '{ }' around $angler_id in your sql

 

$queryspecies = "SELECT 
         *
         FROM submit
         WHERE angler='{$angler_id}'
         GROUP BY species_id";

[Skipjackrick]

Oops - misread your question, sorry.

 

ignore the below.

 

You could try adding '{ }' around $angler_id in your sql

 

$queryspecies = "SELECT 
         *
         FROM submit
         WHERE angler='{$angler_id}'
         GROUP BY species_id";

 

Nah, its not that...

 

It has something to do with the switch statement I think.

 

switch($rowCount) {
   case 1:
$query_speciestotals = "SELECT
                         angler, species_id, yyyy
                         SUM(IF(species_id=1,1,0)) AS speciesA,
                         SUM(IF(angler=1,1,0)) AS species_total
                         FROM submit
                         WHERE angler=1
                         GROUP BY species_id AND yyyy
                         ORDER BY yyyy";

$speciestotals = mysql_query($query_speciestotals) or die(mysql_error());

[PFMaBiSmAd]

There is no comma after the yyyy to separate it from the next item in the SELECT -

 

angler, species_id, yyyy,

[Skipjackrick]

There is no comma after the yyyy to separate it from the next item in the SELECT -

 

angler, species_id, yyyy,

 

AH DARN!!!

 

THANK YOU SO MUCH!!  WORKS PERFECT!

 

That's not the first time a comma has got me before...

[fenway]

If you put the commas IN FRONT of your columns, this won't ever happen again.

[Skipjackrick]

If you put the commas IN FRONT of your columns, this won't ever happen again.

 

That's a good idea!

 

Do you know of a good site for formatting code?

[fenway]

Not sure exactly what you mean by that... since you're writing it, why not write it properly at the get-go? But this tool is handy when reviewing old/other ppl's code.