Updating right after an insert, in the same script

[kreut]

Hello,

 

I'm trying to write my first database using SQL and PHP, without much luck, I'm afraid.  After inserting a new record (for which I didn't have a problem), I wanted to automatically generate a username which is the person's first name (they'll then be able to change it later).  Trying to use Dreamweaver as a model, I've come up with this:

 

$updateSQL = sprintf("UPDATE users SET username=%s",

                      GetSQLValueString($_POST['first_name'], "text"))

 

Well...it does update the username, but it does so on ALL of the records as opposed to the one that was just inserted.  Any help would be appreciated...

 

Thank you.

[Maq]

You need to specify a WHERE conditional to only UPDATE the row(s) you want.  Please refer to the manual:

http://dev.mysql.com/doc/refman/5.0/en/update.html

[Pikachu2000]

Why run an update immediately after the insert? Why not just insert it all at once, since you apparently already have the data needed?

[kreut]

  Thanks for pointing me in that direction.  I tried:

 

 

  $updateSQL = sprintf("UPDATE users SET username=%s",

                  GetSQLValueString($_POST['email'], "text"), "WHERE first_name = 'Bob'");

 

But I still am getting the same issue.  Any other others would be appreciated..

[Pikachu2000]

Where is your initial INSERT query?

[kreut]

It starts like this:

 

((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form2")) {

  $insertSQL = sprintf("INSERT INTO users (first_name, last_name, user_type, email, school_id,username) VALUES (%s, %s, %s, %s, %s, %s)",

                      GetSQLValueString($_POST['first_name'], "text"),

                      GetSQLValueString($_POST['last_name'], "text"),

                      GetSQLValueString($_POST['user_type'], "text"),

                      GetSQLValueString($_POST['email'], "text"),

                      GetSQLValueString($_POST['school_id'], "int"),

  GetSQLValueString($_POST['username'], "text"));

 

  mysql_select_db($database_connalgebra, $connalgebra);

  $Result1 = mysql_query($insertSQL, $connalgebra) or die(mysql_error());

 

then I do my update:

 

$updateSQL = sprintf("UPDATE users SET username=%s",

                  GetSQLValueString($_POST['email'], "text"), "WHERE first_name = 'Bob'");

                     

                           

  $Result2 = mysql_query($updateSQL, $connalgebra) or die(mysql_error()); 

 

***Truth be told, my ultimate goal is to create a username for the user as a concatenation of the new user_id (which my DB creates in the Insert step?) and the first name to avoid any username duplicates. 

 

Any thoughts would be welcome!

 

 

[Pikachu2000]

For a much more user-friendly way to avoid duplicates, just run a SELECT COUNT() query against the user's choice of username. If no results are returned, the username is available, otherwise display an error. Also a good idea is to create a UNIQUE index on the username field in the DB, just in case two users are trying to register the same username at about the same time.

[kreut]

Sorry if didn't make total sense:

 

I'm the administrator of the site.  Users are going to have to get my permission to use the site (it won't be an automated process since I have to check their credentials).  I'll then input their first name, last name, email, at which point I'd like my database to insert a new record (this works!) and generate a starting username for them.  I'm hoping to base this username on their name and the index of the record to ensure uniqueness, such as Fred121.  With this in mind, I think that the method I'm using should, in theory, work.  But, alack, and alas, I can't seem to get the database to change just one specific username.  :'(  Any thoughts?

[PFMaBiSmAd]

The WHERE clause you added to the UPDATE query, wasn't actually a part of the query.

[kreut]

Could you please tell me the correct syntax to make it a part of the query?  This is literally my first week with databases.  Sorry if it's such a basic question.

[Pikachu2000]

After the initial INSERT query, get the value of the autoincrement index field with mysql_insert_id, then use that value for the UPDATE query.

 

// right after INSERT query . . .
$id = mysql_insert_id();

// Then the next query string would be similar to:
$query = "UPDATE `table` SET `username` = CONCAT(`username`, $id) WHERE `index_id` = $id";

 

However, if you're going to allow the user to change their username in the future, as you indicated, you'll still need to implement to protections noted above at some point.

[kreut]

Thanks so much! It finally worked! 

 

Enjoy your day....