Stop sql from updating blank or empty fields from my update form

Question

I am currently trying to make an update page for user profiles on my site and. The code below works fine if the user updates all of their info, but if they leave out a field it inserts a blank record into the table.

Currently to get past this problem if the user has left a field blank I replace the blank field with $_SESSION['user']['field'] so it just re-inserts current data.

Here is my php at the moment

<?php
session_start();
if($_SESSION['uname']) {
$logged_in=true;
} else {
$logged_in=false;
}
include_once("../connection/conn.php");

if(isset($_POST['update'])) {

if($_POST['firstname']){ $firstname = $_POST['firstname']; }
else { $firstname = $_SESSION['uname']['firstname']; }

if($_POST['lastname']){ $lastname = $_POST['lastname']; }
else { $lastname = $_SESSION['uname']['lastname']; }

if($_POST['email']){ $email= $_POST['email']; }
else { $email = $_SESSION['uname']['email']; }

$id = $_SESSION['uname']['id'];

$query = "UPDATE users SET firstname=?, lastname=?, email=? WHERE id=? ";

$results = $condb->prepare($query);

$results->execute(array($firstname, $lastname,$email,$id));

if($results) {
echo "updated";
}
}

?>

Can't you just check for blanks with javascript before sending the form? — Peon, Aug 01 '12 at 06:29
So what exactly is not working? It seems like you've posed a solution that works. Or does it? When doesn't it work? — Bailey Parker, Aug 01 '12 at 06:30
@DainisAbols Javascript can be disabled or bypassed, if the request is coming from a tool such as curl there's no javascript engine to even run the script. You should treat client-side validation as a usability aid, not as a system that can guarantee the validity of the submitted data, for that you have to validate server side. — GordonM, Aug 01 '12 at 07:41
@PhpMyCoder... Indeed it works but am looking for a different solution — zorlac, Aug 01 '12 at 08:04

score 2 · Answer 1 · answered Aug 01 '12 at 06:52

UPDATE `tablename`
SET `field` = IF(? <> '', ?, `field`)
WHERE ...

This subs the job of checking for empty entries to MySQL and field uses its previous value instead of an empty value. You need to pass the value into execute() twice for this to work. It's does basically the same thing as you are doing but without having to store the value in your PHP session.

Using this approach, your update code would look like this:

/*
  This block is no longer necessary
if($_POST['firstname']){ $firstname = $_POST['firstname']; }
else { $firstname = $_SESSION['uname']['firstname']; }

if($_POST['lastname']){ $lastname = $_POST['lastname']; }
else { $lastname = $_SESSION['uname']['lastname']; }

if($_POST['email']){ $email= $_POST['email']; }
else { $email = $_SESSION['uname']['email']; }
*/

$query = "
  UPDATE `users`
  SET
    `firstname` = IF(? <> '', ?, `firstname`),
    `lastname` = IF(? <> '', ?, `lastname`),
    `email` = IF(? <> '', ?, `email`)
  WHERE `id` = ?
";

$results = $condb->prepare($query);

$results->execute(array(
  $_POST['firstname'], $_POST['firstname'],
  $_POST['lastname'], $_POST['lastname'],
  $_POST['email'], $_POST['email'],
  $_SESSION['uname']['id']
));

Your existing code would have stopped the user from entering a single 0 on its own, which this won't - you may want to add a check for that as well.

+1 for the idea, but if you used named parameters you wouldn't have to double up the parameters like you're doing. — GordonM, Aug 01 '12 at 07:39
@GordonM ...which is why I passed the same value twice. That would still be true of named parameters, because you can only use them once in each query (annoyingly): [`You cannot use a named parameter marker of the same name twice in a prepared statement`](http://php.net/manual/en/pdo.prepare.php) — DaveRandom, Aug 01 '12 at 07:40

score 0 · Answer 2 · answered Aug 01 '12 at 06:41

0

You have to give field validation

if($firstname!="" && $lastname!="" && $email!=""){

  $query = "UPDATE users SET firstname=?, lastname=?, email=? WHERE id=? ";
  $results = $condb->prepare($query);
  $results->execute(array($firstname, $lastname,$email,$id));
  if($results) {
   echo "updated";
  }
}
else{
  echo "Fill all the fields!";
}

answered Aug 01 '12 at 06:41

Sumesh TG

440
1
4
15

Thanks for sugestion, But the problem is i want the users to be able to change just one of the fields and not have to fill them all out – zorlac Aug 01 '12 at 08:22

Stop sql from updating blank or empty fields from my update form

2 Answers2

Linked