How to make a text field NULL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
charp
Forum Commoner
Posts: 85
Joined: Sun Oct 26, 2003 3:00 pm
Location: Rancho Cucamonga, Calif. USA

How to make a text field NULL

Post by charp »

I guess this is really a MYSQL question, but I'll ask any how. Let me know if this is not the right place to post such a question.

Below is the code that I'm using to create a table in a MYSQL database. I'd like to set the "complete" field to have a default value of NULL.

Code: Select all

$installIt = mysql_query("
CREATE TABLE $table (
  id int(5) NOT NULL auto_increment,
  title varchar(60) default NULL,
  date varchar(10) default NULL,
  full text,
  complete text,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  FULLTEXT KEY title (title,full)
) TYPE=MyISAM;", $db);
I tried this with no luck:

Code: Select all

complete text default NULL,
I can see how to work it if the field type was varchar(), but this field is likely to have far more than 255 characters, which I think is the maximum for the varchar() type.

A second question: Could anyone explain the following line from that code snippet above:

Code: Select all

FULLTEXT KEY title (title,full)
Thanks in advance!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

if you set an default value it will be used for inserts that do not provide a value for this specific field. Setting NOT NULL makes a values mandatory, by providing either a default value or a value in the insert statement. If you do not declare the field as NOT NULL it can be skipped
try

Code: Select all

CREATE TABLE mytable (
  id int(5) NOT NULL auto_increment,
  title varchar(60) NOT NULL default 'no title',
  date varchar(10) NOT NULL default 'no date',
  full text,
  complete text,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  FULLTEXT KEY title (title,full)
) TYPE=MyISAM;
and

Code: Select all

insert into mytable (date) values(Now())
you should get an table entry like
id,title,date,full,complete
1,no title,2003-10-29,NULL,NULL
btw: if the field `date` holds a date why not make it a date-field of some kind?

for the second question: http://www.mysql.com/doc/en/Fulltext_Search.html ;)
User avatar
charp
Forum Commoner
Posts: 85
Joined: Sun Oct 26, 2003 3:00 pm
Location: Rancho Cucamonga, Calif. USA

Post by charp »

OK. Let's see if I have this straight:

If the field is set to NOT NULL, then an entry is required. This make sense for my date and title fields that must be filled in.
If you do not declare the field as NOT NULL it can be skipped
Is this the same as saying that a field set to NULL may be skipped or left blank??? If yes, that's what I want for my "complete" field. But I still don't know how to set that field to NULL.

As for my second question, I had read that section of the MYSQL site on full-text indexing and searching. It's the syntax that has me confused. The MYSQL site has an example:

Code: Select all

FULLTEXT (body, title)
which I take to mean that the body and title fields are available for searches. My code:

Code: Select all

FULLTEXT KEY title (title,full)
doesn't follow the same pattern as the previous example.

I'm wondering if I even need that line when creating my table. Under what circumstances would the FULLTEXT line not be required? Any simple examples?

Thanks again. I'm going to experiment with your suggested changes. :D
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

ok, let's take the table definition again

Code: Select all

CREATE TABLE mytable (
  id int(5) NOT NULL auto_increment,
  title varchar(60) NOT NULL default 'no title',
  date varchar(10) NOT NULL default 'no date',
  full text,
  complete text,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  FULLTEXT KEY title (title,full)
) TYPE=MyISAM;
a complete insert would be something like

Code: Select all

INSERT into mytable (id, title, date, full, complete) values (1, 'the title', 'the date', 'full description', 'complete status')
now you don't usually insert the id field, it's an unique,auto-increment index

Code: Select all

INSERT into mytable (title, date, full, complete) values ('the title', 'the date', 'full description', 'complete status')
and the database automagically assigns a value to that field. Now take out the `complete` field

Code: Select all

INSERT into mytable (title, date, full) values ('the title', 'the date', 'full description')
the database notices that two fields are missing, id and complete. id is assigned as before. For `complete` it performs a look up to see wether there is an default value bound to the field. There isn't but it is allowed to be NULL => NULL is assigned.
You can set the value NULL manually if you must

Code: Select all

INSERT into mytable (title, date, full, complete) values ('the title', 'the date', 'full description', NULL)
about FULLTEXT KEY ... I don't know what it does. I guess although the fulltext search includes both fields (title & full) the hash is built only by the value of title; or maybe it's an additional index...
User avatar
charp
Forum Commoner
Posts: 85
Joined: Sun Oct 26, 2003 3:00 pm
Location: Rancho Cucamonga, Calif. USA

Post by charp »

volka, Thanks so much for taking the time to lay things out in such a clear and concise manner.

The purpose of all these questions goes back to previous post of mine that you answered:
viewtopic.php?t=13993&highlight=
I'm trying to fix my table so that I can use the faster solution you suggested:

Code: Select all

"SELECT * FROM ".$table." WHERE complete IS NOT NULL ORDER BY date DESC"
With your help, I now understand how to make my "complete" field NULL or NOT NULL. It all works as you say. The problem remains the PHP code above returns all records in the database regardless of the value or lack of values in the "complete" field and regardless of that field's NULL status. :?

Obviously I'm missing something here in addition to a working knowledge of PHP and MYSQL. Will the IS NOT NULL thing work on text fields? Could the FULLTEXT function be involved?

I could go back to the slower option:

Code: Select all

"SELECT * FROM ".$table." WHERE length(complete) > 0 ORDER BY date DESC"
but I'd really like to expand my horizons here by making it work both ways.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

hm, I've create the table, too, and the NULL values work as expected.
You might convert all existing length-0 strings in `complete` to NULL by running this query once

Code: Select all

UPDATE $table SET complete=NULL WHERE length(complete)=0
But be careful and make sure you understand the query before you execute it ;)
User avatar
charp
Forum Commoner
Posts: 85
Joined: Sun Oct 26, 2003 3:00 pm
Location: Rancho Cucamonga, Calif. USA

