conditional insert column values

Question

i am having a problem with inserting records to my tables as i want to insert values into specific columns if only the value is not null, my query goes like this

i have tried:

INSERT INTO users(id,name,phone,address) VALUES($userId,$userName,$userPhone,$userAddress);

but it gives me error if on client side one of the parameters is not sent not all the time the client side send all the parameters (id,name,phone,address) i want to have some kind of condition instead of the handle all combinations to the query to go over this problem

`INSERT INTO users(id,name,phone,address) VALUES('$userId','$userName','$userPhone','$userAddress');` — Nana Partykar, Dec 17 '15 at 18:14

score 0 · Answer 1 · edited May 23 '17 at 10:27

You should be using prepared queries, but your immediate problem is that you aren't quoting anything:

$query = "INSERT INTO users(id,name,phone,address) VALUES('$userId', '$userName', '$userPhone', '$userAddress')";

Now, assuming you're doing this properly using a PDO connection, this is how you should be doing it to protect your database:

$db = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
$query = "INSERT INTO users (id, name, phone, address) VALUES (?, ?, ?, ?)";
$stmt = $db->prepare($query);
$result = $stmt->execute(array($userId,$userName,$userPhone,$userAddress));
if ($result) {
    //success
} else {
    //failure
}

score 0 · Answer 2 · answered Dec 17 '15 at 18:31

Put ' inside VALUES () like '$userId'.

INSERT INTO users(id,name,phone,address) VALUES('$userId','$userName','$userPhone','$userAddress');

If parameter are not coming, then let it Insert Null in DB Table column. (Allow Null). And, if you don't want to insert NULL in DB Table column, then assign any default value before inserting.

conditional insert column values

2 Answers2