zzsys_user

[danielf]

Hi there,

Hopefully someone can help me out here.

I'm trying to populate the primary key of a field with a concatenation of a number generated through next_number and the username of the user. The reason I want to do this is that I will likely be running different instances of my site locally at different machines. I will at some point have to merge the different databases, so I want to ensure all primary keys are unique across different sites. Using unique usernames and generating PKs on the basis of these usernames (e.g. Jones1234) should allow me to do this.

I've seen a number of ways to retrieve the user_id for the current user. The Javascript function zzsys_user_id() returns the PK of the current user, and I can use this to populate a field through JavaScript as kindly pointed out by FBCTim in another thread.

current_user = zzsys_user_id();
$('#issue_owner#').val(current_user);

This works alright, but as expected, returns the PK for user_id. What I'm after is the username (zzsys_user.sus_name), and I've been unable to retrieve this using javascript.

What I can do, is use php (in the edit code section of the form) using $this_zzsys_user_id

$id = $this->zzsys_user_id;
$t = nuRunQuery("SELECT zzsys_user.sus_name FROM zzsys_user WHERE zzsys_user_id = '$id'");
$r = db_fetch_row($t);
$current_user = $r[0];
nuDebug($current_user);

Returns the username for the current user (provided the current user is not Globeadmin, as that user isn't in the zzsys_user table). What I've not been able to do is to use that php variable to populate the issue_owner field and I'm stuck as to how I should do this. I tried various ways of sticking the php variable into the default SQL value in the text area for the field but can't seem to populate it. Somehow it seems as if the SQL statement doesn't accept a php variable. I've also tried sticking

SELECT zzsys_user.sus_name FROM zzsys_user WHERE zzsys_user_id = '#zzsys_user_id#'

directly into the default SQL value, but the field remains blank, even when logged in as another user (i.e. not Globeadmin).

I'm sure this should be pretty simple, but being unfamiliar with php or JavaScript and the different types of variables, I've already spent far too long trying to figure this out, so hopefully someone can help me out here.

Cheers,

Dan

[admin]

Dan,
nuBuilder generates unique IDs using a php function called uniqid() and although its very unlikely you would create any duplicates, uniqid() can take a parameter that it will add to the beginning of that id for reasons like you are suggesting (currently you will find we use '1').

I believe the easiest solution would be to change this hard coded parameter in the db_functions.php file for each of your locations.

You'll find it here (line 38)..

Code: Select all

			$id   = uniqid('1');

This would be my suggestion.


Steven

[danielf]

Hmm, that's not quite what I wanted to hear, but thanks for your response anyway.

If the easy solution is to hard code for different locations, I might as well do that within the SQL statement (i.e. SELECT CONCAT("jones", next_number_id) FROM next_number WHERE nxn_key = '#TT#'

This has the advantage that the PK will be of the form jones1234, and is thus easily human-readable. Using uniqid('jones') would likely give me something like jones44e6a9e1a which isn't really something I want to show to my users. The disadvantage is that I'll have to hard code it 3 times, rather than just once per location, but that's only a minor issue.

I'm still a little puzzled why you can apparently use SQL queries in php code through nuRunQuery, but can't use php variables in a SQL statement that populates a database field. I'm sure I'm missing something here, but it kinda makes me wonder what the purpose of the php section and nuRunQuery is.

I suppose that should teach not to mess about with programming languages I don't really know

Dan

[admin]

Dan,

If you use a Display object,

display.PNG

SELECT zzsys_user.sus_name FROM zzsys_user WHERE zzsys_user_id = '#zzsys_user_id#'

will give you the value you need and you could use it anyway you want.

Maybe this will help.

Steven

[FBC-Tim]

To assign a key field of your own you could possible use something like this ...

Code: Select all

if (#recordID# == -1) {
  #-- Get the Note No from the details table
  $sc_query  = "SELECT sc_next_note FROM springcleaning_details";
  $sc_query .= " WHERE springcleaning_details_id = 'FBC'";
  $sc_data = nuRunQuery($sc_query);
  $sc_row = db_fetch_row($sc_data);
  $sc_new = $sc_row[0];

  #-- Increment the Note No ready for the next new record
  $sc_query  = "UPDATE springcleaning_details";
  $sc_query .= " SET sc_next_note = sc_next_note +1";
  $sc_query .= " WHERE springcleaning_details_id = 'FBC'";
  nuRunQuery($sc_query);

  #-- Get the login name to add to our Note No
  $sc_query  = "SELECT sus_login_name FROM zzsys_user WHERE zzsys_user_id = '#zzsys_user_id#'";
  $sc_data = nuRunQuery($sc_query);
  $sc_row = db_fetch_row($sc_data);
  $sc_uname = $sc_row[0];

  #-- Make sure we have a username and add it to the front of the Note No
  if ($sc_uname == '') $sc_uname = 'GA-';
  $sc_new = $sc_uname . $sc_new;

  #-- Save the Note No we got to the clean record
  $sc_query  = "UPDATE note";
  $sc_query .= " SET n_no = '$sc_new'";
  $sc_query .= " WHERE note_id = '#newID#'";
  $sc_data = nuRunQuery($sc_query);
}

I have the PHP code above placed in the AFTER SAVE section on the EDIT CODE tab of the nuBuilder Form.

I made this by analysing the Sample Debtors nuBuilder application that is available from the website. There is a lot of good code in that example application to learn from.

I never let the user see the primary key from any of my tables, it stays hidden in the background to make everything work. If the user is going to see a 'code' of some type I generate it in a normal field like I have above with my Note Number.

I have a table called springcleaning_details that has a field sc_next_note that holds the next number to be allocated. Using SQL i read this field into a variable then fire off an UPDATE query to increment the number ready for next time. I know there is a chance that this may be done concurrently by two users and get the same id, this would not be a problem for you because you are adding the user name that will always make it unique anyway. Use an UPDATE query again along with #newid# to poke the number you just made into the newly saved record.

I have not tried it, but if you want to force the primary key you could change this last UPDATE query to set the key field. This will cause a problem if you have a subform also trying to save detail records. Doing this would only work if you have no subforms on your edit form and ONLY allow the user to have a "Save & Close" button so the edit window is killed as soon as the save is done. Once again I would not recommend fiddling with the primary key, just let nuBuilder do what it wants with the key and don't show the user.

nuBuilder is a fantastic tool! Hope this gives you some ideas

[danielf]

Thanks Guys.

Steven: I tried that and it didn't work. I was also slightly surprised you suggest a display object since AFAIK the whole point of a display object is that it doesn't update the data base?

FBC-Tim: *YOU_ARE_A_STAR!!!*

Thanks so much. Your example code gave me enough to work on, and understand a bit more about PHP and SQL in the process. Having an example to work from makes it so much easier to piece things together when you're working with an unfamiliar language. I think I'm a fairly competent programmer, but my self-taught skills in LISP don't transfer to this project much. Using your code as a template and piecing things together I came up with the following, which does exactly what I want.

Code: Select all

    if (#recordID# == -1) {

      #-- Get the login name 
      $sc_query  = "SELECT sus_login_name FROM zzsys_user WHERE zzsys_user_id = '#zzsys_user_id#'";
      $sc_data = nuRunQuery($sc_query);
      $sc_row = db_fetch_row($sc_data);
      $sc_uname = $sc_row[0];

      #-- Assign default for Globeadmin
      if ($sc_uname == '') $sc_uname = 'GA-';
  
      #-- Generate and retrieve next_number
      $sc_new_id = uniqid(1);
      $sc_query = "INSERT INTO next_number SET nxn_key = '$sc_new_id'";
      $sc_data = nuRunQuery($sc_query);
      $sc_get_num = "SELECT next_number_id FROM next_number WHERE nxn_key = '$sc_new_id'";
      $sc_data = nuRunQuery($sc_get_num);
      $sc_row = db_fetch_row($sc_data);
      $sc_new_num = $sc_row[0];

      $sc_new_id = $sc_uname . $sc_new_num;

     #-- update the new record.
     $sc_query = "UPDATE cases SET case_number = '$sc_new_id' WHERE case_id = '#newID#'";
     $sc_data = nuRunQuery($sc_query);
     }

I take on board what you say about not messing with the PK (and you'll notice I not doing that in the above code). When I mentioned not showing the keys to my user, I was actually talking about the merged database, which will need a unique key of sorts, and using uniqid for that would generate a partially hex key. I'm sure having the PK autogenerated will save me grief further down the line.

Anyway: now that I've got this cracked (thanks so much again), I have another question. You mention a save & close button, which I've been desperately looking for but unable to find. Is this functionality provided by the 'Close All' plugin? I'm not too keen on leaving a window open after save, so the option of automatically closing a (save)window sounds very good.

Dan.

EDIT: I think I found the save&close button. It's the 'close' button from the action butttons...

This was a bit confusing, as I was trying to create a form where a user could update a single field in a record containing several fields, and I didn't want to to use a save button in the edit form for the record (I have a subform on a separate tab in the edit form, which doesn't play nice on 'save', and I also don't want to confuse the user regarding what gets saved when you have different tabs in an edit form). So: I have all fields on the edit form as readonly, and provide a separate button on the form to edit the one field I want to be editable. When this button gets clicked, I open a new edit form. I use a cookie to pass the PK for the record to the new form, where I use it to retrieve and display the field to be edited.

The plan was to use some PHP code (in for instance the after browse section) to update (rather than save) the record with the edited field. (I'm not sure if this is going to work now). I put a 'close' action button on the form, cleverly renaming it to 'save & close', to ensure the user that the field was saved. What happened, much to my surprise, was that clicking the close button created a new record in the database which contained all the fields on the form. Since I had passed the record id (PK) of the original record, this also meant I ended up with a duplicate PK in my database.

Doing some poking around I learned that the close button is actually labeled 'save & close' if you don't change its title. Looking at the 'action buttons' tab, I figure the description 'close' was more visually pleasing than 'save & close', but it really is a 'save & close' button, which is rather a different thing...

So, the upshot is that I've found the 'save & close' button, and I'm now after a proper 'close' button that I can use as a trigger to update fields of a record. I need beer...

On the upside: I'm starting to like Nubuilder more and more as I learn more about what is possible with it

[admin]

.