Can anyone tell me what is wrong with this query? I have attempted several verisons of this but continue to get errors. I believe the problem is in the strtotime() call in the query due to the fact that I can get the query to work when I replace those calls with a real timestamp ...

I also need this to return an integer count of all "bugs" found that match the query, but I haven't even gotten this to return the query yet.

Here is is

function countbugs ($fromdate, $todate, $status) {

$fromdate and $todate are unix timestamps

$status is a String

bug_when is in the format of YYYY-mm-dd hh:mm:ss

#

    include("include/dbconnect.php");

    $result= mysql_query("select DISTINCT  bugs.bug_id from bugs_activity, bugs where (
   (bugs_activity.bug_id = bugs.bug_id)

AND
((bugs_activity.added='$status')
AND
(strtotime('bugs_activity.bug_when') >= '$fromdate')

AND
(strtotime('bugs_activity.bug_when') <= '$todate'))

OR
(strtotime('bugs.creation_ts') >= '$fromdate'

AND
strtotime('bugs.creation_ts') <= '$todate'
AND
('bugs.bug_status' = 'NEW')))
ORDER by
strtotime('bugs.creation_ts')"
,$db);

print "\n " . mysql_error() . "\n";
return $result;
}

    loose som ' around column names.

    $result= mysql_query("select DISTINCT bugs.bug_id from bugs_activity, bugs where (
    (bugs_activity.bug_id = bugs.bug_id)
    AND
    ((bugs_activity.added='$status')
    AND
    (strtotime(bugs_activity.bug_when) >= '$fromdate')
    AND
    (strtotime(bugs_activity.bug_when) <= '$todate'))
    OR
    (strtotime(bugs.creation_ts) >= '$fromdate'
    AND
    strtotime(bugs.creation_ts) <= '$todate'
    AND
    (bugs.bug_status = 'NEW')))
    ORDER by
    strtotime(bugs.creation_ts)"
    ,$db);

      I did as you suggested and still get the same error (below)

      You have an error in your SQL syntax near '(bugs_activity.bug_when) >= '1009774800') AND (strtotime(bugs_activit' at line 6

        Ok now that I'm using mysql commands and I've broken my query up to two separate queries. I still have problems.

        Here is the Query followed my the results:

        function countbugs ($fromdate, $todate, $status) {

        $fromdate and $todate are unix timestamps

        $status is a String (Currently set to "ASSIGNED" )

        bug_when is in the format of YYYY-mm-dd hh:mm:ss

        #

            include("include/bug_dbconnect.php");

        $result1 = mysql_query("select COUNT(*) from bugs_activity where

           bugs_activity.added='$status'

        AND
        unix_timestamp(bugs_activity.bug_when) >= '$fromdate'
        AND
        unix_timestamp(bugs_activity.bug_when) <= '$todate'" ,$db);

        $result2 = mysql_query("select COUNT(*) from bugs where

           unix_timestamp(bugs.creation_ts) >= '$fromdate'

        AND
        unix_timestamp(bugs.creation_ts) <= '$todate'
        AND
        bugs.bug_status = 'NEW' "
        ,$db);

        #$count1 = mysql_num_rows($result1);
        #$count2 = mysql_num_rows($result2);

        print "\n " . mysql_error($db) . " <br>";

        print "\n " . mysql_errno($db) . "\n";

        print "\n " . $count1 . " - " . $count2 . "\n";

        print "\n " . $result1 . " - " . $result2 . "\n";
        }

        ----------- Results Below ------------

        12 31, 2001 - 01 06, 2002 0 - 0 Resource id #38 - Resource id #39
        01 07, 2002 - 01 13, 2002 0 - 0 Resource id #40 - Resource id #41
        01 14, 2002 - 01 20, 2002 0 - 0 Resource id #42 - Resource id #43
        01 21, 2002 - 01 27, 2002 0 - 0 Resource id #44 - Resource id #45
        01 28, 2002 - 02 03, 2002 0 - 0 Resource id #46 - Resource id #47
        02 04, 2002 - 02 10, 2002 0 - 0 Resource id #48 - Resource id #49
        02 11, 2002 - 02 17, 2002 0 - 0 Resource id #50 - Resource id #51
        02 18, 2002 - 02 24, 2002 0 - 0 Resource id #52 - Resource id #53
        02 25, 2002 - 03 03, 2002 0 - 0 Resource id #54 - Resource id #55
        03 04, 2002 - 03 10, 2002 0 - 0 Resource id #56 - Resource id #57
        03 11, 2002 - 03 17, 2002 0 - 0 Resource id #58 - Resource id #59
        03 18, 2002 - 03 24, 2002 0 - 0 Resource id #60 - Resource id #61
        03 25, 2002 - 03 31, 2002 0 - 0 Resource id #62 - Resource id #63

        +++++++ Now for the interactive query and results ++++++

        mysql> select COUNT() from bugs_activity where bugs_activity.added="ASSIGNED" AND unix_timestamp(bugs_activity.bug_when) >= 1016427600 AND unix_timestamp(bugs_activity.bug_when) <= 1037595600 ;
        +----------+
        | COUNT(        ) |
        +----------+
        | 48 |
        +----------+
        1 row in set (0.01 sec)

          After checking on what i was passing to the function, I found that I had an error in the calling routine. I now get the results I expect.

          Thank you for all the help

          Kevin

            Write a Reply...