>> 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
>--
You can also retrieve the field types with:
SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'whatever';
Which might help the programmatic approach.
