April 25th, 2000, 09:02 AM
i am developing a web site using php+mysql to allow users to register/signup...
i am considering 2 options...
1) store userid + password + email + personal particulars such as address, contact info ...(about 20 fields) all in one table
2) use one table to store [userid + password + email] and another to store [userid + all personal particulars]
the only queries would be insert/update/select
option 1), though more straight forward, may result in more "congestion" as the table is likely to be used very often...
while option 2) may result in inconsistent records as mysql does not support transactions and rollbacks
any clever way of implementing option 1) without incurring high overhead? or is my concern unfounded?
April 25th, 2000, 10:32 AM
Option 1 shouldn't be too big of a concern if you only pull the fields you need when querying.
Option 2 shouldn't be a concern as you can use the last insert id option to get the userid (assuming you are using an auto_increment field to generate it) for the second table update. IOW, the userid should be auto_incremented in only 1 table.
The logical flow would be:
1) insert into first table
2) get last insert id
3) insert into second table setting userid to the userid received with last_insert_id
April 25th, 2000, 09:50 PM
> 1) insert into first table
> 2) get last insert id
> 3) insert into second table setting userid to the userid received with last_insert_id
my concern is that if 1) is successful but 3) is not - maybe user hit STOP button after 1) but before 3), or database insertion error, then i'll be in trouble...right?
April 26th, 2000, 08:03 AM
You'd only be in trouble insofar as that one insert. The next user would still be correct. You can set up a cron job to compare the two tables periodically, or have the second table be the one with just the login id. Set ALL info including the login info into the first table. If someone signs in and isn't in the second table you could verify against the first to double check. If they are there, update the 2nd table then.
BTW, the user hitting the stop button is not going to affect the PHP script in most circumstances, especially if you are not outputting to the browser between the two queries, the script will continue to run.
There is also a setting I can't recall now, check the manual, that tells PHP to execute the script to completion no matter what the user does.
As far as errors, thorough testing should catch all possible bugs in your code that might cause a problem with the second insert.
Still, a once an hour cron job should catch any problems and be more than sufficient. If you really want to see if you had a concern or not, you can have the cron job log any repairs it performs.