This solution is shown in Example 6-10, using an auxiliary table named ids that manages the next available region_id attribute. The use of the additional table prevents duplicate rows being inserted, and avoids any problems with locking or updates.
Example 6-10. An auxiliary table manages the next region_id attribute
<?php
// This code needs an auxiliary table called "ids"
// that might be created with:
// CREATE TABLE ids (
// region_id int default 0,
// other_id int default 0,
// another_id int default 0
// );
// It has one row, and no primary key is required.
// After creating the table, a row is needed,
// so issue an: INSERT INTO ids (NULL, NULL, NULL);
// (if it's being added later, use MAX( ) to get the
// correct ID values!)
include 'db.inc';
include 'error.inc';
function getNextRegion ($connection)
{
// A nice way to do it... use an auxiliary table
// Lock the auxiliary table
$query = "LOCK TABLES ids WRITE";
if (!mysql_query($query, $connection))
showerror( );
// Add one to the region_id attribute
$query = "UPDATE ids SET region_id = region_id + 1";
if (!mysql_query($query, $connection))
showerror( );
// Find out the new value of region_id
$query = "SELECT * FROM ids";
if (!($result = mysql_query($query, $connection)))
showerror( );
// Get the row that is returned
$row = mysql_fetch_array($result);
// Unlock the table
$query = "UNLOCK TABLES";
if (!mysql_query($query, $connection))
showerror( );
// Return the region_id
return ($row["region_id"]);
}
// MAIN -----
if (!($connection = @ mysql_connect($hostName,
$username,
$password)))
die("Could not connect to database");
if (!mysql_select_db($databaseName))
showerror( );
if (empty($regionId))
{
$regionId =
getNextRegion($connection, $databaseName);
?>
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Insert a region</title>
</head>
<body bgcolor="white">
region_id: <?= $regionId ?>
<br>
<form method="post" action="example.6-10.php">
<input type="hidden"
name="regionId" value="<?=$regionId;?>">
<br>region_name:
<br><input type="text" name="regionName" size=80>
<br>description:
<br><textarea name="description" rows=4 cols=80>
</textarea>
<br><input type="submit">
</form>
</body>
</html>
<?php
}
else
{
$regionId = clean($regionId, 3);
$regionName = clean($regionName, 20);
$description = clean($description, 255);
$query = "INSERT INTO region SET " .
"region_id = " . $regionId . ", " .
"region_name = \"" . $regionName . "\", " .
"description = \"" . $description . "\"";
if ((@ mysql_query ($query, $connection))
&& @ mysql_affected_rows( ) == 1)
header("Location:insert_receipt.php?" .
"values=$regionId&status=T");
else
header("Location: insert_receipt.php?status=F");
}
?>
by updated