Concatenating a string and primary key Id while inserting

by

If user_id is an AUTO_INCREMENT primary key, then you can’t do this with a single statement, even if you use a trigger.

The problem is that the AUTO_INCREMENT value isn’t generated until after the BEFORE INSERT trigger runs, but you can’t change username in the AFTER INSERT trigger.

So you just have to do the INSERT, then immediately do an UPDATE.

If user_id is not an AUTO_INCREMENT, but instead is something you specify yourself, then it’s easy, you just do the concatenation in your PHP code before you pass the values as parameters.

Update: You can’t do it with MySQL 5.7 generated columns either. It results in this error when you try to create the table:

Generated column ‘username’ cannot refer to auto-increment column.

More Related Contents:

  1. php+mysql Fatal error: Call to undefined function mysql_connect()
  2. drop down menu using php and mysql [closed]
  3. php code for multiple search box after connecting to mysql [closed]
  4. Checking the availability of rooms
  5. How to sort user data by id
  6. mysql_fetch_array()/mysql_fetch_assoc()/mysql_fetch_row()/mysql_num_rows etc… expects parameter 1 to be resource
  7. How to properly set up a PDO connection
  8. Using PHP 5.5’s password_hash and password_verify function
  9. Example of how to use bind_result vs get_result
  10. Search with comma-separated value mysql
  11. mysqli::query(): Couldn’t fetch mysqli
  12. How to do this in Laravel, subquery where in
  13. I have an array of integers, how do I use each one in a mysql query (in php)? [duplicate]
  14. Levenshtein: MySQL + PHP
  15. PHP MySQLI Prevent SQL Injection [duplicate]
  16. mysql PDO how to bind LIKE
  17. How to test if a MySQL query was successful in modifying database table data?
  18. jQuery UI Sortable, then write order into a database
  19. How do i “echo” a “Resource id #6” from a MySql response in PHP?
  20. Does mysql_real_escape_string() FULLY protect against SQL injection?
  21. Delete multiple rows by selecting checkboxes using PHP
  22. How can I solve incompatible with sql_mode=only_full_group_by in laravel eloquent?
  23. PHP, MySQL and Time Zones
  24. IP address storing in MySQL database using PHP [duplicate]
  25. “No such file or directory” or “No such host is known” when running migrations
  26. Using LIKE in bindParam for a MySQL PDO Query [duplicate]
  27. Password_Hash not working on my PHP login
  28. Select last 20 order by ascending – PHP/MySQL
  29. Formatting an SQL timestamp with PHP
  30. mysqli – fetch_Array error call to a member function fetch_array() on a non-object mysqli [duplicate]

Leave a Comment