Member Avatar for aditi_19

Hi,

I have a problem. I am storing projectid (varchar) as a field in this format :- DD-NNN where DD is the last two digits of the year and NNN is the next project number within that year (i.e. 09-500 is the 500th project in year 2009). The user has the ability to modify the automatically generated projectid.

Plus, there is 1 internal only project for year 2009 (09-000). This project should automatically be created at the start of a new year.

How can this be done? Please help me out !

Thanks

  • 2 Contributors
  • 1 Reply
  • 110 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by somedude3488

Dani AI

Generated

As described, you need a stable per-year label YY-NNN (with an internal YY-000) and a safe way to auto-assign the NNN that resets each year. ’s direction to keep a simple numeric id is sound — here is a practical, concurrency-safe pattern you can apply.

Keep projects in a normal table and maintain a tiny per-year counter table. Use InnoDB + transactions and lock the counter row with SELECT ... FOR UPDATE before you increment it so two concurrent inserts cannot allocate the same NNN.

Example schema:

CREATE TABLE project_counters (
  yy CHAR(2) PRIMARY KEY,
  next_num INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE projects (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  projectid VARCHAR(6) UNIQUE,   -- e.g. 24-001
  internal_only TINYINT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

Allocation workflow (PHP + PDO sketch):

$yy = date('y');
$db->beginTransaction();

$stmt = $db->prepare('SELECT next_num FROM project_counters WHERE yy = ? FOR UPDATE');
$stmt->execute([$yy]);
$row = $stmt->fetch();

if (!$row) {
  // first time this year: create counter and the internal YY-000
  $db->prepare('INSERT INTO project_counters (yy, next_num) VALUES (?, 2)')->execute([$yy]);
  $db->prepare('INSERT IGNORE INTO projects (projectid, internal_only) VALUES (?, 1)')->execute([sprintf('%s-000', $yy)]);
  $num = 1;
} else {
  $num = $row['next_num'];
  $db->prepare('UPDATE project_counters SET next_num = next_num + 1 WHERE yy = ?')->execute([$yy]);
}

$projectid = sprintf('%s-%03d', $yy, $num);
$db->prepare('INSERT INTO projects (projectid, internal_only) VALUES (?, 0)')->execute([$projectid]);
$db->commit();

Notes and cautions:

  • Always use InnoDB and transactions. Add a UNIQUE index on projectid as a last line of defense.
  • Create the YY-000 either on first-insert (shown above), with a daily cron that ensures it exists, or via MySQL Event Scheduler.
  • If users may edit projectid, validate uniqueness and consider limiting edits to non-YY parts or storing an immutable internal id for references.
  • If you only need the label for display and not as a stable identifier, MySQL 8+ window functions can generate per-year row numbers, but that is not stable if rows are deleted or reordered.
Member Avatar for somedude3488

I think you are going about this the wrong way.

Just use a regular auto_increment primary key field and then store the year in the rows themselves under a 'year' column.

Or you can just store the time the project was created in the database, which you can easily derive the year from with some simple mysql or php functions. That way you also have the creation time as well as the year.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.