How to validate if certain value is duplicate or if certain input field is not filled using PHP AJAX Javascript

[Borkg85]

Hi, 

This is a simple REST API for listing, creating and deleting products in/from database. I am having issues with validating/checking if certain values duplicate or if certain input field is not filled... I've attempted doing that with rowCount for the duplicate, in the php file, however I think there must be a better way for this, however I am having issues finding out how to do that... 

here is the post.php

$query_check = 'SELECT * FROM skandi WHERE sku = :sku'; 
    $stmt_check = $this->conn->prepare($query_check);
    $stmt_check->bindParam(':sku', $this->sku);
    $stmt_check->execute();

    if ($stmt_check->rowCount() > 0) {
        echo 'product name already exists!';
    } else {
    $query = 'INSERT INTO ' . $this->table . '
            SET             
            
            sku = :sku,
            name = :name,
            price = :price,
            productType = :productType,
            size = :size,
            weight = :weight,
            height = :height,
            length = :length,
            width = :width';

            $stmt = $this->conn->prepare($query);
          
            $this->sku;
            $this->name; 
            $this->price; 
            $this->productType; 
            $this->size;
            $this->weight;
            $this->height;
            $this->length;
            $this->width;

            $stmt->bindParam(':sku', $this->sku);
            $stmt->bindParam(':name', $this->name);
            $stmt->bindParam(':price', $this->price);
            $stmt->bindParam(':productType', $this->productType);
            $stmt->bindParam(':size', $this->size);
            $stmt->bindParam(':weight', $this->weight);
            $stmt->bindParam(':height', $this->height);
            $stmt->bindParam(':length', $this->length);
            $stmt->bindParam(':width', $this->width);

            if($stmt->execute()) {
                return true;

            } else {
                ini_set('display_errors',1);

                return false;
            }

        }

this is the ajaxcall for posting... 

$(document).ready(function () {
  $("#saveBtn").click(function (e) {
    e.preventDefault();

    //serialize form data
    var url = $("form").serialize();

    //function to turn url to an object
    function getUrlVars(url) {
      var hash;
      var myJson = {};
      var hashes = url.slice(url.indexOf("?") + 1).split("&");
      for (var i = 0; i < hashes.length; i++) {
        hash = hashes[i].split("=");
        myJson[hash[0]] = hash[1];
      }
      return JSON.stringify(myJson);
    }
         
    //pass serialized data to function
    var test = getUrlVars(url);

    //post with ajax
    $.ajax({
      type: "POST",
      url: "/api/post/create.php",
      data: test,
      ContentType: "application/json",

      success: function () {
        alert("successfully posted");
      },
      error: function () {
        
        console.log("Could not be posted");
      },
    });
  });
});

Any assistance, suggestion will be welcomed, thanks...

[benanamen]

You should not be checking for duplicates. Set a unique constraint on the relevant column and then attempt the insert. Catch the duplicate error if any and handle however you want.

[Borkg85]

Hi, 

Because I am in a learning stage, do I catch the duplicate error in ajax or in the post php and relay message via ajax? 

[mac_gyver]

the catching of the database statement error would be in the server-side code, where you would detect if the error number is for a duplicate index error, and setup a message telling the user what was wrong with the data that they submitted, which in this case would be that the sku already exists.

here's the whole story - you always need error handling for statements that can fail. for database statements that can fail - connection, query, prepare, and execute, the simplest way of adding error handling, without adding logic at each statement, is to use exceptions for errors and in most cases simply let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) the exception to this rule is when inserting/updating duplicate or out of range user submitted data values, which are the only kind of database errors that the 'user' on your site can recover from (all other database errors are either due to programming mistakes, the database server not running, or nefarious activity, that the user doesn't need to know anything at all about), by potentially entering new value(s) and resubmitting the data. in these cases, your code would catch the exception, test if the error number is for something that your code is responsible for handling, and setup a message for the user letting them know what was wrong with the data that they submitted. for all other error numbers, just re-throw the exception and let php handle it. you would set the PDO error mode to exceptions when you make the connection.

the net result of submitting the form data to the server-side code for this Create/insert operation should be either a success value/message or failure message(s) - consisting of all the user/validation errors. since you are making an ajax request, you should create an array with a 'success' or 'error' key, with the appropriate values, json_encode() this and output it to the browser for the ajax success code to test and use.

some points about the posted code -

1. OOP is not about wrapping a class definitions around parts of your main code and adding $var-> in front of everything to get it to work. all this is doing is exchanging one defining/calling syntax for another one, taking 2-3 times the amount of typing to accomplish a task.
2. you won't be using a SELECT query anymore for this current operation, but when you do use a select query, list out the columns you are selecting. for cases where you just need a count of the number of matching rows, use a SELECT COUNT(*) ... query, then fetch and use the count value. related to this, the rowCount() method may not work for a data retrieval query for all database types, and should be avoided for these type of queries. you should always fetch all the data that a query returns (the current code should be producing an out of sync error since you didn't fetch the row(s) of data from the select query.) also, for a select query, the only variable name that must be unique is the final variable holding the data fetched from the query. just use simple names like $sql, $stmt, ... for the variables in the common code.
3. writing out variables/properties by themselves does nothing (doesn't even produce any php byte code.) why do you have those  9 lines in there?
4. if you use simple positional ? place-holders and implicit binding, by supplying an array to the ->execute([...]) call, it will save a ton of repetitive typing and typo mistakes.
5. any php error related settings should be in the php.ini on your system so that they can be changed at a single point. having a display_errors setting inside some conditional logic in your code makes no sense.

 

[Borkg85]

On 10/13/2022 at 10:27 AM, mac_gyver said:

the catching of the database statement error would be in the server-side code, where you would detect if the error number is for a duplicate index error, and setup a message telling the user what was wrong with the data that they submitted, which in this case would be that the sku already exists.

here's the whole story - you always need error handling for statements that can fail. for database statements that can fail - connection, query, prepare, and execute, the simplest way of adding error handling, without adding logic at each statement, is to use exceptions for errors and in most cases simply let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) the exception to this rule is when inserting/updating duplicate or out of range user submitted data values, which are the only kind of database errors that the 'user' on your site can recover from (all other database errors are either due to programming mistakes, the database server not running, or nefarious activity, that the user doesn't need to know anything at all about), by potentially entering new value(s) and resubmitting the data. in these cases, your code would catch the exception, test if the error number is for something that your code is responsible for handling, and setup a message for the user letting them know what was wrong with the data that they submitted. for all other error numbers, just re-throw the exception and let php handle it. you would set the PDO error mode to exceptions when you make the connection.

