Trouble in updating a record in mysql database

Question

below is my code:

<?php
$response = array();

if ($_POST['code_input'] != ''){
    $code_input = $_POST['code_input'];
    $email_code = $_POST['email_code'];

    $link = mysql_connect('localhost','root','') or die ('Could not connect: '.mysql_error());
    mysql_select_db('ichop') or die ('Could not connect to database');

    //check if redemption code exist
    $exist = mysql_query("select * from redemption where red_code = '$code_input'");

    //check if redemption code is usable
    $usable = mysql_query("select * from redemption where code_status = 'usable' and red_code = '$code_input'");

    //check if users already have the card
    $possess = mysql_query("select * from customer customer join card card on customer.customer_id = card.customer_id join redemption redemption on card.merchant_id = redemption.merchant_id where card.merchant_id = redemption.merchant_id and redemption.red_code = '$code_input'");

    //check if reward name is "reward point"
    $point = mysql_query("SELECT * FROM redemption redemption JOIN reward reward ON redemption.merchant_id = reward.merchant_id WHERE reward.reward_name LIKE  '%point%' AND redemption.red_code =  '$code_input'");
    $data3 = mysql_fetch_array($point);

    $customer = mysql_query("select * from customer where C_email = '$email_code'");
    $data1 = mysql_fetch_array($customer);

    $merchant = mysql_query("select * from redemption where red_code = '$code_input'");
    $data2 = mysql_fetch_array($merchant);

    $card = mysql_query("select redemption.Total_Point, card.card_id from customer customer join card card on customer.customer_id = card.customer_id join redemption redemption on card.merchant_id = redemption.merchant_id where redemption.red_code = '$code_input'");
    $data4 = mysql_fetch_array($card);

    if(mysql_num_rows($exist) == 1){
        if(mysql_num_rows($usable) == 1){
            if(mysql_num_rows($possess) == 1){

            } else {
                //create new card for customer              
                $create = mysql_query("INSERT INTO card (Card_ID, Chop_Amt, Customer_ID, Merchant_ID) VALUES ('', '0', '".$data1["Customer_ID"]."', '".$data2["Merchant_ID"]."')");

                if(mysql_num_rows($point) == 1){
                    //update the chop amount in card details
                    $update1 = mysql_query("UPDATE card SET Chop_Amt = '".$data3["Total_Point"]."' where Customer_ID = '".$data1["Customer_ID"]."' and Merchant_ID = '".$data2["Merchant_ID"]."'");

                    $update2 = mysql_query("UPDATE redemption SET Code_Status = 'Unusable', Red_Date = now(), Point_Balance = '".$data3["Total_Point"]."', Card_ID = '".$data4["Card_ID"]."' where red_code = '$code_input'");

                    $response["success"] = 1;
                    $response["message"] = "Code redeemed!";

                    echo json_encode($response);
                } else {
                    $response["success"] = 0;
                    $response["message"] = "You do not have enough point to use the code!";

                    echo json_encode($response);
                }
            }
        } else {
            //error for non-usable code
            $response["success"] = 0;
            $response["message"] = "Code is not usable!";

            echo json_encode($response);
        }
    } else {
        //error for non existing code
        $response["success"] = 0;
        $response["message"] = "Code does not exist!";

        echo json_encode($response);
    }
} else {
    //error for blank field
    $response["success"] = 0;
    $response["message"] = "Please fill in the code field!";

    echo json_encode($response);
}
?>

My situation is that I want my system to create a new record in "Card" if they don't have 1 and then update the "Redemption" table accordingly..

However, I only managed to create a new card but I am not able to update the "Redemption" table...can anyone help me? Please tell me any thing that you need to examine this...thanks!

I have tried

