How do I convert pl/pgSQl to mySQL??

I have this code that I would like to use for my website. Its a script that automatically creates ISRC codes in the   database automatically: Basically what I have is a ISRC code of djx  the basic format of an ISRC is as follows:

US-DJX-YY-00000
(country of origin - company code- Current year - unique serial -ascending integer)

THE FORMAT:
2 characters for the current year (05)
3 characters for our company code (PROBLEM! see below)
5 characters for a unique serial-ascending integer

What happens is when the year changes the unique serial ascending integer starts over at zero.

The problem:

The script below was written in pl/pgSQL and my webserver is on a linux with mysql. How do I convert this script so it will work for me?? Ok Please see the script below and let me  know.
------------------------------------------------------------------
CREATE OR REPLACE FUNCTION current_isrc_company_code() RETURNS char(3) AS '
DECLARE
current_code char(3);
current_number integer;
BEGIN
SELECT INTO current_code, current_number code, number FROM isrcs ORDER BY id DESC LIMIT 1;
IF current_code IS NULL THEN
return ''DJX'';
END IF;
SELECT INTO current_number setval(''isrcs_number_seq'', 1, false);
END IF;
RETURN current_code;
END;
' LANGUAGE plpgsql;




-- USAGE: INSERT INTO isrcs (song_id) VALUES (12345);
-- It auto-creates the rest.
CREATE TABLE isrcs (
id serial,
year char(2) not null DEFAULT SUBSTRING(CURRENT_DATE, 3, 2),
code char(3) not null DEFAULT current_isrc_company_code() CHECK (code='DJX'),
number serial not null CHECK (number < 10000),
song_id int not null REFERENCES songs(id) ON DELETE CASCADE,
CONSTRAINT unique_isrc PRIMARY KEY(year,code,number)
);

LHood1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LHood1Author Commented:
I would like to create a page on my site to input the song title and have this code create these ISRCs automatically..

LH
todd_farmerCommented:
What table type are you using?  More to the point, are you using transactions?  I can provide a solution, but it is not transaction-safe (it involves locking tables).
LHood1Author Commented:
I just created the mySql database on my server.
username:lhood1
pass:isrcpass

database name is isrcdata or larry_isrcdata
 my connection strings:

        Connection Strings
        Perl      $dbh = DBI->connect("DBI:mysql:larry_isrcdata:localhost","larry_lhood1","<PASSWORD HERE>");
        PHP      $dbh=mysql_connect ("localhost", "larry_lhood1", "<PASSWORD HERE>") or die ('I cannot connect to the database because: ' . mysql_error());
        mysql_select_db ("larry_isrcdata");
________________________________________________

So I guess not I need to create a page to feed into the database?? Im not sure what you mean by transaction safe. I have created a database that we can use. does the info above help?
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

LHood1Author Commented:
Song Id (ISRC code), Title, Composer, Performance Artist, Engineer, Affiliation.

These are the fields I would like in my table..

I was thinking about have a page on my site where users can put in the other information and the song Id (ISRC code) is automatically created in the database once they hit the submit button..then i can later retrieve the data from a text file on my server..
LHood1Author Commented:
or create some query to return the results in a table in html..
LHood1Author Commented:
im open to the most efficient method to get this accomplished..
todd_farmerCommented:
I was able to do something using triggers in MySQL 5.0:
CREATE TABLE `isrcs` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `year` char(2) NOT NULL default 'XX',
  `code` char(3) NOT NULL default 'DJX',
  `number` int(10) unsigned NOT NULL default '0',
  `song_id` int(11) NOT NULL,
  PRIMARY KEY  (`year`,`code`,`number`),
  UNIQUE KEY `id` (`id`));

DELIMITER ||

DROP TRIGGER isrcs_insert||

CREATE TRIGGER isrcs_insert BEFORE INSERT ON isrcs
FOR EACH ROW BEGIN
SET @number = 0;
SET @year = 'XX';
SELECT DATE_FORMAT(NOW(), '%y') INTO @year;
SET NEW.year = @year;

SELECT COUNT(*)+1 INTO @number FROM isrcs WHERE year=NEW.year;
SET NEW.number = @number;
END;
||

DELIMITER ;


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LHood1Author Commented:
Looks good I follow you there however, in my php code how do I set that up to test..im just want to make sure im going in the write direction here.
todd_farmerCommented:
Once you have the table and trigger created (which I would use the mysql command line tool or similar for), you simply need do an insert such as you defined:

INSERT INTO isrcs (song_id) VALUES (123);

The trigger does the rest.
LHood1Author Commented:
ok thanks for help. I will try that..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.