MySql Update语句不更新数据库

I am quite new to coding in PHP, althought I did some research (tons of it that is), I cannot seem to see the problem with my query statement. There seem to be so much way to write a query that I am kind of lost here. Be aware that I might not have the best php coding techniques. I also tried the Msqli prepare/execute syntax, but nothing was happening at all and I had error messages and it didn't even enter my if statement to check the number of rows modify, so I will likely stick to the current syntax which execute and logs my SuccessDB.log and FailDB.log. At least I know it tries to do it.

我对PHP编码很新,虽然我做了一些研究(大量的研究),我似乎无法看到我的查询语句的问题。似乎有很多方法来编写一个我在这里丢失的查询。请注意,我可能没有最好的PHP编码技术。我也尝试了Msqli准备/执行语法,但没有发生任何事情,我有错误消息,它甚至没有输入我的if语句检查修改行数,所以我可能会坚持执行当前语法并记录我的SuccessDB.log和FailDB.log。至少我知道它试图这样做。

In my application, I have a Paypal IPN listener page. Once Paypal sends the request, I can process it but only halfway. Once, I validated that all IPN variables are ok and the payment is Completed and Verified, I need to update my invoice table in my database. It's the final processing update that fails.

在我的应用程序中,我有一个Paypal IPN监听器页面。一旦Paypal发送请求,我就可以处理它但只有一半。有一次,我验证了所有IPN变量都没问题且付款已完成并已验证,我需要在我的数据库中更新我的发票表。这是最终的处理更新失败。

I've been trying all sorts of code/query for two days straight and now I'm at a loss. Help would be appreciated.

我已经连续两天尝试各种代码/查询,现在我不知所措。帮助将不胜感激。

The problematic UPDATE statement (excerpted from whole code):

有问题的UPDATE语句(摘自整个代码):

$sql2 = "UPDATE payment_invoice SET p_user_name='$full_name', p_user_address='$address_and_state', p_user_postalcode='$address_zip', p_user_country='$address_country', p_datetime='$dateConverted', p_payment_completed='$p_payment_completed', p_user_email='$payer_email', p_paypal_txn_id='$txn_id' WHERE p_unique_invoice_id='$item_number'";

if ($conn->query($sql2) === TRUE) {
    error_log(date('[Y-m-d H:i e] ') ."\r\n". "RECORDS UPDATED", 3, "./SuccessDB.log");
} else {        
    error_log(date('[Y-m-d H:i e] ') ."\r\n". "FAILED UPDATE", 3, "./FailDB.log");
}
$conn->close();

All the details of the universe (Whole/partial code):

宇宙的所有细节(整体/部分代码):

<?php
$host="localhost"; // Host name
$username="myusername"; // Mysql username
$password="mypass"; // Mysql password
$db_name="mydbname"; // Database name
$conn = null;

//Connect to server and select database.
$conn = new mysqli($host, $username, $password, $db_name);

if ($conn->connect_errno) {
    error_log($conn->connect_errno, 3, "error_log.txt");
}

//**ADD CURL CODE HERE**
//CURL code to catch paypal IPN request and Code to post validate back to paypal
//I will skip this lenghty code, as the problem doesn't occur here

$tokens = explode("\r\n\r\n", trim($res));
$res = trim(end($tokens));

