I have searched & read through some of the topics on here to figure this out before posting, however I haven't found anything to help me solve this. Prior to looking on here I have made several attempts to work this out and none came close to what I want to achieve therefore not even worth posting here.
Basically I have a MariaDB table that is being populated daily from an external source with booking details for multiple rooms. I'm running an SQL query to extract data based on date and using a portion of phone numbers as a pin code to unlock the door of the booked room.
I would like to manipulate the way the data is presented to have the pin code nested within the array. The attached image shows the query result showing the pin as the last item. I'm not sure whether this is best to be done in the query itself or using a function/change node from the output of the database.
This is the query:
t = "SELECT Concat(
firstname, ' ',
lastname) AS 'name',bookings.room AS 'roomaccess',bookings.starttime AS 'timefrom',bookings.endtime AS 'timeto',bookings.mode AS 'mode',bookings.daysofweek AS 'daysofweek',bookings.twofactor AS 'twofactor',bookings.code AS 'pin' FROM bookings WHERE startdate LIKE '"+msg.payload+"'"
The +msg.payload+ is a date being fed into the query.
I'm hoping someone can assist with this as it sounds so simple but I haven't had any joy with it.