By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,813 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

PHP & MySQ + unique keys

P: n/a
Hi,

I'm trying to write a system thats used for about 50 clients that uses html
forms and php to log details. The problem is that when a client loads the
form page it's given a value which is the last record in a table +1 (i.e. so
its the next record). The problem with that is that a client could sit on
that page for 20 mins (or whatever length of time) and a different client
could use that record number and there what be an error "duplicate key....".
I could use an MySQL UPDATE to lock the record when its issued to each
client and maybe use a timestamp and check if the timestamp is over 10 mins
and delete any records that are over 10mins but there is the possibility of
redundant data (or really useless data, a unique record and a timestamp) in
the table sitting there for 10mins at a time.

Is there an easy way of 'gauranteeing' a client a unique key? So you don't
have to lock the record when the page loads (this also means that when you
refresh the page that the unique key is incremented as you locked the last
record and didn't put anything into the table, so the refreshed page is
issued the next key +1 again).

Thanks,

Tony.
Jul 17 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a

"Tony Clarke" <cl*****@eircom.net> wrote in message
news:Ts*******************@news.indigo.ie...
Hi,

I'm trying to write a system thats used for about 50 clients that uses
html forms and php to log details. The problem is that when a client loads
the form page it's given a value which is the last record in a table +1
(i.e. so its the next record). The problem with that is that a client
could sit on that page for 20 mins (or whatever length of time) and a
different client could use that record number and there what be an error
"duplicate key....".
I could use an MySQL UPDATE to lock the record when its issued to each
client and maybe use a timestamp and check if the timestamp is over 10
mins and delete any records that are over 10mins but there is the
possibility of redundant data (or really useless data, a unique record and
a timestamp) in the table sitting there for 10mins at a time.

Is there an easy way of 'gauranteeing' a client a unique key? So you don't
have to lock the record when the page loads (this also means that when you
refresh the page that the unique key is incremented as you locked the last
record and didn't put anything into the table, so the refreshed page is
issued the next key +1 again).

Thanks,

Tony.


Why don't you retrieve the key once the form is submitted? This way the
client has the key for only a fraction of time.
You could also autoIncrement the field in the table.

Brent Palmer.

Jul 17 '05 #2

P: n/a
Its possible I could do it this way, but it would make life much more akward
for the people using the system, ideally I would like to do it before its
submitted as its used as a reference number that is quoted to people.
"Brent Palmer" <ba******@bigpond.net.au> wrote in message
news:3H**************@news-server.bigpond.net.au...

"Tony Clarke" <cl*****@eircom.net> wrote in message
news:Ts*******************@news.indigo.ie...
Hi,

I'm trying to write a system thats used for about 50 clients that uses
html forms and php to log details. The problem is that when a client
loads the form page it's given a value which is the last record in a
table +1 (i.e. so its the next record). The problem with that is that a
client could sit on that page for 20 mins (or whatever length of time)
and a different client could use that record number and there what be an
error "duplicate key....".
I could use an MySQL UPDATE to lock the record when its issued to each
client and maybe use a timestamp and check if the timestamp is over 10
mins and delete any records that are over 10mins but there is the
possibility of redundant data (or really useless data, a unique record
and a timestamp) in the table sitting there for 10mins at a time.

Is there an easy way of 'gauranteeing' a client a unique key? So you
don't have to lock the record when the page loads (this also means that
when you refresh the page that the unique key is incremented as you
locked the last record and didn't put anything into the table, so the
refreshed page is issued the next key +1 again).

Thanks,

Tony.


Why don't you retrieve the key once the form is submitted? This way the
client has the key for only a fraction of time.
You could also autoIncrement the field in the table.

Brent Palmer.

Jul 17 '05 #3