the net result of submitting the form data to the server-side code for this Create/insert operation should be either a success value/message or failure message(s) - consisting of all the user/validation errors. since you are making an ajax request, you should create an array with a 'success' or 'error' key, with the appropriate values, json_encode() this and output it to the browser for the ajax success code to test and use.

some points about the posted code -

1. OOP is not about wrapping a class definitions around parts of your main code and adding $var-> in front of everything to get it to work. all this is doing is exchanging one defining/calling syntax for another one, taking 2-3 times the amount of typing to accomplish a task.
2. you won't be using a SELECT query anymore for this current operation, but when you do use a select query, list out the columns you are selecting. for cases where you just need a count of the number of matching rows, use a SELECT COUNT(*) ... query, then fetch and use the count value. related to this, the rowCount() method may not work for a data retrieval query for all database types, and should be avoided for these type of queries. you should always fetch all the data that a query returns (the current code should be producing an out of sync error since you didn't fetch the row(s) of data from the select query.) also, for a select query, the only variable name that must be unique is the final variable holding the data fetched from the query. just use simple names like $sql, $stmt, ... for the variables in the common code.
3. writing out variables/properties by themselves does nothing (doesn't even produce any php byte code.) why do you have those  9 lines in there?
4. if you use simple positional ? place-holders and implicit binding, by supplying an array to the ->execute([...]) call, it will save a ton of repetitive typing and typo mistakes.
5. any php error related settings should be in the php.ini on your system so that they can be changed at a single point. having a display_errors setting inside some conditional logic in your code makes no sense.

 

Hi, first off thanks for the great insights, since I am in a learning stage they are very welcomed... I attempted with the COUNT (*) query, however I guess I am doing something wrong, because it's duplicating existing data. With regards to the validation, I was thinking of doing jQuery validation for required inputs, before posting.

I will provide the changes here, I cannot seem to find edit option in my original post so I don't have to double post code...

   public function create () {
    try {
        $query = 'SELECT COUNT(*) FROM ' . $this->table . ' WHERE sku = "$this->sku"';
        $stmt = $this->conn->prepare($query);
        // $stmt->bindParam(':sku', $this->sku);
        $stmt->execute();
        $data = $stmt->fetch(PDO::FETCH_COLUMN);
        
        if ($data > 0) {
            throw new Exception('SKU already exists');
        }

        $query = 'INSERT INTO ' . $this->table . '
        SET             
        
        sku = :sku,
        name = :name,
        price = :price,
        productType = :productType,
        size = :size,
        weight = :weight,
        height = :height,
        length = :length,
        width = :width';

        $stmt = $this->conn->prepare($query);
      
        $stmt->bindParam(':sku', $this->sku);
        $stmt->bindParam(':name', $this->name);
        $stmt->bindParam(':price', $this->price);
        $stmt->bindParam(':productType', $this->productType);
        $stmt->bindParam(':size', $this->size);
        $stmt->bindParam(':weight', $this->weight);
        $stmt->bindParam(':height', $this->height);
        $stmt->bindParam(':length', $this->length);
        $stmt->bindParam(':width', $this->width);

        if($stmt->execute()) {
            return true;
        } 

    } catch (PDOException $e) {
        echo $e->getMessage();
    }
    }

 

[gizmola]

It's fine to add code in a thread.  That's not really double posting the way we look at it.  The main problem that happens is when people change their code over the course of a thread, and don't post the current version, leading to lots of confusion.  We would rather have people repost code so it's clear to everyone what the current code looks like.

Don't do this:

$query = 'SELECT COUNT(*) FROM ' . $this->table . ' WHERE sku = "$this->sku"';

You have introduced a possible SQL injection.  Bind all parameters whether that be from insert/update/delete (DML) or select.

Alternatively to doing this, what mac suggested is that you wrap your insert in a try/catch.  If you have a relevant constraint on sku (primary key or unique index) on that attribute the database itself will disallow the query with a constraint violation.  You then handle the database exception.  There's a couple arguments for that approach:

• You don't add a SELECT every time since it really is an exception
• You will need to have made sure that your database has the proper constraints so that duplication sku's can't happen which is more robust
  • Furthermore it is handled in mysql via a unique index, which means that any queries you do that might need to search by sku will be covered by that same index
• There are other errors that can occur and your code will catch those and send back an appropriate error, rather than possibly leaving your UI in an incomplete state because of a runtime error within the ajax script

 

 

[mac_gyver]

the main point behind just attempting to insert the data and test for a unique index error, is because your database must enforce uniqueness. it is the last step in the process. when there are multiple concurrent requests to your current code, a race condition exists where all the instances will find from the SELECT query logic that the value doesn't exist. they will then all attempt to run the INSERT query, resulting in either duplicate data (if your column(s) are not defined as unique indexes), or the first query will win the race, with the others resulting in duplicate errors. since your database must enforce uniqueness, you might as will eliminate the initial SELECT query, since it isn't doing anything useful.

as to why your current code with the SELECT query, which you are going to eliminate from the design, isn't finding if the value exists, is because php variables/class-properties are not replaced with their value when enclosed in over-all single-quoted strings. had you used a proper prepared query, which the first code at the top of this thread was doing, this problem wouldn't have occurred.

also, the name column should be defined as a unique index as well, i.e. both the sku and name must be unique.

7 hours ago, Borkg85 said:

I was thinking of doing jQuery validation for required inputs, before posting

client-side validation is a nicety for legitimate visitors. data submitted to your web site can come from anywhere, not just your code, can be anything, and cannot be trusted. you MUST trim (mainly so that you can detect if all white-space characters were entered), and validate the data on the server before using it.

[Borkg85]

Hi, thanks to everyone for the support so far. I have succeeded in solving the jquery/ajax validate/post data with a trim value example that I found in the stackoverflow forums, I don't know if its valid, and I would like to do it better in PHP, as well as the setting of the sku and name as unique constraints and try/catching them... It is working although the JSON does report that Product is not created, but it does appear in the database. I don't know what's causing this. Also I wanted to ask @mac_gyver if you could point me to some materials so I could better understand the trim and validate server side before using. 

the post.php for the create function...

public function create () {
    try {
        
        $query = 'INSERT INTO ' . $this->table . '
        SET             
        
        sku = :sku,
        name = :name,
        price = :price,
        productType = :productType,
        size = :size,
        weight = :weight,
        height = :height,
        length = :length,
        width = :width';

        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':sku', $this->sku);
        $stmt->bindParam(':name', $this->name);
        $stmt->bindParam(':price', $this->price);
        $stmt->bindParam(':productType', $this->productType);
        $stmt->bindParam(':size', $this->size);
        $stmt->bindParam(':weight', $this->weight);
        $stmt->bindParam(':height', $this->height);
        $stmt->bindParam(':length', $this->length);
        $stmt->bindParam(':width', $this->width);
        
        $stmt->execute();
        

    } catch (PDOException $e) {
        if(str_contains($e, '1062 Duplicate Entry')) {
            // header('Location: ajaxProd.html');
        } {
            echo $e->getMessage();
        }
        
    }
    }

