0

I got a problem with querying for auto number. I got a table tbUnit which contain unit_id, unit_name and table tbStudent which contain stu_id, stu_name. Stu_id comes from unit_id + year + 0000 (4 digits autobumbering).

I can create auto numbering if not seeing the unit_id, but right now, I want to create data autonumbering depend on unit_id, for example: A20150001, A20150002, B20150001 (restart from 1 because no B unit before), ...

Any idea how to create those condition? Auto numbering depending on ID at other tables. I'm using MySQL and PHP script.

user3608809
  • 55
  • 9

1 Answers1

1

If I understand correctly, I wouldn't recommend trying to use autonumbering.

Instead, just populate a separate table attribute based on your rules and the values in the source table. I think $string = sprintf("%04d", $digits); should work for the last four digits. Then you can get the current year with date("Y") in case you were not aware. Then, just get the latest unit (e.g. "B") from the Unit table, concatenate all of that together and store it in the Student table.

steve klein
  • 2,566
  • 1
  • 14
  • 27
  • I give additional info : can't create AI row with a PK at same row, so I create the AI with INDEX KEY, and it works fine.. that solution from here: http://stackoverflow.com/questions/14086324/mysql-innodb-autoincrement-non-primary-key – user3608809 Apr 25 '15 at 04:07
  • hello Steve, I forgot 1 problem, how to restart it from 1 again when unit changes? – user3608809 Apr 25 '15 at 04:09
  • I'm not sure I have this right, but I am picturing that you have units getting recorded on the `Unit` table with _increasing_ `unit_id` (e.g. A for a block of units, then B for the next block, etc). If so, can you compare latest block with latest letter assigned to a student ID and if they differ, restart the counter? – steve klein Apr 25 '15 at 04:13
  • Well, nope. It's not A then B... but depend on what client choose from category list. For example, there's 3 category: A,B,C. When client choose A, then it will return A20150001, when other client choose A too it will return A20150002, but when other client choose B, it will return B20150001... – user3608809 Apr 25 '15 at 04:19
  • OK, so there are really three running counters, one for each category? – steve klein Apr 25 '15 at 04:24
  • btw I think I did enough with my answer to earn a check mark. I am happy to continue to help but it is a bit tacky to remove the check mark because you forget you had another question. Just saying. – steve klein Apr 25 '15 at 04:32
  • hello, sorry for late reply. Yes, 3 running counter, category - year - 4 digits number. I don't have another question, its wrote on my question before. I accept answer before i test it to another category, so when I test it on another cat and the counter not started from 1 again, I uncheck the answer. – user3608809 Apr 26 '15 at 04:20
  • So as long as you have a counter for each category, you should be fine. You should probably store the counters in the database or make them persistent in some other way so that they work properly from program run to program run. For instance, you can have a table "counters" with columns for category and value. Each time you assign a student ID, use the value from the appropriate category, then increment it for the next assignment. – steve klein Apr 26 '15 at 05:03
  • That's it, I don't have counter for each category. It's for student ID. Imagine that student ID have prefix with A2015, B2015, C2015, till Z2015 then next is their ordered ID per alphabet (0001, 0002, ...) So its only in 1 table, A20150001, B20150001, .... Right now I'm using PHP to counter it (which is bad, already said by you at comment of my question), and still looking for better idea... – user3608809 Apr 26 '15 at 17:06
  • I would create a table "unit_counters" with fields "id", "category", "counter". "id" is just unique id per row. Initialize table with a row for each category (26 letters?) and "counter" for each row set to 1. Then, as part of the step to create a student ID, find the row which matches the `unit_id` and use the corresponding counter from that DB row to build the student ID. Then, increment the counter in that row for next time. – steve klein Apr 26 '15 at 18:52
  • well, if i'm doing that, category need to be input first by the user, because it's not only A-Z but maybe AB, CD, DE, etc.If I not misunderstand, "Then, as part of the step to create a student ID, find the row which matches the unit_id and use the corresponding counter" it means I do increment manually? If yes, it same with my code right now... – user3608809 Apr 28 '15 at 00:07