Inserting input value or NULL not working

[nayen]

Hi,

I have the following setup:

$post_name = $_POST['post_name'];
$post_type = (isset($_POST['post_type'])) ? $_POST['post_type'] : NULL;

DB::$db->query("INSERT INTO posts (post_name, post_type) VALUES ('$post_name', '$post_type')");

which is supposed to insert the post_type value if it is entered into the input field or NULL otherwise. It inserts the input value (post_type) fine when I enter a value, but when I leave the input field empty, it doesn’t insert anything into the database.

Any ideas what I am missing?

Thanks.

[r937]

run this query directly in mysql, i.e. not via php, and let me know if it produces an error message

INSERT INTO posts (post_name, post_type) VALUES ('foobar', 'NULL')

i don’t do php, but i think your code is trying to insert the string ‘NULL’ rather than the sql keyword NULL

[nayen]

@r937, thank you. The following two queries works:

    INSERT INTO posts (post_name, post_type) VALUES ('foobar', 2)

    INSERT INTO posts (post_name, post_type) VALUES ('foobar', NULL)

This one doesn’t work:

INSERT INTO posts (post_name, post_type) VALUES ('foobar', 'NULL')

When NULL has quotes, it doesn’t create a new entry in the database. For the record, the post_type is of INT type. Perhaps I need to find a way to assign the NULL value with an IF (?) check inside the query itself, not before the query like I did in the first post.

[Mittineague]

@nayen

When NULL has quotes, it doesn’t create a new entry in the database. For the record, the post_type is of INT type.

You really wouldn’t want MySQL to succeed at entering the text string “NULL” into an int field anyway, would you?

How does this work?

INSERT INTO posts (post_name, post_type) VALUES ('foobar', )

[nayen]

For clarification, I am not trying to insert text string “NULL”. I am trying to insert the post_type (INT) value if it is provided, and insert NULL if post_type isn’t provided.

I tried your query, it didn’t insert an entry.

[Mittineague]

@nayen

I tried your query, it didn’t insert an entry.

If the post_type field is a “NOT NULL” does it have a DEFAULT value?
i.e. the table schema - SHOW CREATE TABLE posts

[nayen]

post_type field accepts NULL values and its default value is NULL.

[Mittineague]

Thanks for making that clear.

Are you doing anything with $post_type between these lines?

$post_type = (isset($_POST['post_type'])) ? $_POST['post_type'] : NULL;

DB::$db->query("INSERT INTO posts (post_name, post_type) VALUES ('$post_name', '$post_type')");

The POST array contains strings, so unless you cast it into an INT that could explain why the INSERT is failing.

Maybe

if ( (isset($_POST['post_type'])) 
   && (is_numeric($_POST['post_type'])) ) {
      $post_type = (int) $_POST['post_type']; 
    } else {
      $post_type = NULL;
    }

[r937]

[quote=“nayen, post:3, topic:223417, full:true”]Perhaps I need to find a way to assign the NULL value with an IF (?) check inside the query itself, not before the query like I did in the first post.
[/quote]

no, not with sql, with php – php has a perfectly adequate IF capability

just do the test in php, and send the right sql statement to the database