if (strcmp ($res, "VERIFIED") == 0) {

    //Initialize variables/Post variables    
    $payment_status = $_POST['payment_status'];
    $transactionOK = false;
    $p_total_amount = 0;
    $p_payment_completed = 0;
    $receiver_email = $_POST['receiver_email'];
    $txn_id = $_POST['txn_id'];
    if ($_POST['mc_gross'] != NULL){
        $payment_amount = floatval ($_POST['mc_gross']);
    }
    else{
        $payment_amount = floatval ($_POST['mc_gross1']);
    }
    $item_number = $_POST['item_number'];
    $payment_currency = $_POST['mc_currency'];
    $reason_code = $_POST['reason_code'];
    $pending_reason = $_POST['pending_reason'];
    $payment_type = $_POST['payment_type']; 
    $payer_id = $_POST['payer_id'];
    $payer_status = $_POST['payer_status'];
    $first_name = $_POST['first_name'];
    $last_name = $_POST['last_name'];
    $full_name = $first_name." ".$last_name;
    $address_zip = $_POST['address_zip'];
    $address_country = $_POST['address_country'];
    $address_state = $_POST['address_state'];
    $address_street = $_POST['address_street'];
    $address_and_state = $address_street.", ".$address_state;
    $payer_email = $_POST['payer_email'];
    //Represent the unique random ID we created on click of buy button
    $item_number = $_POST['item_number'];
    date_default_timezone_set('PST'); 
    $payment_date = $_POST['payment_date'];  
    $dateTime = strtotime($payment_date);
    $dateConverted = date('Y-m-d H:i:s', $dateTime);    
    $query = null;
    $query_update = null;    
    $statement = null;
    $statement_update = null;
    $p_paypal_txn_id = "";    
    $res = null;
    $results_update = null;
    $nrows = null;

    //Check to see if payment amount is higher than regular price of item
    //We do this to detect if a user/hacker tried to tamper with page variables and try to buy
    if($payment_amount > "21.95"){

        //Check to see if payment is Completed, if payment type is instant and if currency is Canadian dollar
        if($payment_status == "Completed" && $payment_type == "instant" && $payment_currency == "CAD"){

            //THIS STATEMENT WORKS, THE CODE ENTERS THE ELSE STATEMENT AS INTENDED
            //Check to see if the transaction ID has already been processed in my database
            $sql = "SELECT * FROM payment_invoice WHERE p_paypal_txn_id=$txn_id";
            if ($conn->query($sql) === TRUE) {
                error_log(date('[Y-m-d H:i e] ') ."\r\n". "TNX EXIST", 3, "./TnxAlreadyExist.log");
            }
            } else {

                try{
                    $p_payment_completed = 1;

                    //This is the problematic statement
                    $sql2 = "UPDATE payment_invoice SET p_user_name='$full_name', p_user_address='$address_and_state', p_user_postalcode='$address_zip', p_user_country='$address_country', p_datetime='$dateConverted', p_payment_completed='$p_payment_completed', p_user_email='$payer_email', p_paypal_txn_id='$txn_id' WHERE p_unique_invoice_id='$item_number'";

                    if ($conn->query($sql2) === TRUE) {
                        error_log(date('[Y-m-d H:i e] ') ."\r\n". "RECORDS UPDATED", 3, "./SuccessDB.log");
                    } else {        
                        error_log(date('[Y-m-d H:i e] ') ."\r\n". "FAILED UPDATE", 3, "./FailDB.log");
                    }
                    $conn->close();
                }
                catch(Exception $e){
                    error_log(date('[Y-m-d H:i e] ') ."\r\n". "FAILED UPDATE".$e->getMessage(), 3, "./FailDBMSG.log");
                }
            }
            $conn->close();
        }
    }
}
else if (strcmp ($res, "INVALID") == 0) {
    // log for manual investigation
    // Add business logic here which deals with invalid IPN messages
    if(DEBUG == true) {
        error_log(date('[Y-m-d H:i e] '). "Invalid IPN: $req" . PHP_EOL, 3, LOG_FILE);
    }
}

?>

Database Details:

p_user_name = varchar(800) / string
p_user_address = varchar(4000) / string (I know it's a lot of chars..)
p_user_postalcode = varchar(50) / string
p_user_country = varchar(500) / string
p_datetime = datetime
p_payment_completed = tinyint / boolean 1/0
p_unique_invoice_id = varchar(25) / string
p_user_email = varchar(100) / string
p_paypal_txn_id = varchar(50) / string

2 个解决方案

#1

Do you not have to concatenate the query string together? As so:

你不必将查询字符串连接在一起吗?如此:

$sql2 = "UPDATE payment_invoice SET p_user_name='".$full_name."' WHERE p_unique_invoice_id='".$item_number."'";

I think you what you're doing is trying to update using the name of the variable as a key, rather than the actual value.

我认为你正在做的是尝试使用变量的名称作为键进行更新,而不是实际值。

#2

EDIT: Answering my own question to close this topic.

编辑:回答我自己的问题来结束这个话题。

I forgot a variable in which I tried to parse a date/string to datetime, with as parameter a variable that doesn't even exist anymore. Second of all, in all my hurry to get the expected results as fast as possible, I forgot to take into account browser/server cache which probably took some time to process the code I added in my post.

我忘了一个变量,我试图将日期/字符串解析为datetime,as参数是一个甚至不再存在的变量。第二,尽管我急于尽快得到预期的结果,但我忘了考虑浏览器/服务器缓存,这可能需要一些时间来处理我在帖子中添加的代码。

So, my code works and I need to sleep. The end. Ashamed (a bit).

所以,我的代码工作,我需要睡觉。结束。惭愧(有点)。

#1