Learning::Syntax::PHP/MySQL/Joomla!::MAX(id)

1283 Posts
Bucklash
8 years ago
1
Topic

Hi Sebloders

In my quest to use afterStore field,  I have discovered a little piece of info. I was trying to get the MAX(id) of #__categories, and failing miserably.

I found a stackoverflow forum post and someone has answered my question (thanks Riccardo Zorn!!).

I was unable to get a result because my syntax was incorrect 

WRONG ->

$query->select('MAX(id)');

CORRECT ->

$query->select('MAX('.$db->quoteName('id').')');

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('MAX('.$db->quoteName('id').')');
$query->from($db->quoteName('#__usergroups'));
$db->setQuery($query);
$result = $db->loadResult();
// // Display results in a message
JFactory::getApplication()->enqueueMessage($result, 'type');<br>

Hope that helps someone

Bucklash

Get a VIP membership
1283 Posts
Bucklash
8 years ago
0
Level 1

Hi Sebloders

I have since finished what I was trying to do with afterStore field and thought I would share it to help others get a handle on syntax for Joomla/Seblod/MySQL/PHP. It was hard work and I think this will help someone, at some point, somewhere.

Aim

1 Create a category, and at the same time, dynamically create a usergroup with the same name as category, and then 

2 Create a reference to the  usergroup in that category.

To begin with I have in my category content type (called organisation), in the Site Form view, these visible fields:

Cat Title (cat_title)

Cat Alias (cat_alias)

Cat Parent Id (cat_parent_id) 
[live value set to appropriate id]

Button Save (button_save)

I have these hidden fields:

User Group Title (user_group_title) 
[the JS section of the config window contains JavaScript to add the contents of cat_title to this field]
[I might change this to be carried out in the beforeStore field, hmmm]
[this contains the JS shown below]

User Group Parent Id (user_group_parent_id)
[live value set to appropriate id]

OG2 UG ID (tp_og2_ug_id) 
[crazy naming reference that works for me][it is a text field where I will store the Usergroup ID]

OG After Store For OG UG (tp_og_after_store_for_og_ug) 
[afterStore Field] 
[this contains the PHP code shown below]

JS added to user_group_title field, gets input from one field and adds it to another field...:

/* The field I wish to retrieve the data from */
$( ".tp-cat-title #cat_title" ).keyup(function(e) {
    /* Storing the value as a variable */
    $catTitleValue = $(this).val();
    /* The field I wish to set with the variable */
    $(".tp-ug-title #user_group_title").val($catTitleValue); 
    /* funky :) */
});

PHP added to tp_og_after_store field, after storing category and usergroup, gets id of category, and id of usergroup, both based on title, and assigns usergroup id to relevant column in relevant db table...:

$orgTitle = $fields['cat_title']->value; // assign value as variable
$db = JFactory::getDBO(); // connect to DB
$q1 = $db->getQuery(true); // create 3 separate queries
$q2 = $db->getQuery(true);
$q3 = $db->getQuery(true);
$orgCatId = $db->quoteName('orgCat.id');
$orgCatTable = $db->quoteName('#__categories', 'orgCat');
$orgCatTitle = $db->quoteName('title') . ' = ' . $db->quote($orgTitle);
$orgUgId = $db->quoteName('orgUg.id');
$orgUgTable = $db->quoteName('#__usergroups', 'orgUg');
$orgUgTitle = $orgCatTitle;
$orgCckTable = $db->quoteName('#__cck_store_form_tp_organisation_2');
$orgCckUgIDColumn = $db->quoteName('tp_og2_ug_id');
// subquery...
$q2->select($orgUgId)
   ->from($orgUgTable)
   ->where($orgUgTitle);
// query...
$q1->select($orgCatId)
      ->from($orgCatTable)
      ->where($orgCatTitle)
      ->union($q2);
$q1Result = $db->setQuery($q1)->loadColumn(); // loadColumn() works, phew...!!
// 3rd query gets results from q1 and uses the values
$q3->update($orgCckTable)
      ->set($orgCckUgIDColumn . ' = ' . $q1Result[1])
      ->where($db->quoteName('id') . ' = ' . $q1Result[0]);    
$db->setQuery($q3);
$result = $db->execute(); // important<br>
Get a VIP membership