On 27 Jan 2009 at 8:53, revDAVE wrote:
> Hi Folks,
>
> Newbie question....
>
> I have a mysql table with 100 fields, currently all do not allow nulls.
> Rather than hand typing in phpMyAdmin, I would like a way to loop through
> all fields and update them to allow nulls....
>
> My Beginning attempt needs help...
>
>
> $i = 1;
> while ($i <= 100):
>
> // how do I word this to just change whatever field we are on to allow
> nulls?
>
> $sql = 'ALTER TABLE `mytable` ?*update*? `'.$???WhatEverField??[$i].'`
> ?ALLOWNULL?;';
>
> //mysql_query($sql);
>
> $result = mysql_query($sql) or die("<br /><br /> Could not renumber dB $sql
> <br /><br />" . mysql_error());
>
>
> $i++;
> endwhile;
>
>
> Thanks in advance
Hi,
The MySQL syntax to alter a column is:
ALTER TABLE `table` MODIFY `column` BIGINT NOT NULL;
[ http://dev.mysql.com/doc/refman/5.1/en/alter-table.html ]
The sql statement
SHOW COLUMNSFROM `table`;
[ http://dev.mysql.com/doc/refman/5.1/en/show-columns.html ]
will give you a list of all the fields with there type, default values, null etc...
You can then use this in the loop to find all the fields where null=NO.
Warning from the manual:
When you change a data type using CHANGE or MODIFY, MySQL tries to
convert existing column values to the new type as well as possible.
Warning
This conversion may result in alteration of data. For example, if you shorten a
string column, values may be truncated. To prevent the operation from
succeeding if conversions to the new data type would result in loss of data, enable
strict SQL mode before using ALTER TABLE (see Section 5.1.6, “SQL Modes”).
I don't think this will affect you but bare it in mind.
Regards
Ian
--
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
