Re: [PHP] Global Changes With Loop To Allow Nulls In A Table...
On Tue, Jan 27, 2009 at 5:24 PM, Boyd, Todd M. wrote: >> -Original Message- >> From: Chris [mailto:dmag...@gmail.com] >> Sent: Tuesday, January 27, 2009 4:04 PM >> To: Boyd, Todd M. >> Cc: php-general@lists.php.net >> Subject: Re: [PHP] Global Changes With Loop To Allow Nulls In A >> Table... >> >> >> >> The other responses should get you started if this is something you >> >> really want to do. However, I'll play devil's advocate here and just >> >> raise the question why you would want to make this change in the >> first >> >> place. I'm not quite as anti-NULL as a lot of arguments I've read >> >> against them, but I tend to agree that the number of columns that >> >> accept NULL values should be kept as small as possible. Even if you >> >> decide that you need to allow NULL values in some cases, IMHO I >> >> wouldn't write a script that ran through my entire database and >> opened >> >> every column in every table to accept. >> > >> > I just thought I'd throw this out there... >> > >> > A lot of people who post questions on this list are programming their >> algorithms and structuring their applications in a certain way because >> that's what the client wants, or that's what their boss told them to >> do. Yes, accepting NULL values in a database is frowned upon (unless >> the table is a transaction table)... but I doubt his boss or his client >> cares in the least. >> >> I don't understand what you mean about a "transaction table" - you >> should only use nulls if you understand what they do and why you'd need >> them in that particular case. I'd ask why and find specifically what >> they want/why they suggested it and make sure they understand the >> repercussions. > > A transaction table -- a table that is used to house the state of a > transaction. If the transaction is incomplete, some of its values will be > NULL. This is, of course, only one method for employing a transaction system. > There exist others that use many disparate tables for separate steps in the > transaction, but I've seen several that use one table with NULL columns for > steps that haven't yet been processed. > > > // Todd > Even so - I would think it undesirable (and dangerous) for EVERY column (including primary/foreign keys) in EVERY table to allow NULL values. Granted, I don't know the nature of the project here, nor the problem being addressed. I just wanted to raise a flag noting that this MAY not be a great idea in the event a self-described "newbie" hadn't considered the potential pitfalls he could be introducing. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Global Changes With Loop To Allow Nulls In A Table...
Thanks so much for everyone's help! I fooled with this and it was cool... In the mysql client, do a SHOW CREATE TABLE tableName\G And ultimately got it working with: - 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 ] Thanks again -- Thanks - RevDave Cool @ hosting4days . com [db-lists 09] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Global Changes With Loop To Allow Nulls In A Table...
Boyd, Todd M. wrote: -Original Message- From: Chris [mailto:dmag...@gmail.com] Sent: Tuesday, January 27, 2009 4:04 PM To: Boyd, Todd M. Cc: php-general@lists.php.net Subject: Re: [PHP] Global Changes With Loop To Allow Nulls In A Table... The other responses should get you started if this is something you really want to do. However, I'll play devil's advocate here and just raise the question why you would want to make this change in the first place. I'm not quite as anti-NULL as a lot of arguments I've read against them, but I tend to agree that the number of columns that accept NULL values should be kept as small as possible. Even if you decide that you need to allow NULL values in some cases, IMHO I wouldn't write a script that ran through my entire database and opened every column in every table to accept. I just thought I'd throw this out there... A lot of people who post questions on this list are programming their algorithms and structuring their applications in a certain way because that's what the client wants, or that's what their boss told them to do. Yes, accepting NULL values in a database is frowned upon (unless the table is a transaction table)... but I doubt his boss or his client cares in the least. I don't understand what you mean about a "transaction table" - you should only use nulls if you understand what they do and why you'd need them in that particular case. I'd ask why and find specifically what they want/why they suggested it and make sure they understand the repercussions. A transaction table -- a table that is used to house the state of a transaction. If the transaction is incomplete, some of its values will be NULL. This is, of course, only one method for employing a transaction system. There exist others that use many disparate tables for separate steps in the transaction, but I've seen several that use one table with NULL columns for steps that haven't yet been processed. Ah like a queue processing table? I understand what you mean now :) -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Global Changes With Loop To Allow Nulls In A Table...
> -Original Message- > From: Chris [mailto:dmag...@gmail.com] > Sent: Tuesday, January 27, 2009 4:04 PM > To: Boyd, Todd M. > Cc: php-general@lists.php.net > Subject: Re: [PHP] Global Changes With Loop To Allow Nulls In A > Table... > > > >> The other responses should get you started if this is something you > >> really want to do. However, I'll play devil's advocate here and just > >> raise the question why you would want to make this change in the > first > >> place. I'm not quite as anti-NULL as a lot of arguments I've read > >> against them, but I tend to agree that the number of columns that > >> accept NULL values should be kept as small as possible. Even if you > >> decide that you need to allow NULL values in some cases, IMHO I > >> wouldn't write a script that ran through my entire database and > opened > >> every column in every table to accept. > > > > I just thought I'd throw this out there... > > > > A lot of people who post questions on this list are programming their > algorithms and structuring their applications in a certain way because > that's what the client wants, or that's what their boss told them to > do. Yes, accepting NULL values in a database is frowned upon (unless > the table is a transaction table)... but I doubt his boss or his client > cares in the least. > > I don't understand what you mean about a "transaction table" - you > should only use nulls if you understand what they do and why you'd need > them in that particular case. I'd ask why and find specifically what > they want/why they suggested it and make sure they understand the > repercussions. A transaction table -- a table that is used to house the state of a transaction. If the transaction is incomplete, some of its values will be NULL. This is, of course, only one method for employing a transaction system. There exist others that use many disparate tables for separate steps in the transaction, but I've seen several that use one table with NULL columns for steps that haven't yet been processed. // Todd
Re: [PHP] Global Changes With Loop To Allow Nulls In A Table...
The other responses should get you started if this is something you really want to do. However, I'll play devil's advocate here and just raise the question why you would want to make this change in the first place. I'm not quite as anti-NULL as a lot of arguments I've read against them, but I tend to agree that the number of columns that accept NULL values should be kept as small as possible. Even if you decide that you need to allow NULL values in some cases, IMHO I wouldn't write a script that ran through my entire database and opened every column in every table to accept. I just thought I'd throw this out there... A lot of people who post questions on this list are programming their algorithms and structuring their applications in a certain way because that's what the client wants, or that's what their boss told them to do. Yes, accepting NULL values in a database is frowned upon (unless the table is a transaction table)... but I doubt his boss or his client cares in the least. I don't understand what you mean about a "transaction table" - you should only use nulls if you understand what they do and why you'd need them in that particular case. I'd ask why and find specifically what they want/why they suggested it and make sure they understand the repercussions. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Global Changes With Loop To Allow Nulls In A Table...
On Tue, Jan 27, 2009 at 12:58 PM, Boyd, Todd M. wrote:
>> -Original Message-
>> From: Andrew Ballard [mailto:aball...@gmail.com]
>> Sent: Tuesday, January 27, 2009 11:39 AM
>> To: revDAVE
>> Cc: php-general@lists.php.net
>> Subject: Re: [PHP] Global Changes With Loop To Allow Nulls In A
>> Table...
>>
>> On Tue, Jan 27, 2009 at 11:53 AM, 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(" Could not renumber
>> dB $sql
>> > " . mysql_error());
>> >
>> >
>> >$i++;
>> > endwhile;
>>
>>
>> The other responses should get you started if this is something you
>> really want to do. However, I'll play devil's advocate here and just
>> raise the question why you would want to make this change in the first
>> place. I'm not quite as anti-NULL as a lot of arguments I've read
>> against them, but I tend to agree that the number of columns that
>> accept NULL values should be kept as small as possible. Even if you
>> decide that you need to allow NULL values in some cases, IMHO I
>> wouldn't write a script that ran through my entire database and opened
>> every column in every table to accept.
>
> I just thought I'd throw this out there...
>
> A lot of people who post questions on this list are programming their
> algorithms and structuring their applications in a certain way because that's
> what the client wants, or that's what their boss told them to do. Yes,
> accepting NULL values in a database is frowned upon (unless the table is a
> transaction table)... but I doubt his boss or his client cares in the least.
>
> My 2 cents. ;)
>
>
> // Todd
>
Understood. I just thought I'd raise the question since the OP is says
he is a PHP newbie (and based on his previous posts I'm guessing a
MySQL newbie as well, though I could be mistaken) just to make sure
the question (and its implications) has been at least considered
before making such a sweeping change.
FWIW, I would also be a little cautious about a client who asked me to
allow every column in every table accept NULLs.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Global Changes With Loop To Allow Nulls In A Table...
> -Original Message-
> From: Andrew Ballard [mailto:aball...@gmail.com]
> Sent: Tuesday, January 27, 2009 11:39 AM
> To: revDAVE
> Cc: php-general@lists.php.net
> Subject: Re: [PHP] Global Changes With Loop To Allow Nulls In A
> Table...
>
> On Tue, Jan 27, 2009 at 11:53 AM, 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(" Could not renumber
> dB $sql
> > " . mysql_error());
> >
> >
> >$i++;
> > endwhile;
>
>
> The other responses should get you started if this is something you
> really want to do. However, I'll play devil's advocate here and just
> raise the question why you would want to make this change in the first
> place. I'm not quite as anti-NULL as a lot of arguments I've read
> against them, but I tend to agree that the number of columns that
> accept NULL values should be kept as small as possible. Even if you
> decide that you need to allow NULL values in some cases, IMHO I
> wouldn't write a script that ran through my entire database and opened
> every column in every table to accept.
I just thought I'd throw this out there...
A lot of people who post questions on this list are programming their
algorithms and structuring their applications in a certain way because that's
what the client wants, or that's what their boss told them to do. Yes,
accepting NULL values in a database is frowned upon (unless the table is a
transaction table)... but I doubt his boss or his client cares in the least.
My 2 cents. ;)
// Todd
Re: [PHP] Global Changes With Loop To Allow Nulls In A Table...
On Tue, Jan 27, 2009 at 11:53 AM, 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(" Could not renumber dB $sql
> " . mysql_error());
>
>
>$i++;
> endwhile;
>
>
> Thanks in advance
>
>
>
> --
> Thanks - RevDave
> Cool @ hosting4days . com
> [db-lists 09]
The other responses should get you started if this is something you
really want to do. However, I'll play devil's advocate here and just
raise the question why you would want to make this change in the first
place. I'm not quite as anti-NULL as a lot of arguments I've read
against them, but I tend to agree that the number of columns that
accept NULL values should be kept as small as possible. Even if you
decide that you need to allow NULL values in some cases, IMHO I
wouldn't write a script that ran through my entire database and opened
every column in every table to accept.
Just my 2 cents.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Global Changes With Loop To Allow Nulls In A Table...
>> 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(" Could not renumber dB $sql
>> " . 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.
Re: [PHP] Global Changes With Loop To Allow Nulls In A Table...
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(" Could not renumber dB $sql
> " . 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
