• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

RECORD 1 DATA IN 2 TABLES DIRECTLY

Good Morning.
would need the data in a "gp_hab_estathabitatge" column of table "HAB" to be identical to the data in a "gp_exp_estathabitatge" column of the "EXP" table.
example:
when I'm in the "EXP" form and record "gp_exp_estathabitatge"
I would like the value of this record to be recorded directly to the "gp_hab_estathabitatge" record of the "HAB" table.
Is it possible to do it in an automated way ??
or would it have to do it using PHP code ... in PHP Validations element ??
to see if any interesting idea comes up ...
If you need any more data ... tell me ..!
thank you.
alberto
 
Or possibly a Join and a calc element if that makes sense.

But definitely not a php validation which would run before the save.
 
good again,
the plugin 'upsert' seems very powerful.
I have tried several configurations ... and I can not "modify" the value.
What I observe is that it "inserts" a new row.
What I would like is that when inserting a data in the "EXP" form, automatically modify the "HAB" form.
Attached image, where it looks, how the result is: "insert new row"
I hope I have explained myself better
thanks

upload_2018-2-21_16-31-15.png
 
If you want to update an existing record in the second table, this implies that there is something that links it to the first table, like a userid.

So another way to do it is to join the two tables on this common column and then use a hidden calc element to store it in the database.
 
If you want to update an existing record in the second table, this implies that there is something that links it to the first table, like a userid.

So another way to do it is to join the two tables on this common column and then use a hidden calc element to store it in the database.
good
I'm going to look if I find an example in the wiki about "union of tables"
and the calc element hidden.
thanks
 
To join the second table, goto the List settings for the first table / Data / Joins and add a join there.

This will create additional elements automatically - unpublish / trash&delete the ones you don't want and convert the field you want to copy to a calc field and do something like "return '{placeholder}';" as the calc php.
 
good...
This option that you mention to me, can be interesting ... I will try ...
for the moment I created this simple php plugin ... and it seems to work ...
I'll pass it to you, in case it might interest someone ...
It's simple ... but it works.
thank you for your attention!!

$ query = 'SELECT * FROM gpexpedientss order by id DESC LIMIT 1';
$ result = mysqli_query ($ conn, $ query);
while ($ fila = $ resultado-> fetch_array ())
$gp_hab_exp = $ row ['gp_exp_hab'];
$ sql = "UPDATE gphabitatge SET gp_hab_estathabitatge = 'llogat' WHERE gp_hab_estathabitatge = 'disponible' AND gp_hab_id = $ gp_hab_exp";
mysqli_query ($ conn, $ sql);
 
Yes - but although this works there are lots of reasons to do it differently.

1. You are issuing one SQL update statement for every row in the expedients table - as this table grows, this code will get slower and slower and sslloowweerr and ssslllooowwweeerrr and sssslllloooowwwweeeerrrr..... You could do it with a single UPDATE that copies the values of all rows, but again that will be a lot more work for the database than just updating the single record that you have changed.

(Note: If you have a List where a value is a calc field that e.g. analyses rows in another table, and you don't want Fabrik to calculate the values 1 by 1 like this, then if you don't mind coding the same calc algorithm again as pure SQL, then doing an update of all rows in one update statement in the PHP Events List plugin can be a very good way to refresh the list values before the List gets the data from the database.)

2. If you want to do it using your own SQL, then you should try to use the Joomla SQL routines - because for example they will give you debugging information if you ever need it. See "Selecting data using JDatabase" and "Inserting, Updating and Removing data using JDatabase".

3. If you still want to do it using php, then in the onAfterProcess part of the PHP form plugin (click here for the wiki page) you can update the single row of the second table with something like
Code:
UPDATE gphabitatge SET gp_hab_estathabitatge = 'llogat' WHERE gp_hab_estathabitatge = 'disponible' AND gp_hab_id = '{gpexpedientss__gp_exp_hab}';

Hope this helps.
 
Last edited:
Hello...
now doubts arise ... to see if you can help me.

1) I am issuing a SQL update statement from only the last row ... from the file table ... I understand that even if it grows, I should take only the last row ... is that so ...? or there is something that escapes me ... I thought that by putting "order by id DESC LIMIT 1 '", I directly took the first record (starting at the end of the table)
I'm interested in taking the value of the last row ...

2) I will consult Jtdatabase what you tell me ... thanks

3) About 3)If you still want to do it using php, then in the onAfterProcess part of the PHP form plugin (click here for the wiki page) you can update the single row of the second table with something like.

