problem with mysql database

Anon

 In my database I have an autoincrement field.The id is incremented but the position of the records is changed.Should the record pointer be moved explicitly after every insertion If so how to achieve that?

Anon

how do you mean "the position of the record is change"?

Anon

I mean that I've inserted 8 records into the database.Then I did some manual updation.Then I tried inserting values now the 10th record inserted is next to 8th instead of being the last record in the database. so the order is 1..8,10,9 and so on.Should the manual updation be avoided?Is there any alternative?

Anon

In MySQL, and auto_increment fields means that when you INSERT, the inserted value of that field is allways one higher than the maximum value found in that column.

This does not mean that the rows will allways be sorted by that column.
If you want to sort rows by the column that is of the auto_increment type,
add " ORDER BY name_of_auto_increment_column" to your statement.

If you want to UPDATE fields manually, don't supply a value for the auto_increment field.

Anon

THe beauty of RDBMS(mysql) is that the data should NOT be sorted for maximal efficency in the table .. use Order by in your SELECT statement

Anon

I have a problem... I'm using PHP3 and I need to upload a tab delimited text file, and match the apropriate coloum in the MySQL table. when I upload a tab delimited text file (using a program called mascon 2000), I get an error saying "filed incorrect" ... because all coloums are being entered in to the first field. ... to add to the correct coloum, I will need to add a " ", (start/end quotes and a colma <-- don't know how to spell :-) ... I can't do this every time I need to upload the data..

how do I program a script that will take a tab delimited file, and modify it and/or place the correct tab delimited fields into the appropriate coloum???? PLEASE HELP!! ANYONE!! -thanxxx, eyal.

Anon

Correctus.

Anon

You can upload tab-delimited files directly into a mysql database,
provided your table in the database has the same columns as your textfile.

Read the mysql manual about mysqlimport.

You can use the PHP function called EXEC to start the mysql import.

Also, you can read the file in PHP, and use the "explode" or "serialize" function
to seperate the fields in the textfile into seperate variables.
You can then put these variables into a mysql statement and insert them into a database.

Anon

When you do these manual inserts of data, are you dumping the database to a file and then adding the data and then rebuilding the database. I have never had your problem when I dumped then added and then rebuilt. I know it is not a problem of sorting, but I know that what someone else said is true, it is not written anywhere that the id field is to be in ascending order. By dumping with the command--> c:\mysql\bin\MYSQLDUMP 'databasename' > dumpinfo.txt , you get a text file with all the database structure listed:

CREATE TABLE table1 (
id int(20) NOT NULL auto_increment,
user char(15) NOT NULL,
etc....
.....

....

and so on with all the data after that in comma delimited strings:

INSERT INTO table1 VALUES('1','b','c');
INSERT INTO table1 VALUES('2','b','c');
etc.......

I just add the data I need or will be needing for testing on to the insert into's and put it all back together with a:

MYSQL DATABASENAME < dumpedinfo.txt

away you go!
When you go to add another record the regular way, the last id value is pulled when a null value is attempted to be inserted into a NOT NULL mysql field ID CHAR(22) NOT NULL and mysql does the rest,increment the value and insert the new value in the new record. I never have problems, it takes up right where it should.

Anon

And what if your database holds 50k records?
:-)

Anon

Obviously if you have 50k records that would be crazy, but what may I ask are you doing adding data or wanting to add data manually with all those records. The data you want to add needs to go somewhere in the database other than at the end because why? Because the id value is 24 and not 300 for example???
What difference does that make to anything but when the records are being viewed with out being queried using a sort by statement???

I don't understand??

Anon

I can have 100 reasons for wanting to update a single record in my table of 50k manually.

The point is that if you update a record manually you can enter a new value for the auto_increment field, that overrules the "auto_increment" property of the field.

(I can delete record with ID 8, and manually insert a new record with ID 8.
But, it will appear at the end of the table, not between 7 and 9)

You should ofcourse never manually change the value of an auto_increment field unless you have a special reason for it.

You should also never depend on the sorting of records inside the table.
Instead, the sorting should be done by your query.

Anon

Vincent wrote:

I can have 100 reasons for wanting to update a single record in my table of 50k manually.

Update is different than adding a record Vincent. If you truly do an update then you would not delete record 8 you would just update it thereby leaving it where it is. You would not change the id value because from what I gathered from what you stated you want the record there in 8, so update all the other fields except the id. Am I missing something here or is that entirely possible?

The point is that if you update a record manually you can enter a new value for the auto_increment field, that overrules the "auto_increment" property of the field.

(I can delete record with ID 8, and manually insert a new record with ID 8.
But, it will appear at the end of the table, not between 7 and 9)

You should ofcourse never manually change the value of an auto_increment field unless you have a special reason for it.

You should also never depend on the sorting of records inside the table.
Instead, the sorting should be done by your query.

Anon

Jokingone.

I know update and insert are different. I'm not the one who asked the original question.

The original problem was that if you manually change records (either through update or insert or delete) it is possible to change the order of the numbers in the auto_increment column.

Anon

I have found out the solution.The reason after autoincrementing at any one point if u manually do some operation in the database then the increment sequence though exists,the record pointer remain at the point of updation itself.So we should move the record to the last insertion point if at all you want the last record in the database to be the recent one inserted.This is done using the command mysql_data_seek().