the create.php...

<?php

//Headers
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json');
header('Access-Control-Allow-Methods: POST');


include_once '../../config/database.php';
include_once '../../models/post.php';

//Instantiate db

$database = new Database();
$db = $database->connect();


//Instantiate post
$product = new Post($db);

//Get raw data

$data = json_decode(file_get_contents("php://input"));


$product->sku = $data->sku;
$product->name = $data->name;
$product->price = $data->price;
$product->productType = $data->productType; 
$product->size = $data->size;
$product->weight = $data->weight;
$product->height = $data->height;
$product->length = $data->length;
$product->width = $data->width;



//Create 

if($product->create()) {
    echo json_encode(
        array('message' => 'Product Created')
    );
} else {
    echo json_encode(
        array('message' => 'Product Not Created')
    );
}

and last the ajax/jquery validate/post...

$(document).ready(function () {
  $("#apiform").validate({
    rules: {
      sku: {
          required: {
            depends:function(){
              $(this).val($.trim($(this).val()));
              return true;
            }
          },
          minlength: 9,
      },
      name: {
          required: true,
          maxlength: 20,
      },
      price: {
          required: true,
          maxlength: 5,
      },
      productType: {
        required: true,
      },
      size: {
          required: true,
          maxlength: 4,
      },
      weight: {
          required: true,
          maxlength: 4,
      },
      height: {
          required: true,
          maxlength: 3,
      },
      width: {
          required: true,
          maxlength: 3,
      },
      length: {
          required: true,
          maxlength: 3,
      }
      },
      messages: {
        sku: {
          required: "Please, enter valid SKU",
          minlength: "Please enter at least 9 characters"
        },
        name: {
          required: "Please, enter valid name",
          maxlength: "Please enter "
        },
        price: {
          required: "Please, enter price",
          maxlength: ""
        },
        productType: {
          required: "Please select an option from the list",
        },
        size: {
          required: "Please, provide the size of the DVD",
          maxlength: ""
        },
        weight: {
          required: "Please, provide the weight",
          maxlength: ""
        },
        height: {
          required: "Please, provide the required dimension",
          maxlength: ""
        },
        width: {
          required: "Please, provide the required dimension",
          maxlength: ""
        },
        length: {
          required: "Please, provide the required dimension",
          maxlength: ""
        }
      }
    });

      $("#saveBtn").click(function (e) {
      e.preventDefault();
      
      if (!$("#apiform").valid()) {
        return false 
      } else {
          
              //serialize form data
    var url = $("form").serialize();

    //function to turn url to an object
    function getUrlVars(url) {
      var hash;
      var myJson = {};
      var hashes = url.slice(url.indexOf("?") + 1).split("&");
      for (var i = 0; i < hashes.length; i++) {
        hash = hashes[i].split("=");
        myJson[hash[0]] = hash[1];
      }
      return JSON.stringify(myJson);
    }
         
    //pass serialized data to function
    var test = getUrlVars(url);

    //post with ajax
    $.ajax({
      type: "POST",
      url: "/api/post/create.php",
      data: test,
      ContentType: "application/json",
      success: function () {
        alert("successfully posted");
      },
      error: function () {
        
        alert("SKU and or Name already exists");
      },
    });

      };
  
  });

});

 

[mac_gyver]

51 minutes ago, Borkg85 said:

the JSON does report that Product is not created

your ->create() method code doesn't return a specific value, so null is returned. since null is not a true boolean value, the conditional logic where you are calling that method executes the else {...} branch, which outputs 'message' => 'Product Not Created'.

the ->create() method should return a true value upon success. a great point of using exceptions for errors is that your main/in-line code only 'sees' error free execution, since exaction transfers to the nearest correct type exception handler, or to php's exception handler if there's none in your code, upon an error. what this means is that if your code reaches the line after the ->execute() call, that the insert query was successful. it is at this point where you would return a true value.

1 hour ago, Borkg85 said:

      error: function () {
        
        alert("SKU and or Name already exists");
      },

the .ajax error code is for if the ajax request fails, meaning that the http(s) request didn't work at all. this doesn't mean that the server-side operation failed. the .ajax success code will be executed if the ajax request works correctly. you must access the json data in the success code.

1 hour ago, Borkg85 said:

if you could point me to some materials so I could better understand the trim and validate server side before using.

see the example code in this post - https://forums.phpfreaks.com/topic/315403-saving-to-mysql-database-not-working/?do=findComment&comment=1601407

 

[Borkg85]

17 hours ago, mac_gyver said:

your ->create() method code doesn't return a specific value, so null is returned. since null is not a true boolean value, the conditional logic where you are calling that method executes the else {...} branch, which outputs 'message' => 'Product Not Created'.

the ->create() method should return a true value upon success. a great point of using exceptions for errors is that your main/in-line code only 'sees' error free execution, since exaction transfers to the nearest correct type exception handler, or to php's exception handler if there's none in your code, upon an error. what this means is that if your code reaches the line after the ->execute() call, that the insert query was successful. it is at this point where you would return a true value.

the .ajax error code is for if the ajax request fails, meaning that the http(s) request didn't work at all. this doesn't mean that the server-side operation failed. the .ajax success code will be executed if the ajax request works correctly. you must access the json data in the success code.

see the example code in this post - https://forums.phpfreaks.com/topic/315403-saving-to-mysql-database-not-working/?do=findComment&comment=1601407

 

Hi @mac_gyver I placed the data from json into an array, and that seems to have solved the issue, since it is printing success message and status: 200; Although not completely sure why, and the same principle doesn't work for the delete.php, but I won't bother you with that. 

Here is the corrected code... 

<?php

//Headers
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/x-www-form-urlencoded');
header('Access-Control-Allow-Methods: POST');
header('Access-Control-Allow-Headers: Access-Control-Allow-Headers,Content-Type,Access-Control-Allow-Methods, Authorization, X-Requested-With');

include_once '../../config/database.php';
include_once '../../models/post.php';

//Instantiate db

$database = new Database();
$db = $database->connect();


//Instantiate post
$product = new Post($db);

//Get raw data

$json = json_decode(file_get_contents("php://input"));

$data = [];

if(isset($json)) {
    $data = [
        'sku' => $json->sku,
        'name' => $json->name,
        'price' => $json->price,
        'productType' => $json->productType,
        'size' => $json->size,
        'weight' => $json->weight,
        'height' => $json->height,
        'width' => $json->width,
        'length' => $json->length,
    ];

} else {
    echo json_encode(array('message' => 'Error'));
    exit();
}

$product->sku = $data['sku'];
$product->name = $data['name'];
$product->price = $data['price'];
$product->productType = $data['productType']; 
$product->size = $data['size'];
$product->weight = $data['weight'];
$product->height = $data['height'];
$product->length = $data['length'];
$product->width = $data['width'];

//Create 

try {

$product->create();
$response = [
    'message' => "Created Successfully",
        'status' => 200
];
    echo json_encode($response);

} catch (\Throwable $e) {
    $response = [
        'message' => $e->getMessage()
    ];
    echo json_encode($response);
}

With regards to the example, where should I incorporate this, create another class/validate.php file which would be somehow connected with the post.php. With regards to the error messages placement, could they be somehow connected with the jquery.validate, since my form is in a .html file... Here it is...

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js" type="text/javascript"></script>
    <script src="ajaxCall.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.0/jquery.validate.min.js"></script>

    
    
    
    <link rel="stylesheet" href="style.css" />
  <head>
  <body>
    <header>
      <h2>Product List</h2>
      <nav>
        <button class="add-btn" id="saveBtn" type="submit" name="save" value='save' form="apiform">Save</button>
        <button id="cancelBtn" action="action" type="button" 
        value="Cancel" onclick="location.href='ajaxCall.html'"
        >
          CANCEL
        </button>
      </nav>
    </header>
    
    <section>
    <section class="product-form">
      <form id="apiform" name="apiform">
        <div class="input-data">
            <label id="sku-label" for="sku">SKU</label>
            <input type="text" name="sku" id="sku" class="form-control" placeholder="#sku" autocomplete="on"/>
    
          <div class="input-data">
            <label id="name-label" for="name">Name </label>
            <input type="text" name="name" id="name" class="form-control" placeholder="#name" autocomplete="on" />
          </div>
          <div class="input-data">
            <label id="price-label" for="price">Price ($)</label>
            <input type="number" name="price" id="price" class="form-control" placeholder="#price"  autocomplete="on" />
          </div>
    
          
          <div class="input-switcher">
            <label for="productType">Type Switcher</label>
            <select id="productType" name="productType" value="productType" onchange="selectChanged()" >
              <option value="" style="visibility: hidden;">Please select</option>
              <option value="dvd">DVD</option>
              <option value="book">Book</option>
              <option value="furniture">Furniture</option>
            </select>
          </div>
          <div id="dvd">
            <div class="data-input product">
              <label for="size">Size (MB):</label>
              <input type="number" id="size" name="size" class="form-control " autocomplete="on">
              <p class="describe">Please provide size in (MB)</p>
            </div>
          </div>
    
          <div id="book">
            <div class="data-input product">
              <label for="weight">Weight (KG):</label>
              <input type="number" id="weight" name="weight" class="form-control " autocomplete="on">
              <p class="describe">Please provide weight in KG</p>
            </div>
    
          </div>
    
          <div id="furniture">
            <div class="data-input product">
              <label for="height">Height (CM):</label>
              <input type="number" id="height" name="height" class="form-control " autocomplete="on">
              <p class="describe">Please provide measurements in (CM)</p>
            </div>
            <div class="data-input product">
              <label for="length">Length (CM):</label>
              <input type="number" id="length" name="length" class="form-control" autocomplete="on">
              <p class="describe">Please provide measurements in (CM)</p>
            </div>
            <div class="data-input product">
              <label for="width">Width (CM):</label>
              <input type="number" id="width" name="width" class="form-control " autocomplete="on">
              <p class="describe">Please provide measurements in (CM)</p>
            </div>
    
          </div>
      </form>
    </section>
    <script text="type/javascript">
        function selectChanged() {
    var sel = document.getElementById("productType");
    let dvd = document.getElementById("dvd");
    let book = document.getElementById("book");
    let furniture = document.getElementById("furniture");
    for (var i = 1; i < 4; i++) {
      dvd.style.display = sel.value == "dvd" ? "block" : "none";
      book.style.display = sel.value == "book" ? "block" : "none";
      furniture.style.display = sel.value == "furniture" ? "block" : "none";
    }
  }
    </script>

