Concatenate UserName with RecordID using mysql_insert_id() during Insert

[Kevin373660]

I really need some help with this, please. I am new to php and have been trying on my own for six weeks and just can’t seem to get this working. It is very important for me to get this feature working.

In my member_Registration.php page I need to insert in to the MySQL tbl_members a concat value of kidSID.mysql_insert_id() into the kidNickname field. The kidSID is the unique incremented record field.

Someone suggested putting an update command right after the insert. I have rem’d the test code out in attached. It did not work. And from another post in the forum, I read that it would be important to perform the update before the redirect. That makes since as the user session with the table would still be connected. I have tried setting up both an insert and an update with DataAssist for the same page but have not found that successful.

The closest I have come is to edit line 75 which inserts into the kidNickname field:
GetSQLValueString($_POST['kidFirstName'].mysql_insert_id(), "text"),
The result in the table for example is: Tom0. The value for the record kidSID is not being identified and is simply appended as a zero. This also disabled the Server Behaviors “Check New Username.

[Ray Borduin]

You should be able to use the DataAssist insert server behavior twice on the member_registration page.

Make sure the first one has a blank redirect setting.

The Insert server behavior will automatically create a session variable with the newly inserted ID. You name it in the interface.

Use that session variable name to reference the newly inserted ID in your second insert.

[Kevin373660]

Ray, I really appreciate your help. I tired again, adding the second insert with DataAssist. It created two records in the db tbl. One with the values from the 1st and the second with values from the 2nd insert code block.

Using DataAssist, I have tried adding a single record Update for the same page as per revised attached. To simplify for testing I used the form value for the kidFirstName. I did not include the concat value for now. Only one record is created with the first Insert values from the form but the Update kidnickname seems to be egnored.

It does redirect back to the parents_Profile correctly. I belive that I removed ("") the redirect url value in the first insert correctly and have it at the end of the Update. But there must be something that I have not done correctly since it skips the Update.

[Ray Borduin]

It looks like you are using the Dreamweaver insert server behavior. Remove that and apply the insert server behavior from dataassist in its place.

[Kevin373660]

The original insert was generated using the Wizard. I removed the first insert and and added a replacment Insert block using Data Assist. After adding the concat code it inserts and updates the nickname fine. Awesome!

However:
1) There is a DW warning regarding the "Check New Username" server behavior. I believe that this is checking as if the kids have login (In this case the parent's have login and they are setting up the kids unique registration.) and may need to be removed and replaced/edited? Or did I miss something in the setup?

2) I am not clear on how to read and edit the insert syntax used by DataAssist. I need to edit the insert for the current registration date statement: 'NOW()'

"".((isset($_POST["kidRegDate"]))?$_POST["kidRegDate"]:"") ."" . "|" .
|',none,''|

3) Confirmation Email is sent with no value for the kidNickname. Just a blank. Suspect that the new concat variable needs to be stated so how and somewhere in the update code?

[Ray Borduin]

1) Not missing anything. It shows up as broken because it thinks it needs a DW Insert server behavior applied for it to work. It should probably still work on the page as long as the form fulfills the IF statement trigger (may need to add a properly named hidden form element).

So the short story is you can get it to work, but it will have the red exclamation and you can't edit it.

Another option is to remove it and use validation toolkit for the validation of the unique record instead. It allows a lot more flexability and is a good exercise to learn, but is kind of complex after just crossing one hurdle not sure if you are up for that challenge just yet but you can read about it on the forums where I have walked people through it.

2) In your database set that field as type timestamp and give it a value of CURRENT_TIMESTAMP and don't set a value for the field in the insert server behavior. mySQL will automatically insert the current date and time.

Another option is to set the value in the server behavior to:
<?php echo(date("Y-m-d h:i:s")); ?>

But I prefer the database default technique.

[Kevin373660]

Yes, the nickname function works! And the date is also working!

I ran into a problem with retaining/passing the parent's session values when I return from the kid's registration page back to the parent's. In DA should I have checked off "Pass original querystring?" What should I do?? I need to tackle this next.

For others reading this post. I used DataAssist to add the first insert server behavior and then a second update server behavior. I had to move the code blocks arround partly because the original file was created with a form and had used the Wizzard and it sets up some of the nessissary code.

I went Ray's server behavior sugestion to add the date. Just double click and open the DataAssist server behavior and set the value with the php code <?php echo(date("Y-m-d h:i:s")); ?>. In my case I wanted to record in my db table both the user LastVisitDate (TimeStamp-Current Date) and RegDate (Date-inserted through the server behavior.

I also had to edit the Universal Email file with both elements of the nickname.

[Ray Borduin]

I think I need a better description of the problem, but my guess the solution does involve passing parameters in the URL.

[Kevin373660]

I would prefer notto use the url to pass variables. Rather I am using session variables. From the parents_Profile links to kids_Registration. The parents session variables are retained ok at that point associate their identity with each new member and work ok with the confirmation email. However, after the email is sent the redirect goes back to the parents_Profile page. At that point the parents session variables are not being passed back. The page loads but without the parents values it can not find the parents record.

[Ray Borduin]

Are the session variables set? Do you have session_start() somewhere above where they are referred to?

[Kevin373660]

The session_start() is at the top of the page. From what I understand the sesion variables are set from the login. I can echo their values and they are ok unitl the redirect.

They were working fine until I changed replaced with the two insert/update using DataAssist. I also just ran a test with selecting the pass querystring option on the update server behaviour and it did not help.

[Ray Borduin]

If you write the session variable to the page immediately before the redirect with a die(); statement is it set at that point and just not being used on the next page?

If you just add session_start(); and die(); to the results page with the session variable value is it set? Maybe you just need to add a session_commit() before the redirect?

[Kevin373660]

I went back to the WAUE file that worked and edited the concat and have everything working ok at the moment. I had tired to add a link inside the email and must have done something without realizing it to that file. I am so pleased with this working! Thank you for your help Ray. Now on to the next challenge, member activation process.