Hello

I have a web application (php/mySQL) that has an auto-increment primary key field called job_id.

Could anyone help me add a prefix to this?

I want to add the year and month (YYMM/) in front of the field (e.g. 0811/1234 then 0811/1235 – assuming current month and year is Nov 2008).

Maybe I’m wrong about adding the prefix to the primary key. Should I maybe add an additional field to maintain this?

Any advice greatly appreciated...

Regards

Paul

Use a separate field

Cheers Chris!

Next question, on insert the original job_id is given the next auto-increment id (e.g. "1776"), how do I append this "1776" to my "0811/" prefix to make the new job_ref field equal "0811/1776"

Thanks again!

http://www.php.net/mysql_insert_id would be a good place to start

Why would you do that on insert? It would be wasting space in the database by duplicating data and making the job_ref field need to be a larger field type.

as YYMM it can be a CHAR(4) or VARCHAR(4), but by appending the auto_increment it would need to be VARCHAR(make_a_guess) because you do not know how many records will be in the table.

SELECT CONCAT_WS("/", y_m_column, CAST(job_id AS CHAR)) AS refid FROM table

will append the YM column to the id with "/" as a separator as the records are retrieved.

great stuff guys!! its nearly 11pm here in Scotland so I'll try some of this out tomorrow morning and let you know how I get on!

thanks again!!!

Originally Posted by chrishirst

SELECT CONCAT_WS("/", y_m_column, CAST(job_id AS CHAR)) AS refid FROM table

Works great in isolation but not within my Adobe Dreamweaver Develop Toolbox (ADDT) web application.

Totally GUTTED! Need to find a solution ASAP.

Any other ideas???

Thanks for you time Chris...

but not within my Adobe Dreamweaver Develop Toolbox (ADDT) web application.

Set up the queries outside of that, otherwise you'll be stuck with the most inefficient ways of querying the database imaginable.

Chris, thanks again!

I did find two other points of note...

1) I found the separator has to be in '/' as opposed to "/"

PHP Code:

SELECT CONCAT_WS("/", y_m_column, CAST(job_id AS CHAR)) AS refid FROM table 


PHP Code:

SELECT CONCAT_WS('/', y_m_column, CAST(job_id AS CHAR)) AS refid FROM table 


2) I find that mySQL does not allow you to use an alias field in a WHERE clause. Do you know of any further work around I could use to allow me to progress as I need to filter by this concat field alias???

Cheers

Paul

Why would you filter by the alias?
the auto increment column is going to be unique anyway