Cannot be thankful enough for the help so far... 

[mac_gyver]

On 10/22/2022 at 2:06 AM, Borkg85 said:

I placed the data from json into an array

while you should keep the posted form data as an array and operate on elements of this array throughout the rest of the code. e.g. in the validation logic and the ->create() logic, what you are actually doing is decoding the json to an object, manually building an array from those properties, then manually setting the product class properties from the elements in that array. all of this typing for every field/column and copying properties/variables to other properties/variables is not necessary and is a waste of typing time. just set the 2nd parameter of json_decode() to a true value and you will directly end up with an array of data.

next, related to item #1 in the list in my first post in this thread, user written classes/functions should be general-purpose and reusable. your class methods/properties should not contain any application specific values, code, queries, or html markup. they should instead do useful things that help you to produce applications. the way to make your code general-purpose is to use a data-driven design, where you have a data structure (array) that defines the fields/columns, display labels, validation rules, and type of processing each field is to be used for. you would use this defining data structure to control what the general-purpose code will do. this will eliminate all of the repetitive application specific typing you have in the current code.

On 10/22/2022 at 2:06 AM, Borkg85 said:

it is printing success message and status: 200

the 'status' => 200 value that you are putting into the json encoded data has nothing to do with the http status value that the ajax code uses for the success/error sections. the status value that the ajax code receives is something that the web server outputs as part of the response header.

as already stated, the ->create() code needs to return a true value at the point that it determines that the insert query was successfully executed. it needs to return a false value when it determines that the insert query failed due to a duplicate index error number. the code that calls the ->create() method must test the returned value to determine what message to set up, json encode, and echo as the response. for a true returned value, you would setup the "Created Successfully" message. for a false returned value, if you only have one unique column, you would just setup a 'canned' message that the value submitted for that column is already in use. however, for what you are doing, the sku and the name columns must both be defined as unique indexes. the insert query will fail at the first duplicate index. you would like to tell the user all the columns that have duplicate values at once, so that they don't need to keep correcting and resubmitting the values one at a time. to do this you would execute a SELECT query for just the unique columns and setup a message for each column that contains a value matching the submitted form value in the result set. you would then output the resulting duplicate error message(s) in the json encoded response.

On 10/22/2022 at 2:06 AM, Borkg85 said:

With regards to the example, where should I incorporate this

the example code outlines what the post method form processing code should do. this would become part of the create.php code. since you are using ajax to submit the form data, you would only need the initialization and post method form processing sections from the example.

the post method form processing would -

1. detect that a post method form was submitted
2. json_decode the json data to an array, that you would then use as the input data for the validation logic and supply as a call-time parameter to the ->create($data) method.
3. trim all the elements in the data array at once. since this is an array, you can use php's array functions to operate on it, such as array_map() to apply php's trim function to all the elements in the array.
4. validate all the elements in the array, storing validation errors in an array using the field name as the main array index.
5. after all the validation logic, if there are no errors, the array holding the user/validation errors is empty, you would use the form data. it is at this point that you would call the ->create() method and test the returned value from that call. if the returned value is false, you would add the duplicate data messages to the array holding the user/validation errors.
6. at the point near the end, where the - // if no errors, success comment is, is where you would build the $response array, with either a success message or the user/validation error messages, json encode it, and echo it.

 

[Strider64]

I just want to add it seems like you want to validate the data after it has been sent that to me doesn't makes sense to me. 🤔 To me this invalidates the reason you are using Ajax in the first place. I could be looking at the code wrong?

Edited October 24, 2022 by Strider64

[Borkg85]

2 hours ago, Strider64 said:

I just want to add it seems like you want to validate the data after it has been sent that to me doesn't makes sense to me. 🤔 To me this invalidates the reason you are using Ajax in the first place. I could be looking at the code wrong?

Elaborate, if you will... I would like to know what should I be doing differently, so I can improve the code and of course learn. 

[mac_gyver]

3 hours ago, Strider64 said:

I just want to add it seems like you want to validate the data after it has been sent that to me doesn't makes sense to me. 🤔 To me this invalidates the reason you are using Ajax in the first place. I could be looking at the code wrong?

i want to know what you mean by this as well, since -

On 10/17/2022 at 4:27 PM, mac_gyver said:

client-side validation is a nicety for legitimate visitors. data submitted to your web site can come from anywhere, not just your code, can be anything, and cannot be trusted. you MUST trim (mainly so that you can detect if all white-space characters were entered), and validate the data on the server before using it.

 

[Borkg85]

So @mac_gyver I've succeeded at putting this together in the create.php...

<?php

//Headers
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json, true');
header('Access-Control-Allow-Methods: POST');
header('Access-Control-Allow-Headers: Access-Control-Allow-Headers,Content-Type,Access-Control-Allow-Methods, Authorization, X-Requested-With');

include_once '../../config/database.php';
include_once '../../models/post.php';

//Instantiate db

$database = new Database();
$db = $database->connect();


//Instantiate post
$product = new Post($db);

//Get raw data

$json = json_decode(file_get_contents("php://input"), true);

$post = [];
$errors = [];

$post = array_map('trim', $json);

if ($post['sku'] === '' || $post['name'] === '' || $post['price'] === '' || $post['productType'] === '' || 
$post['size'] === '' || $post['weight'] === '' || $post['height'] === '' || 
$post['length'] === '' || $post['width'] === '') {

    $errors['sku'] = 'Sku is required';
    $errors['name'] = 'Sku is required';
    $errors['price'] = 'Sku is required';
    $errors['productType'] = 'Sku is required';
    $errors['size'] = 'Sku is required';
    $errors['weight'] = 'Sku is required';
    $errors['height'] = 'Sku is required';
    $errors['length'] = 'Sku is required';
    $errors['width'] = 'Sku is required';

}

$product->sku = $post['sku'];
$product->name = $post['name'];
$product->price = $post['price'];
$product->productType = $post['productType']; 
$product->size = $post['size'];
$product->weight = $post['weight'];
$product->height = $post['height'];
$product->length = $post['length'];
$product->width = $post['width'];

