hi all, does anybody can tell me how to insert data to specific id which mean that i want to store record to mysql which is not follow the last link identifier "ID"
Currently my database had 10 record, if i insert next data to mysql which the key id will increment to 11, but i want store the record to key id 20, leave the 11-19 blank.
Is it possible ?
The other question is how to override the record and how to prompt alert before override.
If we're talking about an auto-incrementing integer that is the primary key, there is nothing stopping you from specifying a new (so far unused) value for it. Perhaps the real question would be, "why?" Normally such a field's only purpose is to provide a unique identifier for that row, not to have any "meaning" such that it matters what the number is. If it does matter for you, it may be that you either need a separate field for that purpose, or else it should not be an auto-increment field in the first place.
yup...you are right absolutely
i will create other field to do that.
how about update/override specific record
for example the table contain :
Code Color LabelID 10 AC1010 Red 1
ID 11 AC1011 Green 2
ID 12 AC1012 Orange 3
ID 13 AC1013 Green 5
In case i want to update/override the record in ID 12
what is the code look like
Depends on your specific needs. First thought that comes to my mind is to use the INSERT ... ON DUPLICATE KEY UPDATE Syntax.
EDIT:
tommyy wrote:i will create other field to do that.
If this "ID" value you are getting is a unique identifier (determined by your specific application/data/source/etc.), then there's no need to create a separate column. You can still use this ID column as the PRIMARY key for your table... it just sounds like it shouldn't be an AUTO_INCREMENT column as well (since the ID value isn't arbitrary and is tied to a specific set of data, thus an automatically generated ID value wouldn't be appropriate).
can i modify the record base on searching the code column
i mean that the statement where ....code = ??
is it possible
what is wrong in this statement
mysql_query("INSERT INTO example (code, color, label) WHERE code = AC1010 values (AC2222, white, 60) ON DUPLICATE KEY UPDATE");
INSERTs do not have a WHERE clause. Also, the literal string values must be quoted in the query.
$query = "INSERT INTO example (code, color, label) values ('AC2222', 'white', 60) ON DUPLICATE KEY UPDATE";
how does the system know which record should be update/override if there is no where clause ?
how can i update/override the record in code AC1012 for below instance ?
ID Code Color Label
ID10 AC1010 Red 1
ID11 AC1011 Green 2
ID12 AC1012 Orange 3
ID13 AC1013 Green 5
thanks NogDog
tommyy wrote:how does the system know which record should be update/override if there is no where clause ?
It knows which record needs to be updated because the ID column should have a UNIQUE or PRIMARY key. Thus, when you attempt to INSERT a row with the same ID value, the unique constraint will fail causing the INSERT statement to fail because of the existing row. The "ON DUPLICATE KEY UDPATE ..." clause is then used to update that existing row with different data.
Read the MySQL manual page I linked to above - it explains all this and more (and even includes example queries).
tommyy;10955582 wrote:how does the system know which record should be update/override if there is no where clause ?
how can i update/override the record in code AC1012 for below instance ?
ID Code Color Label
ID10 AC1010 Red 1
ID11 AC1011 Green 2
ID12 AC1012 Orange 3
ID13 AC1013 Green 5thanks NogDog
What you want is to UPDATE not INSERT, so you don't use INSERT.
Tonata;10955716 wrote:What you want is to UPDATE not INSERT, so you don't use INSERT.
No, what he wants to do is an INSERT (specifically, with the "ON DUPLICATE KEY UPDATE" clause added onto the end). Read the entire thread next time. 