Generate ref number

PHP
1

Is it okay to generate the reference number,using the unixtimestamp + auto_incremented_id and stored it to datatype bigint ?

2

databases are capable of creating their own reference numbers and some even support UUIDs. unless the number needs to be used outside the DB scope, itā€™s best to leave the generation to the DB.

3

Are you already storing these two values on your database? If so, then just combine them in your query rather than creating a new, faux key.

4

@Dormilich, @DaveMaxwell,

Actually this is the flow of my form, when the customers pay then submitting the form to stored the data, I generate reference number for this by using the timestamp and lastinserted idā€¦then update the row to insert the generated reference number. I donā€™t know if this is correct way. :confused:

5

does the reference number serve any purpose?

6

I think just only for searching the payment record ? :confused:

7

Itā€™s still overkill. If you can derive the value from pre-existing values on the database, thereā€™s no reason to create a whole new field.

So search for it using the pre-existing records


SELECT field1, field2, ...fieldN
  FROM orderTable
 WHERE orderNo + orderDate = $generatedKey

You might have to convert the two fields to character before you query, but thatā€™s the general gist of it.

8

What is the reasoning for this? With something like orders the customer may need to quote the number over the phone or use it somewhere. With a number thatā€™s derived from a timestamp and other fields it might get quite long and unwieldy. What is wrong with just using the insert id as the order number?

1 Like
9

Only issue might be privacy/security if the order number is an autoincrement field. You wouldnā€™t want someone to be able to change the order number by one or so and be able to see someone elseā€™s order.

10

then youā€˜re missing order access verification. certainly there is somewhere a relation between customers and their orders. then itā€™s just a matter of joining them to get only the orders for this customer. any order that is outside this set should automatically be rejected.

11

Youā€™re assuming the person is logged in. There are anonymous checkouts (which I use more often than I create accounts) which may not have a profile to tie intoā€¦

12

This may indeed be a good reason for reference numbers if their purpose is to make it difficult to guess other reference numbers - I suppose this is the case here since the OP wants to use them for identifying payments. If that is the case then a timestamp is a poor choice, anyway. I would combine the auto-increment order ID with a short random number. The reference number need not be even stored in the database as it can be calculated on the fly if the ā€œrandomā€ number is a checksum or a hash of existing data that wonā€™t change (like the order ID, order datetime, etc.). But for simplicity of usage if I were to generate payment (pseudo-)random reference numbers Iā€™d store them in the db anyway, just for the ease of searching and a better guarantee they wonā€™t change in some unexpected cases.

13

This is something I ran into, looking for a way to basically ā€œhideā€ the actual ID of a record.

Playing with the demo shows the capabilities.

http://codepen.io/ivanakimov/pen/bNmExm

Scott

14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.