On 5/18/05, James Keeline <[EMAIL PROTECTED]> wrote: > It could be risky to drop a column and then add it back in like you are > trying to do. The SQL "ALTER TABLE ..." syntax is used to add or drop > columns or change their data type or add an index, etc. For mass rewriting > of a column value, like you seem to be trying to do, something like "UPDATE > ... SET" may be a better choice. <
Yes Summary :- I want my auto-increment field to be 'tidy' IE - When records/lines are added / deleted - The unique reference number just increases & increases..... - & when records are deleted - Its got gaps.... - Im future-proofing my script now - instead of making changes later... its not used (much) now - Except by me - So it doesnt matter (much) if i screw things up... - But If I screw things up later - when its heavily used - Thats not good.. > Normally an INT datatype is given a size of 11 to represent the number of > characters in the largest value (roughly 2.1 billion). It actually uses 4 > bytes to store this integer number. If you use "UNSIGNED INT" as your > datatype you can have values from 0 to roughly 4.3 billion. < CHANGED :- $query = "ALTER TABLE Events ADD EventID MEDIUMINT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (EventID);"; Ive taken the unsigned thing off & changed to Medium int. >> I don't think you can set an arbitrary length of an INT field like you can with VARCHAR (not you can't with TEXT either). The length is related to the number of bytes used to store the value. < No length mentioned ? No worries > From your latest example, it seems that you are trying to renumber the value > in a field. As you have discovered, dropping and readding the column won't > do this. Nothing in your queries has actually placed a value in that column. < (ive posted 2-3 messages about this - Not sure what youve seen... Heres the latest :- ============= $query="ALTER TABLE Events DROP EventID"; $result=mysql_query($query) or die( "Unable to view data1\n" . mysql_error()); $query = "ALTER TABLE Events ADD EventID MEDIUMINT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (EventID);"; $result=mysql_query($query) or die( "Unable to view data2\n" . mysql_error()); ============= >That is what statements like UPDATE are used for. Since you are trying to get a unique value for each input, you will probably want to SELECT the entire table and then run an UPDATE on each row found. Are we talking about dozens, hundreds, thousands, or millions of rows here? < For now - Its only a test script - Ive got maybe a dozen entries right now - But yesterday, around 200.... When its fixed, & ive done the website for it (basically its all done via email now - No website for it yet..) - & more users use it... - i'll get more & more entries / records.. > I think it is possible to use some arithmetic in the "UPDATE ... SET" syntax. > I haven't tried this, however. Well - Ive wasted 5 or so hours trying to get it fixed - & the above script does work :) (different people giving different things. ..) - I'll work on your idea during the week - Ps - I havnt done it yet, But im going to add my above script as a cron-job (weekly).... - Is the above script unsafe ?? Ok -Im off to bed - Goodnight.... PS - If the above is unsafe, - I acn use ALTER TABLE tbl_name AUTO_INCREMENT = 100 http://dev.mysql.com/doc/mysql/en/example-auto-increment.html & a 'slow' cron-job to find the last record, & re-number that 1 record to a smaller number & reset the auto-increment... (but thats a lot slower / more processing) Ok Im off to bed -- G Stewart NZ: [EMAIL PROTECTED] NZ Bus Website http://busnz.kwister.com/ - Updated Community email addresses: Post message: [email protected] Subscribe: [EMAIL PROTECTED] Unsubscribe: [EMAIL PROTECTED] List owner: [EMAIL PROTECTED] Shortcut URL to this page: http://groups.yahoo.com/group/php-list Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/php-list/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
