Page 1 of 2

INSERTING NULL on the fly

Posted: Wed Jun 22, 2005 8:47 am
by kendall
Hello,

in using PHP to insert data from a form into a database. how exactly can you insert NULL values into the table

I have been reading up and experimenting but i just don't get the picutre.

i have the following query

Code: Select all

$query = sprintf("INSERT INTO careers VALUES ('','%s','%s','%s','%s','%s','%s','%s','%s')",$postdate,$position,$image_file,$criteria,$deadline,$email,$sendto,$publish);
and im using php to check for null values thus...

Code: Select all

$image_file = (empty($_POST['file']))? NULL : $_POST['file'];
However i note that because im formatting the query string...NULL is bein cast as a string.
So how do i get aounrd this. i have been looking at the DEFAULT and IFNULL() functions in mysql however i havent seen it being used in INSERT statments but rather SELECT i have been trying to create a query string using these functions but i dont get it

Code: Select all

INSERT INTO table VALUES('',NULLIF('%s',NULL)) // where '%s' is $image_file...
this doesnt work... am i suppose to not u quotes? if so then waht if image_file wasnt null wud it still need quotes? secondly how does a DEFAULT work...as the reason i have columns with default values is so that empty values would default to the columns default value

Kendall[/b]

Posted: Wed Jun 22, 2005 9:06 am
by Weirdan
to insert nulls you would put literal 'null' in your query, e.g:

Code: Select all

insert into table1 values(null, 'value', null)
it sets first and last fields of the newly inserted record to NULL

Posted: Wed Jun 22, 2005 9:16 am
by timvw
Here is a snippet how i generate a INSERT query...
Notice that i use ADOdb... And that i do some extra formatting for times and datetimes too...

// $column is an associative array with column=value pairs
// if $column['foo'] = null... then query will add NULL instead of 'value'
//
// $ddlcolumns is an associative array with all the columns in the table

Code: Select all

$db = ADONewConnection($this->_dsn);
    
    $q1 = "INSERT INTO $this->_table (";
    $q2 = ") VALUES (";
    
    foreach(array_keys($ddlcolumns) as $column)
    {
      if (array_key_exists($column, $columns))
      {
        $q1 .= "$column, ";
        if (array_key_exists("type", $ddlcolumns[$column]) && $ddlcolumns[$column]['type'] == "date")
        {
          $columns[$column] = $db->DBDate($columns[$column]);
          $q2 .= $columns[$column] . ", ";
        }
        else if (array_key_exists("type", $ddlcolumns[$column]) && $ddlcolumns[$column]['type'] == "datetime")
        {
          $columns[$column] = $db->DBTimeStamp($columns[$column]);
          $q2 .= $columns[$column] . ", ";
        }

        else if (is_null($columns[$column]))
        {
          $q2 .= "NULL, ";
        }
        else
        {
          $q2 .= $db->qstr($columns[$column]) . ", ";
        }
      }
    }
    
    $q1 = rtrim($q1, " , ");
    $q2 = rtrim($q2, " , ");
    $query = $q1 . $q2 . ")";

INSERTING NULL on the fly

Posted: Wed Jun 22, 2005 9:57 am
by kendall
Weirdian,

While this may be so the following

Code: Select all

$query = sprintf("INSERT INTO careers VALUES ('','%s','%s','%s','%s','%s','%s','%s','%s')",null,'',NULL,'null',....);
doesnt work. im looking at a syntax quoted in mysql's documentation

Code: Select all

INSERT їLOW_PRIORITY | DELAYED | HIGH_PRIORITY] їIGNORE]
    їINTO] tbl_name ї(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    ї ON DUPLICATE KEY UPDATE col_name=expr, ... ]