P: n/a
>I'm trying to write a system thats used for about 50 clients that uses html
forms and php to log details. The problem is that when a client loads the
form page it's given a value which is the last record in a table +1 (i.e. so
its the next record). The problem with that is that a client could sit on
that page for 20 mins (or whatever length of time) and a different client
could use that record number and there what be an error "duplicate key....".
I could use an MySQL UPDATE to lock the record when its issued to each
client and maybe use a timestamp and check if the timestamp is over 10 mins
and delete any records that are over 10mins but there is the possibility of
redundant data (or really useless data, a unique record and a timestamp) in
the table sitting there for 10mins at a time.
Use an auto_increment field for the key. When you insert the record,
put in NULL for the value. Then retrieve the ID of the record you
just inserted (if you need it) with "SELECT MYSQL_INSERT_ID()".
Since mysql_insert_id() operates on a per-connection basis, you do
NOT have to worry about someone else using some other connection
messing up the record ID by inserting another one.
Is there an easy way of 'gauranteeing' a client a unique key? So you don't
auto_increment does a fairly good job, assigning the ID when you
insert the record.
have to lock the record when the page loads (this also means that when you
refresh the page that the unique key is incremented as you locked the last
record and didn't put anything into the table, so the refreshed page is
issued the next key +1 again).


Gordon L. Burditt
Jul 17 '05 #4

P: n/a
Tony Clarke (cl*****@eircom.net) wrote:
: "Brent Palmer" <ba******@bigpond.net.au> wrote in message
: news:3H**************@news-server.bigpond.net.au...
: >
: > "Tony Clarke" <cl*****@eircom.net> wrote in message
: > news:Ts*******************@news.indigo.ie...
: >> Hi,
: >>
: >> I'm trying to write a system thats used for about 50 clients that uses
: >> html forms and php to log details. The problem is that when a client
: >> loads the form page it's given a value which is the last record in a
: >> table +1 (i.e. so its the next record). The problem with that is that a
: >> client could sit on that page for 20 mins (or whatever length of time)
: >> and a different client could use that record number and there what be an
: >> error "duplicate key....".
: >> I could use an MySQL UPDATE to lock the record when its issued to each
: >> client and maybe use a timestamp and check if the timestamp is over 10
: >> mins and delete any records that are over 10mins but there is the
: >> possibility of redundant data (or really useless data, a unique record
: >> and a timestamp) in the table sitting there for 10mins at a time.
: >>
: >> Is there an easy way of 'gauranteeing' a client a unique key? So you
: >> don't have to lock the record when the page loads (this also means that
: >> when you refresh the page that the unique key is incremented as you
: >> locked the last record and didn't put anything into the table, so the
: >> refreshed page is issued the next key +1 again).
: >>
: >> Thanks,
: >>
: >> Tony.
: >>
: >
: > Why don't you retrieve the key once the form is submitted? This way the
: > client has the key for only a fraction of time.
: > You could also autoIncrement the field in the table.

: Its possible I could do it this way, but it would make life much more akward
: for the people using the system, ideally I would like to do it before its
: submitted as its used as a reference number that is quoted to people.

0) Have two tables, ONE with multiple rows, one per form, indexed by a
serial number, this is not an autoindex column, and not all possible
serial numbers are necessarily used. Table TWO is a table with just one
row used strictly to record the next available serial number.

1) use sessions

2) when a person requests a new, "blank", form, then allocate the next
number from the (one row) sequence table. i.e. update the number stored
in the only row in the table. Each request for a new blank form will get
a unique number.

3) use the session to store the number on the server, and also use and
display it for the user in the form.

4) when the user saves the form, check the serial number is the one the
session is using. Then try to update a row with that serial number. If
the update works then the user has updated the form. If the row is not
found then insert a new row with that serial number and save the form data
in that row.
Notice that it is the session variable stored on the server that double
checks the update/insert is legit - this is what prevents some other user
from updating or inserting some data with a stolen serial number.

For some applications that is enough - once the session expires then the
user cannot update the record.

If you don't want to use sessions, then implement a check sum to prove the
serial number is valid. I.e. on the server, where the users can't see
what is going on, your php script combines the serial number with a secret
key, and encrypts the result, and includes that with the form. A person
who doesn't know the secret key will not be able to fake the serial number
because they won't know how to generate the checksum (unless they were
able to monitor the earlier transmission). Each time your script gets a
form it regenerates the checksum from the serial number and checks that
the checksum in the form is correct.

