relating user table with content table

[jarow]

I am relatively new to php and mysql and here is what I would like to do. I have a mysql table with the curriculum vitae (CV) information of about 50 researchers. I would like each researcher to be able to edit and update his/her information. In order to do this I need to create a membership system for the researchers to access their respective CV page.

Before I do anything I want to plan this out, hopefully with a little help of more experienced developers and programmers. So I am open to any ideas.

This is the thing that stumps me the most right now:

Right now I have a CV table (with all the curriuculum vitae information) and need to create a users table that will somehow link with the CV table. Eventually, what I would like is with a login and password (that they choose) they will automatically open their CV page. These are the questions I have:
What fields in the user table should I use to link the user table with the CV table?
Once that is established, how, by providing their login and password can I direct them to their own page to update? Should one of the fields in one of the tables contain the (already existing) address of their CV page?

I think once I can figure this out, the other stuff will not be so difficult.

Would greatly appreciate your suggestions.

[raf]

About the db-design --> 2 tables. Lets call the useraccounts and CV

useracount minimaly needs to have
- userID
- username
- userpwd

CV needs to have
- CVID
- userID
- your CV columns.
- createdatetime
UserID here contains the value of the corrspondig useraccount's userID
(These could also be merged into one table ...)

About the login. Writing a solid and safe login isn't exactly easy and takes about 1000 lines of code... So i'll give you my 5 seconds code

Say we have a form with fields uname and upwd.
Then we see if there is a record with the usernama and pwd

$select="SELECT userID FROM useraccount
WHERE username=". $_POST['uname'] ." AND userpwd=". $_POST['userpwd'];
$result=mysql_query($select) or die ('Queryproblem');
if ((mysql_num_rows($result) == 0) or (mysql_num_rows($result) > 1)){
echo ('Incorrect userdetails');
}else{ // correct login !!
$row=mysql_fetch_assoc($result);
session_start();
$_SESSION['UID']=$row['userID'];
header('Location: http://'.$_SERVER['HTTP_HOST']
.dirname($_SERVER['PHP_SELF'])
.'editCV.php');
}

so now we have the users unique identificationcode inside a sessionvariable. Inside this editCV.php page, we can use it to select the cv-details from our other table
So we only need 1 page for all users, that we fill with that users. You don't need aseperate page for each user.
You can get the info of that user with

session_start();
$select="SELECT CVID, firstname, lastname, othervar1, othervar2 FROM CV WHERE userID =" . $_SESSION['UID'] . " ORDER BY createdatetime desc LIMIT 1"; // i use the createdatetime to get the lates record of this user.
$result=mysql_query($select) or die ('Queryproblem');
if ((mysql_num_rows($result) == 0) or (mysql_num_rows($result) > 1)){
echo ('No user found. Expected 1');
}else{ // correct login !!
$row=mysql_fetch_assoc($result);
// $row now contains the CV details you can present in textboxes etc
}


Hope this gets you started.

[jarow]

Thanks alot, Raf. You gave me some great information. That cvedit.php will be a great help!

I am sure I will have more questions as I go along but my first one is the following:

How do I pass the userid data from the useraccount table to the cv table. My idea is that the userid will be an auto-increment that will be created when the user registers.

Jim

[raf]

check the sticky http://www.codingforums.com/showthread.php?t=22879 ;)

So you insert inside the useraccount and then get this ID back.
The store it inside the session and load the page where the client or where you as admin can enter the CV data. Like

$sql="INSERT INTO useraccount (username, userpwd) values('". $_POST['uname'] ."', '". $_POST['userpwd'] . "')";
$result=mysql_query($sql) or die ('Queryproblem');
if (mysql_affected_rows() != 1){
echo ('Useraccount not insered');
}else{ // correct login !!
session_start();
$_SESSION['UID']= mysql_insert_id());
header('Location: http://'.$_SERVER['HTTP_HOST']
.dirname($_SERVER['PHP_SELF'])
.'editCV.php?action=new');
}

So this is the same page as the edit-page, but we add the action=new to the querystring. Inside editCV.php, you then have something like

if (isset($_GET['action']) and ($_GET['action']=='new')){
$intro='Enter the data for your CV';
}else{
$intro='Edit your CV';
session_start();
$select="SELECT CVID, firstname, lastname, othervar1, othervar2 FROM CV WHERE userID =" . $_SESSION['UID'] . " ORDER BY createdatetime desc LIMIT 1"; // i use the createdatetime to get the lates record of this user.
$result=mysql_query($select) or die ('Queryproblem');
if ((mysql_num_rows($result) == 0) or (mysql_num_rows($result) > 1)){
echo ('No user found. Expected 1');
}else{ // correct login !!
$row=mysql_fetch_assoc($result);
...
}
}

