Forums

I hope someone can help me - I am out of my depth!
​
I have created an array called $data in a custom code in the setup tab box using SQL. When I print_r the array I get:
​
Array ( [0] => stdClass Object ( [asset_id] => 12 [type_of_resource] => OhBot [start_date] => 2018-08-01 00:00:00 [end_date] => 2018-08-13 00:00:00 [request_delivery] => 1 ) )
​
I want to be able to echo out the values onto the form, but when I try:
​
<?php echo $data -> description ?> there is nothing there
​
Many thanks
​
Mikki

Sorry - description is not in there - I mean $data ->asset_id
​
Thanks
​
Mikki

In your custom code action:
​

$this->set("name_of_variable", value);

If you're using a PHP action, instead of using this->set just RETURN the variable, and then you can access it by the name of the php action.
​
When you want to echo it out, either just use the shortcode {var:name_of_variable.field} or if you absolutely MUST show it in PHP for whatever reason

$this->get("name_of_variable", "default if not found");

Thanks healyhatman, I have successfully used $this ->set ("data_joined, $data); to create the shortcode "data_joined".When I put {data_joined} as the value of a text box, the whole array displays as this:

[{"asset_id":"12","type_of_resource":"OhBot","start_date":"2018-08-01 00:00:00","end_date":"2018-08-13 00:00:00","request_delivery":"1"}]

But when I try to access the data for asset_joined by putting {data_joined.asset_id] as the value for the text box nothing shows.

Is there something 'funny' about the format of my array?

Thanks again for your help

Mikki

Hi Mikki,
​
The data being returned is a json encoded array, you can use the 'jsonde' shortcode to convert that to an array of variables that you can access. url=https://www.chronoengine.com/faqs/74-chronoforms/chronoforms6/5289-variable-shortcodes]See the Variable Shortcodes FAQ[/url].
​
Bob

From the look of your data try
​

{datajoined.0.asset_id}

​
But the better option would be
​
{var:customcodename.0.asset_id}

Hi Bob
​
Thanks - you are right - it is JSON. I've tried this:
​

{data.jsonde:data_joined} 

​
but when I output data_joined it still looks like JSON. Have I used the shortcode incorrectly? I couldn't find an example to follow.
​
Healyhatman, thanks but I can't get the value the way you suggested..
​
Thanks
​
Mikki

It won't work because you "made your own shortcode" and the parser doesn't work that way.
​
Try {var.jsonde:data_joined}
​
{data:} for input form data
{var:} for back-end / setup tab actions.

Thanks for explaining the difenerence between data and var healyhatman - but I still dont seem able to decode the JSON.
​
Here is my code:

<?php
$db = \JFactory::getDBO();
$query = "
SELECT
  assets.asset_id,
  assets.type_of_resource,
  bookings.start_date,
  bookings.end_date,
  bookings.request_delivery
FROM
  #__tblAssets AS assets
LEFT JOIN #__tblBookings AS bookings
ON assets.asset_id = bookings.asset_id
LIMIT 1;
";
$db->setQuery($query);
$data = $db->loadObjectList();?>
<h5>This is the result of print_r on $data</h5>
<?php print_r ($data);
?>
<?php $this->set("data_joined", $data)?>
<br /><h5> trying to print data_joined</h5>
{var:data_joined}<br />

<br /><h5>decode data_joined and print it</h5>
{var.jsonde:data_joined}
{var:data_joined}

Which returns:
​[h5]This is the result of print_r on $dataArray ( [0] => stdClass Object ( [asset_id] => 12 [type_of_resource] => OhBot [start_date] => 2018-08-01 00:00:00 [end_date] => 2018-08-13 00:00:00 [request_delivery] => 1 ) )[/h5][h5]trying to print data_joined[{"asset_id":"12","type_of_resource":"OhBot","start_date":"2018-08-01 00:00:00","end_date":"2018-08-13 00:00:00","request_delivery":"1"}][/h5][h5]decode data_joined and print it[{"asset_id":"12","type_of_resource":"OhBot","start_date":"2018-08-01 00:00:00","end_date":"2018-08-13 00:00:00","request_delivery":"1"}][/h5]
So the array after the

{var.jsonde:data_joined}

Seems the same as before.
​
Have i misunderstod something fundamental here?
​
I just need to be able to access the individual fields in the array
​
Thanks again for your patience in explaining this.
​
Mikki

When you're printing it it will look that way.
​
Have you tried {var:data_joined.field} or {var:data_joined.0.field}

Hi
​
When I try this: {var:data_joined.field}
​
I get no output, but when I try this:
​
{var:data_joined.0.asset_id}
​
I get an error:
'Cannot use object of type stdClass as array'
​
Thanks
​
Mikki

Thanks for all of your help with this.

No problem. By the way the custom query you made can be done with the standard read data action, and you wouldn't have had all this trouble. Left join and everything.
​
Call the primary model name "asset" and put "booking" in the new model name field, click add model. Set relation to one matching record, foreign key in (whichever table has the foreign key), set Foreign key to asset_id OR in relation conditions put in asset.asset_id:booking.asset_id
​
If you only want one record returned, then change Select Type to first matching. Then access the variables with {var:read_data#.model.field}

Hi
​
I have set up the read data action in the way you describe, and it is working. In the where conditions box I have put asset.description:{data:description} so that I only get the records for the asset chosen on the previous page, and that works.
​
Now I need to find assets that are not booked out already, so I was going to try to filter where:
​
NOT
booking.start_date>{data:start_date} AND booking.end_date<{data:end_date}
​
But there is only a where conditions box on the asset model, which I am using to filter by asset.description, so I can't see how to filter by fields in the booking model as well
​
Can this be done with the read action? I would be very grateful for some help.
​
Mikki

Model.field/logic_operator:value
​
E.g.
​
car.date/<:{data:date}

Thanks for showing me that - it makes it very clear how to structure it and I am now nearly there. I am struggling to show assets which are not booked on the dates selected.
​
I think I need to find all booked assets, then display the assets which are NOT in that group
​
1) I can find the bookings where the asset is booked out for those dates - so :
​
booking.start_date /<={ data:start_date} AND booking.end_date/>={data:start_date}
​
2) I need to display assets which are not in the result of the first query
​
So, how can I put something like this in the conditions box?
​
NOT IN
booking.start_date /<={ data:start_date} AND booking.end_date/>={data:start_date}
​
Thanks again
​
Mikki