in section 3) that you mention to me ... I have a doubt.
I do not have to put all this code ...?

$servername = "localhost";
$username = "usernamer";
$password = "passowordX";
$dbname = "bddatos";
$conn = mysqli_connect($servername, $username, $password, $dbname);
$ query = 'SELECT * FROM gpexpedientss order by id DESC LIMIT 1';
$ result = mysqli_query ($ conn, $ query);
while ($ fila = $ resultado-> fetch_array ())
$ gp_hab_exp = $ fila ['gp_exp_hab'];
$ sql = "UPDATE gphabitatge SET gp_hab_estathabitatge = 'llogat' WHERE gp_hab_estathabitatge = 'disponible' AND gp_hab_id = $ gp_hab_exp";
mysqli_query ($ conn, $ sql);

if I write only one line how do you tell me ..., without specifying database ... user, password the while, and all the code that I wrote before ...
Would it work ???

I have tried this line with you alone that you tell me and it does not work ...
UPDATE gphabitatge SET gp_hab_estathabitatge = 'llogat' WHERE gp_hab_estathabitatge = 'available' AND gp_hab_id = '{gpexpedientss__gp_exp_hab}';
I've also tried a simpler update ... UPDATE gphabitatge SET gp_hab_estathabitatge = 'llogat' WHERE gp_hab_estathabitatge = 'available' and it does not work either ...

I appreciate your comment very much ... since I am very new and I would not like to do something incorrectly.
Every day that passes I like the world of keys and fabrik more.
Thanks again.
 
1. Sorry - you are right - it returns only the row with the highest id - and assuming that id is autoincrement then that is extremely likely to be the last row inserted (though it is possible to insert rows with any id you wish so it is not guaranteed). But if your form has been updating the gpexpedientss table (rather than insertuing into it), then the row you want won't necessarily be the last row in the table.

2. As I said before, you really do not want to make your own connections to the database server rather than using the Joomla standard connection (or a Fabrik one) - and your code shows another reason why, because you have to put the userid and password in every piece of php code you write - so if you ever need to change those you will have a lot of places to visit to change it.

3. If you want to combine the SELECT and UPDATE into one SQL statement, then it would be something like:
Code:
UPDATE `gphabitatge` SET `gp_hab_estathabitatge` = 'llogat' WHERE `gp_hab_estathabitatge` = 'disponible' AND `gp_hab_id` = (SELECT `gp_exp_hab` FROM `gpexpedientss` ORDER BY `id` DESC LIMIT 1);

You will have to follow the examples from the Joomla site to work out how to do JDatabaseDriver calls. But it is not difficult and would be something like:
Code:
$db = JFactory::getDbo();
$db->setQuery("UPDATE `gphabitatge` SET `gp_hab_estathabitatge` = 'llogat' WHERE `gp_hab_estathabitatge` = 'disponible' AND `gp_hab_id` = (SELECT `gp_exp_hab` FROM `gpexpedientss` ORDER BY `id` DESC LIMIT 1);");
$db->execute();
which if you really wanted to put on one line could be written:
Code:
JFactory::getDbo()->setQuery("UPDATE `gphabitatge` SET `gp_hab_estathabitatge` = 'llogat' WHERE `gp_hab_estathabitatge` = 'disponible' AND `gp_hab_id` = (SELECT `gp_exp_hab` FROM `gpexpedientss` ORDER BY `id` DESC LIMIT 1);")->execute();
 
P.S. I am writing this code as untested examples - I don't promise it will work because it is designed as an example for you to use as a starting point.
 
what good ... your comments.
tomorrow 05:00 I will start to test your comments.
I feel very grateful and accompanied
thank you and good night
 
good...
There is a possibility that with these joomla sentences, you can refer to a database other than the internal joomla ...
I have 2 databases ...
bdjoomla and bdfabrik
it's possible??
if it were possible ... it would be spectacular ... !!

$db = JFactory::getDbo();
$db->setQuery("UPDATE `gphabitatge` SET `gp_hab_estathabitatge` = 'llogat' WHERE `gp_hab_estathabitatge` = 'disponible' AND `gp_hab_id` = (SELECT `gp_exp_hab` FROM `gpexpedientss` ORDER BY `id` DESC LIMIT 1);");
$db->execute();


I guess I get the error, because you are looking for the joomla database.
Table 'bdgjoomla.gphabitatge' does not exist
THANKS
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top