--- Gordon Stewart <[EMAIL PROTECTED]> wrote: > On 5/17/05, Pete <[EMAIL PROTECTED]> wrote: > > > ALTER TABLE DROP ... or something similar. > > > > I have never used it in a script, so I am not sure. > > Yep... > > Ive got this script going :- > > > > ============ > > $query="ALTER TABLE Events DROP EventID"; > $result=mysql_query($query) or die( "Unable to view data1\n" . > mysql_error()); > $num_rows = mysql_num_rows($result); > echo "$num_rows Rows - 55556A\n\n\n\n"; > > $query = "ALTER TABLE Events ADD EventID int(5);"; > $result=mysql_query($query) or die( "Unable to view data2\n" . > mysql_error()); > $num_rows = mysql_num_rows($result); > echo "$num_rows Rows - 55556b\n\n\n\n"; > > =================== > > > that part is going... - > > However "EventID' is now blank... - with int(5) setting.. > > How do I add / make EventID - - I HAVE made it 'Unique' - However I > cant seem to get the auto_increment setting to be activated on it... > > (Ive tried various combinations of commands - from 3-4 different sites > - So below - may not set the 'Uniqe' ... ) > > Latest lines :- > > ========== > > > $query = "ALTER TABLE Events CHANGE COLUMN `EventID` int(5) NOT NULL > AUTO_INCREMENT;"; > $result=mysql_query($query) or die( "Unable to view data3\n" . > mysql_error()); > $num_rows = mysql_num_rows($result); > echo "$num_rows Rows - 55556c\n\n\n\n"; > > I'll try more combinations... - But im following examples from > tutorial websites.... > > PS the error im getting is :- > > ============== > check the manual that corresponds to your MySQL server version for the > right syntax to use near 'int(5) NOT NULL AUTO_INCREMENT' at line 1 > ============== > > -- > > G Stewart > Gmail : [EMAIL PROTECTED]
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. 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. 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. There's TINYINT which uses 1 byte and can store values from -128 to 127 (0 to 127 if a primary key) and SMALLINT uses 2 bytes, MEDIUMINT uses 3 bytes, and BIGINT uses 8 bytes. I normally use INT and forget about the size unless I know the values are bigger. Here are the definitions. http://dev.mysql.com/doc/mysql/en/numeric-types.html >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. 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? I think it is possible to use some arithmetic in the "UPDATE ... SET" syntax. I haven't tried this, however. James _____ James D. Keeline http://www.Keeline.com http://www.Keeline.com/articles http://Stratemeyer.org http://www.Keeline.com/TSCollection http://www.ITeachPHP.com -- Free Computer Classes: Linux, PHP, etc. Spring Semester Begins Jan 31 -- New Classes Start Every Few Weeks. 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/
