2. PHPRunner General questions
  3. Select query to include specific date.
This topic is locked

Select query to include specific date.

 		10/28/2006 8:30:15 AM
PHPRunner General questions
M
 mmponline author

I use the following in the Before Add event to derive specific fields from a table. Works fine! I also need to add a where statement to show only specific dates from the closing date field. Will probably need an and >now() or something. Can't get it right. Tried everything. Please help!!
function BeforeAdd(&$values)

{

global $conn;

//select from Opportunities table
//$str = "select * from Opportunities where RefNo='".$values["RefNo"]."'";

$rs = db_query($str,$conn);

$data = db_fetch_array($rs);

$values["EmplID"] = $data["EmplID"];

$values["RefNo"] = $data["RefNo"];

$values["OppDivision"] = $data["OppDivision"];

$values["OppPosition"] = $data["OppPosition"];

$values["OppQuailification"] = $data["OppQuailification"];

$values["OppKeyRespons"] = $data["OppKeyRespons"];

$values["OppCompSkills"] = $data["OppCompSkills"];

$values["OppSalary"] = $data["OppSalary"];

$values["OppArea"] = $data["OppArea"];

$values["OppBenefits"] = $data["OppBenefits"];

$values["ClosingDate"] = $data["ClosingDate"];
return true;

}
Alexey admin 10/30/2006

Stephan,
give me a detailed description of what do you want to achieve and I'll try to help you.
M
 		mmponline author 10/31/2006

Alexey
I use this event on the add page before add to obtain the record from the Opportunities table and insert it on the OppSelect table to enable the user to pick a records from the Opportunities table and it's then inserted in the OppSelect table. The process works fine.
However, at the moment it shows all records on the add page with reference no's. (I use a lookup wizard selecting the RefNo from the Opprtunities table)
What I need, is an extra WHERE statement to filter the data so that it shows only refnos that has a date of dtday and future. IOW

  1. Show RefNo
  2. Look at only RefNo with dates (in the ClosingDate field) that is today and future dates. IOW not expired refno's
    My sql statement for this works fine on a different project - but in this event I can get it right.
    The sql statement:

    from...

    where (RefNo is not null and RefNo<>'') and

    `ClosingDate` >now()
    I see the Lookup wizard has a where statement available as well. Maybe in there, but my tries doesn't work as well.
Alexey admin 10/31/2006

Stephan,
I'm not sure where dtday field comes from.

You can filter records displayed in Lookup Wizard box using WHERE box in Lookup wizard settings dialog.

Here is the sample code:

"`ClosingDate` >now()"



Don't forget about the quotes.
M
 		mmponline author 10/31/2006

Thanks. This works exactly as I want it.
Question: If it must only include from tomorrow? Tried "`ClosingDate` >now() +1" but the query is then ignored completely.
J
 		Jane 10/31/2006

Try to use DATE_ADD MySQL function.

Here is a sample:

"`ClosingDate` >date_add(now(),INTERVAL 1 DAY)"
M
 		mmponline author 10/31/2006

This works fine on the add page, thanks Jane!
Now, on the list (view) page I currently use the following code:
if ($value<now())

{

$value = "<font color=red>".$value."</font>";

}
It obviously displays today as expired. Is there a way to edit this: now(),INTERVAL 1 DAY) code to display today as active as well?
T
 		thesofa 10/31/2006

This works fine on the add page, thanks Jane!

Now, on the list (view) page I currently use the following code:
if ($value<now())

{

$value = "<font color=red>".$value."</font>";

}
It obviously displays today as expired. Is there a way to edit this: now(),INTERVAL 1 DAY) code to display today as active as well?



Could you use

If (!$value>=now())
?i.e., if the value is NOT greater than or equal to NOW()??

would that do it?
or should it be

If($value!>=NOW())
?
M
 		mmponline author 11/1/2006

Nope
Your first try

Could you use

If (!$value>=now())


returns everything in green, irrespective of the date
and the second:

or should it be

If($value!>=NOW())



a parse error.
Further discovery:
In the sql query, the following statement returns today, yesterday, and older:

`ClosingDate`<=NOW()

But

`ClosingDate`>=NOW() returns only tomorrow and further - not including today
Wierd???????
T
 		thesofa 11/1/2006

I had similar problems, remember NOW() includes the time as well, if it is saved to a date field, the time part of the value is set to 00.00.00, hence any expression with now() in it will be bigger than a field with today's date in it.

It tripped me up mant times till I realised what was happening.
Sorry if my syntax was wrong, it was the idea that I was trying to put across, rather than the exact syntax!

Thinking about it, the first would evaluate as green because of the ! in the expression.

Perhaps we should try the ! on the right hand side of the equation?

dunno, it was more of a tickle in the back of the mind from a snippet I can no longer find.
M
 		mmponline author 11/1/2006

Let's see if anyone has a good suggestion.
HOPE SO...
Alexey admin 11/1/2006

Stephan,
$value and now() function contain a character representation of the date in YYYY-MM-DD ... format.

You can not subtract numbers from them.
To manipulate with these values you need to convert them to PHP time format.
Here is the sample code.

// split $value to array

$arr = db2time($value);

// construct time

$t = mktime($arr[3],$arr[4],$arr[5],$arr[1],$arr[2],$arr[0]);

// compare time with current time subtracted by 2 days

if($t > time())

$value = "<font color=red>".$value."</font>";



See the PHP manual for mktime and time functions for more info
M
 		mmponline author 11/1/2006

Nope, this code gives the following PHP Error
Error type 8

Error description Undefined offset: 3

URL govadress.co.za/govadadmin/Opportunities_list.php?

Error file /usr/www/users/govade/govadadmin/include/commonfunctions.php

Error line 6214

SQL query select `OppID`, `GovAppID`, `EmplID`, `RefNo`, `OppDivision`, `OppPosition`, `OppQuailification`, `OppKeyRespons`, `OppCompSkills`, `OppSalary`, `OppArea`, `OppBenefits`, `ClosingDate`, `NumberOfEdits` From `Opportunities` ORDER BY `ClosingDate` ASC, `RefNo` ASC, `GovAppID` ASC limit 0,20
Alexey admin 11/1/2006

Stephan,
looks like you have NULL values in your field.

Add some checking,i.e.

if($value)

{

// split $value to array

$arr = db2time($value);

// construct time

$t = mktime($arr[3],$arr[4],$arr[5],$arr[1],$arr[2],$arr[0]);

// compare time with current time subtracted by 2 days

if($t < time()-26060*24)

$value = "<font color=red>".$value."</font>";

}
M
 		mmponline author 11/1/2006

Empty field was one of the problems.
The other being that the view as for the table was blank in stead of date. When one changes the custom code, you need to click on date first, otherwise PHPRunner sets it to blank and then the code doesn't work.
It works fine now. It inserts the tima as well as 00:00:00 but that's fine.
Thanks for all the effort to help me.
Stephan