Error message from form link

[Glynneth]

Hello.

Have suddenly had this error message show when I use the link from list-to-form:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'August Tutor ORDER BY text ASC' at line 3 SQL=SELECT DISTINCT(`tutoring`.`Tutor_ID`) AS value, `Tutor_ID` AS text FROM `tutoring` AS `tutoring` WHERE tutoring.tutor_name = New August Tutor ORDER BY text ​

Have worked my way back through what I have done but can't find anything obviously wrong. What do you reckon?

Thanks.

​

 

[troester]

Did you set "Data - where" in a dbjoin element?
WHERE tutoring.tutor_name = New August Tutor ORDER BY text
"New August Tutor" must be quoted.

 

[Glynneth]

Oh right, sure, can't believe I missed that one, thanks. I have used this statement in a dbjoin element: "WHERE tutoring.tutor_name = {$my->name}". The intention is to try and link the selected list record to the record of the current logged-in user. Can you please let me know how I could do this, or do something similar with a single click through the join element/form? I have tried to use the Inline Edit Plugin for this also, but it isn't showing the element input and button in list view. Thanks.

 

[troester]

The Data-Where must be
WHERE tutoring.tutor_name = '{$my->name}'

In principle it's better to use the Joomla user id (names may change) but this depends on your setup.

If you want to select records in the list and set an element have a look at the updatecolumn list plugin.

 

[Glynneth]

OK, have tried with user ids in the above statement, but unfortunately it is still not linking the records between tables. Have been checking this through LEFT JOIN to display linked rows. Have also tried with this kind of statement: WHERE tutoring.id = '{tuition___accepted_by}', using the 'user' element in conjunction with another element used to make edit change to record and hence update to current user. What other things do you suggest might work? Thanks.

 

[cheesegrits]

The join element only stores a foreign key to the other table, it doesn't actually "select" all the data from the other table.

To do that, you need a List Join (under the Data tab for the List settings). That will then add a new group to your form, which contains all the elements from the tabel you are joining.

-- hugh

 

[cheesegrits]

But maybe i'm misunderstanding what you mean by "link".

If all you want to do is make your join element a clickable link in list / details view, so it goes to a view of the table it joins to ...

Introduce the table you want to link to to Fabrik, by creating a List on it. If it's a J! table, like the #__users table, stringly suggest you set "Alter" to No in advanced settings, so Fabrik doesn't make any changes to it.

On your join element that joins to that table, set the "value" be the PK (primary key) of that table.

Create a custom detail link on that element, linking to index.php?option=com_fabrik&view=details&formid=X&rowid={thistablename___joinname_raw}, where X is the ID of the form you created above (ID is in left most column of main display of Forms on backend), and thistablename___joinname is the full element name of your join element to that table, with _raw appended to the name.

That should then render your join element as a clickable link, which goes to a detail view of the table row in question.

-- hugh

 

[Glynneth]

Have tried the LEFT JOIN as List Join as mentioned earlier, but no success. Using purely the ids for connection to keep things simple/reliable. Just wanted a scenario where the logged-in user selects a record from a list and either connects (preferably) with single click in list view, or does so by saving the form and creating a DB connection to their own record with the: WHERE tutoring.id = '{$my->id}', so they don't have to select their id from the dropdown in the join element when it could be taken from the logged in id. Thanks.

 

[troester]

If you want to "assign" a record to a user if he is creating/editing this record use a "user" element (set update on edit=yes).

If you want to have a list button like "assign selected rows to me":
add a update_col plugin to your list, don't select an element for "Update", select your user element in "Update user"

 

[Glynneth]

OK. The overview is: We have a scenario where on a tutoring agency website, tutors login and see a list of new students who joined the agency, who are classified as 'New Jobs'. The logged-in tutors select from the list, which of these new jobs they choose to accept. I am looking to join many students to each tutor in a one-to-many relationship between the two lists: Students and Tutors.

I just need clarification on simplest way to do this from the tutors point of view. Seeing as they are already logged in with an ID, I wanted to use this in the join procedure, so that they don't need to physically enter their ID in a join (it is automatically picked up from log-in and fed into the join), when joining the student to the tutor. Like a seamless procedure.

