How to create action using a multiple query

[grageot]

I have xataface last version.

My base includes 3 tables :
*'donateurs' (fields : id,... Vers_10,...).
*'transac10' (fields : ID_Dons10,...MontantDons10,...)
*'donat_dons' (fields : id, ID_Dons10) used in donateurs/relationship.php

Each donateurs(id) may have several records in 'transac10'(ID_Dons10)
I want to calculated the total of all MontantDons10 for each donateur(id) and update the field Vers_10 with this total.

I have tested in PhpMyAdmin with the following query, and it works well :

Code: Select all

CREATE OR REPLACE VIEW vue_donsindiv10
AS
SELECT `Nom`,
SUM(`MontantDon10`) AS TotalVerse10, dd.id
FROM `donateurs` AS d
INNER JOIN donat_dons AS dd ON d.id = dd.id
INNER JOIN transac10 AS t ON dd.ID_Dons10 = t.ID_Dons10
WHERE  t.`DateDon10`BETWEEN '2009-12-31' AND '2011-01-01'
GROUP BY d.id
ORDER BY TotalVerse10 DESC;

UPDATE donateurs d INNER JOIN vue_donsindiv10 AS v ON d.id=v.id
SET Vers_10= TotalVerse10
WHERE d.id = v.id;

I want to define an action to update the fields "Vers_10" in the table "donateurs" using the above query, but I hav'nt understand how to do it.
I am newbee with xataface and not able to programm in PHP.
Thank you for help

[shannah]

There are a couple of ways to tackle this problem.

1. If you really like the query, you could create a custom action that performs your query exactly using mysql_query() calls.
2. You could find an equivalent way of doing it using the Xataface standard forms (may or may not be possible).

-Steve

[grageot]

Hi Steve
I have a problem with my custom action.
In fact, I use a view table (vue_donindiv10 : id, totalVerse10) which is updaded automatically when i send a new record, thus I have the summation (totalverse10) of each donateurs (id)
I have created an action to update the fields (donateurs.Vers_10) : "maj_versements10"
in action.ini file :
---

Code: Select all

[maj_versements]
        label = "Maj Vers10"
        permission = list
        url = "{$this->url('-action=list')}"
        category = table_tabs
        order = 10
---

In actions/maj_versements10.php :

Code: Select all

<?php
class actions_maj_versements {
    function updateDonationsFor($donateurID){
        $res = mysql_query("UPDATE donateurs d INNER JOIN vue_donsindiv10 AS v ON d.id=v.id SET d.Vers_10= v.TotalVerse10 WHERE d.id = v.id", df_db());
        if ( !$res ) throw new Exception(mysql_error(df_db()));
     
}
}
?>

I have tested in PhpMyAdmin the query and it runs correctly.
I have the button : "Maj Vers10" in the tab section
but after action on the button, the list appears as define in action.ini but the update didn't work : the field '*Vers_10" is not updated.
Where is the bug ?
Best regards

[shannah]

Your action is missing a handle() method. The way actions work through their handle() method. Whatever happens in the handle() method, is what happens when your action is called.

In this case I'm surprised it didn't set of a flurry of errors because the handle() method is missing. At the very least, it doesn't know to run your updateDonationsFor() method unless you call it from the handle() method.

One other thing to note is that it is generally bad practice to update the database in a GET request. This is what POST requests are for.