2. PHPRunner General questions
  3. [SOLVED]  Date related queries
This topic is locked
[SOLVED]

 Date related queries

 		10/10/2006 4:26:05 PM
PHPRunner General questions
M
 mmponline author

I have a project where the date will determine the show (or not of records). There is probably some help in the PHP manuals, but appart from being lazy (I'm sorry), this forum seems to have someone that will have a ready to go answer.
My needs:

  1. How do I set a query that will activate a field (will show up on the list page) once a date is selected /entered. IOW, with no date - not active. Date entered - show on list page. (The admin user will thus have to activate it on a different query / project to set the date) It would problably have to be something like - is null / is not null - as used in Access.
  2. How do I set the query to make the entry inactive (remove from list) on a specific date in future or <today>. Eg. If date is set to 13/10/2006 - it will disappear on Friday. This will be very handy to remove a entry for eg. a concert or match once the last date is reached.
  3. This is the fancy one - To grey out or show in another colour once expiry date is reached. Or even more fancy - turns to another colour with say 3 days to go, or 2 days to go.
    Thanks to the gurus out there with the always ready answers.
    PHPRunner 3.1 is Great!
J
 		Jane 10/13/2006

Stephan,
please see my answers below:
1,2. edit your SQL query on the Edit SQL query tab.

Here are the samples:

select `FieldName` from `TableName` where `DateField` is not NULL

select `Fieldname` from `TableName` where `DateField`>now()+1


3. use custom View as format for this purpose.

Here is a sample:

if ($value<now()-2)

{

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

}
M
 		mmponline author 10/18/2006

QUOTE

if ($value<now()-2)

{

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

}


This seems not to work. It's either red (when if is set to ($value>now()-2) or black if left as is. If the date is only two days or 10 days away, it stays black or red, instead of the one being red (date within 2 days away) and future dates being black.
J
 		Jane 10/19/2006

Stephan,
try to use your condition in the if clause.

Here is a sample (no guarantee it works):

global $conn;

$rs = db_query("select datediff(now(), '". $value. "')",$conn);

$data = db_fetch_numarray($rs);

if ($data[0]>2)

$value = "<font color=red>".$value."</font>";
M
 		mmponline author 10/19/2006

Jane
I get the following PHP error:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(now(), '2006-10-21')' at line 1
J
 		Jane 10/19/2006

Stephan,
this code works with MySQL 4.1.1 and newer.

I recommend you to update MySQL or write you code.

Here is a MySQL documentation:

http://dev.mysql.com/doc/
M
 		mmponline author 10/23/2006

Jane

I gave this a try again:

if ($value<now()-2)

{

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

}


When using the snippet without the -2 after now(), it works perfectly. I've made my color green in visual editor and when I display the fileds, the dates of today and past shows in red (overdue) - Good
Unfortunately, this includes today, that is not really expired. It's the same problem we had with the edit SQL Query `ClosingDate` >now() that also do not work with a -2 or +2 at the end. This also means today is seen as posponed and the field is removed on midnight 22 iso midnight 23.
At least the colors display partially correct now. Any more tries?
Alexey admin 10/23/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()-26060*24)

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



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

I get a parse error using this code.
Alexey admin 11/1/2006

Stephan,
sorry, here is the correct line in the code:

$t = mktime($arr[3],$arr[4],$arr[5],$arr[1],$arr[2],$arr[0]);
M
 		mmponline author 11/1/2006

Nope
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>";

}