MYSQL + PHP : "error in SQL syntax" when updating table using array and variables

Question

I am a new developer and am trying to update a MYSQL table with modified user data after accepting a cc payment. I have taken all the values that need to be updated or inserted into each table and it seems like the arguments are all correct... but the tables are not updating and I am getting the following error message:

Notice: Undefined variable: msqli in /home/ban50/public_html/app/return.php on line 74

Notice: Trying to get property of non-object in
                     /home/public_html/app/return.php on line 74

Error No: 0 - MySQL error You have an error in your SQL syntax; check the
               manual that corresponds to your MySQL server version for the
                         right syntax to use near '' at line 1 

Query: UPDATE `users` SET membership_id='1', mem_expire='' WHERE username=Blev80
#0 {main}

The code that I am using is as follows:

This is the standard "straight forward" variables. The $_POST[] variables are those that I receive from the payment gateway as an (array):

$host="localhost"; // Host name 
$dbusername="Blev80"; // Mysql username 
$password="MYPASS"; // Mysql password 
$db_name="Blev80DB"; // Database name 
$tbl_name="users"; // Table name 
$mname=$_POST['li_0_product_id']; //membership/product ID
$appusername=$_POST['username'];//Users "Username"
$amch = $_POST['li_0_price']; //purchase price
$amtot = $_POST['total']; //total price
$curr = 'USD'; //currency
$muid = $_POST['userid']; // USER ID

I then am trying to insert the END DATE of the purchase - I thought to use the strtotime (+ 1 month ) logic here:

$duration = $_POST['li_0_recurrence']; //recurrence calculation
$eDate =strtotime('+$duration'); //the END date of the purchase
$memexpire = $eDate  ; // my reference for the table (this may be a redundant step but it helps me to think in this way

for testing purposes I echo'd the End Date:

echo $eDate;

I ASSUME the above is working but I cant verify whats going on yet since I seem to have a problem in the code below:

$data = array(
              'txn_id' => time(),
              'membership_id' => $mname,
              'user_id' => $muid,
              'rate_amount' => $amch,
              'tax' => '0',
              'coupon' => '0',
              'total' => $amtot,
              'ip' => $_SERVER['REMOTE_ADDR'],
              'date' => "NOW()",
              'pp' => "2CO",
              'currency' => 'ZAR',
              'status' => 1);

if ($_POST['credit_card_processed'] === 'Y') {



//connect to DB 


$mysqli = new mysqli("$host", "$dbusername", "$password", "$db_name");


//build queries


$query = "UPDATE `users` SET membership_id='$mname', mem_expire='$eDate'  WHERE username=$appusername";

$mins = "INSERT INTO payments ($data)";


//run queries


$mysqli->query($query);

$mysqli->query($mins);


//error checking


if ($mysqli->error) {
try {    
    throw new Exception("MySQL error $mysqli->error <br> Query:<br> $query", $msqli->errno);    
} catch(Exception $e ) {
    echo "Error No: ".$e->getCode(). " - ". $e->getMessage() . "<br >";
    echo nl2br($e->getTraceAsString());
}
}

else {
echo "Thanks database updated";
}


}
else {
echo "Credit card payment failed";
}`

So I have 2 issues

1) The error notice on line 74 references my error checking component

throw new Exception("MySQL error $mysqli->error <br> Query:<br> $query", $msqli->errno);

2) Something is wrong with the MYSQL syntax and the tables are not updating.

• I have tried enclosing the table references in backticks, but I still get the message.

• I have tried changing the mysqli->error to mysqli(error)

• I have been to numerous SO questions and answers and have incorporated code where it seems to be necessary

-- I honestly don't know what else to try - I have been looking at the same 80 lines of code for 2 days now :)

Any help or feedback would be hugely appreciated!

Answer 1

Your update query $query = "UPDATEusersSET membership_id='$mname', mem_expire='$eDate' WHERE username=$appusername" translates to something like

UPDATE `users`
    SET membership_id='mname', mem_expire='2045-08-09'
    WHERE username=some_username

------------------------------------^

You are missing ' around your $appusername.

---

$query = "UPDATE `users` SET `membership_id` = '{$mname}', `mem_expire` = '{$eDate}' WHERE `username` = '{$appusername}"

---

Don't need quotes around parameters here, because there is no concatenation of strings.

$mysqli = new mysqli("$host", "$dbusername", "$password", "$db_name");