PHP doesn't execute functions with sql correct

Question

I will put my code below. I basically check on value in the database and if it's 1 or 0 i want to change it to the opposite (so if 1 change it to 0, if 0 change to 1). If I execute one SQL statement without using the function (but then it only works one way once) it works. But if I want to execute the specific function with it depending on what the value currently is, it doesn't seem to work. Do you know what I am doing wrong here?

<?php 
$date_id = $_POST['dateID'];
$con = mysqli_connect("localhost","root","","secret_name");
$sql = "SELECT * FROM date_list WHERE date_id = ".$dateID;
$result = mysqli_query($con, $sql);
while($row = mysqli_fetch_assoc($result)){
    $occupied = $row['occupied'];
    if($occupied == 1){
        decross_entry();
    } elseif( $occupied == 0){
        cross_entry();
    }else{
        echo "Error"
    }
}

function decross_entry(){
    $dateID = $_POST['dateID'];
    $con_2 = mysqli_connect("localhost","root","","secret_name");
    $sql_edit = "UPDATE date_list SET occupied= '0' WHERE date_id = ".$dateID;
    if($con_2 -> connect_errno){
        echo "Failed to connect to database!" . $con_2->connect_error;
    }
    if ($con_2 -> query($sql_edit) === TRUE)
    {
        echo "saved!";
    } else {
        echo "error: " .$sql_edit."<br>".$con_2->error;
    }
}

function cross_entry(){
    $dateID = $_POST['dateID'];
    $con_2 = mysqli_connect("localhost","root","","secret_name");
    $sql_edit = "UPDATE date_list SET occupied= '1' WHERE date_id = ".$dateID;
    if($con_2 -> connect_errno){
        echo "Failed to connect to database!" . $con_2->connect_error;
    }
    if ($con_2 -> query($sql_edit) === TRUE)
    {
        echo "saved!";
    } else {
        echo "error: " .$sql_edit."<br>".$con_2->error;
    }
}
?>

When the select query is called second time, what is the value in $row['occupied'] — Ajoe, Nov 03 '19 at 09:56
@AA On default occupied is always zero. Only when the function "cross_entry" is called, it changes to 1. Is this what you wanted to know? — roubs3019, Nov 03 '19 at 10:51
Your code is vulnerable to SQL injection. You should use prepared statements. — Dharman, Nov 03 '19 at 12:12

Nick · Answer 1 · 2019-11-04T05:49:05.007

0

If the only possible values of occupied are 0 and 1 then you can do what you want in one query without needing to look up the value of occupied first:

UPDATE date_list
SET occupied = 1 - occupied 
WHERE date_id = ?

In PHP, using a prepared query to avoid SQL injection:

$date_id = $_POST['dateID'];
$con = mysqli_connect("localhost","root","","secret_name");
$sql = "UPDATE date_list SET occupied = 1 - occupied WHERE date_id = ?";
$stmt = $con->prepare($sql);
$stmt->bind_param('i', $date_id); // use 's' if $date_id is not an integer
$stmt->execute();

edited Nov 04 '19 at 05:49

answered Nov 03 '19 at 10:50

Nick

138,499
22
57
95

You can add `and occupied in (0, 1)` to more accurate reflect the OP's code. – Gordon Linoff Nov 03 '19 at 11:27

PHP doesn't execute functions with sql correct

1 Answers1