Logic issue in php

[WebDevGuy32145]

Hey everyone,

I have been going over this in my head and can't seem to figure out the solution. I've got a database table called brands and in my form I'm trying to get it to where when I edit a brand, I can't edit it into an already existing brand. My code just automatically adds the new brand even though it exists as well as adds the existing brand as a new brand if I were to retype the existing brand. I hope that makes sense..

Basically I want to check if a brand exists in the database based on it's id, and if it does, throw out an error. I also want to check if form was submitted with values or if it is empty, if it is, throw error. ..and if no errors are thrown, add/edit the table in databse.

Here is my php logic for validating the form:

  $sql ="SELECT * FROM brand ORDER BY brand";
  $results = $db->query($sql);
  $errors = array();

  //Edit brand
  if (isset($_GET['edit']) && !empty($_GET['edit'])) {
    $edit_id = (int)$_GET['edit'];
    $edit_id = sanitize($edit_id);
    $sql2 = "SELECT * FROM brand WHERE id = '$edit_id'";
    $edit_result = $db->query($sql2);
    $eBrand = mysqli_fetch_assoc($edit_result);
  }

  // Delete brand
  if (isset($_GET['delete']) && !empty($_GET['delete'])) {
    $delete_id = (int)$_GET['delete'];
    $delete_id = sanitize($delete_id);
    $sql = "DELETE FROM brand WHERE id = '$delete_id'";
    $db->query($sql);
    header('Location: brands.php');
  }

  // if add form is submmited
  if (isset($_POST['add_submit'])) {
    $brand = sanitize(mysqli_real_escape_string($db, $_POST['brand']));
    // check if brand is blank
    if ($_POST['brand'] == '') {
      $errors[] .= 'You must enter a brand!';
    }
    // check if brand exists in database
    $sql = "SELECT * FROM brand WHERE brand = '$brand'";
    if (isset($_GET['edit'])) {
      $sql = "SELECT * FROM brand WHERE brand = '$brand' AND id != '$edit_id'";
    }
    $result = $db->query($sql);
    $count = mysqli_num_rows($result);
    if ($count > 0) {
      $errors[] .= $brand.' already exists. Please choose another brand name...';
    }

    // display errors
    if (!empty($errors)) {
      echo display_errors($errors);
    }else {
      // add brand to database
      $sql = "INSERT INTO brand (brand) VALUES ('$brand')";
      if (isset($_GET['edit'])) {
        $sql = "UPDATE brand SET brand = '$brand' WHERE id = '$edit_id'";
      }
      $db->query($sql);
      header('Location: brands.php');
    }

  }

Here is my html form:

<div class="text-center">
  <form class="form-inline" action="brands.php<?=((isset($_GET['edit']))?'?edit='.$edit_id:'');?>" method="post">
    <div class="form-group">
      <?php
        $brand_value = '';
       if (isset($_GET['edit'])) {
        $brand_value = $eBrand['brand'];
      }else {
        if (isset($_POST['brand'])) {
          $brand_value = sanitize($_POST['brand']);
        }
      } ?>
      <label for="brand"><?=((isset($_GET['edit']))?'Edit':'Add a');?> Brand:</label>
      <input type="text" name="brand" id="brand" class="form-control" value="<?=$brand_value; ?>">
      <?php if(isset($_GET['edit'])): ?>
        <a href="brands.php" class="btn btn-default">Cancel</a>
      <?php endif; ?>
      <input type="submit" name="add_submit" value="<?=((isset($_GET['edit']))?'Edit':'Add');?> brand" class="btn btn-success">
    </div>
  </form>
</div>

Any help or advice would be very much appreciated. Thank you!

[cereal]

Hi,

if you don't want to allow duplicates then set a unique constraint to the brand column, and then use INSERT IGNORE ..., INSERT ... ON DUPLICATE KEY UPDATE ... or a regular update query. An example:

create table `brands` (
  `id` int unsigned auto_increment primary key,
  `brand` varchar(100) unique not null
) engine = innodb;

insert into `brands` (`brand`) values('sony'),('canon'),('nikon'),('fuji'),('pentax'),('zeiss');

> select * from `brands` order by `id`;
+------+---------+
|   id | brand   |
|------+---------|
|    1 | sony    |
|    2 | canon   |
|    3 | nikon   |
|    4 | fuji    |
|    5 | pentax  |
|    6 | zeiss   |
+------+---------+
6 rows in set
Time: 0.003s

Now, if you try a regular insert, you get an error for duplicated entry:

> insert into `brands` (`brand`) values('Canon');
(1062, "Duplicate entry 'Canon' for key 'brand'")

If instead you use the INSERT ... ON DUPLICATE KEY UPDATE ... the existing row gets updated and your script can continue:

> insert into `brands` (`brand`) values('Canon') on duplicate key update `brand` = 'Canon';
> select * from `brands` order by `id`;

+------+---------+
|   id | brand   |
|------+---------|
|    1 | sony    |
|    2 | Canon   |
|    3 | nikon   |
|    4 | fuji    |
|    5 | pentax  |
|    6 | zeiss   |
+------+---------+
6 rows in set
Time: 0.003s

What can happen? If in the edit form you select Canon id, and in the input field you write Zeiss, with this setup the zeiss column gets the update.

To avoid this, instead of INSERT ... ON DUPLICATE KEY UPDATE ..., you can try with a regular update query:

-- duplicate entry error
UPDATE `brands` SET `brand` = 'sony' WHERE `id` = 2;

which can fail because the id does not exists or because there is a duplicated entry with another id. In the first case, the affected rows would equal to zero and you would try an INSERT IGNORE query. In the second case you can raise the error for brand name already in use...

Note about the case sensitive texts: the above works fine if you don't use a collation that generates case sensitive indexes. If, for example, the create statement ends like this:

) engine = innodb default charset = utf8mb4 collate utf8mb4_bin;

then, the regular insert would work fine and you get two rows with canon and Canon. In this example it's using a binary collation, for more info see:

• https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html

[WebDevGuy32145]

So after looking at my code, and going to my local server, everything seems to work, (no duplicates are added) except nothing is added to the database. I've written the code that should do this in the code above. Am I missing something? Thank you for your feed back cereal

[AndrisP]

I suggest you use function filter_input()

$edit = filter_input(INPUT_GET,  'edit', FILTER_VALIDATE_INT);
$delete = filter_input(INPUT_GET,  'delete', FILTER_VALIDATE_INT);
$brand = filter_input(INPUT_POST,  'brand', FILTER_SANITIZE_STRING);
if($edit !== NULL){
    $sql = ....
}
if($delete !== NULL){
    $sql = ....
}
if(isset($_POST['add_submit']) && $brand !== NULL){
    $sql = ....
}

and bind variables after prepare SQL statement!

[WebDevGuy32145]

So I've solved my complete Rookie mistake. I had single quotes around my field name in my INSERT sql statement that was preventing me from inserting data. Everything works as it should now. Thank you to everyone who helped me figure out my rookie mistake. I've solved the issue.