if(empty($errors)){

    try {

        $product->create();
        
        $response = [
            'message' => "Created Successfully",
                'status' => 200
        ];
            echo json_encode($response);
        
        } catch (\Throwable $e) {

            $query = "SELECT DISTINCT sku, name FROM ' . $this->table . '";
            $stmt = $this->conn->prepare($query);
            // $stmt->execute(['sku', 'name']);

            if($stmt->execute(['sku']) == 1062) {
                $errors['sku'] = 'sku already in use';
            } else if ($stmt->execute(['name']) == 1062) {

                $errors['name'] = 'name already in use';
              
            } else {
                $response = [
                    'message' => $e->getMessage()
                ];
                echo json_encode($response);
            }

            
        }
}

//Create 

It doesn't give me any errors in the network tab, however when I alert the (data) in ajax I get [object, Object], so I'm guessing there is something seriously wrong. I am currently searching how to output the error messages from php through ajax... 

$(document).ready(function () {
  // $("#apiform").validate({
  //   rules: {
  //     sku: {
  //         required: {
  //           depends:function(){
  //             $(this).val($.trim($(this).val()));
  //             return true;
  //           }
  //         },
  //         minlength: 9,
  //     },
  //     name: {
  //         required: true,
  //         maxlength: 20,
  //     },
  //     price: {
  //         required: true,
  //         maxlength: 5,
  //     },
  //     productType: {
  //       required: true,
  //     },
  //     size: {
  //         required: true,
  //         maxlength: 4,
  //     },
  //     weight: {
  //         required: true,
  //         maxlength: 4,
  //     },
  //     height: {
  //         required: true,
  //         maxlength: 3,
  //     },
  //     width: {
  //         required: true,
  //         maxlength: 3,
  //     },
  //     length: {
  //         required: true,
  //         maxlength: 3,
  //     }
  //     },
  //     messages: {
  //       sku: {
  //         required: "Please, enter valid SKU",
  //         minlength: "Please enter at least 9 characters"
  //       },
  //       name: {
  //         required: "Please, enter valid name",
  //         maxlength: "Please enter "
  //       },
  //       price: {
  //         required: "Please, enter price",
  //         maxlength: ""
  //       },
  //       productType: {
  //         required: "Please select an option from the list",
  //       },
  //       size: {
  //         required: "Please, provide the size of the DVD",
  //         maxlength: ""
  //       },
  //       weight: {
  //         required: "Please, provide the weight",
  //         maxlength: ""
  //       },
  //       height: {
  //         required: "Please, provide the required dimension",
  //         maxlength: ""
  //       },
  //       width: {
  //         required: "Please, provide the required dimension",
  //         maxlength: ""
  //       },
  //       length: {
  //         required: "Please, provide the required dimension",
  //         maxlength: ""
  //       }
  //     }
  //   });

      $("#saveBtn").click(function (e) {
      e.preventDefault();
      
      if (!$("#apiform").valid()) {
        return false 
      } else {
          
              //serialize form data
    var url = $("form").serialize();

    //function to turn url to an object
    function getUrlVars(url) {
      var hash;
      var myJson = {};
      var hashes = url.slice(url.indexOf("?") + 1).split("&");
      for (var i = 0; i < hashes.length; i++) {
        hash = hashes[i].split("=");
        myJson[hash[0]] = hash[1];
      }
      return JSON.stringify(myJson);
    }
         
    //pass serialized data to function
    var test = getUrlVars(url);

    //post with ajax
    $.ajax({
      type: "POST",
      url: "/api/post/create.php",
      data: test,
      contentType: "application/json; charset=UTF-8",
      success: function (data) {
        // location.reload();
        alert("successfully posted");
      },
      error: function (data) {
        
        alert("SKU and or Name already exists");
      },
    });

      };
  
  });

});

 

[mac_gyver]

1 hour ago, Borkg85 said:

$product->create();

I've written at least twice that the ->create() method must return a success or failure value that you test in the calling code. the database specific code in the ->create() method is where that information is known at.

here's what your create.php should look like (without the multiple column duplicate determination code) -

<?php

// when using ajax, the only thing this code will do is handle the post method form processing
// you can save resources on non-post requests by putting all the code inside the request method test

// initialization
//Headers
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/x-www-form-urlencoded');
header('Access-Control-Allow-Methods: POST');
header('Access-Control-Allow-Headers: Access-Control-Allow-Headers,Content-Type,Access-Control-Allow-Methods, Authorization, X-Requested-With');

// use 'require' for things your code must have for it to work
require '../../config/database.php';
require '../../models/post.php';

//Instantiate db
$database = new Database();
$db = $database->connect();

// define the $table and $fields for the Create operation
$table = 'skandi';
$fields = [];
$fields['sku'] = ['label'=>'SKU','validation'=>['required']];
// add other field definitions here...

//Instantiate post for the Create operation
$product = new Post($db,$table,$fields);

$post = []; // array to hold a trimmed working copy of the form data
$errors = []; // array to hold user/validation errors

// post method form processing
if($_SERVER["REQUEST_METHOD"]==="POST")
{
	//Get raw data
	$json = json_decode(file_get_contents("php://input"),true);

	// trim all the input data at once
	$post = array_map('trim',$json); // if any input is an array, use a recursive trim call-back function here instead of php's trim
	
	// validate all inputs
	foreach($fields as $field=>$arr)
	{
		if(isset($arr['validation']) && is_array($arr['validation']))
		{
			foreach($arr['validation'] as $rule)
			{
				switch ($rule)
				{
				case 'required' :
				if($post[$field] === '')
				{
					$errors[$field] = "{$arr['label']} is required";
				}
				break;
				
				// add code for other validation rules here...
				}
			}
		}
	}
	
	// if no errors, use the input data
	if(empty($errors))
	{
		//Create
		if(!$product->create($post))
		{
			// initially, just setup a canned message for the sku column
			$errors['sku'] = "SKU is already in use";
			// the code to detect which of multiple columns contain duplicates would replace the above line of code
		}
	}
	
	// if no errors, success
	if(empty($errors))
	{
		$response = [
			'message' => "Created Successfully"
		];
	}
	else
	{
	    $response = [
		'message' => implode('<br>',$errors)
		];
	}
	echo json_encode($response);
}

