Posted by Joseph Melnick on 06/02/05 23:37

Hello Brian,

NC has provided a solution along the lines of what I was trying to convey
earlier.

If you are still stumped. Email me directly. I can explain NC's response.

Joseph Melnick
JM Web Consultants
http://www.jphp.com



"Brian" <not@given.com> wrote in message
news:BMFne.5120$iy2.453@newsfe1-gui.ntli.net...
> Hi Joseph
>
> Thanks for the reply, I have to admit I'm a little lost on what you have
> said, I have had a look round the net to try and understand it more,
> but now I'm ever more lost.
>
> The table is as follows
>
> id INT AUTO_INCREMENT PRIMARY KEY,
> date varchar(50),
> orderno varchar(50),
> store varchar(100),
> storename varchar(100),
> dnote varchar(50),
> status varchar(20),
> product varchar(200),
> description varchar(200),
> ord varchar(50),
> del varchar(50),
> brand varchar(50),
> timestamp varchar(20)
>
>
> what I need to do is say select from the table all record where timestamp
> is between X and Y, and
> return 4 variables added up
>
> $notrecognisedcount = total up 'ord' where status equals 'n'
> $discontinuedcount = total up 'ord' where status equals 'u'
> $validorderscount = total up 'ord' where status not equal 'n' or 'u'
> $delivered = total up 'del' where status not equal 'n' or
> 'u'
>
>
> Does that make sense?
>
> Brian
>
>
>
>
>
>
>
>
>
> "Joseph Melnick" <jmelnick@jphp.com> wrote in message
> news:-MmdneQo94opMgHfRVn-sg@rogers.com...
>> Hello Brian,
>>
>> If you want to add the values in a number of columns:
>>
>> create a query like this
>> $ch == 'n'
>> $ch == 'u'
>> ($ch != 'n') && ($ch != 'u')
>>
>>
>> select sum(IF(ch='n',1,0)) notrecognisedcount,
>> sum(IF(ch='u',1,0)) discontinuedcount,
>> (count(1)-sum(IF(ch='n',1,0))-sum(IF(ch='y',1,0))) validorderscount
>> from table
>>
>> This type of query may give you what you are looking for directly without
>> having to loop through
>> each record and go though lots of external logic to get your info from
>> the database.
>>
>> If I understood the tables that you are working with then it would be an
>> easy matter of using writing a better query.
>>
>> I hope that this helps.
>>
>> Joseph Melnick
>> JM Web Consultants
>> http://www.jphp.com
>>
>>
>>
>>
>> Joseph Melnick
>>
>>
>> "Brian" <not@given.com> wrote in message
>> news:j7_me.2891$cN2.920@newsfe4-gui.ntli.net...
>>> Hi all
>>>
>>>
>>>
>>> I'm running a query in a PHP script. At the moment I have having to run
>>> a while ($myrow = mysql_fetch_array($result)) {...
>>>
>>>
>>>
>>> As it loops though is has to add up a number of columns, is there a
>>> quicker sql statement then doing this the following
>>>
>>>
>>>
>>> if ($ch == 'n') {
>>>
>>> $notrecognisedcount = $myrow[ord];
>>>
>>> }
>>>
>>> if ($ch == 'u') {
>>>
>>> $discontinuedcount = $myrow[ord];
>>>
>>> }
>>>
>>> if ( ($ch != 'n') && ($ch != 'u') ) {
>>>
>>> $validorderscount = $myrow[ord];
>>>
>>> $delivered = $myrow[del];
>>>
>>> }
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Brian
>>>
>>>
>>>
>>>
>>
>>
>
>

[Back to original message]