PHP - How to Pass in Either POST Value or NULL Value (not NULL string) to a Stored Procedure

Question

On my page I have a few textboxes and two of those, first name and last name, are not required. If nothing is entered in one or both of those fields I need to insert NULL as nothing rather than NULL as a string value. Currently it is entering them as string values. What do I need to do so it inserts NULL values instead of a string?

PHP Code

<?php
// SQL connection and other variables...

if (!empty($_POST['firstname'])) {
    $firstname = $_POST['firstname'];
} else {
    $firstname = 'NULL';
}
if (!empty($_POST['lastname'])) {
    $lastname = $_POST['lastname'];
} else {
    $lastname = 'NULL';
}

$sp = "exec sp_test_proc '$street1', '$street2', '$firstname', '$lastname'";
$res = odbc_exec($conn, $sp);
?>

If I pass in NULL instead of the variable for either of the parameters it works fine. Not sure what is causing the issue by using a variable.

How about using a prepared statement to call your procedure and pass it a primitive value of `null` instead of a string that contains the word NULL. — phpisuber01, Jun 17 '13 at 19:39
@Jessica Got it. I guess I had a bit of a hard time understanding the question. That `3rd cup of coffee` hasn't quite kicked in yet! — Funk Forty Niner, Jun 17 '13 at 19:41
Have a look at http://stackoverflow.com/questions/5329542/php-mysql-insert-null-values — Funk Forty Niner, Jun 17 '13 at 19:52
Thanks @Fred thought I would have come across that when I was searching. Really appreciate the help though. — Brian, Jun 17 '13 at 19:58
@Brian You're welcome. Glad to see you found a solution, cheers. — Funk Forty Niner, Jun 17 '13 at 20:00

score 3 · Accepted Answer · answered Jun 17 '13 at 19:38

3

Write NULL instead of 'NULL'

You're making it a string by using quotes.

answered Jun 17 '13 at 19:38

Jessica

7,075
28
39

This is correct, thank you for the help @Jessica I really appreciate it. – Brian Jun 17 '13 at 19:59

score 1 · Answer 2 · answered Jun 17 '13 at 19:40

1

You need to pass NULL as unquoted string. This is because you are building plain SQL. So make sure the statement is valid one.

For parameterized query it should be NULL value instead of NULL string.

answered Jun 17 '13 at 19:40

Shiplu Mokaddim

56,364
17
141
187

Not to be rude but you're saying the same thing. For plain SQL OR a prepare statement with parameters you would use `NULL` not `'NULL'` – Jessica Jun 17 '13 at 20:24
Well I meant null value = `$var=null;` and null string = `$var='null';`. But I agree there was no formatting as its quite hard in cell phone. – Shiplu Mokaddim Jun 17 '13 at 22:29
It has nothing to do with the formatting - you are saying do X for plain SQL and do X for parameter. My point is it doesn't matter which he is doing, because both require X. Your answer is confusing because of that. – Jessica Jun 18 '13 at 01:30

PHP - How to Pass in Either POST Value or NULL Value (not NULL string) to a Stored Procedure

2 Answers2