Multiple Update Simultaneously On A Single Cell

[deep958]

i want to update my view table data by 1 whenever a user watch a video . this causes update update loss in my database .

plz suggest me some method to perform my work correctly

 

[wolstech]

You can do something like this (assuming $videoID is the unique Identifier of the video to increment the count on, and your `views` counter column is of type INT):

UPDATE videos SET `views` = `views`+1 WHERE `VideoID` = '$videoID';

[deep958]

i had already done this but when 100 users watch same videos simultaneously then this query will run simultaneously , which will cause loss in data .

eg if views value is 50 initially then it should become 150

but may be due to executing simultaneously there will be update loss

 

how can i prevent that loss

[Krydos]

That shouldn't really be possible while using sql strictly. If two mysql queries happen simultaneously the mysql server will pick one, and execute it first and the second one that happened at exactly the same time will have to wait a millisecond or two queued up to go next. However, if your code is to blame then it could be possible:

$con = new mysqli($db_host, $db_user, $db_pass, $db_data);
$sql = "select views from video_table where id = '$video'";
$result = $con->query($sql);
$row = $result->fetch_assoc();
$views = $row['views'];
$views++;

// here is where another process could check the viewcount and get the same value

$sql = "update video_table set views = '$views' where id = '$video'";
$con->query($sql);

So, with that code several php processes could query for the current value, and get the same number, say 50, and all increment it in the php and put 51 back in the database.

 

If you use wolstech SQL query above then you shouldn't lose data.

[deep958]

i dont know anything about wolstech sql , even i heared about it first time .

i am using mysql in my php file . can i use wolstech sql in my file and can i uploa it on phpmyadmin

[wolstech]

He was referring to the SQL I posted above. He's suggesting you use a query like mine where the SQL itself adds 1 and updates the field in a single command.

 

Reading the value into your script, incrementing it using PHP, and writing it back is what causes your data loss.

[deep958]

my sql querry is this

<?php

$con=mysqli_connect("localhost","root","");

mysqli_select_db($con,"together");

$email=$_POST["email"];

$id=$_POST["id"];

$result="";

// if email doesnt exit in watchv table

$aresult=mysqli_query($con,"select * from watchv where email = '$email'");

$anum=mysqli_num_rows($aresult);

if($anum==0)

{

mysqli_query($con,"insert into watchv(email) values ('$email')");

$aresult=mysqli_query($con,"select * from watchv where email = '$email'");

$anum=mysqli_num_rows($aresult);

}

$b="select * from watchv where email= '$email'";

$bresult=mysqli_query($con,$ ;

$brow=mysqli_fetch_array($bresult);

$z="id".$id;

$bid=$brow[$z];

if($bid==0)

{

$a="update watchv set $z='$id' where email='$email'";

$status=mysqli_query($con,$a);

$a="update video set views=views+1 where id='$id'";

$status=mysqli_query($con,$a);

$b="select * from money where email= '$email'";

$bresult=mysqli_query($con,$ ;

$bnum=mysqli_num_rows($bresult);

if($bnum==0)

{

mysqli_query($con,"insert into money values('$email','5')");

}

else{

mysqli_query($con,"update money set wmoney=wmoney+5 where email='$email'");

}

}

elseif($bid==$id)

{

$a="update video set views=views+1 where id='$id'";

$status=mysqli_query($con,$a);

}

?>

 

 

 

blue color query are for update

what i have to change in it to prevent update loss