NOte the VALUES ({expr | DEFULAT},...
I have been trying to figure out how to use the DEAFULT syntax in this case but i keep getting syntax errors

Kendall

Posted: Wed Jun 22, 2005 12:24 pm
by nielsene
If using sprintf, you'll have to quote the null's --> ,"null". (Your third null should have been ok, but the first two would have cased either PHP or SQL problems.)

Alternatively, you can build up the insert strinng in two pieces:

Code: Select all

$fields="INSERT INTO careers (";
$values=" VALES (";
// loop over key-values to insert
$first=TRUE;
foreach ($data as $key=>value)
{
  if ($first)  {  $insert.=", "; $values.=", "; }
  $fields.=$key;
  $values.="'$value'";
}
$query="$fields)$values);";
If your data isn't in a nice little array, you can just have a long string of if's --- If (isset($variable) && $variable!="") add the appropriate field and value.

Code that relies on implicit ordering of columns in inserts is very brittle... its good form to always include the explicit fieldname list.

INSERTING NULL on the fly

Posted: Wed Jun 22, 2005 12:39 pm
by kendall
nielsene,

actually the 4th one worked but third if u mean 'null'. i had to remove the ' in the sprintf's "string" part and tag them on before i sprintf.

this is wierd doe i was hoping to use the functionality of the data column's DEFAULT value.... how does that work?

i think its pretty ugh! that IFNULL('',NULL) treats the '' as a string and not null

Posted: Wed Jun 22, 2005 12:42 pm
by nielsene
Yes I meant the third null in your parameter string.

I personally detest SQL nulls and make almost all my fields NOT NULL, because of the idiocy of the SQL spec with what NULL is equal to at times and what it doesn't equal other times. This does mean I end up with lots of 1:1 side tables to hold things that are in some sense optional. But in the case you mentioned I like that '' is not NULL. (Of course other places the SQL standard contradicts itself.) An empty string, when used in a non-nullable column expresses a posiitive fact.


To use the default, stick in the string 'DEFAULT' in the parameter list, same as 'null'.

INSERTING NULL on the fly

Posted: Wed Jun 22, 2005 12:53 pm
by kendall
nielsene,

i use

Code: Select all

$sendto = (empty($_POST['Name']))? 'DEFAULT' : $_POST['Name'];
sprintf("INSERT INTO careers VALUES ('','%s','%s','%s','%s','%s','%s','%s','%s')",null,'',NULL,$sendto);
but it literally puts it as a string
niether does

Code: Select all

$sendto = (empty($_POST['Name']))? "'DEFAULT'" : $_POST['Name'];
sprintf("INSERT INTO careers VALUES ('','%s','%s','%s','%s','%s','%s','%s',%s)",null,'',NULL,...,$sendto);
work!!
this sucks man!

Posted: Wed Jun 22, 2005 1:08 pm
by nielsene

Code: Select all

CREATE TABLE foo ( bar INT DEFAULT 2, baz INT DEFAULT 4);
INSERT INTO foo (bar,baz) VALUES (DEFAULT,DEFAULT);
SELECT bar,baz FROM foo;
INSERT INTO foo (bar) VALUES (1);
SELECT bar,baz FROM foo;
this works as expected.

Now with strings...

Code: Select all

test=# CREATE TABLE test (str1 TEXT DEFAULT 'hi there', str2 TEXT DEFAULT 'goodbye');
CREATE TABLE
test=# INSERT INTO test (str1,str2) VALUES (DEFAULT, DEFAULT);
INSERT 52714 1
test=# SELECT * FROM test;
   str1   |  str2   
----------+---------
 hi there | goodbye
(1 row)
Note: the SQL needs to have un-quoted DEFAULT/default otherwise it thinks you want the string "DEFAULT" added to the field.

So I think the solution, is something like:

Code: Select all

$sendto = (empty($_POST['Name']))? "DEFAULT" : "'{$_POST['Name']}'";
sprintf("INSERT INTO careers VALUES ('','%s','%s','%s','%s','%s','%s','%s',%s)",null,'',NULL,...,$sendto);
Notice, that the last %s isn't quoted, so your regular strings need to have the ''s added, hence the nested single/double quotes on the $sentTo variable. This way the "DEFAULT" string gets injected un-quoted into the query, while the string values ends up with the needed quotes.

Posted: Wed Jun 22, 2005 1:16 pm
by kendall
nielsene,

still doesnt work

Code: Select all

$sendto = (empty($_POST['Name']))? "DEFAULT" : sprintf("'%s'",$_POST['Name']);
 $query = sprintf("INSERT INTO careers VALUES ('',%s,%s,%s,%s,%s,%s,%s,%s)",$postdate,$position,$image_file,$criteria,$deadline,$email,$sendto,$publish);
i get
You have an error in your SQL syntax near 'DEFAULT,DEFAULT,'N')' at line 1

Posted: Wed Jun 22, 2005 1:22 pm
by nielsene
Hmm.. first, can you create a test table/database to try out the raw sql I showed above?

I'm using PostGreSQL, MySQL's manual says it should work, but you never know... Perhaps you have an older version of MySQL than the manual version you're looking at.

There is still the problaem that one of your 'DEFAULT's was singlely quoted as the error message indicates. There is still the potential problem that the field names are not enumerated in the query, I've heard that some older databases broke defaults when doing implicit match by position. {edit: hmm nevermind, I don't think the single qoute in the error mesage is from the source, its just saying where the error was.]

Can you echo out the assembled query string?

Posted: Wed Jun 22, 2005 1:26 pm
by kendall
nielsene,
There is still the problaem that one of your 'DEFAULT's was singlely quoted as the error message indicates.
Nope... the error is quoting the syntax error
Perhaps you have an older version of MySQL
Nope... 4.2.0
There is still the potential problem that the field names are not enumerated in the query, I've heard that some older databases broke defaults when doing implicit match by position.
uhm.....huh? :?: 8O :?

Posted: Wed Jun 22, 2005 1:30 pm
by nielsene
kendall wrote:nielsene,
There is still the problaem that one of your 'DEFAULT's was singlely quoted as the error message indicates.
Nope... the error is quoting the syntax error
Yeah I noticed that right after I posted... sorry about that...
There is still the potential problem that the field names are not enumerated in the query, I've heard that some older databases broke defaults when doing implicit match by position.
uhm.....huh? :?: 8O :?
The difference between

Code: Select all

INSERT INTO foo VALUES (x,g,y);
and

Code: Select all

INSERT INTO foo (bar, baz, quux) VALUES (x,g,y);
I don't think the full version will fix the problem, but its one less potential trouble spot and some DBs have been known to break on the short version and defaults.

Please try the sample sql scripts in a test databae or table.

and echo out the built query to make sure it looks like you think it should.

Posted: Wed Jun 22, 2005 2:10 pm
by kendall
nielsene,

still no dice man....do you think its because the column is a VARCHAR(255) DEFAULT 'foo'?

Kendall

Posted: Wed Jun 22, 2005 2:12 pm
by nielsene
nope, that shouldn't cause problemss.

I thnk I'd have to see the built query to debug further....