Workaround for have 2 column with pseudo AUTO INCREMENT This is the situation :
the table order has a column with ID AUTO INCREMENT but (there is always a but)
i can't use it because i don't want number order 1234567 after 5 years but i want 1/2014 , 1/2015 etc etc.
For doing this i have created this columns into the table :

ID AUTO INCREMENT &lt;--- unique ID used for manage the order after the insert  
numberorder INT &lt;--- fake number of order showed at the user NOT unique  
IDyear YEAR &lt;--- the year for the order e.g. 2014 or 2015


For doing this i write this code before the query for insert a new order
and processed at the submit form :

$query_Recordset4 = "SELECT MAX(IDyear) AS maxyear FROM order";
$Recordset4 = $mysqli-&gt;query($query_Recordset4);
$row_Recordset4 = $Recordset4-&gt;fetch_assoc();
$maxyear = $row_Recordset4['maxyear'] ;
$thisyear = date("Y") ;

if ($maxyear === $thisyear) {
    $query_Recordset5 = "SELECT MAX(numberorder) FROM order WHERE IDyear=$thisyear ;";
    $Recordset5 = $mysqli-&gt;query($query_Recordset5);
    $row_Recordset5 = $Recordset5-&gt;fetch_assoc();
    $numberorder = $row_Recordset5['MAX(numberorder)'] + 1 ;
}
else {  
    $numberorder = 1 ;
}


And then i use $numberorder for insert them into the table order in his column.

QUESTION REVIEW

Maybe my question was not clear written and now i try to add some elements.

Thit is what i have now into the table order :

|----------|-------------|--------|
|    ID    | numberorder | yearId |
|----------|-------------|--------|
!    1     |     1       |  2014  |
|----------|-------------|--------|
|    "     |     "       |    "   |  &lt;---- more orders
|----------|-------------|--------|
|   500    |    500      |  2014  |  &lt;---- last order year 2014
|----------|-------------|--------|


When the year change what i need is this :

|----------|-------------|--------|
|   501    |     1       |  2015  |  &lt;---- first order year 2015 (2016,2017 etc etc)
|----------|-------------|--------|


Like a suggestion, my code has a concurrency problem.

Have a second table yearid with another AUTO_INCREMENT field
for take to them a unique ID for numberorder field is perfect solution but when the year finish i can't restarting the AUTO_INCREMENT at 1 because for security reason the TRUNCATE and DROP commands are not available for the user created for the connection.

The only way i see is create a new table each year e.g. yearid2015 for have ID = 1.
$query_Recordset4 = "SELECT MAX(IDyear) AS maxyear FROM order";


Whenever you need to postfix the name of a variable, class or anything else with number because you already used that name...you're doing it wrong. Either your function does too much and should be split into smaller functions, or you need to choose better names.



$query_Recordset5 = "SELECT MAX(numberorder) FROM order WHERE IDyear=$thisyear ;";


Even when you declare the variable, you should make a habit out of using parameterized queries.



There's something wrong about your table layout. At the moment I imagine it should look something like this:

TABLE order
    numberorder INT
    IDyear INT


A more sane layout would be this:

TABLE order
    id INT AUTO_INCREMENT
    idYear INT
    orderedAt DATETIME


The main difference is that I rely on an ID field which is managed by the database. It does not matter what the accountant wants or how your boss thinks the billing numbers should look like, you need something unique to identify a record and nothing beats a unique, automatically managed counter for doing that job.

Creating the necessary number you want is as easy as

SELECT CONCAT(idYear, '/', YEAR(orderedAt)) FROM order;


and if you place this in a stored procedure or in a function in your object you never need to think about it again. Incrementing this number is also rather easy:

SELECT IFNULL(MAX(idYear), 0) + 1 FROM order WHERE YEAR(orderedAt) = yourYear;


It can not break and you're free to put the date of the order into the past or the future (which is sometimes needed in accounting). In your method working outside of the current year will break the numbering.



Ideally you would set that number if you insert the record into the database to minimize race-conditions (and possible duplicate numbers).