List:General Discussion« Previous MessageNext Message »
From:Robert L Ramsey Date:December 20 2010 5:43pm
Subject:odd problem with select as statement
View as plain text  
I am having the hardest time getting a select as statement right.  Here is the full query:

SUM(IF(image='EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A317E5FA9899C48C19,
SUM(IF(image='235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7CEBF56FBDA2BF7815,
SUM(IF(image='96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7FE789A18E09BC5889,
SUM(IF(image='D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408391132F451AE724A,
SUM(IF(image='018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2BEB040D241739B784,
SUM(IF(image='98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6E0FEA25BAB0177FE,
SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13,
      <--offending line
SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B275605BD6B69F444700C
from dsrssfeed

If I remove that one line, the query works fine.  If I do:

select  SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from dsrssfeed ;

it works.  But these fail:

select  SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as
4E5664736F400E8B482EA7AA67853D13 from dsrssfeed ;
select  SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from dsrssfeed ;

It can't be field name length, since even 4E5 fails, the field name can start with a
number since 4E succeeds.

Any ideas?

The goal is to see what arbitrary images have information associated with them.  The table
has two fields:  image is a UID that is the primary key, and caption which is a
varchar(255) that has information about the image.  Images are added and deleted from the
table as they are changed on a web page.  The UID is generated by a third party program
that I have to interface with and have no control over.  An array of image UIDs is sent to
the php script and the script needs to determine which UIDs are present in the table. 
Rather than make N number of individual queries as I iterate through the array, I iterate
through the array and build the query on the fly to make one query.  Then I iterate
through the array again and check the value in the field.  1 means the UID has an entry, 0
means it doesn't.  I thought doing 1 mysql call would be more efficient than lots of calls
as I iterate through the array.  But since there will probably never be more than 100
images in the table at any one time, it may not make any difference.  But now I'm just
curious as to why this is happening.



odd problem with select as statementRobert L Ramsey20 Dec
  • Re: odd problem with select as statementJohnny Withers20 Dec
  • Re: odd problem with select as statementHank20 Dec
    • Re: odd problem with select as statementHank20 Dec
      • RE: odd problem with select as statementRobert L Ramsey20 Dec