Field/!=:booked
Or you could use
Field/<>:booked
​
Whichever MySQL syntax you prefer
​
Don't need the AND in between each line you have
​
For things not in the first query if you really need to do it that way:
​
Model.id/not in: {var:read_data#.model.id}

I tried this by creating a second read data action which reads in data from the assets table with model name Available_assets
​
Then in the Where conditions in the object I have put:
​
Available_assets.asset_id /not in:{var:read_data53.assets.asset_id}
​
But I am getting an SQL syntax error that says there is an error near line 1

You'd have to extract all the id's as an array to be able to do it that way sorry. Just do it all in one go using model.field/!=:value

OK , I think I can do it that way if I display items booked before the requested date and items booked after the requested period.
​
But to do that I need to use an OR in the where field so that I get both cases. Is that possible?
​
Thanks

Line1
OR
Line2
​
Or if necessary
​
(
Line1
OR
Line2
)
​
And yes they need to be on separate lines like that

That's great!
​
So now I can get any assets that have a booking and do not clash, then return just the one with the lowest asset_id - but assets that have not been booked will not appear as they have no start and finish date. I could 'seed' the booking table with dummy bookings when we add new assets.
​
It would be better to grab the items that are NOT IN the set of items that clash with the requested bookings. Would that be possible?
​
Thanks