Update 4 different db tables with 2 common values

Hi Folks ! Merry Xmas & Happy New Year to all !

I've got a problem that needs an elegant solution. Project website is for Client who is not computer literate & as such needs a simple frontend solution to co-ordinating Joomla Content & Joomla Application 'Category' naming & updating.

Here's the overview : Site registration is handled by Community Builder. During reg. User chooses one of 10 'Categories' for membership from a dropdown - when Site is delivered it will have 10 dropdown options - 'Category #1, Category #2, .... etc.) - Site owner will need to rename the 10 default 'Categories' & 'Publish' as needed (Example - Category #1' - renamed to 'Fine Art') - There is no way Site Owner will be able to do this in Admin.

Also related to 'Category #1' are other site Apps - Membership App with 'Category #1', Classified advert app with 'Category #1' & Joomla Content 'Category #1' & so on.

SO - to keep 'Category Naming' in-sync between CB & the Apps, when changing / updating the name of the Dropdown Options in CB so the 'Names' of the associated 'Categories' in each of the Apps must be changed.

Each of the 10 main 'Categories' has 5 'Subcategories' - & I have created the same 'Category' structure in Joomla Content & all Apps - so I know each Apps 'Categories' db table names & ids etc.

Now the question is - What is the best way to update 4 different tables with two values (The new 'Category Name' = 'new_cat_name' & 'Published' = '0 / 1' ) when the table name / row is known for each ?

Will most likely use ChronoForms / ChronoConnect to generate a different form to update each individual 'Category' & 'Subcategory' (That's 60 forms !) - but then do I cook up some MySQL UPDATE Query or what ? & How does one string together multiple UPDATEs in either Chrono or MySQL ?

One 'Action' needs to do these four things :


UPDATE 'dbprefix_comprofiler_field_values' > 'fieldlabel'> where 'fieldvalueid' = 111 (known) > write 'fieldlabel' = 'new_cat_name' & *

UPDATE 'dbprefix_categories' > 'title' > where 'id' = 222 (Known) > write 'title' = 'new_cat_name' & 'published' = '1 / 0'

UPDATE 'dbprefix_djcf_categories' > 'name' > where 'id' = 333 (Known) > write 'name' = 'new_cat_name' & 'published' = '1 / 0'

UPDATE 'dbprefix_osmembership_categories' > 'title' > where 'id' = 444 (Known) > write 'title' = 'new_cat_name' & 'published' = '1 / 0'


(* In CB 'dbprefix_comprofiler_field_values' field options don't have a 'Pub / Un-Pub' switch - so the easiest way to make them dissapear / re-appear ( = 'Publish' / 'Un-publish') is to change the 'fieldid' value (The CB Field in which the option appears. That value appears in the same db row) to some other value - then the option wont display in the CB Reg dropdown !).

All ideas & thoughts appreciated !

Cheers - Paulzero

And in the words of the great Ozzie World Champ Boxer, the inimitable Jeff Fenech: “I luv youse all - I reallllly, realllly Dooooo !"

Hi Paulzero,

I'd do this with a Custom Code action with PHP+MySQL to do the DB Save; and I'd do my best to use only one form not 60.

You could use multiple DB Save's but if you are OK with writing a MySQL query you get better control using the Custom Code action

<?php
$db = \JFactory::getDBO();
$query = "
  UPDATE `dbprefix_comprofiler_field_values`
    SET `fieldlabel` = '{$form->data['cat_name']}'
    WHERE `fieldvalueid` = '111' ;
";
$db->setQuery($query);
$save = $db->execute();
// check the value of $save here if necessary
$query = "
  UPDATE `dbprefix_categories`
    SET
      `title` = '{$form->data['cat_name']}',
      `published` = $form->data['published']
    WHERE `id` = '222' ;
";
$db->setQuery($query);
$save = $db->execute();
. . .
?>

Bob

Hi Bob !

Thanks for info - sorry for delay in response - temperature here in Oz hit 42C yesterday - everybody in the country must have turned their air-con on at once - the load blew the grid - no power for most of the day !

Will have a go at this using CCV5 to generate an initial 'Master Category Names List' list, then use one ChronoForm to handle the 'Custom Code' nessesary to update Apps.

To do this I think I will create a new db table called 'dbprefix_updatecnames' & stuff all the 'comprofiler', 'com_content' etc info for each Category & Sub-Category into it :

New DB Table - 'dbprefix_updatecnames' - Which becomes the 'Master Category Names List'

id   cat     name          com_cb       com_con       com_djc       com_osm     State    cat_type

1   c-01     Fine Art       111          222           333          444         1        mc
2   c-01-1   Oil Painting   112          223           334          445         1        sc
3   c-01-2   Watercolor     113          224           335          446         1        sc
4   c-01-3   Still Life     114          225           336          447         1        sc
5   c-01-4   SubCat#C-01-4  115          226           337          448         0        sc
6   c-01-5   SubCat#C-01-5  116          227           338          449         0        sc
7   c-02     Category #2    117          228           339          450         0        mc
8   c-03     Category #3    118          229           340          451         0        mc 

In action : CCV5 loads 'Master Category Names List' (Which reads in 'Category Name' & 'Row-id' from 'dbprefix_updatecnames' - one per line, with each line having an 'Update Cat Name' button) > User clicks button & CCV5 loads one CFV5 form & passes that CFV5 form the appropriate 'dbprefix_updatecnames - Row-id #' > CFV5 form gets appropriate data from 'dbprefix_updatecnames' based on the passed 'Row-id' # > User makes changes to 'name' > User Clicks a 'Save Name Change' button & 'Custom Code' DB Query as per your example runs using data loaded from 'dbprefix_updatecnames' based on 'Row-id' > 'dbprefix_updatecnames' & App data update > User returned to 'Master Category Names List' > Reloads showing new 'name'

Does this seem like a good idea ?

Cheers - Paulzero

Hi Paulzero,

If you are not going to use a full code solution then you may wait for CCv6 as it can handle both the list and the updates without the need to use a form.

Best regards,
Max

Hi Max 'n' Bob !

Greetings from the land down under - still stinking hot here (32c today) ! To cool down we're digging ALDI's 'Sainte Etienne' French brewed premium lager - being consumed by us by the 'Slab' ('Slab' = 4 x 6 pack = 24 bottles = 'Slab' !) - Anyway :

Re the above :

1) Have setup CCV5 form on Client site to read in category names from table 'dbprefix_updatecnames' - all good.
2) In CCV5 'Model > Front List > View Linkable' have setup link to a CFV5 form - CCV5 sends CFV5 form row 'id' from table as $gcb - all good.
3) In CFV5 Form 'Setup > On Load > DB Read > Table = 'dbprefix_updatecnames' > Conditions = '< ? php return array('id' => $form->data['gcb']); ? > to load in stored variables for selected row - all good.
4) In CFV5 Form 'Setup > Code > Custom Code' have setup a table to check loaded results are correct - all good.
5) In CFV5 Form 'Setup > On Submit' I have created a series of DB Updates based on Bobs code examples above - to be tested.