Post by charp »

OK, this query I can do. I'm not worried about the database, as I'm just testing things out before I put it to use and let other people on it.

However, you should know that I've deleted this particular table and recreated it each time I tested a potential solution. So it's not like I had an existing database of information. Each time I created the table, I made a small number of entries -- some with the "complete" field empty and some with the "complete" field containing data.

As I think about this, I should also mention that I make these entries via a form with text fields. Here's the bit of code that writes to the table:

Code: Select all

if($title||$date||$full||$complete)
{
$insertIt=mysql_query("INSERT INTO `$table` (`title`, `date`, `full`, `complete`) VALUES ('$title', '$date', '$full', '$complete') ",$db);
Do you think that my form is writing a text string of length = 0 over the NULL in that field? Like I keep saying, I really don't know much about PHP and MYSQL, so I'm just taking shots in the dark.

Although it's not possible to say thank you enough -- thank you again.

*** UPDATE ****

I think I may have been correct about the form writing a text string of length = 0 into the field. I used phpMyAdmin to set the field of one empty entry to NULL. That particular entry filters correctly. So now my question becomes how is it possible for me to leave an empty text field set to NULL if the form wants to write a text string of length = 0 into that field?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

either by testing each parameter before appending it to the query

Code: Select all

<?php
if($title||$date||$full||$complete)
{
	$query = 'INSERT INTO `$table` (`title`, `date`, `full`, `complete`) VALUES (';
	
	// <- testing here each parameter
	// appending either '<value>' or NULL ->
	$insertIt=mysql_query($query, $db);
}
?>
or by replacing all empty strings (that mysql would receive) by NULL

Code: Select all

$query = "INSERT INTO `$table` (`title`, `date`, `full`, `complete`) VALUES ('$title', '$date', '$full', '$complete')";
$query = str_replace("''", 'NULL', $query);
either way you should test mysql_error() or mysql_errno() because e.g. NULL for `title` isn't allowed
User avatar
charp
Forum Commoner
Posts: 85
Joined: Sun Oct 26, 2003 3:00 pm
Location: Rancho Cucamonga, Calif. USA

Post by charp »

volka, thanks so much for the directions. I think I can limp through things on my own from this point. Well, alone as one can be armed with the online MySQL reference manual, the book on PHP I plan on buying this weekend, and ,more than likely, a few more trips back to this forum.

danke!
User avatar
charp
Forum Commoner
Posts: 85
Joined: Sun Oct 26, 2003 3:00 pm
Location: Rancho Cucamonga, Calif. USA

Post by charp »

After an hour or so of playing around with things, I think I found a solution. I'd like to hear from the experts if my solution is sound or if it's a miracle that it works at all. My problem was to have the "complete" field, which was set to NULL, remain NULL after a user left a form field blank. Previous to my solution, a blank form field for "complete" caused a string of length = 0 to be inserted into the "complete" field. Here's how I think I fixed it:

There are two places where data is inserted into my database: add new data and update data.

For add new data, I'm using this:

Code: Select all

if ($complete == "")
&#123;
$insertIt=mysql_query("INSERT INTO `$table` (`title`, `date`, `full`) VALUES ('$title', '$date', '$full') ",$db);
&#125;
else
&#123;
$insertIt=mysql_query("INSERT INTO `$table` (`title`, `date`, `full`, `complete`) VALUES ('$title', '$date', '$full', '$complete') ",$db);
&#125;
What I think I see happening with this code is that the "complete" field, when left blank, gets no data inserted. If the field is NULL by default, it remains NULL because nothing was inserted. Is that correct?

For updated data, I'm using this:

Code: Select all

if ($complete == "")
&#123; 
$updateIt=mysql_query("UPDATE ".$table." SET title='$title', date='$date', full='$full', complete=NULL WHERE id='$id'",$db);
&#125;
else
&#123;
$updateIt=mysql_query("UPDATE ".$table." SET title='$title', date='$date', full='$full', complete='$complete' WHERE id='$id'",$db);
&#125;
On this code, I think that an update that leaves the "complete" field blank or deletes the text previously in that field will set it to NULL. Is this correct?

Any comments from the experts? Am I in for any headaches if I continue to use this code? Any insights are greatly appreciated.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

as long as your only concerned about the field `complete` I think this will work.
If I work without an abstraction layer and have to care about more fields I usually use an array that holds the prepared parameters. In its simplest form something like

Code: Select all

<?php
$sqlParams = array();
$sqlParams['`title`'] = "'".mysql_escape_string(@$_POST['title'])."'";
$sqlParams['`date`'] = "'".mysql_escape_string(@$_POST['date'])."'";
if (isset($_POST['full']) && strlen($_POST['full'])
	$sqlParams['`full`'] = "'". mysql_escape_string($_POST['full'])."'";
if (isset($_POST['complete']) && strlen($_POST['complete'])
	$sqlParams['`complete`'] = "'".mysql_escape_string($_POST['complete'])."'"

$fields = join(',', array_keys($sqlParams));
$values = join(',', array_values($sqlParams));
$query = 'INSERT INTO mytable ('. $fields . ') values ('. $values .')';
?>
Post Reply