473,320 Members | 1,902 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

PHP & MySQ + unique keys

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
7 2373

"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
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
>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
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
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
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
.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: kevin parks | last post by:
hi. I've been banging my head against this one a while and have asked around, and i am throwing this one out there in the hopes that some one can shed some light on what has turned out to be a...
5
by: Roy Smith | last post by:
I've got a silly little problem that I'm solving in C++, but I got to thinking about how much easier it would be in Python. Here's the problem: You've got a list of words (actually, they're...
3
by: Dan Sikorsky | last post by:
How can I get the recordset attributes for a table field in SQL Server 2000 to report the field updatable attribute correctly ... mine keeps saying the fields are not updatable? That is, (...
4
by: Victor Engmark | last post by:
When looking for a method to fetch unique elements and counting the number of occurences of each of them, I found quite a lot of gross examples of complex XSL. But after realizing the subtle...
9
by: Rolf Kemper | last post by:
Dear Experts, I got stuck with the following problem and need your help. What I wnat to do is to get a set of distinct nodes. Before the distinct I have selected the multiple occourences...
7
by: ProvoWallis | last post by:
I'm still learning python so this might be a crazy question but I thought I would ask anyway. Can anyone tell me if it is possible to join two dictionaries together to create a new dictionary using...
11
by: garyhoran | last post by:
Hi Guys, I have a collection that contains various attributes (stuff like strings, DateTime and Timespan) . I would like to access the collection in various orders at different points in the...
1
by: Zytan | last post by:
http://msdn2.microsoft.com/en-us/library/system.exception.data(VS.80).aspx says: "Avoid key conflicts by adopting a naming convention to generate unique keys for key/value pairs. ... two...
12
by: Florian Brucker | last post by:
Hi everybody! Given a dictionary, I want to create a clustered version of it, collecting keys that have the same value: {1:, 2:, 3:} That is, generate a new dict which holds for each value...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.