$card = mysql_query("select redemption.Total_Point, card.card_id from customer customer 
join card card on customer.customer_id = card.customer_id 
join redemption redemption on card.merchant_id = redemption.merchant_id 
where redemption.red_code = '$code_input'");
$data4 = mysql_fetch_array($card);

at a separate php file and I can get the data I want...however I dun understand why it is not updating ><

Answer 1

Without debugging the code - stepping through it - I can't figure out what's going on, but the way the code is structured makes it very hard to follow the logic. A single SQL query not doing what you expect it to could cause this to fail silently, and there are a large number of nested conditionals that make it hard to keep up with what's going on.

I get the feeling you can write the update more efficiently - you're grabbing data into PHP variables from other queries, and passing them into the update, and you can probably do that by joining to that data in the update statement instead.

Secondly, please consider "breaking early". For instance:

if ($_POST['code_input'] == ''){
    //error for blank field
    $response["success"] = 0;
    $response["message"] = "Please fill in the code field!";

    die json_encode($response);
}

This puts the error you're sending back immediately after the validation step, rather than right at the other end of the codefile.

Next, consider factoring out all those validation/data retrieval steps into functions of their own. So, instead of the code above, consider:

if (!isInputValid($_POST['code_input'])){
    //error for blank field
    $response["success"] = 0;
    $response["message"] = "Please fill in the code field!";

    die json_encode($response);
}
function isInputValid($input){
    if ($input == ''){
       return false;
    }
    return true; 
}

Next, consider not relying on multiple MySQL result sets and their weird "return FALSE or an array" behaviour. Consider creating a variable called $totalPoints, rather than $data3["Total_Point"].

Try this, and I'm pretty sure the bug will become obvious...

Answer 2

You should start using PDO instead of mysql_* functions as they're deprecated. Also, you should be more careful with the queries - I saw you're selecting pretty much the same information from the same table a couple of times but only requesting by different columns. For example these queries $exist and $usable can be merged into a single query and then you can check the query result with a simple if/else statement. This will spare some system resources and would load the application a bit faster.

Also, I don't understand why are you using table aliases inside your sql queries when the alias name itself is the same as the table name? The aliases are meant for cases when you want to shorten your table's name (i.e. my_table_name to become mtn as it's easier and faster to write) or if you're joining a couple of tables having columns with the same names but different meaning and usage.

Regarding the code you've written, as @Neville K pointed out, it will be quite hard to nail what's wrong with it. The way you've written it doesn't make debugging easy. I took the time to reorganize your code using PDO. The code will most probably NOT work right away - I haven't tested it and I don't have the structure of your database. You may need to do some work to get it working. I would like to advice you AGAINST using variable names such as data,data1,data2 and so on. Try giving variables a name that would make sense and would clarify what data it holds.

Here is the code:

<?php

$response = array();
$code_input = $_POST['code_input'];
$email_code = $_POST['email_code'];

if ($code_input != "" && $email_code != ""){

    // PDO link to database;
    $host = 'localhost';
    $port = 3306;
    $dbname = 'ichop';
    $dbuser = 'PUT_YOUR_DB_USER_HERE';
    $dbpass = 'PUT_YOUR_DB_USER_PASS_HERE';
    $connect_string = "mysql:host=".$host.";port=".$port.";dbname=".$dbname;
    $db = new PDO( $connect_string, $dbuser, $dbpass );
    $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    // Get the code from the database using a prepared statement (ps in the variables stands for Prepared Statement)
    $rps = $db->prepare('
    SELECT *
    FROM redemption
    WHERE red_code = :code_input');
    // Bind a the value from $code_input to the :code_input variable in the sql code.
    $rps->bindValue(':code_input', $code_input, PDO::PARAM_STR); // If the $code_input is an integer, use PDO::PARAM_INT
    // Execute the query
    $rps->execute();
    // Fetch the results
    // - PDO::FETCH_ASSOC would return an associative array, containing the column names of the table
    //   i.e.
    //   array(
    //      'red_code' => 1234,
    //      'usable' => true
    //      ..........
    //   )
    // For more information visit http://www.php.net/manual/en/pdo.constants.php
    $redemption_code = $rps->fetch(PDO::FETCH_ASSOC);

    // Check if the code exists in the database.
    if($redemption_code != ""){
        // Check if the code is usable
        if($redemption_code['usable'] == 1 && $redemption_code['red_code'] == $code_input) {
            //check if users already have the card
            $pps = $db->prepare('
            SELECT *
            FROM customer
            JOIN card on customer.customer_id = card.customer_id
            JOIN redemption redemption on card.merchant_id = redemption.merchant_id
            WHERE card.merchant_id = redemption.merchant_id
            AND redemption.red_code = :code_input');
            $pps->bindValue(':code_input', $code_input, PDO::PARAM_STR);
            $pps->execute();
            $possessed = $pps->fetch(PDO::FETCH_ASSOC);

            // This card haven't been used yet
            if($possessed == ""){
                // check if reward name is "reward point"
                // I believe this code can be merged with $redemption_code but I don't know your database structure so I'm leaving it as is.
                $point_ps = $db->prepare("
                SELECT *
                FROM redemption redemption
                JOIN reward reward ON redemption.merchant_id = reward.merchant_id
                WHERE reward.reward_name LIKE '%point%'
                AND redemption.red_code = :code_input");
                $point_ps->bindValue(':code_input', $code_input, PDO::PARAM_STR);
                $point_ps->execute();
                $point = $point_ps->fetch(PDO::FETCH_ASSOC);

                // Please check if the column name "C_email" is with a capital C. Do the check for the column names in the other queries as well.
                $customer_ps = $db->prepare('SELECT * FROM customer WHERE C_email');
                $customer_ps->bindValue(':email_code', PDO::PARAM_STR);
                $customer_ps->execute();
                $customer = $customer_ps->fetch(PDO::FETCH_ASSOC);

                // I've got no idea what this is.
                $cdps = $db->prepare("
                SELECT
                    redemption.Total_Point,
                    card.card_id
                FROM customer
                JOIN card ON customer.customer_id = card.customer_id
                JOIN redemption ON card.merchant_id = redemption.merchant_id
                WHERE redemption.red_code = :code_input");
                $cdps->bindValue(':code_input', $code_input, PDO::PARAM_STR);
                $card = $cdps->fetch(PDO::FETCH_ASSOC);

                // Create new card for the customer
                $insert_ps = $db->prepare("INSERT INTO card(Chop_Amt, Customer_ID, Merchant_ID) VALUES ('0', :customer_id, :merchant_id)");
                $insert_ps->bindValue(':customer_id', $customer["Customer_ID"], PDO::PARAM_INT);
                $insert_ps->bindValue(':merchant_id', $redemption_code["Merchant_ID"], PDO::PARAM_INT);
                $insert_ps->execute(); // This will return true on successful insert and false on unsuccessful.

                if($insert_ps) {
                    // If, when executing the code, the redemption & card tables don't get updated
                    // you need to debug the $point variable - see if a record is being returned and
                    // if that's what you need.
                    if($point != ""){
                        $card_update_ps = $db->prepare("UPDATE card SET Chop_Amt = :total_point WHERE Customer_ID = :customer_id AND Merchant_ID = merchant_id");
                        $card_update_ps->bindValue(':customer_id', $customer["Customer_ID"], PDO::PARAM_INT);
                        $card_update_ps->bindValue(':merchant_id', $redemption_code["Merchant_ID"], PDO::PARAM_INT);
                        $card_update_ps->bindValue(':total_point', $point["Total_Point"], PDO::PARAM_INT); // I guess this is an integer?
                        $card_update_ps->execute();

                        $redemption_update_ps = $db->prepare("UPDATE redemption SET Code_Status = 'Unusable', Red_Date = now(), Point_Balance = :total_point, Card_ID = :card_id WHERE red_code = :code_input");
                        $redemption_update_ps->bindValue(':code_input', $code_input, PDO::PARAM_STR);
                        $redemption_update_ps->bindValue(':total_point', $point["Total_Point"], PDO::PARAM_INT);
                        $redemption_update_ps->bindValue(':card_id', $card['Card_ID'], PDO::PARAM_INT);
                        $redemption_update_ps->execute();

                        $response["success"] = 1;
                        $response["message"] = "Code redeemed!";

                        echo json_encode($response);
                    } else {
                        $response["success"] = 0;
                        $response["message"] = "You do not have enough point to use the code!";

                        echo json_encode($response);
                    }
                }
                else {
                    // Print an error if you can't insert the card.
                }
            }
            // This card was used
            else {
                // Print an error?
            }
        }
        else {
            //error for non-usable code
            $response["success"] = 0;
            $response["message"] = "Code is not usable!";
            echo json_encode($response);
        }
    }
    // The redemption code does not exists
    else {
        //error for non existing code
        $response["success"] = 0;
        $response["message"] = "Code does not exist!";

        echo json_encode($response);
    }
} else {
    //error for blank field
    $response["success"] = 0;
    $response["message"] = "Please fill in the code field!";

    echo json_encode($response);
}

?>