2. PHPRunner General questions
  3. Searchable Calculated Value
This topic is locked

Searchable Calculated Value

 		4/17/2008 9:12:55 PM
PHPRunner General questions
E
 erago author

hi,
I have a table that Contains the following Fields, "begin" , "end", and "Duration"

I need to calculate the number of days based of of "begin" and "end" (end-begin=Duration)

and if there is no value for "end" then it needs to calculate the number of days from "begin" to today (now()-begin=Duration)
I have used the following in the "Edit SQL Query" , SQL menu:
SELECT

begin,

end,

end-begin AS Duration

From`TableName`
the above works if there is a "end" value and does not create an error, The Calculated Value for Duration must show up in a search from the web page.

what is the best way to do this?
J
 		Jane 4/18/2008

Hi,
use IF statement in your SQL query for this purpose:

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html
To add calculated field to the advanced search page check off corresponding checkboxes on the Choose fields tab.
E
 		erago author 4/18/2008

Need Proper Syntax, All IF Statements have been creating Errors. Have tried IF, IFELSE, ELSEIF, IF() All create errors.

Have been trying to get the IF() Function to work because it is supposed to result the NON NULL expression.

in the following query, where and how or change to what?
SELECT

`TableName ID`,

begin,

end,

end-begin

CURDATE()-begin

AS Duration

FROM `TableName`
E
 		erago author 4/20/2008

I found The Solution In the IF() ( IF NULL ) Statement
Proper Query is as Follows,
SELECT

`TableName ID`,

begin,

end,

IF(end IS NULL,CURDATE()-begin,end-begin)
AS Duration

FROM `TableName`
This Achieved the goal to search for a Calculated Value that is not a recorded value in a field, a Value that could be changing every day.
E
 		erago author 5/4/2008

I found The Solution In the IF() ( IF NULL ) Statement

Proper Query is as Follows,
SELECT

`TableName ID`,

begin,

end,

IF(end IS NULL,CURDATE()-begin,end-begin)
AS Duration

FROM `TableName`
This Achieved the goal to search for a Calculated Value that is not a recorded value in a field, a Value that could be changing every day.


I have discovered an error on the date calculation once the months being calculated are not the same. you must use the DATEDIFF() function, here is the working updated Code that fixes the problem:
SELECT

`TableName ID`,

begin,

end,

IF(end IS NULL, DATEDIFF(CURDATE(),begin), DATEDIFF(end,begin)) AS `Duration`

FROM `TableName`