What the hell is wrong with this query??

Adam

SELECT SUM(hours_spent) FROM tbl_hours INNER JOIN tbl_month WHERE (hours_date_logged BETWEEN 2006-03-01 AND 2006-03-31)

That's the php query in question, it doesn't return any data though. It has no errors, its definitely running fine, I ran it in the sqlyog query window. There is data in the hours_spent field, under dates between the dates specified in the query, and yet it returns null??

jmcc

fade_to_grey wrote:

SELECT SUM(hours_spent) FROM tbl_hours INNER JOIN tbl_month WHERE (hours_date_logged BETWEEN 2006-03-01 AND 2006-03-31)

My knowledge of inner joins is a bit rusty but shouldn't the syntax be inner join $table ON ?

Regards...jmcc

Adam

Nope, that gives a syntax error message. The query seems to be fine, it runs perfectly. The whole syntax section looks like this:

$SQL ="SELECT SUM(hours_spent) AS total_hours_spent FROM tbl_hours INNER JOIN tbl_month ON month_id='{$v}' WHERE hours_date_logged>{$row} AND hours_date_logged<{$row}";
debug($SQL);
$result = mysql_query($SQL) or die("Query failed : " . mysql_error());
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
print("<td> ".$row."</td>");

The table tbl_month contains the months of the year and the start and end dates of each month. So the SELECT query selects the values from the field hours_spent in the table tbl_hours where the date the hours was logged falls between the dates of the month. But it gives me nothing...

cyberbob

ive never seen constants used in the ON part like that before , wouldnt it be simpler to go select sum (hours_spent) as total from tbl_hours where month(hours_date_logged) = $v

is month() available in mysql ? i imagine it is

jmcc

The schema on the dates and hours fields could be useful. How have you specified the dates? Is month_id present in both tables?

Also for those limits, it should be => and =< rather than > and <. Otherwise you are losing the start day and end day of the month.

There is also the EXTRACT function in Mysql that you can use to extract the month (or year or day etc) out of a date. This might be a lot simpler than what you are trying to do.

SELECT EXTRACT(MONTH from Datelogged) from $table. It is there in the MySQL manual.

If you've used UNIXTIME for the timestamps you can nest the functions EXTRACT and FROM_UNIXTIME to get the month:

SELECT EXTRACT(MONTH from FROM_UNIXTIME(datestamp))

Using this you could potentially rewrite it as a simple query on a single table if you've got the dates/timestamps in an easy to handle format.

Regards...jmcc

akari no ryu

fade_to_grey wrote:

SELECT SUM(hours_spent) FROM tbl_hours INNER JOIN tbl_month WHERE (hours_date_logged BETWEEN 2006-03-01 AND 2006-03-31)

That's the php query in question, it doesn't return any data though. It has no errors, its definitely running fine, I ran it in the sqlyog query window. There is data in the hours_spent field, under dates between the dates specified in the query, and yet it returns null??

What data is it supposed to be returning?
What's the relation between month and hours?
Isn't INNER JOIN supposed to take an ON?

SELECT SUM(h.hours_spent) 
FROM tbl_hours h INNER JOIN tbl_month m
ON h.id=m.id
WHERE m.hours_date_logged BETWEEN 2006-03-01 AND 2006-03-31

bonkey

fade_to_grey wrote:

t has no errors, its definitely running fine,

With respect...if its not returning data that you believe it should, then it is definitely not running fine, its just running. Similarly, there are errors, just not errors that prevent it from executing.

Nope, that gives a syntax error message. The query seems to be fine, it runs perfectly. The whole syntax section looks like this:

But that section does have an ON statement. So is this an example of the query that doesn't run due to a syntax error, or an example of the query which does run but doesn't return data? And again, by "perfectly" I assume you mean "without generating a compile-error, but its still wrong".

In either which case, I'm not entirely sure why you're joining to the month table at all. It isn't used in the SELECT or in the WHERE clause. At a guess, its also invalid to have a record in tbl_hours that *doesn't* have a matching record in tbl_months, so the inner join isn't giving you any filtering in that respect either.

The only reason I can see is where you'd want to say "Give me all hours worked in January", and have the query figure the start- and end- dates from just the month-name / month-number.

Personally, I think a joining table is overkill from this when its so easy to calculate (given a month, the start-date is the first of that month, and the end date is one day before the first of the subsequent month) in either SQL or before you send the query to the server...

But, if thats what you're doing, then thats what you're doing. What you'll want is probably something like the following. Without a table-schema (or more information) I'm guessing at fields and the overall structure here, but it might help you out....

SELECT     SUM(h.hours_spent) 
FROM       tbl_hours h
INNER JOIN tbl_month m
  ON h.hours_date_logged >= m.month_start_date
  AND h.hours_date_logged <= m.month_end_date
