PHP and MYsql Query problem

[recumbent615]

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;
}

[Lars_Berg]

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);

[recumbent615]

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

[Lars_Berg]

strtotime is a PHP function not a mysql function. Use UNIX_TIMESTAMP instead.

Read
http://www.mysql.com/doc/en/Date_and_time_functions.html

[recumbent615]

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)

[recumbent615]

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