So you just use the same page for the create and edit of the CV data. But maybe first focus on building the form and displaying the CV data in it, and then i could show you how to turn it ito a multipurpose page.

[jarow]

Thanks for the info. I basically have the form and cvedit page done but I can't seem to understand the very basics of how I am going to link and later insert data from CVusers table to the CV table although I basically understand the mysql_insert_id and last_insert_id statements.

Once the form is submitted and heads over to the register page this is where i get lost...basically because I don't know how the userid from cvusers is going to be inserted into the correct ROW in the cv table.

Don't I need something like this?

(I have to write two sql insert statments no?)

$sql = "INSERT INTO cvusers (userid, email, user, password, signup_date)
VALUES('NULL, $email', '$user', '$password', NOW())";
$sql2 = "INSERT INTO cv (userid)
VALUES(LAST_INSERT_ID(),'text')";

(HOW DO IT TELL IT THE CORRECT ROW in the cv table??? Don't I need another common field that will first link the two databases, such as an email field??? Wouldn't I need a query for $sql2 something like this "SELECT * FROM cv WHERE email = ". $_POST['email'] ."; )


LOGIN AND CVEDIT PAGES

I understand what you are doing in these pages, creating session, passing variables, etc. Can't tell you how much you have already helped me.

Thanks

[raf]

Don't I need something like this?

(I have to write two sql insert statments no?)

$sql = "INSERT INTO cvusers (userid, email, user, password, signup_date)
VALUES('NULL, $email', '$user', '$password', NOW())";
$sql2 = "INSERT INTO cv (userid)
VALUES(LAST_INSERT_ID(),'text')";

No. Not like that.
You do the first insert, so the new useraccount is added. And we grab the PK value (=userID) of the newly inserted record with mysql_insert_id() and just store it in the sessionvariable.
There is no need to create the new record inside cv yet. You just store the userID in the session so that you can use it later when you insert all CV data, together with the userID.
If the user decides to logout or so, no problem. Then there just isn't a row inside the CVtable. If you would immedeately create the row, and the user quits, then you have this almost empty row, and you'll need to do all sorts of hacks to make adistincion between 'new' CV records and records that were created earlier and are now edited.

about the CVedit.php
Inside this page, you have 3 sections:
- formprocessing code (--> check values + update/insert)
- CV - valueselection
- (filled in) form.

When you create a new CV (use the action=new in the querystring to identify these request), you skip section 2. When you edit the records, you turn the recordsetvariables into regular variables. Like

foreach($row as $var=>$value){
$$var=$value;
}


Inside the form, you give the formfields the same names as the tablecolumns (if you're concerned about securityissues like disclosing the db's columnnames, then you need to use an array to convert the columnnames into formfieldnames, and back when processing the formfields. You can then also build and proces the forms completely dynamically, which is what i frequently do)

so your form wil look like:

echo '<input type="text" name="firstname" id="firstname" value"'. $firstname .'" />';

When you load the page for a new CV, the $firstname will not be initiated and no value will be printed (--> if you wan't to make sure you din't get parser notices, you can set all variables to '' for the new CV's. Like

if ($_GET['action']=='new'){
$firstname='';
...
}else{
$select="select ... from CV
...
}

Inside the form, you store the action-value in a hidden formfield.
When you proces the form, you again flip all variables from the post or get collection to regular variables.

foreach($_POST as $var=>$value){
$$var=$value;
}

Yhen place your form-processing code before your form-generating code.
This way, you can simply reload the form when there is an invalid value entered, and inject the edited values. Like

$reload='no';
if (!isset($_POST['submit'])){ // $_POST['submit'] = the name of the submitbutton in your form
foreach($_POST as $var=>$value){
$$var=$value;
}
/*your formprocessing
When you find an invalid value,
you set
$reload='yes';

Then before your recordinserting/updating
if ($reload=='no'){
your sql etc
} */
} elseif ($_GET['action']=='new'){
$firstname='';
...
}elseif ($reload=='no'){
$select="select ... from CV where ...";
...
}
// your formcode


So the data will only be selected on first load, when it's not a new CV. On a reload, the posted data will always be injected into the formfields.

The only other thing is the recordinserting/updating. Say we also called the hidden formfield 'action'. Then we could have

if ($action=='new'){
$sql="INSERT (userID, var2, var3) values(" .$_SESSION['UID'] .", ...)";
} else {
$sql="UPDATE ...";
}
$result=mysql_query($sql) or die ('Queryproblem');
...