0

In my prepared statement, I'm trying to append DEFAULT and NULL to my query but this is not working. I've looked up other answers but could not find a clear answer. Here is my code:

  $query = "INSERT INTO tblProjects VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  $query->bind_param(DEFAULT, $projectnumber, 1, $customer, $customerprojectlead, $mgeprojectlead, $controls, $projecttitle, NULL, NULL, $comments, $getuser, $dateentered, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, NULL, NULL);
IncredibleHat
  • 4,000
  • 4
  • 15
  • 27
David
  • 89
  • 12
  • And what is `DEFAULT` in the context of PHP? ;-) – delboy1978uk Jul 23 '18 at 15:12
  • 3
    If you want something set to null, just put `NULL` instead of a placeholder (nothing to bind!!!). You dont have to bind *everything*. – IncredibleHat Jul 23 '18 at 15:13
  • Alternatively, you can specify the columns and not insert the columns with the default values. – aynber Jul 23 '18 at 15:13
  • For column to assume its default value - you omit the column. For a `null` value you... supply `null`. It's *almost* intuitive, wouldn't you say? – N.B. Jul 23 '18 at 15:14
  • 1
    @matthiasbe your PDO tag addition https://stackoverflow.com/revisions/51482086/2 isn't what the OP is using; he's using mysqli here. Edit: which was edited now. – Funk Forty Niner Jul 23 '18 at 15:15
  • what is the db schema for this? – Funk Forty Niner Jul 23 '18 at 15:18
  • *"but this is not working"* - Can you elaborate on that? Are you getting errors and if so, what are they? Are you checking for them if you're not getting any? – Funk Forty Niner Jul 23 '18 at 15:19
  • @FunkFortyNiner MySQL – David Jul 23 '18 at 15:19
  • 2
    I'm guessing the first column is an auto-incremented id, which is why you want to use `DEFAULT` - you're better off actually specifying the columns e.g. `INSERT INTO tblProjects (col1, col2 ... ) VALUES (?, ? ...)` and omitting those columns you want to be `DEFAULT` - this way you can also omit any column that should be `NULL` if it has a default value of `NULL`. – CD001 Jul 23 '18 at 15:20

2 Answers2

3

This is actually an XY Problem - you don't bind DEFAULT or even, in most cases, NULL (although you can).

Your problem is that you're using a shorthand INSERT that doesn't specify the columns, so you have to add data for all of them:

INSERT INTO tblProjects VALUES (?, ? ...);


If you specify which columns to use, you can omit any you'd want to be default (an auto-incremented PRIMARY KEY for example). Also any column you want to be NULL is quite likely to have DEFAULT NULL, in which case you just omit that as well.

INSERT INTO tblProjects (col2, col4 ...) VALUES (?, ? ...)

Note: it is possible to just use SQL keywords in the query, like so:

INSERT INTO tblProjects VALUES (DEFAULT, ?, ?, NULL ...)

... but I wouldn't say it was best practice

CD001
  • 8,332
  • 3
  • 24
  • 28
  • Okay I see, so MySQL would automatically append these values to the entry instead of PHP? – David Jul 23 '18 at 15:41
  • If you leave a column out of an `INSERT` query, yes, MySQL will use the `DEFAULT` value (or the next incremental integer on an auto-incremented id). – CD001 Jul 23 '18 at 15:43
  • 1
    @Jordan mysql really doesn't 'append' them... a table is a table and all columns exist wether you use them in SQL queries or not (insert/update included). So when you omit them, mysql just does whatever is default for those columns, to insert a new row in the whole table. – IncredibleHat Jul 23 '18 at 15:50
1

You can use COALESCE function:

INSERT INTO tblProjects (col1, col2, col3) VALUES (coalesce(?,DEFAULT(col1)), ?, ?)

In this case, if the bound variable were assigned to NULL, the default value specified for col1 will be used instead of NULL.

Note that NULL is not an issue at all. You can execute a query with NULL assigned to the bound variables and they will be interpreted correctly.

Renato Mauro
  • 11
  • 1