0

I want to prevent duplicate values into a database table from a form using PHP.

A database with a table named points for the appointments:

CREATE TABLE `points` (
  `appoint_id` int(11) NOT NULL,
  `appoint_date` datetime NOT NULL,
  `client_name` varchar(255) NOT NULL,
  `client_contact` varchar(255) NOT NULL,
  `sub_total` varchar(255) NOT NULL,
  `vat` varchar(255) NOT NULL,
  `total_amount` varchar(255) NOT NULL,
  `discount` varchar(255) NOT NULL,
  `grand_total` varchar(255) NOT NULL,
  `paid` varchar(255) NOT NULL,
  `due` varchar(255) NOT NULL,
  `payment_type` int(11) NOT NULL,
  `payment_status` int(11) NOT NULL,
  `appoint_status` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The form processing script to create the appointment

   <?php    

require_once 'core.php';

$valid['success'] = array('success' => false, 'messages' => array(), 'appoint_id' => '');

if($_POST) {    

    $appointDate        = date('Y-m-d H:i:s', strtotime($_POST['appointDate']));

  $clientName                   = $_POST['clientName'];
  $clientContact                = $_POST['clientContact'];
  $subTotalValue                = $_POST['subTotalValue'];
  $vatValue                         =   $_POST['vatValue'];
  $totalAmountValue     = $_POST['totalAmountValue'];
  $discount                         = $_POST['discount'];
  $grandTotalValue          = $_POST['grandTotalValue'];
  $paid                                 = $_POST['paid'];
  $dueValue                         = $_POST['dueValue'];
  $paymentType                  = $_POST['paymentType'];
  $paymentStatus                = $_POST['paymentStatus'];

    $check=mysqli_query("SELECT * from points WHERE appoint_date='$appointDate'");

    $checkrows=mysqli_num_rows($check);

   if($checkrows>0) {
      echo "appointment exists";
   } else {  
    //insert results from the form input

    $query = "INSERT IGNORE INTO points (appoint_date, client_name, client_contact, sub_total, vat, total_amount, discount, grand_total, paid, due, payment_type, payment_status, appoint_status) VALUES ('$appointDate', '$clientName', '$clientContact', '$subTotalValue', '$vatValue', '$totalAmountValue', '$discount', '$grandTotalValue', '$paid', '$dueValue', $paymentType, $paymentStatus, 1)";

     $result = mysqli_query($query) or die('Error querying database.');

    }
    echo "Customer Added";




    $appoint_id;
    $AppointStatus = false;


    if($connect->query($sql) === true) {
        $appoint_id = $connect->insert_id;
        $valid['appoint_id'] = $appoint_id; 

        $valid['appoint_id'] = $appoint_id; 




        $AppointStatus = true;
    }


    $AppointItemStatus = false;

    for($x = 0; $x < count($_POST['serviceName']); $x++) {          
        $updateServiceQuantitySql = "
SELECT service.quantity 
  FROM service 
 WHERE service.service_id = ".$_POST['serviceName'][$x]."";
        $updateServiceQuantityData = $connect->query($updateServiceQuantitySql);


        while ($updateServiceQuantityResult = $updateServiceQuantityData->fetch_row()) {
            $updateQuantity[$x] = $updateServiceQuantityResult[0] - $_POST['quantity'][$x];                         
                // update  table
                $updateServiceTable = "
UPDATE service 
   SET quantity = '".$updateQuantity[$x]."' 
 WHERE service_id = ".$_POST['serviceName'][$x]."";
                $connect->query($updateServiceTable);

                $appointItemSql = "
INSERT INTO point_item 
( appoint_id
, service_id
, quantity
, rate
, total
, appoint_item_id
) VALUES 
( '$appoint_id'
, '".$_POST['serviceName'][$x]."'
, '".$_POST['quantity'][$x]."'
, '".$_POST['rateValue'][$x]."'
, '".$_POST['totalValue'][$x]."', 1)";

                $connect->query($appointItemSql);       

                if($x == count($_POST['serviceName'])) {
                    $appointItemStatus = true;
                }       
        } // while  
    } // /for quantity

    $valid['success'] = true;
    $valid['messages'] = "Successfully Added";      

    $connect->close();

    echo json_encode($valid);

} // /if $_POST
// echo json_encode($valid);

The code above works and the problem is for a record displays error to add a unique date. However, for a duplicate record it fails to add. I need help to create a unique date and time to avoid duplication of the appointment making a appointment unique until it is deleted.

If a duplicate row is found on entering a data the combination it should display the message the appointment is already listed. If no duplicate row is found on entering a appointment date on the form it should insert the to the database and display the message "added". I unsure of the correct method to select or insert.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Usmaan Gill
  • 3
  • 5
  • 1
    [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Apr 17 '17 at 15:39
  • my focus is for the functionality this is for testing purposes – Usmaan Gill Apr 17 '17 at 15:40
  • 4
    If you don't have time to do it right the first time, when will you find the time to add it later? I hate when people say *"I'm not that far along..."* or *"This site will not be public..."* or *"It's only for school, so security doesn't matter..."*. If teachers and professors are not talking about security from day one, they're doing it wrong. Challenge them. They're teaching sloppy and dangerous coding practices which students will have to unlearn later. I also hate it when folks say, *"I'll add security later..."* or *"Security isn't important now..."* or *"Ignore the security risk..."*. – Jay Blanchard Apr 17 '17 at 15:43

0 Answers0