Was looking to be able to do this all in list view, so that the tutor doesn't need to click into a form before they join the records/lists.

Hope this clarifies, thanks.

 

[cheesegrits]

So can each student only have one tutor, or are there other criteria involved, like 'subject', which means that each student can have (say) a Math tutor AND a Human Beingness tutor?

Assuming the first to be the case, such that once a new student has been "claimed" bya tutor they no longer show up as "new", I would probabky use the update_col method Troester described.

Add a join element in your students list, pointing to the J! users table, called (say) tutor_user_id, using the 'id' as the key (value).

Add an update_col plugin to that list, which updates that join element, with an eval'ed value of ...

return JFactory::getUser()->get('id');

You could then pre-filter a copy of that list with something like "tutor+user_id (raw) LESS THAN 1", so it only shows un-claimed students.

You could filter another copy with "EQUALS {$my->id}" for tutors to see all "their" students.

-- hugh

 

[Glynneth]

OK, have followed your instructions for joining a tutor and student as above, but hasn't worked so far. Just wanted to check... Concerning the join element, you say to point to the J! users table; is this the table in the 'Table' drop-down list under the 'Data' tab selecting: 'vto_users'? I was going to select the actual tutors list that I had created. Could you please clarify this? Also, regarding your stated "updates that join element, with an eval'ed value of ...", should I be placing this statement in the 'To' field next to the 'Update' drop-down, or in the 'Post Eval' box? Thanks.

 

[cheesegrits]

Well, let's start by getting an answer to my question:

So can each student only have one tutor, or are there other criteria involved, like 'subject', which means that each student can have (say) a Math tutor AND a Human Beingness tutor?

-- hugh

 

[cheesegrits]

It would be possible to use your tutors table, but that makes the updating (where the tutor "claims" a student) and the pre-filtering (to see only "my" students) more convoluted. So let's get that question answered first as that makes a big difference on how you go about this.

-- hugh

 

[Glynneth]

This is a maths tutoring agency, no other subjects involved. Would be looking for the tutor to "claim" a student, as you have stated and for the filters to be applied. Need the student and tutors as part of the relational model and both sets to be created as Joomla! users at the same time as the Fabrik records are created for them, so they can login as authorized users in the future.

 

[cheesegrits]

OK. In which case, the eval'ed code on the update_col would be a little more complex. Something like this:

$user = JFactory::getUser();
$userid = $user->get('id');
$db = JFactory::getDbo;
$query->getQuery(true);
$query->select('id')->from('tutors')->where('userid = ' . $db->quote($userid));
$db->setQuery($query);
return (int) $db->loadResult();

You may need to run all that on to one line, remove all the line breaks, in order to paste it in to the one line 'value' field.

It assumes the tutors table is calle d'tutors', and the user ID element on the tutors table is called 'userid'. Modify as appropriate.

You'll also need to update to latest github, to pick up the fix to update_col I committed today.

-- hugh

 

[Glynneth]

OK, PHP code looks good, but now I have updated the 'fields.xml' file for 'update_col', the plugin won't load. What do you suggest? Do I have to update the entire Fabrik system, or can I just copy and paste individually updated files? Just wanted to check, thanks.

 

[Glynneth]

Hi. Hadn't had a response in nearly four days and was wondering if my last post got somehow overlooked?

 

[cheesegrits]

Typically if one of us hasn't responded within 3 days, then yes, it's dropped off the radar. We do our best, but sometimes things slip through the cracks. Basically, do as "Last 3 days" check (navbar at the top of any forum page), and if your thread isn't on it, we've probably dropped the ball, so bump it.

Yes, you always need to do entire updates, you can't cherry pick individual files. Often changes in one file rely on changes in other (seemingly unrelated) files. So although it takes a little while, always re-upload EVERYTHING (and DO NOT use any "only overwrite if newer" option your ftp client may have). This will ensure all your files are copacetic with each other.

-- hugh

 

[Glynneth]

The thing is that you are charging for a service with a defined response time, you know? OK, I understand the potential related file changes effects, so will upload it all again.