Mysql Auto-increment conditional on a table column

Asked

Viewed 336 times

1

Hello,

I’m trying to create a vehicle table that looks like this with php and mysql:

+----+-----------------+-------+--------+
| id | nome do veiculo | marca | codigo |
| 1  | Gol             | VW    | 1      |
| 2  | Golf            | VW    | 2      |
| 3  | Fox             | VW    | 3      |
| 4  | Palio           | Fiat  | 1      |
| 5  | Argo            | Fiat  | 2      |
+----+-----------------+-------+--------+

Where id has auto increment and code would be added as the vehicle number of the brand.

Ex: If one more vw car came in it would get id 6 and code 4. If it was fiat it would get id 6 only code 3.

I tried to make the value of the code as follows:

$saddles = mysqli_query($connection, "SELECT id FROM vehicles Where brand='$brand'"); $codveiculo = 1 + mysqli_num_rows($selveiculos);

Where I check the amount of vehicles of an X mark and make the code be the number returned.

My problem is that I am taking the data from the vehicles with a $.ajax function that already goes straight to this php code to insert into the database. So what’s happening is to create equal codes for vehicles of the same brand.

You could create the id with auto-increment and code with auto-increment but related to the brand?

Any suggestions to resolve this?

Thank you

php javascript mysql

  • I think it’s best to separate the information into two tables, veiculo would have id, nome and id_marca and marca would have id and nome_marca or marca

  • This way the problem would continue because the id would be auto-increment and would be with values 1,2,3,4,5. and not the amount of vehicles per brand. Example: Started by inserting 10 fiat cars, then the id would go from 1 to 10. Ai start adding vw car , in this case the id would start from 11 and not from 1 as I would like

1 answer

1

You cannot have an AUTOINCREMENT field with two equal values. A solution would be a query to get the next id of that tag

SELECT MAX(codigo)+1 AS proximo_codigo WHERE marca = :IDMARCA

Browser other questions tagged php javascript mysql

You are not signed in. Login or sign up in order to post.