CODEWITHSUNDEEP
phpmysql
a2zbollywood
a2zbollywood

Reputation: 29

PHP MySqli - Subtract & Add & Echo if 0

I am trying to create a simple loyalty program. In which users will be allotted a few Free visits.

so there's 2 fields One is"balance" which shows how many visits left and when they reach 0 it echo or show alert that u reach 0.

2nd is total which count total visits done by users.

This is what i tried but not getting the above solution:

if(isset($_POST['Submit'])) {

    $my_query = mysqli_query($connection, "SELECT * FROM `countvisits` WHERE `id` = ".$id);

    if(mysqli_affected_rows($connection) > 0 ){

        $query = "UPDATE `countvisits` SET `balance` = `balance` - 1 And `TotalVisits` = `TotalVisits` + 1 WHERE `id` = ".$id;

        $result = mysqli_query($connection, $query);   

        echo '<script language="javascript">';
        echo 'alert("Successfully Scanned")';
        echo '</script>';

       /* get new updated data */
       $new_query = mysqli_query($connection, "SELECT * FROM `countvisits` WHERE `id` = '$id'");

    }else{
        echo '<script language="javascript">';
        echo 'alert("User Not Found")';
        echo '</script>';

        /* get new updated data */
    }
}
?>

above query only subtract and shows balance & doesnt add +1 to total visits How can i achieve above results?

Upvotes: 0

Views: 301

Answers (2)

Cipryst
Cipryst

Reputation: 223

Change this: 

$query = "UPDATE `countvisits` SET `balance` = `balance` - 1 And `TotalVisits` = `TotalVisits` + 1 WHERE `id` = ".$id;

With this:

$query = "UPDATE `countvisits` SET `balance` = `balance` - 1, `TotalVisits` = `TotalVisits` + 1 WHERE `id` = ".$id;

When you update a table in sql the separator must be comma , not and keyword.

Upvotes: 2

Death-is-the-real-truth
Death-is-the-real-truth

Reputation: 72289

Instead of And in query use , as a seperator.

Like below:-

$query = "UPDATE `countvisits` SET balance = balance - 1, TotalVisits = TotalVisits + 1 WHERE `id` = ".$id;

NOTE:- your query is wide-open for SQL INJECTION. So try to use prepared-statements to prevent from it.

Reference:-

mysqli::prepare

PDO::prepare

You can check the balance first to see if it is 0 or not:-

if(isset($_POST['Submit'])) {

    $my_query = mysqli_query($connection, "SELECT * FROM `countvisits` WHERE `id` = ".$id);

    if(mysqli_affected_rows($connection) > 0 ){

       $data = mysqli_fetch_assoc($my_query);
       if($data['balance'] ==0){
        echo '<script language="javascript">';
        echo 'alert("Balance become 0")';
        echo '</script>';
       }else{
            $query = "UPDATE `countvisits` SET balance = balance - 1, TotalVisits = TotalVisits + 1 WHERE `id` = ".$id;

            $result = mysqli_query($connection, $query);   

            echo '<script language="javascript">';
            echo 'alert("Successfully Scanned")';
            echo '</script>';

           /* get new updated data */
           $new_query = mysqli_query($connection, "SELECT * FROM `countvisits` WHERE `id` = '$id'");
        }
    }else{
        echo '<script language="javascript">';
        echo 'alert("User Not Found")';
        echo '</script>';

        /* get new updated data */
    }
}

Upvotes: 1

Related Questions