UPDATE database but leave out any that have 0 as any of the data

What I’m doing is adding another level of security into a clients website, that means any of the entries contain a zero then its left behind and not updated.

This is what I got, pretty basic:


$sql = mysql_query("UPDATE hazzard SET Name='$name', Employerofperson='$employer', Contactphone='$telephone', NearMissDetails='$detail', Anyimmediateactions='$action', HazardorNearMiss='$hazardornearmiss', Primarycause='$primarycause3', Primarycause2='$primarycause1', Rottcause='$rootcause', Rottcause2='$primarycause2', Behaviours='$behaviours', PotentialOutcome='$potentialoutcome', Likelihood='$likelihood', Comments='$comments', report_Completed_By='$completedByManager' WHERE UniqueIdentifier='$report_ID'") or die (mysql_error());

So how do you add so that if any of those variables contains ‘0’ then skip it.

Before you get any further, please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn’t tie you down so much to a particular database server software.

Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.

Hi SpacePhoenix,

Thanks for the advice, and have looked into it and have changed it to below, could you see if I have done the right thing.

$sql = $mysqli->prepare("UPDATE hazzard SET Name = ?,
					Employerofperson = ?,
					Contactphone = ?,
					NearMissDetails = ?,
					Anyimmediateactions = ?,
					HazardorNearMiss = ?,
					Primarycause = ?,
					Primarycause2 = ?,
					Rottcause = ?,
					Rottcause2 = ?,
					Behaviours = ?,
					PotentialOutcome = ?,
					Likelihood = ?,
					Comments = ?,
					report_Completed_By = ?,
			WHERE UniqueIdentifier = ?");
$sql->bind_param('$name',
		'$employer',
		'$telephone',
		'$detail',
		'$action',
		'$hazardornearmiss',
		'$primarycause3',
		'$primarycause1',
		'$rootcause',
		'$completedByManager',
		'$report_ID');
$sql->execute();
$sql->close();

If this is fine, then I’m thinking I have got alot of work to do next week as I have a lot of updating to get through too.

If good, could you look at what I was trying to do on the first post too.

Maybe there not a way of checking it quite that easy in the update statement, so I’m guessing the best way to do it is by adding an if else statement to the post values before moving to the update.


$name = mysql_real_escape_string($_POST["txtname"]);
$employer = mysql_real_escape_string($_POST["txtemployer"]);
$telephone = mysql_real_escape_string($_POST["txtphone"]);
$detail = mysql_real_escape_string($_POST["txtdetails"]);
$action = mysql_real_escape_string($_POST["txtaction"]);
$hazardornearmiss = mysql_real_escape_string($_POST["hazardornearmiss"]);
$primarycause= mysql_real_escape_string($_POST["primarycause"]);
$primarycause1= mysql_real_escape_string($_POST["primarycause"]);
$rootcause = mysql_real_escape_string($_POST["rootcause"]);
$behaviours = mysql_real_escape_string($_POST["behaviours"]);
$potentialoutcome = mysql_real_escape_string($_POST["potentialoutcome"]);
$likelihood = mysql_real_escape_string($_POST["likelihood"]);
$comments = mysql_real_escape_string($_POST["comments"]);
$completedByManager = mysql_real_escape_string($_POST["completedBy"]);

So I would so something like this on every one.



if ($name=="0") {
$message = "This value contains 0 and is not a valid answer.";
echo "<script type='text/javascript'>alert('$message');</script>"; 
echo"<script type='text/javascript'>window.location = '/some/url';</script>";
} else {

}

First, you’re going to have trouble with that query. You have 15 placeholders in the SET but only 11 in the bind.

And you should not mix (or even use at all) deprecated mysql_ with the mysqli_

I don’t think you need complex PHP code for this, but that you can do it all with a properly constructed query.

By “0”, do you mean only the integer or also false. empty and Null?

I’ll move this to the Database forum for now.

What I mean by 0 is just literally the number 0 as that is a value of one of the drop downs, and basically they should have selected a different value.

So its just a value.

I also did later notice the 15 - 11 issue and rectified it on the site, but didnt on the forum sorry.

Also what do you mean by:

And you should not mix (or even use at all) deprecated mysql_ with the mysqli_

Thanks for getting back to me.

And all 15 fields could potentially be 0, or only some of them.

I think you could put WHERE poss_0_field != 0 AND another_poss_0_field != 0 AND …for each.

Your code example in post#4 has mysql_real_escape_string(… instead of mysqli_

… back to PHP …

OK I have added the extra ones in to make it 15 & 15, and wondered if the extra AND I have added in is right, and in the right place before i put the other 14 in.

$sql = $mysqli->prepare("UPDATE hazzard SET Name = ?, Employerofperson = ?, Contactphone = ?, NearMissDetails = ?, Anyimmediateactions = ?, HazardorNearMiss = ?, Primarycause = ?, Primarycause2 = ?, Rottcause = ?, Rottcause2 = ?, Behaviours = ?, PotentialOutcome = ?, Likelihood = ?, Comments = ?, report_Completed_By = ?, WHERE UniqueIdentifier = ? AND Name != ?" );
$sql->bind_param('$name', '$employer', '$telephone', '$detail', '$action', '$hazardornearmiss', '$primarycause3', '$primarycause1', '$rootcause', '$primarycause2', '$behaviours', '$potentialoutcome', '$likelihood', '$comments', '$completedByManager', '$report_ID', '0');
$sql->execute();
$sql->close(); 

Better. Now you should put in the “types” bind_param

types

A string that contains one or more characters which specify the types for the corresponding bind variables:
Type specification chars
Character 	Description
i 	 	 	corresponding variable has type integer
d 	 	 	corresponding variable has type double
s 	 	 	corresponding variable has type string
b 	 	 	corresponding variable is a blob and will be sent in packets

add the "not zero"s

Then you’ll be all set to test it.