advice appreciated - database design

[Benluke]

Hi,
I really need some advice on the following, ill try and keep it a short as poss:

currently working on a site where people register all their information to take part in a training prog.

Payments for this programme have to be made offline after information has been given.

once payment received the user will be able to access a secure area on the site to access workbooks etc.

This is how i plan on doing it:
note: am fairly new to php mysql and .htacces

1. The user registers all their info which is stored in a database name, tel, address etc- no problem

2. When payment is received by phone/post they are given a username and password by customer services.
I have set this up so customer services log in through a .htaccess directory to a page which allows them to fill in a form giving the customer a username and password which is then written to the already existing database.

How do i make sure that when the users username and password is written to the database it will go to the corresponding customer?

do you know what i mean??

Thanks for any advice

 

[HMarcBower]

You need a way to uniquely identify which customer is being updated (with a username, password, payment status) in your table. This is easy enough inside the database itself, as you can set an auto-increment field as an index to the table. However, your customer service folks need a way to uniquely identify the customer as well. If you're requiring an email address when the person registers, you could use that. The Customer provides the customer service people with his or her email address, the CSR uses another script you'd write to search the database on the email address field, and present matches (if you want to be that lenient, or you could require an exact email match.. probably easier on the CSR if you can have it be a LIKE search, so they just type in booger654 and it pulls up all addresses that match that string). The CSR then clicks an "Edit" link beside that customer listing, passing with the click, via post or get (I recommend post), the unique database identifier. The page that it passes to has the information that the CSR can enter (username - which could be made simpler by just using the person's email address - password and payment status), and you arrange your UPDATE statement to be something along the lines of:

UPDATE tbl_customers set username='<?php echo $_POST['username']; ?>',password=MD5('<?php echo $_POST['pass']; ?>,paystatus=1 where custid_index=<?php echo $_POST['userid']; ?> LIMIT 1"

I haven't tested that, so I don't know if I've messed up some syntax somewhere, but that's the gist of it. The CSR will be on a page that was passed the unique database identifier for the customer, will fill in a small form with username, password, paid status, and a submit button. Clicking submit executes the mysql query which should only update the record that corresponds to the unique id.

Did that actually answer your question, or did I answer a totally different question that you didn't ask?

 

[Benluke]

That did answer my question, uniquely identifing which customer is being updated is exactly what i need to do.

From your description i get the gist of it but implementing that will be another thing, i am a total newbie and do not know how to do that.

Thanks a lot though ill have to try and do some reading see if i can work it out


any other help hints would be much appreciated

 

[Benluke]

I think ive worked out a way of doing thanks for the advice it reallyu helped!!

Ben

 

[HMarcBower]

You're welcome