Posting order form fields to separate database table

[djones]

I need to create a new table from the data the customers submit with the order forms. I'm not very skilled with PHP or SQL so any suggestions would be greatly appreciated.

So far I posted the following query into the Order Creation Code, found in the order forms advance tab:

Code:

mysql_query("INSERT INTO jos_newtable (column1)
VALUES (form_field)");

When I substitute the form_field with static value it works fine but I can't seem to figure out what to put in the form_field spot to insert the form data into the table...

[netshine]

You might be better off using the submit code rather than the order creation code - that way all the values will still be in memory and you won't have to look them up. The values entered by the client will appear in the $_POST array. For example if you have a field on your form named 'my_field', the value will be in $_POST['ctl_my_field']. However, remember to sanitise the data before inserting in the database or you will risk SQL injection. For string values you can sanitise with nbf_common::get_param($_POST, 'ctl_my_field'). For integer values you can use intval(nbf_common::get_param($_POST, 'ctl_my_field')).

[djones]

Thanks for the advice!

I got it to work but there was a small problem with the code you posted. The code I used is posted below... I just had to remove the quotes around ctl_my_field.

Code:

mysql_query("INSERT INTO jos_newtable
VALUES ('$_POST[ctl_my_field]', '$_POST[ctl_my_field2]', '$_POST[ctl_my_field2]')");

It works perfectly... now where should I put the code to sanitize the field? In the beginning before my query? Exactly how you posted it?

[netshine]

The way you have written it there will generate quite a few PHP notices, as it will try to resolve the array index as a constant name which will fail and it will default to the value. It will also generate a notice if the key does not exist. If you're not a coder that probably won't make much sense to you, but to sanitise you can use something like this:

mysql_query("INSERT INTO #__newtable (column1, column2, column3) VALUES ('" . nbf_common::get_param($_POST, 'ctl_my_field') . "', '" . nbf_common::get_param($_POST, 'ctl_my_field2') . "', '" . nbf_common::get_param($_POST, 'ctl_my_field2') . ")");

Note that I have added the column names to the query - just in case you need to insert a new column or change the column order later.

[djones]

Thanks again!

The final code I used is below:

Code:

mysql_query("INSERT INTO #__newtable (column1, column2, column3)
VALUES ('" . nbf_common::get_param($_POST, 'ctl_my_field') . "', '" . nbf_common::get_param($_POST, 'ctl_my_field2') . "', '" . nbf_common::get_param($_POST, 'ctl_my_field2') . "')");

(I just had to add the extra ' toward the end of the code... just in case someone else gets hung up on it like I did)

[djones]

I'm also trying to send the current user_id to my other database table as well. I'm guessing the best way to do this is by creating a hidden field and prepopulating it with the user_id field then sending it the same method I used for the other fields. Does this sound about right? If so, do you know what code I should put into the default value of this hidden field?

[netshine]

If you use a hidden field, a hacker can change the value and post back someone else's user ID. You can use nbf_cms::$interop->user->id to get the user ID of the currently logged in user (as long as you are in the 'submit code' event, not the 'order creation code' event, as the latter can be executed by an administrator activating a pending order).