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

score 0 · Answer 1 · edited Oct 29 '21 at 16:17

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

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

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

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; }
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

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