Don't insert when maximum is reached

How to control if a record is inserted based on a condition?

Don't insert a record if certain conditions are not met.

There is a way of using pure MySQL to stop inserting records when a certain number of them has been reached. Let us say that you have the following tables:

Say you have an item table of item types and a limited number of each items like this:

item_types

item_id	item_name	limit
1	car	3

items

item_type	customer
1	bob
1	gerard

If you want to limit the amount of records of a certain item_type inserted into the table items then you can use the following technique:

The secret is to build off the following statement:

INSERT INTO `items` (`item_type`,`customer`) VALUES(1, 'bob');

This is a good start. It will insert a record with the correct data, but the statement above will do the insert into items without checking to first see if we have too many items in there already. So we replace the VALUES statement with a SELECT statement containing only two fields (item_id and customer), we set the select to return the id (1) and a customers name like bob by hard coding the result.

INSERT INTO `items` (`item_type`,`customer`)

SELECT 1, 'bob' FROM `item_types` WHERE `item_types`.`item_id`=1;

But we still are not limiting the insert by the number of this type of item (item_id 1). So we add an additional clause to the conditional statement:

INSERT INTO `items` (`item_type`,`customer`)

SELECT 1, 'bob' FROM `item_types` WHERE `item_types`.`item_id`=1 AND `item_types`.`limit` > (SELECT COUNT(`item_type`) FROM `items` WHERE `items`.`item_type`=1 GROUP BY `item_type`);

This looks pretty good, but it won't insert a record if there is not at least one record of that type in the `items` table, so we have to add an additional clause to make sure it works if the table is empty. Here is the final solution:

INSERT INTO `items` (`item_type`,`customer`)

SELECT 1, 'bob' FROM `item_types` WHERE `item_types`.`item_id`=1 AND `item_types`.`limit` > (SELECT COUNT(`item_type`) FROM `items` WHERE `items`.`item_type`=1 GROUP BY `item_type`) OR NOT EXISTS(SELECT 1 FROM `items` WHERE item_type=1);

Now we are making sure that the current number of items with the item_type 1 is under the limit listed for each item in item_types. Here is the PHP code to do it:

$customerName='george';

$itemID=1;

$dbHost='localhost';

$dbUser='someuser';

$dbPass='somepass';

try {

$mysqli = new mysqli

(

$dbHost,

$dbUser,

$dbPass

);

} catch (Exception $e){

echo $e->getMessage();

exit();

}

if ($mysqli->connect_errno)

{

echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error."\n";

exit();

}

//save the log information

$safeCustomerName=mysql_real_escape_string($customerName);

$insertSQL =

INSERT INTO `items` (`item_type`,`customer`)

SELECT {$itemID}, '{$safeCustomerName}' FROM `item_types` WHERE `item_types`.`item_id`={$itemID} AND `item_types`.`limit` > (SELECT COUNT(`item_type`) FROM `items` WHERE `items`.`item_type`={$itemID} GROUP BY `item_type`) OR NOT EXISTS(SELECT 1 FROM `items` WHERE item_type={$itemID});

try {

$result = $mysqli->query($insertSQL);

if (!$result) {

echo "error";

exit();

} else {

echo "success";

exit();

}

} catch(Exception $e){

echo "invalid";

exit();

}