Reputation: 15
Hi I am trying to update my mysql database using php. I can update it perfectly with the following:
<?php
$conn = mysqli_connect("localhost", "root", "", "logintest");
if(!$conn){
die("Connection failed: ".mysqli_connect_error());
}
?>
<?php
$sql = "UPDATE user SET bot = '1' WHERE id = 9";
if($conn -> query ($sql) === TRUE){
echo "record updated successfully";
}else{
echo "Error updating record" . $conn -> error;
}
$conn -> close ();
?>
But before I update the above bot column to 1, I want to check and see if it is 0, as it can only be 0 or 1. To do this I done the following (see below) but it isn't working, Is it possible and or Is there a different way of doing it? All help is appreciated thanks!!
$sql = "SELECT bot FROM user"; // bot is the column in the table which should be 0 or 1
if( $sql == '0') { //if its 0 i can update it
echo 'here'; //if i get here i will update using code above
}
Upvotes: 0
Views: 199
Reputation: 2857
There are 2 approaches
SELECT
and UPDATE
$query = "SELECT bot FROM user where id=9"
$res = $conn->query($query);
if ($res->num_rows == 1) {
// it should return only one row as id is unique
$row = $result->fetch_assoc()
if($row["bot"] == 0){
// UPDATE
}
}
CASE
construct
UPDATE user SET bot = CASE
WHEN bot = 0
THEN 1
ELSE bot
END
WHERE id='9'
How it works:
It updates bot value depending on returned value of matched cases. If current bot
value is 0
, then it returns 1
, else it returns current value
for row with id=9
.
Advantage: Only 1 query
Upvotes: 1
Reputation: 1997
Please try
<?php
$conn = mysqli_connect("localhost", "root", "", "logintest");
if(!$conn){
die("Connection failed: ".mysqli_connect_error());
}
$sql= "SELECT bot FROM user where columnid=value"; // change is according to your real value
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
if($row["bot"] == 0){
$sql = "UPDATE user SET bot = '1' WHERE id = 9";
if($conn -> query ($sql) === TRUE){
echo "record updated successfully";
}else{
echo "Error updating record" . $conn -> error;
}
}
}
}
$conn -> close ();
?>
Upvotes: 0