For other applications the user will need to be able to update the record
later on. In that case you need to provide another mechanism, such as a
regular login, to control which record a person can update. The serial
number will be used in the sql to access the row, but it will be the login
that controls which rows they are allowed to access.
--

This space not for rent.
Jul 17 '05 #5

P: n/a
Gordon Burditt wrote:
I'm trying to write a system thats used for about 50 clients that uses html
forms and php to log details. The problem is that when a client loads the
form page it's given a value which is the last record in a table +1 (i.e. so
its the next record). The problem with that is that a client could sit on
that page for 20 mins (or whatever length of time) and a different client
could use that record number and there what be an error "duplicate key....".
I could use an MySQL UPDATE to lock the record when its issued to each
client and maybe use a timestamp and check if the timestamp is over 10 mins
and delete any records that are over 10mins but there is the possibility of
redundant data (or really useless data, a unique record and a timestamp) in
the table sitting there for 10mins at a time.

Use an auto_increment field for the key. When you insert the record,
put in NULL for the value. Then retrieve the ID of the record you
just inserted (if you need it) with "SELECT MYSQL_INSERT_ID()".
Since mysql_insert_id() operates on a per-connection basis, you do
NOT have to worry about someone else using some other connection
messing up the record ID by inserting another one.

Is there an easy way of 'gauranteeing' a client a unique key? So you don't

auto_increment does a fairly good job, assigning the ID when you
insert the record.

have to lock the record when the page loads (this also means that when you
refresh the page that the unique key is incremented as you locked the last
record and didn't put anything into the table, so the refreshed page is
issued the next key +1 again).

Gordon L. Burditt

Yup concur with Graham, use autoincrement as the primary key, and if you
really want to be paranoid then use the previous suggestions and encrypt
the primary key

Jul 17 '05 #6

P: n/a
Gordon Burditt wrote:
I'm trying to write a system thats used for about 50 clients that uses html
forms and php to log details. The problem is that when a client loads the
form page it's given a value which is the last record in a table +1 (i.e. so
its the next record). The problem with that is that a client could sit on
that page for 20 mins (or whatever length of time) and a different client
could use that record number and there what be an error "duplicate key....".
I could use an MySQL UPDATE to lock the record when its issued to each
client and maybe use a timestamp and check if the timestamp is over 10 mins
and delete any records that are over 10mins but there is the possibility of
redundant data (or really useless data, a unique record and a timestamp) in
the table sitting there for 10mins at a time.

Use an auto_increment field for the key. When you insert the record,
put in NULL for the value. Then retrieve the ID of the record you
just inserted (if you need it) with "SELECT MYSQL_INSERT_ID()".
Since mysql_insert_id() operates on a per-connection basis, you do
NOT have to worry about someone else using some other connection
messing up the record ID by inserting another one.

Is there an easy way of 'gauranteeing' a client a unique key? So you don't

auto_increment does a fairly good job, assigning the ID when you
insert the record.

have to lock the record when the page loads (this also means that when you
refresh the page that the unique key is incremented as you locked the last
record and didn't put anything into the table, so the refreshed page is
issued the next key +1 again).

Gordon L. Burditt

Whoops sorry Gordon
Jul 17 '05 #7

P: n/a
.oO(Gordon Burditt)
Use an auto_increment field for the key. When you insert the record,
put in NULL for the value. Then retrieve the ID of the record you
just inserted (if you need it) with "SELECT MYSQL_INSERT_ID()".
Since mysql_insert_id() operates on a per-connection basis, you do
NOT have to worry about someone else using some other connection
messing up the record ID by inserting another one.


Another way would be to use sequences (not natively supported in MySQL,
but easy to emulate). So whenever a client requests a form he gets the
next available ID, which is always unique.

Micha
Jul 17 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.