How to prevent null values to updating MySQL database

Question

My update query is

"UPDATE registration SET `dob` = '".$theDate."' , pwd='".$_REQUEST['n_password']."', name='".$_REQUEST['n_name']."' where id='".$_SESSION['id']."' "

Problem is that it is not necessary that user update all fields so if it happens there are null values coming from form and it will replace earlier value in database.

I can update it one by one after checking if field value is not null but if there is any other way r tutorial please help me

Isn't it so that you should employ all checks (both server-side and client-side) before any external data reaches your script? — verisimilitude
– verisimilitude, Commented Aug 25, 2012 at 15:23
This looks horrible like one big SQL injection honeypot. Oh, and probably the php tag is missing, I guess? — Uwe Keim
– Uwe Keim, Commented Aug 25, 2012 at 15:29
@LLIa now i understand that what is sql injection and now start using escaping input's but still not understand PDO what it exactly, i had gone through mysql pdo tutorial but not getting much.... — Om prks
– Om prks, Commented Aug 26, 2012 at 7:31

Dan Grossman · Accepted Answer · 2012-08-25 17:12:46Z

2

I can update it one by one after checking if field value is not null but if there is any other way r tutorial please help me

Don't issue an UPDATE query after you check each value, instead add that column to the query you're building, then execute just one UPDATE with only the columns that had values.

$dbh = new PDO('mysql:host=localhost;dbname=whatever', 'user', 'password');
$params = array();

$sql = "UPDATE REGISTRATION SET `dob` = ?";
$params[] = $theDate;

if (!empty($_REQUEST['n_password'])) {
  $sql .= ", `pwd` = ?";
  $params[] = $_REQUEST['n_password'];
}

if (!empty($_REQUEST['n_name'])) {
  $sql .= ", `name` = ?";
  $params[] = $_REQUEST['n_name'];
}

$sql .= " WHERE `id` = ?";
$params[] = $_SESSION['id'];

$stmt = $dbh->prepare($sql);
$stmt->execute($params);

answered Aug 25, 2012 at 17:12

Dan Grossman

52.5k10 gold badges115 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Om prks Over a year ago

thanks a lot but i don't think that i can use PDO as i have not enough experience & knowledge and i have to finish project soon, but i will try it latter, if you have any other way then please let me know

Dan Grossman Over a year ago

This is the complete code to use PDO to execute your query. There is nothing else to learn.

MarcoZen Over a year ago

@DanGrossman - Thanks. Just wondering why you opted for !empty instead of isset in the if blocks ?

Collectives™ on Stack Overflow

How to prevent null values to updating MySQL database

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related