Now I need some advice on how to 'Pretty Up' the CFV5 form ! What need to do is setup a 'Dropdown' field with 2 values : 0 = Un-Publised & 1=Published. 'On Load' I need that dropdown to display the current published state for that Category as the selected option in that Dropdown (eg : if Category field 'published' = 1, then selected option in Dropdown will also = 1. & so on.

Further, if Dropdown value is 0 (Un-published) I would like to dim & make read only the 'Name' field - If the User changes the Dropdown value to 1 (Published), then the 'Name' field becomes writable - guess this is ajax / java - I'm a no-hopper with that stuff - help / advise needed please to make this CFV5 form beautiful !.

Cheers - Paulzero

Hi All !

This matter is turning into a CFV5 question - but will post again here for now.

I have the setup as described above working but need a little more help with the finer points of CFV5. Recap : Using CCV5 to create a listing of static category info from a specially created DB table called 'undatecnames'. On click CCV5 loads a CFV5 form & passes that form the variable 'gcb'. The CFV5 form uses that variable to call in a row from 'undatecnames' as follows :

CFV5 setup :

CFV5 > Setup > On Load > DB Read > Table Name : dbprefix_updatecnames | ModelID : category | Conditions : < ? php return array('id' => $form->data['gcb']); ? >

CFV5 > Setup > HTML (Render Form) > Submit event : submit | Form method : Post

CFV5 > Code > Custom Code :

< ? php
$catid = $form->data['gcb'];
echo "<h4>'Category ID = $catid</h4>"; 
? >

<table style="width:100%;" >
<tbody>
< ? php
foreach ( $form->data['category'] as $p ) {
  echo "<tr>
  <td colspan='2' width='100%' ><h4>Category Name | {$p['name']}</h4></td>
 </tr>
<tr>
  <td style='padding: 8px;' width='27%' >Com_Con db-ID</td>
  <td style='padding: 8px;' width='73%'>{$p['com_con']}</td>
 </tr>
<tr>
  <td style='padding: 8px;' width='27%' >Published 0=Unpub 1=Pub</td>
  <td style='padding: 8px;' width='73%'>{$p['published']}</td>
 </tr>
</tbody>
</table>";
}
? >

< ? php
$pubstate = $form->data['published'];
if ( $pubstate = "1" ) { echo "Current Category State | Published"; } // OK
? >

< ? php
if ( $pubstate = "1" ) 
{ $p['published'] = "0";
echo "  |  $ p-published  |  " . $p['published'] ; // OK - get 0
echo "<input type='hidden' name='category[published]' /><br>
<input class='button' style='background-color:red;' type='submit' value='Un-Publish This Category' /><br>" ;} 
? >  "

(Above - name='category[published]' - I'm not sure that this the value to pass to 'Submit' ? And is this the right way to use 'On Submit' ? Do I need to incorporate the Action Label in some way ?

CFV5 > Setup > On Submit > Custom Code > Action Label : update_cats_now

CFV5 > Setup > On Submit > Custom Code > Content

< ? php

// Update - com_con categories
$query = "
  UPDATE `dbprefix_categories`
    SET
      `title` = '{$form->data['cat_name']}',
      `published` = $form->data['published']
       WHERE `id` = 'com_con' ;
";
$db->setQuery($query);
$save = $db->execute();

? >

As always - any advice / help appreciated.

Cheers Paulzero

Hi Paul,

What is exactly not working ?

Also please check Connectivity v6 as it gives you the possibility to work with multiple tables, and integrate forms directly from the connection page.

Best regards,
Max

Hi Max,

Thanks for response. Setup as described works but not sure if syntax for passing updated value to CFV5 'On Submit' from 'Custom Code' is correct - or - whether i'm passing the right variable to 'On Submit' :

From CFV5 'Custom Code' i'm sending 'name='category[published]' to CFV5 'On Submit' :

'Custom Code'

< ? php
if ( $pubstate = "1" ) 
{ $p['published'] = "0";
echo " | $ p-published | " . $p['published'] ; // OK - get 0
echo "<input type='hidden' name='category[published]'/><br>
<input class='button' style='background-color:red;' type='submit' value='Un-Publish This Category' /><br>" ;} 
? > "

I've setup CFV5 'On Submit' like this :

'On Submit' :

< ? php    // Updating - com_con categories
$query = "
UPDATE `dbprefix_categories`
SET
`title` = '{$form->data['cat_name']}',
`published` = $form->data['published']
WHERE `id` = 'com_con' ;
";
$db->setQuery($query);
$save = $db->execute();
? >

So in 'On Submit' above i'm using 'published` = $form->data['published']' - should I be using 'published` = '$category[published]' (same syntax as used in 'Custom Code) in 'On Submit' instead ?

Confused about relationship between name='category[published]' in 'Custom Code' and 'published` = $form->data['published']' in 'On Submit' ?

All help appreciated !

Cheers Paulzero

Hi Paulzero,

I'm not quite clear what you are doing here but as far as I can see this does not have any value set in your code - is it coming from somewhere else?

<input type='hidden' name='category[published]'/>

Bob

Hi Bob,

Can't get 'On Submit' to recognize & insert 'Created on the fly' '$form>data' variables into SQL Update Query - Picture explains :



In 'Form > Code > Form Type > Custom Code' I also tried the following :

switch ($p['published']) {
    case "1":
        $form->data[category]['newpubstate'] = "0";
        $form->data[category]['newcomcbvalue'] = "0";

And Tried :

switch ($p['published']) {
    case "1":
        $form->data[category][0]['newpubstate'] = "0";
        $form->data[category][0]['newcomcbvalue'] = "0";

As you'll note, these 'Created on the fly' variables where inserted into the Array before 'Submit' & It didn't make any difference where in the Array I put the 'Created on the fly' variables - 'On Submit > Custom Code > SQL Update Query' wouldn't process them.

In 'On Submit - Custom Code' - tried & don't work:

SET `com_cb_value` = '{$form->data[category]['newcomcbvalue']}' -- Doesn't work
SET `com_cb_value` = '{$form->data[category][0]['newcomcbvalue']}' -- Doesn't work

In 'On Submit Custom Code' - these work :

WHERE `id` = '{$form->data['gcb']}' ";
(Static) `published` = '1' 

Any idea whats happening ?

Cheers Paulzero

Hi,

Where does the "$p" in the code come from ?

And I think that using version 6 can help you in this situation.

Best regards,
Max

Hi Max,

$p is just a variable for use in 'Code' area for 'table display' on form :

<?php
$catid = $form->data['gcb'];
echo "<h4>'changecname' ID = $catid</h4>"; 
?>

<table style="width:100%;" >
<tbody>
<?php
foreach ( $form->data['category'] as $p ) {
  echo "<tr>
  <td colspan='2' width='100%' ><h4>Updating Category | {$p['name']}</h4></td>
 </tr> .... etc...etc.

Re: My picture - very urgently need to know whats happening in 'On Submit' - string syntax for calling variable values from form into 'On Submit' not working - Very urgently need to know correct string syntax.

Why would Version 6 be any better ? This a ChronoForm V5 form loaded from CCV6 via ' Front List > Actions > View > Form Event > update_cat_published:load' - where 'update_cat_published' is the above CFV5 form. I don't think there is a Version 6 of ChronoForms.

Cheers Paulzero.

Hi Paul,

Any data can be found under $form->data, you can check its contents using a debugger action.

I don't use the {$var} syntax inside strings, please try to use the

"string ".$var." string"

to be sure that the code is working as expected.

v6 would provide the most part of the code you have written here, it has a table and custom lists, and other tools to help, you would not need to use Chronoforms to build a form because there is a simple fields view which can be used instead until Chronoforms v6 is ready.

And technically you can could still use Chronoforms v5 by pointing the links from v6 to your form, and updating the "Action url" in your form to point to the correct v6 event.

Best regards,
Max