Updating a MySQL database using PHP

Question

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
     }

You need to execute your query and not only store it in a variable :) — Antony
– Antony, Commented Jan 23, 2017 at 17:27
Sorry, I'm relatively new to this, how would I do that? And Thank You — Steven
– Steven, Commented Jan 23, 2017 at 17:37
It's not useful to have if (x === TRUE) in your code. Just test using if (x) most of the time. — tadman
– tadman, Commented Jan 23, 2017 at 17:48

Priyesh Kumar · Accepted Answer · 2017-01-23 18:46:35Z

1

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

edited Jan 23, 2017 at 18:46

answered Jan 23, 2017 at 18:24

Priyesh Kumar

2,8571 gold badge17 silver badges29 bronze badges

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

Comments

RiggsFolly · Accepted Answer · 2017-01-23 17:38:09Z

0

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 ();
?>

edited Jan 23, 2017 at 17:38

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

answered Jan 23, 2017 at 17:36

Nishant Nair

1,9971 gold badge14 silver badges18 bronze badges

1 Comment

RiggsFolly Over a year ago

Why should the OP "try this"? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer.

Collectives™ on Stack Overflow

Updating a MySQL database using PHP

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related