PHP / MySQL table referencing

[Ancoats]

Hi guys, hope you're all well this morning.

 

I have looked on the internet regarding the problem I currently have (which is that I have little idea on how to go about it!)

 

Basically, I am creating a website using PHP for a final year college project, and the basic idea of it is that like social networking sites, users will be able to register to the site, and create a member profile which will be displayed.

 

I have set up two tables for this process, one of which is members (registration), and the other memberprofile (where the user will be able to fill out profile information once registered and logged into the site)

 

The tables look like this:

 

MEMBERS

 

$tablename = "members";

$create = "CREATE TABLE members (

  memberID int(4) NOT NULL auto_increment,

  firstname varchar(20) NOT NULL default '',

  lastname varchar(20) NOT NULL default '',

username varchar(20) NOT NULL default '',

password char(32) NOT NULL default '',

  email varchar(255) NOT NULL default'',

  PRIMARY KEY (memberID)

)

ENGINE=MyISAM";

 

MEMBERPROFILE

 

$tablename1 = "memberprofile";

$create = "CREATE TABLE memberprofile (

  firstname varchar(20) NOT NULL default '',

  lastname varchar(32) NOT NULL default '',

userDOB date NOT NULL default '',

userGend varchar(32) NOT NULL default '',

userLoc varchar(255) NOT NULL default '',

userInt text NOT NULL default '',

userDesc text NOT NULL default '',

  userPic varchar(255) NOT NULL default''

  )

ENGINE=MyISAM";

 

Now, I know from investigation, that to make the information that the user sends when entering in the MEMBERPROFILE information (using a form) there has to be a thing called table referencing, where basically I want each user's member profile information to be relevant to the unique memberID that is set in the MEMBER table, but how do I go about this? Any help, or specific tutorials that will tell me how to do this?

 

My current memberprofile form code that will be used to send the data to the MEMBERPROFILE table is: (I'm aware that it is not complete, but it might help if pointing out where exactly i need to reference the fields to)

 

<p>Welcome to your Member Login Profile <?php echo $_SESSION['isloggedin']; ?>. You may now enter your profile information</p>

 

 

<form action="<?php $_SERVER['PHP_SELF'];?>" method="post" enctype="multipart/form-data">

<legend>Member Profile Information</legend>

 

<p><label>First Name:</label>

  <br />

  <input name="firstname" type="text" maxlength="20" <?php if(isset($error)) {echo "value='$firstname'";} ?> />

</p>

<p>

<label>Last Name:</label>

<br />

<input name="lastname" type="text" maxlength="20" <?php if(isset($error)) {echo "value='$lastname'";} ?> />

</p>

<p>

<label>Date of Birth:</label>

<br />

<input name="userDOB" type="text" maxlength="20" <?php if(isset($error)) {echo "value='$userDOB'";} ?> />

</p>

<p>

<label>Male / Female:</label>

<label> <br />

<select name="userGend" id="userGend">

  <option>Male</option>

  <option>Female</option>

  <?php if(isset($error)) {echo "value='$userGend'";} ?>

</select>

</label>

</p>

<p>

<label>Location:</label>

<br />

<label>

<input name="userLoc" type="text" maxlength="20" <?php if(isset($error)) {echo "value='$userLoc'";} ?> />

</label>

</p>

<p><label>Interests:<br />

</label>

  <label>

  <textarea name="userInt" id="userInt" cols="45" rows="5"> <?php if(isset($error)) {echo "value='$userInt'";} ?> </textarea>

  </label>

  <br />

</p>

<p><label>Short Description:</label>

  <br />

  <label>

  <textarea name="userDesc" id="userDesc" cols="45" rows="5"><?php if(isset($error)) {echo "value='$userDesc'";} ?></textarea>

  </label>

</p>

<p><label>User Picture:</label>

  <br />

  <label>

  <input type="file" name="userPic" id="userPic" <?php if(isset($error)) {echo "value='$userPic'";} ?>/>

  </label>

  <br />

</p>

<p>

<input type="submit" name="submit" value="Create Profile">

</p>

</form>

 

 

Cheers for the help

Ben

[ToonMariner]

you need to use a key...

 

add a field - something like userid and make it the primary key in both tables - in the registration table it should have auto increment. Once the user registers add a record in the profile table and simply reference the key that was generated for their record in the registration table....

[Ancoats]

you need to use a key...

 

add a field - something like userid and make it the primary key in both tables - in the registration table it should have auto increment. Once the user registers add a record in the profile table and simply reference the key that was generated for their record in the registration table....

 

So, use memberID for the memberprofile table?

 

How do I go about referencing the key too?

[gevans]

Currently in th first table you have this

 

- memberID int(4) NOT NULL auto_increment

 

In the second table you need a reference to this, it can be called the same thing,

 

- memberID int(4) NOT NULL

 

When you INSERT profile data into the second table for the first time, also add the user's id.

 

The you can use a similar query to reference the user..

 

$query = "SELECT `table1`.`field`, `table2`.`field`

FROM `table1`, `table2`

WHERE `table1`.`id` = '$memberID' AND `table1`.`id` = `table2`.`id`"