LIMIT the user accessebility on a particular table data in DB

Question

I been working on a code, where the user can access the promocode in DB along with discount that comes with it. Now when user has to pay and got a promocode, he enters the same and avails some discount in his pay. Now how to limit the promocode access to once?

My code goes as ::

<?php include('db.php'); ?>
<?php

$promocode = $_POST['promocode'];
$payamount = $_POST['payamount'];
$mobile = $_SESSION['mobile'];


if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql3 = "SELECT * FROM apromocode WHERE code = '$promocode' ";
$result3 = $conn->query($sql3);
if ($result3->num_rows > 0) {                               
while($row3 = $result3->fetch_assoc()) {

$discount = $row3['discount'];  

$total1 = $discount;
$total2 = $pay - $total1;

$sql = "UPDATE userpaytoget SET payamount = '$total2' 
WHERE mobile = '$mobile'";

if ($conn->query($sql) === TRUE) {
echo '<script language="javascript">';
echo 'alert("PromoCode Applied")';
echo '</script>';
echo '<a href="user-profile.php"></a>';
}
else {
    echo "ERROR" . $sql . "<br>" . $conn->error;
}
}
}
else {
    echo "ERROR" . $sql3 . "<br>" . $conn->error;
}
$conn->close();
?>

Here, i get the payamount and promocode form user while i will check the promocode with the db and get the discount accordingly...

Now here, i dont have any restrictions for user where he can only avail this for once.. Any help is appreciated...

Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Jan 21 '17 at 14:18
Some sensible code indentation would be a good idea. It helps us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — RiggsFolly, Jan 21 '17 at 14:19
Please read [What topics can I ask about](http://stackoverflow.com/help/on-topic) and [How to ask a good question](http://stackoverflow.com/help/how-to-ask) and [the perfect question](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) and how to create a [Minimal, Complete and Verifiable example](http://stackoverflow.com/help/mcve) SO is **not a free Coding or Code Conversion or Debugging or Tutorial or Library Finding service** ___We fix your attempts, we do not write your code___ — RiggsFolly, Jan 21 '17 at 14:19

score 0 · Answer 1 · answered Jan 21 '17 at 14:44

This is probably more of a design issue than a code issue. You have to consider whether the rule "promocode can be used only once by a user" applies to every promocode and every user. If yes, then:

I presume promocode defined in apromocode table is already a UNIQUE KEY if not PRIMARY KEY and that mobile is equivalent to certain user_id as far as identifying a unique user is concerned.
In userpaytoget table, make a composite UNIQUE KEY of mobile and code. This will make sure that a promocode can be used by a mobile only once.

Now, this will not necessarily ensure that the user gets the error message (since the INSERT or UPDATE operations would simply fail by throwing a DUPLICATE KEY error). So, for you to be able to relay that to the user, you could:

Before inserting or updating a record, check the count of mobile and code. If it's greater than 0, give your custom error message to the user.

A slightly different logic and error handling mechanism will be required if you decide that certain promocodes can be used more than once. In that case,

You could add a new column, say, max_usage_count in apromocode.
This will also mean that Composite UNIQUE KEY defined in userpaytoget will have to be removed. However, you could still check the COUNT of mobile and promocode before INSERT / UPDATE, compare it with the max_usage_count and accordingly decide whether or not to allow the operation.

Hope that helps.

LIMIT the user accessebility on a particular table data in DB

1 Answers1