SQL query to update column in a table

Question

I have a code based website in which an employee has to update their reward points by the coupon code provides them and when that code reflect their account means when points are updated in their account they are able to shop in the website. But there is a restriction for the code that code is deleted once used. Sometimes I found a query from customers that they update their account with the code provided but code did not reflect the account and deleted from the database and so thereafter they are not able to use the code again now I want that code only deleted when the code update points in their account. I have an another table named customer_reward where code saved after add points in the customers account but the code that not reflect account recharge is not saved in that table so I want that code only delete when that code is saved in the customer_reward table.

the complete code is given below:

<?php
if(isset($_POST['sub'])){
    $db_host="localhost";
    $db_username="root";
    $db_password="";
    $db_name="14";
    $con=mysql_connect("$db_host", "$db_username", "$db_password") or die("could not connect to mysql!!!");
    if($con=="")
    {
        echo "Database not connected!!!!";
    }
    else
    {
        $isdb=mysql_select_db("$db_name") or die("database not available!!!!");
        if($isdb=="")
        {
            echo "database not selected!!!!";
        }
        else
        {   
            $emp_ID=$_POST['emp_ID'];
            $code=$_POST['code'];

            $query = mysql_query("select * from oc_abhireward where `Code`='$code'") or die (mysql_error());
            $data=mysql_fetch_assoc($query);
            $code_db=$data['Code'];
            $points_db=$data['Point'];
            if($code==$code_db)
            {
            $query1 = mysql_query("select * from oc_customer where `emp_ID`='$emp_ID'") or die (mysql_error());
            $data1=mysql_fetch_assoc($query1);
            $customer_id=$data1['customer_id'];
            $query2=mysql_query("INSERT INTO `oc_customer_reward` (customer_id, order_id, description, Code, points, date_added) VALUES ($customer_id, 0, 'rewarded',  '$code', $points_db, NOW());");
            $query4=mysql_query("INSERT INTO `oc_customer_recharge`(emp_ID, Code, points, date_added) VALUES ('$emp_ID', '$code', $points_db, NOW());");
                    if ($code==$code_db)
                    {
                    query5 = mysql_query("select * from oc_customer_recharge where Code='$code'")or die (mysql_error());
                    $data2=mysql_fetch_assoc($query4);
                    $emp_ID=$data2['emp_ID'];
                    $query6 = mysql_query("DELETE FROM oc_abhireward WHERE Code='$code'");
                    }
                    else
                    {
                    exit();
                    }

            header("location:http://localhost/14/index.php?route=account/account");
            exit();
            }
            else
            {

            }           
        }
    }
}
?>

Answer 1

I think the problem is that you are really not veryfing whether a record was inserted in your oc_customer_reward table.

There are multiple ways of solving this problem.

1. You can modify your delete query to check oc_customer_reward table. This could be something on the lines of:

    DELETE Table1
    FROM Table1
    INNER JOIN Table2 ON Table1.ID = Table2.ID
   

2. Create a trigger which will delete data in oc_reward table whenever a record is inserted in oc_customer_reward. You can look up triggers here

   CREATE TABLE reward_code_table
      (reward_code INT, random_col VARCHAR(50))
   ;
   
   INSERT INTO reward_code_table
      (`reward_code`, `random_col`)
   VALUES
      (1, 'First code'),
      (2, 'Second code'),
      (3, 'Third code')
   ;
   
   CREATE TABLE insert_code_table
       (customer_code INT, another_random_col VARCHAR(50))
   ;
   
   DELIMITER //
   CREATE TRIGGER del_after_insert
   AFTER INSERT
   ON insert_code_table
   FOR EACH ROW
   BEGIN
      DELETE FROM reward_code_table WHERE reward_code = NEW.customer_code;
   END;
   //
   DELIMITER ;
   
   INSERT INTO insert_code_table(customer_code, another_random_col)
   VALUES (2, "del 2 from reward table");
   

After inserting into one table, it deletes record from the other table.

You can checkout a sample SQLFiddle. Note that I have kept the delimiter as // in the fiddle example

Also consider using prepared statements to prevent basic mysql injections.

Answer 2

What's the relationship between Employee and Customer?

You are querying oc_customer by emp_ID, and getting customer_id from it. So is oc_customer unique on emp_id? If not, then the customer you end up getting (and so the one you'll apply the reward to) is effectively random. Instead, you need to pass in the customer's customer_id rather than the emp_id.

One other thing; You're using the "$POST"ed values directly in the SQL statements. That opens you up to a SQL-Injection attack. Check out How can I prevent SQL injection in PHP?

Answer 3

The best way possible here could be creating a trigger (for deleting the code) that will fired only when the update of points has been made.

CREATE TRIGGER trigger_name
AFTER INSERT 
   ON table_name FOR EACH ROW

BEGIN

   -- logic for deleting the corresponding CODE

END;

Hope this will bring you closer to what you seek.

Ak