WHERE m.month_id = <some user-supplied-value)

Hope this helps...

jc

Zaph0d

Does the month table store calendar months or accounting months?

Adam

Cheers for the replies guys. I'm using INNER JOIN tbl_month to set the dates between which the SELECT statement should return data.

Ok, some schema here for you.

Table tbl_hours contains hours_spent, which is entered from a form. That same form also has a date field called hours_date_logged.

Table tbl_month contains the month names Jan to Dec and the fields month_from and month_to. For example the first row looks like: month_id = 1, month_name = January, month_from = 2006-01-01, month_to = 2006-01-31

So i have a page that SELECTS * FROM tbl_month and prints the month name, and when you click on the month you want, it carries a variable id to the next page where the query in qestion is contained. That variable sets the start and end dates of the month and should select the hours worked between those dates. Am I making more sense?

jmcc

fade_to_grey wrote:

Ok, some schema here for you.

Not exactly schema. Is are the fields month_from and month_to defined as date or varchar?

You could also use the EXTRACT function to pull the month out of hours_date_logged.

Regards...jmcc

Adam

All date fields are defined as date

jmcc

fade_to_grey wrote:

All date fields are defined as date

Then the EXTRACT function should enable you to simplify the process. The second table is not really necessary. Use PHP to change the integer value of the month (pulled from the date string with EXTRACT) to the name of the month. SQL has its limits as a programming language but these limits are not shared by PHP.

Regards...jmcc

Adam

Could you give me an example of how to do this as I've only been using php for a couple of months and haven't come across the extract funtion, cheers!

Adam

Just as a point of interest, if I even try to run this query:

SELECT SUM(hours_spent) FROM tbl_hours WHERE hours_date_logged >= 2006-01-01 AND hours_date_logged <= 2006-12-31

in the sqlyog query window it returns null...

jmcc

fade_to_grey wrote:

Could you give me an example of how to do this as I've only been using php for a couple of months and haven't come across the extract funtion, cheers!

The EXTRACT function is a MySQL function to pull the stuff out of date fields. It should return the month number when you do something like EXTRACT(MONTH FROM $datefield) FROM $tablename

It seems that you might have the necessary data in the first table to do the query without having to resort to a second table. A simple case statement in PHP could turn the month number in to the month name.

This is the MySQL manual section on date/time functions:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

It even has a MONTHNAME feature.

Regards...jmcc

Zaph0d

Just as a point of interest, if I even try to run this query:

SELECT SUM(hours_spent) FROM tbl_hours WHERE hours_date_logged >= 2006-01-01 AND hours_date_logged <= 2006-12-31

in the sqlyog query window it returns null...

Try putting the dates in single quotes.

mysql has MONTH and MONTHNAME functions that return the indexed month or the month name from a date so you can just run something like:

SELECT SUM(hours_spent) FROM tbl_hours 
WHERE MONTHNAME(hours_date_logged) = 'January'

Always test your sql in a query window before dropping it into code. Better still, write stored procedures if your mysql version accepts them.

jmcc

fade_to_grey wrote:

SELECT SUM(hours_spent) FROM tbl_hours WHERE hours_date_logged >= 2006-01-01 AND hours_date_logged <= 2006-12-31

SELECT SUM(hours_spent) FROM tbl_hours WHERE EXTRACT(MONTH FROM hours_date_logged)=3;

or

SELECT SUM(hours_spent) FROM tbl_hours WHERE EXTRACT(YEAR FROM hours_date_logged)=2006;


If the date is stored in UNIXTIME format then you have to convert the date from UNIXTIME first.

SELECT SUM(hours_spent) FROM tbl_hours WHERE EXTRACT(MONTH FROM FROM_UNIXTIME(hours_date_logged))=3;


I am a bit wasted with flu at the moment so I am not sure if these will even work.

Regards...jmcc

Adam

Zaph0d wrote:

Try putting the dates in single quotes.

mysql has MONTH and MONTHNAME functions that return the indexed month or the month name from a date so you can just run something like:

SELECT SUM(hours_spent) FROM tbl_hours 
WHERE MONTHNAME(hours_date_logged) = 'January'

Always test your sql in a query window before dropping it into code. Better still, write stored procedures if your mysql version accepts them.

On the button there, cheers Zaph0d!

democrates

Tip - On complex queries I often run a select without the SUM() / Group By parts first, make sure I've got the exact records expected, then copy and paste the results into a spreadsheet and total them there to compare with my final sql calculations.

It's not that I love testing, or ever make a mistake, I have to do it because the CIA hack in and change my code to try and stop me making progress. I may be wrong, could be the Illuminati.
:mad: