Update value only if Null php/mysql

Question

Thanks for checking out my question. I am trying to only update a value in my database if that field is null (so existing users won't be overwritten if someone tries to signup for a spot that is all ready taken and an error message will be output). I have listed below 2 of the most recent scripts I have tried. The first script works for updating the database if the select statement is not there but will overwrite users if entered for the same day and time. Thanks everybody!

$sql = ("SELECT `player1` FROM `users` where id = '$id' and Times = '$time'"); 


$result = $conn->query($sql);

if ($result->fetch_assoc === NULL) {
$update_player = ("UPDATE users SET player1 = '$name' where id = '$id' AND Times =   '$time'")

if($update_player){
echo "Date for $name inserted successfully!";
}
}

else {
  echo 'That spot is all ready taken!';
}

//2nd script 

$query=mysql_query("UPDATE users SET 
player1 = isNULL (player1, $name) 
where id = '$id' AND Times = '$time'" );

if($query){

 echo "Data for $name inserted successfully!";
}

else {
 echo 'That spot is all ready taken!';
}

I think you forgot to say what your problem is. BTW, use pdo or mysqli_* instead of mysql_* because the last one is deprecated and won't be supported in future. — Leri
– Leri, Commented Dec 27, 2012 at 14:56
Maybe the NULL information should be a condition in your query. Try some deeper tests in an SQL interface. — Nick.T
– Nick.T, Commented Dec 27, 2012 at 15:12

tmuguet · Accepted Answer · 2012-12-27 15:00:45Z

2

The following code should do the trick:

$query=mysql_query("UPDATE users SET 
player1='$name' 
where id = '$id' AND Times = '$time' AND player1 IS NULL" );

if(mysql_affected_rows() == 1){
  echo "Data for $name inserted successfully!";
}
else {
  echo 'That spot is all ready taken!';
}

Note that you should use pdo or mysqli functions instead.

answered Dec 27, 2012 at 15:00

tmuguet

1,1656 silver badges10 bronze badges

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

Comments

rizon · Accepted Answer · 2012-12-27 15:02:47Z

0

Try This.

 while($row = $result->fetch_assoc) {
    if($row['player1'] == NULL){
      $update_player = ("UPDATE users SET player1 = '$name' where id = '$id' AND Times =   '$time'")
    }

answered Dec 27, 2012 at 15:02

rizon

8,1971 gold badge29 silver badges17 bronze badges

Collectives™ on Stack Overflow

Update value only if Null php/mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related