Custom Table

[jazzon]

My Goal: When a new user is added to the database a custom table named with that users login ID needs to be created. Since this is a function which would overwrite any existing table with the same name (theoretically impossible due to unique login names) I am putting this function on a separate form accessible to admin rights group only.

So Far: Form opens with a list of users on the database. When a user is selected, the edit window appears with no information displayed, just a warning about overwriting data, and a button to execute.

My Problem: It appears that this would most easily be accomplished with an after save piece of code, however I do not know where the data is stored internally in nubuilder (I.E. what variable name I would reference in my CREATE TABLE SQL statement). I assume you are passing an array of data to the edit window. So my question is twofold 1) What is the name of this array, and 2 ) Can I access it's members in a SQL statement from an "After Save" code block?

[steven]

jazzon,

If you put the following in After Save of the form...

tofile(print_r($arrayOfHashVariables,true));

This will give you...

Array
(
[#thename#] => Steven Copley
[#thedate#] => 30-Jul-2008
[#art_confirmed#] =>
[#art_rating#] => 9
[#EMAIL_ADDRESS#] =>
[#EMAIL_MESSAGE#] =>
[#session_id#] => 14b846f450a858
[#formsessionID#] => 14b846f4e46f06
[#framenumber#] => 1
[#beenedited#] => 0
[#recordID#] => 148907281328ea
[#clone#] =>
[#close_after_save#] => 0
[#number_of_tabs#] => 1
[#refresh_after_save#] => 1
[#customDirectory#] => db/ac
[#TheSubforms#] => 0
[#____xxx#] => 1
[#____art_confirmed#] => 1
[#NOTHING#] =>
[#zzsys_session_id#] => 14b846f450a858
[#access_level#] => Artist
[#zzsys_user_id#] => 145944f376113a
[#zzsys_user_group_name#] => Artist
[#small#] => 0
[#form_list#] =>
[#parameter#] =>
[#local_ip#] =>
[#session_seconds#] => 1266970450
[#session_date#] => 2010-02-24
[#ses#] => 14b846f450a858
[#session_parameter#] =>
[#newID#] => 148907281328ea
)


in the zzsys_trap table (which will obviously give you other useful variable too)

these variables can be used as nuBuilder hash variables in the After Save section eg.

$sql = "SELECT `sus_login_name` FROM `zzsys_user` WHERE `zzsys_user_id` = '145944f376113a'"

can be...

$sql = "SELECT `sus_login_name` FROM `zzsys_user` WHERE `zzsys_user_id` = '#zzsys_user_id#"

(#sss_zzsys_user_id# will be replaced with 145944f376113a before this code is run)

you can also find this in the wiki.

http://wiki.nubuilder.com/tiki-index.php?page=Hash+Variables.

Steven

[jazzon]

Thank you very much Steven,

I have read that wiki page several times over thelast few days and appear to have never noticed that code snipet at the bottom. Thanks for not giving me the usual "search the site" speech.


Jazzon

[steven]

jazzon,

Just quickly, if I understood correctly, you were going to make a table named after each user.

I would suggest you create just the one table with an extra field that held the users name or id.

There are a number of reasons for this but a user's records can easily be filtered out with a where clause in reports or even in a list screen.


Steven

[jazzon]

<<Steven

Correct, the only reason I was going to use a separated table per user is table data size. The table is store an availability schedule, which could have 3-8 hour pairs (in-out) per day. With a user base in the hundreds, and not being in control of the server (rented), i was concerned about seek time for the data and server timeouts. I have written several back end data sets, but this is my first stab at a front end. Also, i am a rank armature at php, so I am endeavoring to do this all via nubuilder, with no external pages. Any thoughts would be appreciated.

[steven]

jazzon,

If you put everything in the one table you'll make your job so much easier, a mysql table with a primary key that has a indexed foriegn key holding your user, could handle a couple of hundred thousand records before you will see anything slow down (probably more).

And another important thing to consider is that you can run one query over that table to get stats on all users. Reporting becomes a lot easier because you dont have to join a heap of tables to see an overview.

Steven