practical question about insert form/2 tables/email

[antonio]

Hi all,
I need a little advice on how to develop a simple procedure.
I have 2 tables:
1- users (complete anagraphical data)
2- orders

I have a page where the user can login and go to the insert order page.
In the order page I have a form that will be filled and sent by email to a fixed specified address.

The question is:
When I send the e-mail I have at hands only the UserID and Username (session vars) along with the order details (table 2).
But I need to include the whole user anagraphical data (from table 1) in the e-mail.
How can I do that?
I need to retrieve all data with a recordset and then... then what...?

What is the correct procedure to do something similar?

I hope my question is clear enough, sorry.

TIA for any advice.

tony

[Fred]

Hi Tony,
I assume you have the used id in the orders table...

So it should be easy enough to use the user_id in the orders table as a foreign key to join to the user_id in the users table.

Something like the following:

Code: Select all

SELECT
order.a_item,
order.a_total,
members.`name`,
members.surname,
members.email
FROM
order
INNER JOIN members ON order.a_member_id = members.id
WHERE
order.a_id = {variable} AND
members.id = {session_variable}

This should give you all the info you want and then just populate the email as usual

[antonio]

Hi Fred,
Thanks for your reply.
I can easily link the two table with the same query you posted.
My problem is retrieving the USER data while inserting data in another table.

I have an Insert Record Form server behaviour on my page.
The IRF will insert data in the order table.
In the same table I have the UserID foreign key.
But the Send Mail server behaviour doesn't see the USER data table.

How can I provide user data from the user table while inserting record in another table?

I think the only solution is to make all user data available as Session Variables. I can do this easily, adding all available user fields in the ADDT Login Settings control panel (in the Sessions TAB).
This way, I will include all order data along with session variables in my Send Mail message.

Is this a viable solution?

TIA

tony

[Fred]

Tony,
Ok I am with you...

No creating all those variables is not worth it only to get data into one form. More overheads and things that can go wrong.

What I would try is to create a record set based on the session variable / user_id.
Make sure the query is the first to run when you land on the page. To ensure that you already have all the necessary data before you begin constructing the form and send email behaviour.

Now you should be able to hand code the variables from the recordset into your email but instead of using <?php echo $mem_name;?>" you need to use {GLOBALS.mem_name}.

If it doesn't see the data, try adding it into variables directly after you run the query and then access the data by {GLOBAL.data}

Well that is for the interakt products. I assume it would still be the same for ADDT.