>> 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.

Reply via email to