CODEWITHSUNDEEP
phpmysql
Steven
Steven

Reputation: 15

Updating a MySQL database using PHP

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

Answers (2)

Priyesh Kumar
Priyesh Kumar

Reputation: 2857

There are 2 approaches

  1. 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 
  }
}

  2. 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

Nishant Nair
Nishant Nair

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

Related Questions