the corresponding ->create() method would be -

	public function create($data)
	{
		// build the sql query
		$set_terms = [];
		$params = [];
		foreach(array_keys($this->fields) as $field)
		{
			$set_terms[] = "`$field`=?";
			$params[] = $data[$field];
		}
		$sql = "INSERT INTO `$this->table` SET " . implode(',',$set_terms);
		$stmt = $this->conn->prepare($sql);
		try { // a 'local' try/catch to handle a specific error type
			$stmt->execute($params);
			// if you are at this point, the query executed successfully
			return true;
		} catch (PDOException $e) {
			if($e->errorInfo[1] == 1062) // duplicate key error number
			{
				return false;
			}
			throw $e; // re-throw the pdoexception if not handled by this logic
		}
	}

as to the server-side validation logic, you should validate each input separately and setup a unique and helpful message for each validation error. the code posted in this reply shows how to use a data-driven design to dynamically validate the data and build the sql query. however, it just occurred to me that the product characteristics (size, weight, height, length, and width) change depending on the selected productType, so the data-driven definition would need entries for each productType value (you should also have a separate product characteristic table, rather than columns in this main table for these characteristic values, where you would only insert rows for each characteristic that exists for each defined product.)

1 hour ago, Borkg85 said:

how to output the error messages from php through ajax... 

for debugging, just temporarily output (echo/print_r/var_dump) things in the php code, and send everything to the console log in the javascript -

      success: function (data) {
        console.log(data);
        alert("successfully posted");
      },

 

Edited October 24, 2022 by mac_gyver

[Borkg85]

15 hours ago, mac_gyver said:

so the data-driven definition would need entries for each productType value

Yes, as I was writing your suggestions, I noticed that. I will try to see, how and if I could correct this.   In another note according to the task using switch is not allowed, if/else conditionals are ok, but not if used to often... 

15 hours ago, mac_gyver said:

I've written at least twice that the ->create() method must return a success or failure value that you test in the calling code.

Yes, I totally misunderstood that, I'm sorry.

[Borkg85]

With the code as is provided it outputs the following in the network tab of the console... 

Warning:  Undefined property: Post::$fields in models\post.phpon line 55
Fatal error:  Uncaught TypeError: array_keys(): Argument #1 ($array) must be of type array, null given in models\post.php:55
Stack trace:
#0 models\post.php(55): array_keys(NULL)
#1 api\post\create.php(127): Post->create(Array)
#2 {main}
  thrown in models\post.phpon line 55

 

[mac_gyver]

i'm going to guess that you didn't write the OOP post class or if you did, you copied something you saw and didn't actually learn the meaning of what you were doing?

in your existing OOP post class definition, there's a __construct() method that looks like -

	public function __construct($db)
	{
		$this->conn = $db;
	}

you need to add the $table and $fields input parameters and class property assignments.

[Borkg85]

3 hours ago, mac_gyver said:

i'm going to guess that you didn't write the OOP post

I did that, however I totally omitted the fact that I need to add the parameters, and spent several hours, trying to find what is the problem, after which I just had to post here and realize the stupid mistake I made...

I wanted to post before all this, if maybe adding different foreach loops is a viable solution with regards to the dropdown menu and the values included there? 

foreach(array_keys($this->productType) as $type) {
        $set_terms[] = "`$type`=?";
			$params[] = $data[$type];
    }

And also now, my read() and delete() methods aren't functioning, due to undefined variables, I will try to solve this somehow...

Thank you for the support so far, it is invaluable to me, since it's hard to find good quality instructions that help me learn and do better. 

[Borkg85]

Okay, the aforementioned is definitively not the way to go, will keep searching... 

[mac_gyver]

if the last snippet of code, with the foreach() loop using $this->productType, was an attempt at 'fixing' the last error, that's not it. there was nothing wrong with the code i posted. it was in fact tested code. there is however, something that needs to be changed about the __construct() code in that class, which i wrote a reply stating.

at this point, i don't think you understand what your OOP code is doing, so, wont be able to modify it or troubleshoot it when any errors occur. whatever learning resource you are using, isn't teaching you the meaning of what you are doing.

[Borkg85]

1 hour ago, mac_gyver said:

if the last snippet of code, with the foreach() loop using $this->productType, was an attempt at 'fixing' the last error, that's not it. there was nothing wrong with the code i posted.

No, that was my question/'attempt' if maybe that is the way to go at, with regards to the dropdown menu values.

My mistakes mentioned before were fixed, the code you provided is of course working.

My learning resources are videos and forums, and I try to make sense of that... I am open to suggestions in that regard, since i kinda find this very intriguing... 

[Borkg85]

On 10/24/2022 at 9:10 PM, mac_gyver said:

I've written at least twice that the ->create() method must return a success or failure value that you test in the calling code. the database specific code in the ->create() method is where that information is known at.

here's what your create.php should look like (without the multiple column duplicate determination code) -

<?php

// when using ajax, the only thing this code will do is handle the post method form processing
// you can save resources on non-post requests by putting all the code inside the request method test

// initialization
//Headers
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/x-www-form-urlencoded');
header('Access-Control-Allow-Methods: POST');
header('Access-Control-Allow-Headers: Access-Control-Allow-Headers,Content-Type,Access-Control-Allow-Methods, Authorization, X-Requested-With');

// use 'require' for things your code must have for it to work
require '../../config/database.php';
require '../../models/post.php';

//Instantiate db
$database = new Database();
$db = $database->connect();

// define the $table and $fields for the Create operation
$table = 'skandi';
$fields = [];
$fields['sku'] = ['label'=>'SKU','validation'=>['required']];
// add other field definitions here...

//Instantiate post for the Create operation
$product = new Post($db,$table,$fields);

$post = []; // array to hold a trimmed working copy of the form data
$errors = []; // array to hold user/validation errors

