2. PHPRunner General questions
  3. [SOLVED]  Totals from another table with date condition
This topic is locked
[SOLVED]

 Totals from another table with date condition

 		4/5/2011 1:40:13 PM
PHPRunner General questions
F
 fantasmino author

Hi all

I have an event in before record add and edit that give me a total and work fine:

global $conn;

$str = "select totale_accessori from pql_accessori WHERE Compagnia='" . $values["Compagnia"] . "'and Container='" . $values["Container"] . "'and Zona='" . $values["Zona"] . "'";

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

if($data = db_fetch_array($rs))

$values["totacc"] = $data["totale_accessori"];

$values["Totale"] = $values["totacc"] + $values["Tariffa"];
But I have to put a date condition something like this:
global $conn;

$str = "select totale_accessori from pql_accessori WHERE pql_accessori.valido >=now() and pql_accessori.scadenza <=now() Compagnia='" . $values["Compagnia"] . "'and Container='" . $values["Container"] . "'and Zona='" . $values["Zona"] . "'";

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

if($data = db_fetch_array($rs))

$values["totacc"] = $data["totale_accessori"];

$values["Totale"] = $values["totacc"] + $values["Tariffa"];
and then move the event in list before display so the raws updates automaticly without using edit.
It's my first time posting, usualy I find the solution in other post, so thanks all for every past solutions.
Sergey Kornilov admin 4/6/2011

Not sure I understand the question.
If that something doesn't work?
F
 		fantasmino author 4/6/2011

I don't know how to set the date fields condition "see the text in red " . I have tried in different modes, but it didn't work.

I have two fields with expiry date "scadenza" that should be >=now() and valid from date "valido" that should be <=now() in table pql_accessory and I have to put them as one of the conditions before to make the totals in "pql" table event.

Then if it's possibile,I have to move the event in "before list" instead of "before record update" so the reocords should be updated automaticly.

I apologize for my english and hope that I have managed to explain the problem.
Thank you
Sergey Kornilov admin 4/6/2011

Your code look correct, you just miss and after the second now().
Still don't understand what you need to move and where.
F
 		fantasmino author 4/6/2011

Noooooooooo I can't believe I forgot "and". That make me feel so stupid <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=57479&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />).

I have try the code many time <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=57479&image=2&table=forumreplies' class='bbc_emoticon' alt=':)' /> .

now works fine in "before record update" and "before record add".

I have only to find a way to put the event in "before list" to have all the records always updated if you have a suggestion also for that otherwise can closed the topic as solved.

thanks a lot