Allow a maximum of three rows in the table during insert

Question

How can i limit inserts to just 3 rows for each category in table? I am working with php and pdo. I have some categories and each category must have max 3 subcategories. Everything is stored in db, tables look like this.

To make it clear! I have some categories in menu and each of those category can have maximum of 3 subcategories. Of course not with the same name, but it can't be more then 3 in each category. I have input for one at the time, that means you can insert more then one at the time, but it can't be more then 3 in db.

Some category

 - some subcategory
 - some subcategory
 - some subcategory

category

id | category |

subcategory

id | subcategory_name | id_category |

For now, my code looks like this.

    if(isset($_POST['sub'])) {

    $InputSubcategory = $_POST['InputSubcategory'];
    $InputId          = $_POST['InputId'];

    $sql = $pdo->prepare("INSERT INTO subcategory(subcategory_name,id_category) 
                          VALUES(:field1,:field2)");
    $sql->execute(array(':field1' => $InputSubcategory,
                        ':field2' => $InputId));
    $affected_rows = $sql->rowCount();
    if ($affected_rows > 0) {
    header( "refresh:0" );
    die();
    }
}         
if(isset($_POST['cat'])) {

    $InputCategory = $_POST['InputCategory'];

    $sql = $pdo->prepare("INSERT INTO category(category) 
                          VALUES(:field1)");
    $sql->execute(array(':field1' => $InputCategory));
    $affected_rows = $sql->rowCount();
    if ($affected_rows > 0) {
    header( "refresh:0" );
    die();
    }
}

EDIT: I made it work! What i did is next. Count() rows in each table, then in if compare whether the counted number greater than or equal to 3. If if is true show error if false continue to insert.

    if(isset($_POST['cat'])) {

    $nRows = $pdo->query('select count(*) from category')->fetchColumn();  

    if ($nRows >= 3) {
      echo'
      <div class="alert alert-warning">
         <span class="glyphicon glyphicon-ok"></span> <strong>Warning</strong>
          <hr class="message-inner-separator">
          <p>You reach maximum category limit.</p>
      </div>';
      header( "refresh:2;url=insert.php" );
    } else {

      $InputCategory = $_POST['InputCategory'];

      $sql = $pdo->prepare("INSERT INTO category(category) 
                            VALUES(:field1)");
      $sql->execute(array(':field1' => $InputCategory));
      $affected_rows = $sql->rowCount();
      if ($affected_rows > 0) {
      header( "refresh:0" );
      die();
      }  

    }
}

One solution is to add an insert trigger to the `category` table to prevent the insert. What do you want to happen when an attempt is made to insert a fourth subcategory? — Gordon Linoff, Jun 01 '15 at 11:10
Nothing special, just to say that maximum number of subcategories are inserted. If i LIMIT 3 that means that i cant insert more then 3 at once or that 3 is maximum of rows in table? — Davor Miljkovic, Jun 01 '15 at 11:12
then you need to take care of that at the input level, not as the SQL level. ^ that's what I got from that comment — Funk Forty Niner, Jun 01 '15 at 11:14
As you can see in m code, i have rowCount() at the end of insert query..can i use it? But, user can only insert one row at the time..will it work? — Davor Miljkovic, Jun 01 '15 at 11:16
your question is a bit unclear. Do you mean if there is the same sub-category names? you need to give us an example and put it in your question please, and not in comments so everyone will see it. — Funk Forty Niner, Jun 01 '15 at 11:38
then query select first, count if there 3 already in db. if not, continue with insert. This Q&A http://stackoverflow.com/q/883365/ might help and `if($stmt->rowCount() >= 3)` might work too. Use PHP's `count()` function too http://php.net/manual/en/function.count.php — Funk Forty Niner, Jun 01 '15 at 11:57
It's funny. This question never came up before. It's come up 3 or 4 times now in as many weeks! — Strawberry, Jun 01 '15 at 14:23
What if someone enters a row in between your SELECT and your INSERT! — Strawberry, Jun 01 '15 at 14:30
Your solution isn't proper, you're vulnerable to concurrency, just like @Strawberry wrote. If you have max 3 something per something, a much saner option is to just always insert without caring for the item amount and always display last 3 items. That way you still prevented people from having more than 3 something per something, and you are not going to experience any issues due to concurrent access. — N.B., Jun 02 '15 at 22:00

score 0 · Answer 1 · answered Jun 02 '15 at 21:55

You could prevent more than 3 from being inserted into the database itself, with a trigger.

DELIMITER $$
CREATE TRIGGER subcategory_bi BEFORE INSERT ON subcategory FOR EACH ROW
BEGIN

  IF (SELECT COUNT(1) FROM subcategory sc WHERE sc.category_id = NEW.category_id) >= 3 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'this category already has the max allowed subcategories';
  END IF;

END $$
DELIMITER ;

Allow a maximum of three rows in the table during insert

1 Answers1