Database join? Or something else?

[guyfromnola]

I'm having trouble getting this off the ground. Maybe someone can shed some light. I'll analogize my concept/design/needs to something more basic to make it a little easier to follow:

List - LIBRARY
List - LIBRARIANS
List - BOOKS

There are one or more libraries in the city. There are one or more librarians at each library. There are books added by each librarian.

My current setup uses a plugin to create a Joomla user account when a librarian is added to a library by the Joomla site admin. Librarians would then be able to login with their own account and add books to the book list. Based on a database join between the library list id and the librarian list id, when a book is added by a librarian, it should allow for that book to be able to be viewed when viewing the books listed filtered by library, or further filtered by librarian.

The dilemma I am presented with is, when a book is being added by a librarian, to have it (possibly in a hidden fashion) linked to the particular library, which is based off of the library that the librarian is assigned to. My thought was to create another database join between the librarian and the book, but it only generates errors when trying to even view the list before being able to add any books.

As an admin, I should be able to view the master list (LIBRARY), see the joined list (LIBRARIANS), and then see the list of books in the entire library or by filtered list of librarian.

Although it may seem counter-intuitive to a real-life scenario, for the sake of this analogy, librarians, when viewing the book list, should be able to see only books in their library, along with which librarian is tied to each book entry. Furthermore, if simple enough to implement, librarians should be able to view all books, but only able to edit those that were added by them.

I'm somewhat of a novice when it comes to database coding, but I tend to learn better by example and I cannot find something in laymen terms that would help me understand how to implement this design. Any insight or assistance is very much appreciated. I'd be happy to elaborate on this scenario, if needed. Thanks!

 

[guyfromnola]

Ok....I'm thinking I can actually achieve what I'm looking to do by using a default value evaluation.

Can someone help me with constructing a php eval code snippet? I'm using a databasejoin to populate a dropdown. On the add page the dropdown is rendered as expected. However, now I want the dropdown to default to the same value that is found such as follows (as a best guess on how to build this):

SELECT librarian FROM library WHERE librarian_id = '(the id of the currently logged in librarian, which is a Fabrik list tied to users plugin to create a Joomla user)'

I've played with a few codes snippets I have found around the forum, but none seem to be achieving the desired result. Some yield a white page, others give a msg about contacting the site owner, and others just don't do anything.

Also, I've seen it mentioned in threads that 'hidden' is required for the dropdown default to work. Is that correct?

 

[guyfromnola]

Me again...
Ok, making progress (even though this feels like a party of one :/ )

I'm using the following code in the php eval field to test that the php syntax works, which I see it does. It returns the correct value from the databasejoin dropdown.

$db =& JFactory::getDBO();
$query = "SELECT entity FROM contacts WHERE id = 1";
$db->setQuery($query);
return $db->loadResult();

However, now I'm stuck with trying to replace that '= 1' with something that is not static so that it can reference the currently logged in user. I tried replacing the 1 with {$my->id}, but that didn't do anything and in the form add mode. It defaulted to the first option in the list; the initial problem I was having.

What else can I put in place of the '= 1' to get it to fill that criteria with the id value of the current user (keeping in mind that the current user exists in a users list, which employed the juser plugin to create a user account). I've tried variations with quotes and other syntax, but nothing seems to be taking with {$my->id}.

Thanks!

 

[guyfromnola]

Another lesson learned...

{$my->id} will not work for me if the Joomla user ID and the PK ID in the list of users is not the same...duh. So, I guess I can rule out using that variable.

Not sure what else would be suitable for recalling the current user's PK ID from the Fabrik list. Ideas???

 

[Bauer]

I've found instances where a fabrik placeholder in a SELECT statement won't work.
Instead of
$query = "SELECT entity FROM contacts WHERE id = {$my_id}";
try
$user_id = '{$my->id}';
$query = "SELECT entity FROM contacts WHERE id=".$user_id;
or
$user = JFactory::getUser();
$query = "SELECT entity FROM contacts WHERE id=".$user->id;