// post method form processing
if($_SERVER["REQUEST_METHOD"]==="POST")
{
	//Get raw data
	$json = json_decode(file_get_contents("php://input"),true);

	// trim all the input data at once
	$post = array_map('trim',$json); // if any input is an array, use a recursive trim call-back function here instead of php's trim
	
	// validate all inputs
	foreach($fields as $field=>$arr)
	{
		if(isset($arr['validation']) && is_array($arr['validation']))
		{
			foreach($arr['validation'] as $rule)
			{
				switch ($rule)
				{
				case 'required' :
				if($post[$field] === '')
				{
					$errors[$field] = "{$arr['label']} is required";
				}
				break;
				
				// add code for other validation rules here...
				}
			}
		}
	}
	
	// if no errors, use the input data
	if(empty($errors))
	{
		//Create
		if(!$product->create($post))
		{
			// initially, just setup a canned message for the sku column
			$errors['sku'] = "SKU is already in use";
			// the code to detect which of multiple columns contain duplicates would replace the above line of code
		}
	}
	
	// if no errors, success
	if(empty($errors))
	{
		$response = [
			'message' => "Created Successfully"
		];
	}
	else
	{
	    $response = [
		'message' => implode('<br>',$errors)
		];
	}
	echo json_encode($response);
}

the corresponding ->create() method would be -

	public function create($data)
	{
		// build the sql query
		$set_terms = [];
		$params = [];
		foreach(array_keys($this->fields) as $field)
		{
			$set_terms[] = "`$field`=?";
			$params[] = $data[$field];
		}
		$sql = "INSERT INTO `$this->table` SET " . implode(',',$set_terms);
		$stmt = $this->conn->prepare($sql);
		try { // a 'local' try/catch to handle a specific error type
			$stmt->execute($params);
			// if you are at this point, the query executed successfully
			return true;
		} catch (PDOException $e) {
			if($e->errorInfo[1] == 1062) // duplicate key error number
			{
				return false;
			}
			throw $e; // re-throw the pdoexception if not handled by this logic
		}
	}

as to the server-side validation logic, you should validate each input separately and setup a unique and helpful message for each validation error. the code posted in this reply shows how to use a data-driven design to dynamically validate the data and build the sql query. however, it just occurred to me that the product characteristics (size, weight, height, length, and width) change depending on the selected productType, so the data-driven definition would need entries for each productType value (you should also have a separate product characteristic table, rather than columns in this main table for these characteristic values, where you would only insert rows for each characteristic that exists for each defined product.)

for debugging, just temporarily output (echo/print_r/var_dump) things in the php code, and send everything to the console log in the javascript -

      success: function (data) {
        console.log(data);
        alert("successfully posted");
      },

 

I've finished this project, and I wanted to thank @mac_gyver for the assistance, as he helped me learn a lot throughout the process... I will post the finished code here, maybe someone will find it useful...

This is the create.php

$table = 'skandi';
$fields = [];

$fields['sku'] = ['label' => 'SKU','validation' => ['required']];
$fields['name'] = ['label' => 'Name','validation' => ['required']];
$fields['price'] = ['label' => 'Price','validation' => ['required']];
$fields['productType'] = ['label' => 'Product Type','validation' => ['required'],
            'options' => ['dvd', 'book', 'furniture']];
$fields['size'] = ['label' => 'size','validation' => ['required']];
$fields['weight'] = ['label' => 'weight','validation' => ['required']];
$fields['height'] = ['label' => 'height','validation' => ['required']];
$fields['length'] = ['label' => 'length','validation' => ['required']];
$fields['width'] = ['label' => 'width','validation' => ['required']];


//Instantiate post
$product = new Post($db, $table, $fields);

$post = []; // array to hold a trimmed working copy of the form data
$errors = []; // array to hold user/validation errors

if ($_SERVER["REQUEST_METHOD"] === "POST") {
    //Get raw data

    $json = json_decode(file_get_contents("php://input"), true);

    // trim all the input data at once
    $post = array_map('trim', $json);


    foreach ($fields as $field => $arr) {
        if (isset($arr['validation']) && is_array($arr['validation'])) {
              foreach ($arr['validation'] as $rule) {
                switch ($rule) {
                    case 'required':
                        if (!$post[$field]) {
                            $errors[$field] = "{$arr['label']} is required";
                        }
                        
                        if (isset($arr['options']) && is_array($arr['options'])) {
                            if (empty($post[$field])) {
                                $errors[$field] = "{$arr['label']} must be one of the following: " . implode(', ', $arr['options']);
                            }
                        }
                        break;
                }
            }
        }
            
        
    }

    if (empty($errors)) {
        $result = $product->create($post);
        if (isset($result['success']) && $result['success'] === true) {
            $response = [
                'success' => true,
                'message' => "Created Successfully"
            ];
        } else {
            $response = [
                'success' => false,
                'errors' => $result['errors']
            ];
        }
    } else {
        $response = [
            'success' => false,
            'errors' => $errors
        ];
    }
    echo json_encode($response);
}

and the create method in post.php

 public function create ($data) {

    $set_terms = [];
    $params = [];

    foreach(array_keys($this->fields) as $field) {
        
            $set_terms[] = "`$field`=?";
            $params[] = $data[$field];
        
    }
    $params = array_values($params);
 
    $query = "INSERT INTO `$this->table` SET " . implode(',',$set_terms);

    $stmt = $this->conn->prepare($query);

    try {
        $stmt->execute($params);
        return ["success" => true];
    } catch (PDOException $e) {
        if ($e->errorInfo[1] == 1062) {
            $errors = [];
            if (stripos($e->errorInfo[2] , 'sku') !== false) {
                $errors['sku'] = "SKU is already in use. ";
            }
            if (stripos($e->errorInfo[2] , 'name') !== false) {
                $errors['name'] = "Name is already in use. ";
            }
            return ["success" => false, "errors" => $errors];
        } else {
            return ["success" => false, "errors" => ["sku" => $e->getMessage(), "name" => $e->getMessage()]];
        }
    }
    }