Hi everyone,
I am starting using PHP and MYSQL and I need help to generate and insert fields like below on MySQL database.
161013001
Where:
16 is Year,
10 is Month,
13 is day
and 001 is auto-increment numbers that reset each days.
Eg.
161012-001
161012-002
161012-002
161013-001
161013-002
161014-001
161014-002
161014-003
161014-004
161014-005
161015-001
Regards
Pablo
Welcome, @wmastermz . Have you made a start with this? If so, please post your code so we can see where you need help. To post your code, just type three backticks (`) on the line before you paste your code, and three backticks on the line after.
If you have no code yet, the first step would be to break down the problem into small pieces. For example, how would you use PHP to make a date in the format you have described, such as 161015? When you want a new field, how do you test to see if it is the same date as the previous one, so the last 3 digits are incremented, or if it is a new date, in which case you use 001. How would you put these pieces together (do you know how to concatenate strings)?
If you get stuck on one of the pieces of the puzzle, let us know and we can point you in the right direction. But I don’t think you will learn as much if we just give you the entire code for this.
php.net has all the resources for using php. However, it’s hard to get through if you are new and don’t understand the terminology and need someone to show you examples.
I will point you to the page where it tells you everything you need to know about using dates and times in you pages: http://php.net/manual/en/book.datetime.php Included is how to format a date.
About concatenating strings, as WebMachine mentioned. If you go to php.net, you’ll find it hard to find this information. It would be under “Operators > String Operators,” which is not helpful to a newbie! If you Google “concatenate strings php,” you’ll find the page in php.net that helps you add the -xxx to the date: http://php.net/manual/en/language.operators.string.php
You still need to do other small tasks, as WebMaching mentioned. Why don’t you make a list of those tasks, put them in order, and find the individual code pages and see how you do?
[quote=“wmastermz, post:1, topic:239859, full:true”]… I need help to generate and insert fields like below on MySQL database.
[/quote]
my first reaction is: don’t do this
instead, use an INTEGER AUTO_INCREMENT PRIMARY KEY which will increase over the entire table, and a DATE column for the date
Agreed
First you would be using code to put the string together, which would mean later you would need to use code to pull it apart. This seems very inefficient to me.
I’m assuming the goal here is so that you can do a date based sort and have the daily sequence to work with. i.e. a “code is the heavy lifter” approach.
Is so I think a type of ORDER BY the_date, id query could do more of that work for you.
Hi everyone,
First of all I would like to say thanks for your quick reply.
Yes I am a newbie in PHP & MySQL and after dig for some time I have below idea.
The engine type of the table in MySQL must be MyISAM;
Create two columns, the first one as CHAR(6) and give yymmdd values, the second column as MEDIUMINT AUTO_INCREMENT and set the first column and the second column as PRMARY KEY;
eg. CREATE TABLE tickets (
tt CHAR(6) NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
subject VARCHAR(200) NOT NULL,
PRIMARY KEY (tt,id)
) ENGINE=MyISAM;
Guys, I think you should not give advice like this because he might have a business requirement for this kind of numbering scheme, which is not for discussion. Often generating document numbers like invoices needs to follow numbering rules like this so you can’t just say don’t do it. I’ve had a few cases like this where I needed to generate sequential numbers for documents within a day, month or year.
I don’t think a auto increment column will help you in any way since you need a counter that resets each day while an AI cannot be reset.
Supposing you store the whole number like 161012-001 in an id field and the number is always fixed length then it should be easy to find the highest number within a day like this:
SELECT MAX(RIGHT(id, 3)) FROM mytable WHERE id LIKE '161012-%';
then add 1 to it and you have the next number sequence for the day. You can use str_pad() function in php to make it a 3-digit number padded with zeroes and then insert the new row with you new number.
Some things to note:
Create an index on the id column so that the LIKE search is fast. A good idea is to use a unique index to prevent duplicates.
In high concurrent environments it might be possible that two scripts will generate the same sequence number at the same time. You might either lock the table for the whole procedure or be prepared to handle insert errors when the unique index is violated and simply have a loop to try again in such a case.
sure i can…
i’ve seen literally dozens and dozens of cases like this over the years, and i have yet to see one where it wasn’t a case of whimsy on the part of the database developer, but an actual ironclad business rule
OP needs to know best practice, if only to break it on purpose
True, I guess there could be a Duce calling the shots.
My impression is that date and id are being combined into an id and that at some point the date portion of the id will likely be used as a date.
To me this feels semantically wrong.
IMHO an id should be an id and a date should be a date.
i.e. an id should not be a date-id hybrid
Can it be done? Sure.
Must it be done? Perhaps
Should it be done? No
Is it done? All the freakin’ time. Especially when dealing with manufacturing organizations. That’s more common than you’d think…
Yeah, we need more information, especially what’s the purpose of this generated number? If it’s programmer’s whim to create fancy primary keys or some hybrid data type then this should be discouraged. But if it’s a requirement for a financial document number then there is nothing to debate and it must be done one way or the other.
Correct, all these fields should be separate. If this generated number is necessary for some reason then it should be kept in a separate field.
the first one as CHAR(6) and give yymmdd values
Please use a DATE type for a date column, not CHAR.
So you cannot have 2 primary keys on 1 column as far as i know of. If your saving a data string in column tt it could make more sense for it to be a DATE column but this is not a huge concern as your current structure for DATE saving is ok as far as the code understands but for other developers this is bad practice and becomes confusing on what the data is in the column. So if you must have the column structured as a char(6) you should re-name it to be something so people can understand the data type being saved instead of some random chars. As for your php please escape and validate your INSERT php values before inserting as your current implementation is a security and data integrity risk.
But as suggested it would be wise to change your tt column to a DATE as having a char(6) will allow an insert of any character as long as it does not exceed the 6 char limit. a DATE column has better support for data integrity.
but you can definitley have 1 primary key on 2 columns
… which is what is necessary to get the secondary auto_increment to work
see http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html
Hi everyone,
Today I was able to test the idea that I presented before and everything is working as expected.
Many thanks everyone and have a wonderfully weekend.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.
