PHP & MySQL : query if data exist and create if it not exist

Question

I have a primary key column, containing id so it must be unique

the content look like this

format of id is <index>/<division>/<month in roman>/<last 2 digit of year>

my question is what the best query to check if id already exist, and create id with <index>+1/<division>/<month in roman>/<last 2 digit of year>

this is my function to generate id

    public function gen_id($id_type = "HL.BY") {
    $id_num = 123;
    $month = $this->romanic_number(date("n"));
    $year = substr(date("Y"),-2);
    $index_number = sprintf("%04d", $id_num);

    $id = $index_number . "/" . $id_type . "/" . $month . "/" . $year;
    return $id;
}

if my question is not clear please ask

Insert if not exist http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — Mi-Creativity, Oct 20 '15 at 04:58
Why not make the id auto-increment? Your other data should be in separate columns, it's going to be hard to use this data down the road. — chris85, Oct 20 '15 at 04:59
@chris85 do you mean auto-increment index_number?, unfortunately i need to use previous programmer database — stacheldraht27, Oct 20 '15 at 05:02
I would step back and undo (separate the data) the incorrect work the previous developer did. — chris85, Oct 20 '15 at 12:43

Kuya · Answer 1 · 2015-10-20T06:39:57.397

If you are required to use the existing database you can ensure unique ID's like this...

1) Create a table with two fields...

$createSQL1 = $conn->prepare("CREATE TABLE IF NOT EXISTS `nxtnum` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nxtnum` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1");

$createSQL1->execute();

$insertSQL1 = $conn->prepare("INSERT INTO `nxtnum` (`id`, `nxtnum`) VALUES (1, 501)");

$insertSQL1->execute();

Where "501" is the highest first-part of all existing ID's plus 1

2) Get the number for this line of your code... $id_num = 123; ...

$sql = "SELECT * FROM nxtnum";
$query = $conn->prepare($sql);
$query->execute();
$row = $query->fetch(PDO::FETCH_ASSOC);

where $conn is your database connection file

3) Create the new unique ID...

public function gen_id($id_type = "HL.BY") {
$id_num = $row['nxtnum']; // this is the new incremented number (incremented in step 4)
$month = $this->romanic_number(date("n"));
$year = substr(date("Y"),-2);
$index_number = sprintf("%04d", $id_num);

$id = $index_number . "/" . $id_type . "/" . $month . "/" . $year;
return $id;
}

4) Increment your counter...

$num = $row['nxtnum'] + 1;
$sql = "UPDATE nxtnum SET nxtnum=:num";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':num', $num, PDO::PARAM_INT);
$stmt->execute();

Now you're all set for the next ID and your ID's will always be unique

Happy Coding !

NOTE: Of course step one only has to be done once.

PHP & MySQL : query if data exist and create if it not exist

1 Answers1