Thank you very much for all your help. Every thing works well now although I need to take measures to avoid any sql injections.
I am still using the original code with the modifications to the money format and will modify it now as the cart is working.
As i have not worked with prepared queries before, I will have to learn that part of PHP and will take your advice. First I have to learn how to go about with the new concept.
When you switch to use prepared queries, you need to switch from the mysqli extension to the PDO extension. The mysqli extension takes more statements and the programming interface is completely different between prepared and non-prepared queries. The PDO extension takes less statements and you can deal with the result from prepared and non-prepared queries the same way.
I was wondering if you could give me an example from your point raised in particular number 2 with this code so that I can understand better.
<?php /////////////////////////////// Discount code ////////////////////////////////////////////// // include "storeadmin/connect_to_mysql.php"; $discountcode = ""; $discountamt = 0; $grossTotal = 0; $discount = 0; if (isset($_POST["promocode"])){ $mycode = $_POST['promocode']; $sql4 = mysqli_query($db, "SELECT code, Discount, activate FROM promo_codes WHERE promo_codes.activate='1'"); $productCount = mysqli_num_rows($sql4); // count the output amount if ($productCount > 0){ while($row = mysqli_fetch_array($sql4)){ $code = $row["code"]; $Discount = $row["Discount"]; $activate = $row["activate"]; if ($mycode == $code && $activate == 1){ $Discount = $row["Discount"]/100 ; $discountamt = $cartTotal * $Discount; $grossTotal = $cartTotal - $discountamt; { } }// while }// productcount } // if sql4 }// if isset ?>It would be appreciated for my learning curve. Thank You
Thereās a current thread that shows how to use PDO and a prepared query for an INSERT query - Issues inserting data into MYSQL table via PHP file! help!
The following is typical connection code. I recommend using variables for the connection parameters so that you can reuse the code just by changing the values, not by editing the actual code.
$DB_HOST = ''; // db host name or ip address
$DB_USER = ''; // db username
$DB_PASS = ''; // db password
$DB_NAME = ''; // db name
$DB_ENCODING = 'utf8'; // db character encoding
$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assocThe above code would be put into an external .php file and ārequiredā when needed.
To use a non-prepared query, you just use the PDO query() method, which returns a PDOStatement object. You would use PDOStatement methods to operate on the result of the query. Information about these can be found in the php.net documentation.
For a prepared query, you are removing the php variable(s), any single-quotes around the variables, any concatenation dots, and any {} that are in the sql query statement and replacing each with just a ? place-holder. The variable(s) that just got removed are supplied as an array to the execute() method call. You would use PDOStatement methods to operate on the result of the query, the same as for a non-prepared query.
For the discount query, as already stated, it should use a prepared query to find the discount, rather than to loop over all the rows of data. The code above would look like -
/////////////////////////////// Discount code //////////////////////////////////////////////
// include "storeadmin/connect_to_mysql.php";
require 'pdo_connection.php'; // require the code that makes the database connection using the PDO extension.
// note: if this code is being added to the existing cart display code, there's already a database connection. don't make more connections.
// $discountcode = ""; // this is not used.
$discountamt = 0;
$grossTotal = 0; // note: the $grossTotal should initially be set to the $cartTotal, so that if the following logic is all false, the $grossTotal will be the correct value, without needing more logic to calculate it.
// $discount = 0; // this is not the same letter-case as $Discount in the following code and is probably not used outside of the following code.
// if there's a promocode, it exists and is active, retrieve the corresponding discount and set/calculate the $discountamt and $grossTotal
if(!empty($_POST["promocode"]))
{
$sql = "SELECT Discount FROM promo_codes WHERE activate = 1 AND code = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$_POST["promocode"]]);
// attempt to fetch the row and detect if there was a row
if($row = $stmt->fetch())
{
// there is a row, the code was found and it is active
$Discount = $row["Discount"]/100 ;
$discountamt = $cartTotal * $Discount;
$grossTotal = $cartTotal - $discountamt;
} else {
// there is not a row, the code wasn't found or is not active
// if you want to display distinct messages for each of these possibilities, you would remove the activate = 1 from the sql query and change this logic accordingly
// set up any user message here... or just do nothing, in which case, remove the else {} branch entirely.
}
}
// at this point, $discountamt and $grossTotal are either the initial values or the values set/modified by the promocode logic.Thank you very much for the modified code and tips , I will look forward to implementing the new concept and will be interested in the results and challenges.
I came across a problem with my current system and with the new concept may help.
The problem isā¦ as the discount PHP block is implemented after the main Cart, to implement the discount to paypal button is challenging. If I insert the block before the cart, and as the calculations of the carttotals and each item price etc are not recognised as they donāt exist and are calculated in the code proceeding the discount PHP code.
I hope it makes sense
Kind Regards
Auriff