[SOLVED] Full Calendar | Limit returned data to logged in user + Solution

[marcq]

Hi,

I would like to limit the returned data to the data (dates) that belongs to the logged in user.

So I have created a prefilter query :

Field : book_crewmember (which contain the value(s) of a Picklist element like ["1","36"]
Condition : equal
Value :
$user = JFactory::getUser();
$userid = $user->get('id');
// var_dump($userid);exit;

$dbcrew = JFactory::getDbo();
// GET THE FABRIK ID FROM THE JOOMLA USER ID
$dbcrew->setQuery("SELECT id FROM fab_crew WHERE userid = '$userid'");
$loggedincrewmember = $dbcrew->loadResult();
// GET THE LOGGED IN ID FROM THE COMMA SEPARATED FIELD
$dbcrew->setQuery("SELECT * FROM fab_booking WHERE find_in_set('$loggedincrewmember',book_crewmember) >0");

I guess I have an issue with the query, since it is returning only one date in the full calendar for logged in members which have more than one "dates" that should be displayed.

I would appreciate your support.

Thank you in advance.

Cheers, marc

 

[cheesegrits]

Well find_in_set won't work, because it's a JSON array, not a comma separated field.

And ... well, lots of other problems with that code.

Here's how I'd attempt it ...

Use a pre-query to get the crew id ...

$user = JFactory::getUser();
$userid = $user->get('id');
$dbcrew = JFactory::getDbo();
// GET THE FABRIK ID FROM THE JOOMLA USER ID
$dbcrew->setQuery("SELECT * FROM fab_crew WHERE userid = '$userid'");
return $dbcrew->loadObject();

Then in the pre-filter ...

WHERE
Field: book_crewmember
Condition: CONTAINS
VALUE: "{q->id}"

... although you may have to backslash escape those quotes.

-- hugh

 

[marcq]

Hi Hugh,

Thank you for your reply.

Query in prefilter

$user = JFactory::getUser();
$userid = $user->get('id');
$dbcrew = JFactory::getDbo();
$dbcrew = setQuery("SELECT * FROM fab_crew WHERE userid = '$userid'");
return $dbcrew->loadObject();

Prefilter :
Join : AND
Field : book_crewmember
Condition : CONTAINS
Value: \"{q->id}\"
Type : Text

Prefilter query works, But as soon as I'm adding the prefilter, then there's again a sql error displayed while loading the list.

Is the way I'm doing my backshlash escape wrong ?

 

[cheesegrits]

Oh, crap, my bad, you can't use PHP in the pre-filter query, so it'd just be ...

SELECT * FROM fab_crew WHERE userid = '{$my->id}'

-- hugh[/quote]

 

[marcq]

Oh, crap, my bad, you can't use PHP in the pre-filter query, so it'd just be ...

SELECT * FROM fab_crew WHERE userid = '{$my->id}'

-- hugh

[/QUOTE]

Hi Hugh,
Thanks for you reply.
I tried already this since I read the bubble message of the field.
But it doesn't work too.

Is my prefilter ok ?

Prefilter :
Join : AND
Field : book_crewmember
Condition : CONTAINS
Value: \"{q->id}\"
Type : Tex

 

[cheesegrits]

Did you try it with and without the \ ?

-- hugh

 

[marcq]

Did you try it with and without the \ ?

-- hugh

Yes, but same result, no dates are returned

 

[cheesegrits]

It should be {$q->id}

-- hugh

 

[marcq]

Did you try it with and without the \ ?

-- hugh

ohoho, of course, it works, thanks Hugh

Solution is :

SQL query in the Pre-filter query :

SELECT * FROM fab_crew WHERE userid = '{$my->id}'

Join : AND
Field : field where you are searching the value
Condition : CONTAINS
Value : "{$q->id}" | Value you are seaching in the field
Type : Text