troubleshooting Question

Creating different tables for a membership registration system (PhpMysql)(Register page)

Avatar of Abraham Augustus Nortey
Abraham Augustus Nortey asked on
PHPSQL
20 Comments1 Solution143 ViewsLast Modified:
I have created a login and registration system , but i want to classify some specific columns into separate tables linked to the same user id .. as at now all the columns amounting to 131 are in the same table


I already have the users table and the new tables(listed below) which i will distribute some specific columns from the users table into..How do i connect all those tables to my registration system
Users table
Qualification table
Computer Knowledge table
Position held
Educational institution
Organization table



Any help received is appreciated

Registration Form Code
   <div class="container">
    <div class="flag">
        <div align="center"><img src="images/Login.png" width="145" height="140">
 <h4 style="color:white" align="center">Register with Place Center Now!!</h4> 
        <div>
       <div><?php if(isset($result)) echo $result; ?>
        <?php if(!empty($form_errors)) echo show_errors($form_errors); ?></div>
        </div>
        <div class="clearfix"></div>
      <!--  <section class="col col-lg-12">-->
       <div align="left">
		   
		   <div class="container-fluid">
			<div class="row jumbotron">
				<div class="col-10">
					<p class="lead"> 
           <form class="form-horizontal" action="" method="post" >
    <fieldset>
      
      <!-- Form Name -->
<legend style="color:#000;"> Account information </legend>
        
        <!-- Text input-->
      <div class="form-group">
        <label class="col-md-4 control-label">E-Mail</label>
        <div class="col-md-6  inputGroupContainer">
          <div class="input-group"> <span class="input-group-addon"><i class="glyphicon glyphicon-envelope"></i></span>
            <input class="form-control" id="email" type="email" placeholder="Enter Email" 
                       name="email" data-minLength="5"
                       data-error="some error"
                       required/>
                <span class="glyphicon form-control-feedback"></span>
                <span class="help-block with-errors"></span>
            
          </div>
        </div>
      </div>
      
    
        <div class="form-group has-feedback">
            <label for="usename"  class="col-md-4 control-label">
                    Username
                </label>
                <div class="col-md-6  inputGroupContainer">
                <div class="input-group"> <span class="input-group-addon"><i class="glyphicon glyphicon-user"></i></span>
            <input class="form-control" id="username" type="text" placeholder="User name" 
                       name="username" data-minLength="5"
                       data-error="some error"
                       required/>
                <span class="glyphicon form-control-feedback"></span>
                <span class="help-block with-errors"></span>
                </div>
             </div>
        </div>
     
       <div class="form-group has-feedback">
            <label for="usename"  class="col-md-4 control-label">
                    Password
                </label>
                <div class="col-md-6  inputGroupContainer">
                <div class="input-group"> <span class="input-group-addon"><i class="glyphicon glyphicon-lock"></i></span>
            <input class="form-control" id="password" type="password" placeholder="Enter Password" 
                       name="password" data-minLength="5"
                       data-error="some error"
                       required/>
                <span class="glyphicon form-control-feedback"></span>
                <span class="help-block with-errors"></span>
                </div>
             </div>
        </div>
        <br>
    <br>
      <legend style="color:#000;"> Personal Information </legend>
    <fieldset>
      <!-- Text input-->
      
      <div class="form-group">
        <label class="col-md-4 control-label">First Name</label>
        <div class="col-md-6  inputGroupContainer">
          <div class="input-group"> <span class="input-group-addon"><i class="glyphicon glyphicon-user"></i></span>
            <input  name="firstname" placeholder="First Name" class="form-control"  type="text" required>
          </div>
        </div>
      </div>
      
      <!-- Text input-->
      
      <div class="form-group">
        <label class="col-md-4 control-label">Other Name</label>
        <div class="col-md-6  inputGroupContainer">
          <div class="input-group"> <span class="input-group-addon"><i class="glyphicon glyphicon-user"></i></span>
            <input  name="othername" placeholder="Other Name" class="form-control"  type="text" required>
          </div>
        </div>
      </div>
      
      <!-- Text input-->
      
      <div class="form-group">
        <label class="col-md-4 control-label" >Last Name</label>
        <div class="col-md-6  inputGroupContainer">
          <div class="input-group"> <span class="input-group-addon"><i class="glyphicon glyphicon-user"></i></span>
            <input name="lastname" placeholder="Last Name" class="form-control"  type="text" required>
          </div>
        </div>
      </div>
		
		 <div class="form-group">
           
              <label class="col-md-4 control-label">Gender</label>
        <div class="col-md-6  inputGroupContainer">
             <label class="radio-inline"><input type="radio" name="gender" value="Male"> Male</label>
             <label class="radio-inline"><input type="radio" name="gender" value="Female"> Female</label>
    		 <label class="radio-inline"><input type="radio" name="gender" value="Others"> Others</label>
</div>
    <br>
    <br>
    <br>
			 
			   <div class="form-group">
        <label class="col-md-4 control-label">Marital Status</label>
        <div class="col-md-6 selectContainer">
          <div class="input-group"> <span class="input-group-addon"><i class="glyphicon glyphicon-list"></i></span>
            <select name="marital_status" class="form-control selectpicker" required>
              <option value=" "  >Please select your status</option>
              <option>Single</option>
              <option>Married</option>
              <option >Divorced</option>
            </select>
          </div>
        </div>
      </div>
      <br/>
			 <div class="form-group">
        <label class="col-md-4 control-label" >Computer Literacy</label>
        <div class="col-md-6  inputGroupContainer">
          <div class="input-group"> <span class="input-group-addon"><i class="glyphicon glyphicon-user"></i></span>
            <input type="text" name="computer_literate" value="" class="form-control" id="computer_literate" placeholder="Computer courses taken" required>
          </div>
        </div>
      </div>
		<br>	 
			   
						 
						
			   <div class="form-group">
        <label class="col-md-4 control-label">Departments/Functions/Roles</label>
        <div class="col-md-6 selectContainer">
          <div class="input-group"> <span class="input-group-addon"><i class="glyphicon glyphicon-list"></i></span>
            <select name="dept" class="form-control selectpicker" >
             
             <option value="None">Select Department</option>
  <option value="Information technology">Information technology</option>
  <option value="Research and Development">Research and Development</option>
  <option value="Purchasing">Purchasing</option>
  <option value="Marketing">Marketing</option>
  <option value="Human Resource Management">Human Resource Management</option>
  <option value="Accounting and Finance">Accounting and Finance</option>
            </select>
          </div>
        </div>
      </div>
			 
      <br> <div class="form-group">
        <label class="col-md-4 control-label">Job type</label>
        <div class="col-md-6 selectContainer">
          <div class="input-group"> <span class="input-group-addon"><i class="glyphicon glyphicon-list"></i></span>
          
			  <select name="job_type" class="form-control selectpicker" required>
              <option value="#">Select Job Type</option>
             <option value="Intenship">Intenship</option> 
					<option value="Part time">Part Time</option>
					<option value="National service">National Service</option>
				  <option value="Temporal Employment">Temporal Employment</option>
				  <option value="Permanent Employment">Permanent Employment</option>
            </select>
          </div>
        </div>
      </div>
      <br/>
     			
			 
     
       <div class="form-group">
        <label class="col-md-4 control-label">Keywords</label>
		   
        <div class="col-md-6  inputGroupContainer">
			   <h5>Enter Keywords (Could be titles, positions held or any relevant term that can be used to search for your cv eg. Driver, Accountant, Banker etc)</h5>
          <div class="input-group"> <span class="input-group-addon"><i class="glyphicon glyphicon-user"></i></span>
			
               <input type="text" name="keyword" value="" class="form-control" id="keyword" required placeholder="Enter Keywords">
          </div>
			<br>
				
        </div>
      </div>
			 </section>	 
			<br>
			<br>
				<div class="col-lg-12">
					  <h3 style="color: #000">Educational  History</h3>  
                 
					 <div class="form-group">
						
                  <h5>Highest Education and qualification achieved</h5>
                       <table width="1446" height="78" align="center" class="table table-bordered table-condensed">
                    <tr><th width="29%" height="34" style="width: 20%;">Educational Institution Attended:</th>
                     <th width="21%" height="34" style="width: 20%;">Qualification Attained:</th>
                   
                    </tr>
                     <td><input name="edu1" type="text" class="form-control" id="edu1" value="" size="50" maxlength="1000" placeholder="Educational institute attended"></td>
                      
					  <td><input type="text" name="qualifications1" value="" class="form-control" id="qualifications1" placeholder="Qualification attained"></td>
						 
	
                
               
			 	
			  
                    </tr>
					  </table>
			
            </div>
	
</div>

	   
						   
						   </td>
						 
                      <td>
					
		
		
		</div>
				
            <br>      
           
	<div class="col-lg-12">
     <h3 style="color:#000">Career History</h3>  
			
 					<div class="form-group">
					   <h5>Current Job with years of experience,Leave this field if unemployed</h5>
                       <table width="1446" height="78" align="center" class="table table-bordered table-condensed">
                    <tr>
						<th width="23%" height="34" style="width: 20%;">Oganization:</th>
						<th width="29%" height="34" style="width: 20%;">Position held:</th>
                    
                     <th width="18%" height="34" style="width: 20%;">Experience Attained:</th>
                    
                    
                    </tr>
                      
                       <td><input name="org_name1" type="text" class="form-control" id="org_name1" value="" placeholder="Current Institution "></td>
					 
						   <td><input name="pos_held1" type="text" class="form-control" id="pos_held1" value="" placeholder="Position held"></td>
                      
					 
						 
		
                 <td> 
					 <div class="dropdown">
                      
                       
                <select name="work_yoe1" value"" class="form-control" >
  <option value="Specify Value">Specify Value</option>
  <option value="None">None</option>
 <option value="1">1</option><option value="2">2</option><option value="3">3</option><option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option><option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option><option value="26">26</option><option value="27">27</option><option value="28">28</option><option value="29">29</option><option value="30">30</option><option value="31">31</option><option value="32">32</option><option value="33">33</option><option value="34">34</option><option value="35">35</option><option value="36">36</option><option value="37">37</option><option value="38">38</option><option value="39">39</option><option value="40">40</option><option value="41">41</option><option value="42">42</option><option value="43">43</option><option value="44">44</option><option value="45">45</option><option value="46">46</option><option value="47">47</option><option value="48">48</option><option value="49">49</option><option value="50">50</option><option value="51">51</option><option value="52">52</option><option value="53">53</option><option value="54">54</option><option value="55">55</option><option value="56">56</option><option value="57">57</option><option value="58">58</option><option value="59">59</option><option value="60">60</option><option value="61">61</option><option value="62">62</option><option value="63">63</option><option value="64">64</option><option value="65">65</option><option value="66">66</option><option value="67">67</option><option value="68">68</option><option value="69">69</option><option value="70">70</option><option value="71">71</option><option value="72">72</option><option value="73">73</option><option value="74">74</option><option value="75">75</option><option value="76">76</option><option value="77">77</option><option value="78">78</option><option value="79">79</option><option value="80">80</option><option value="81">81</option><option value="82">82</option><option value="83">83</option><option value="84">84</option><option value="85">85</option><option value="86">86</option><option value="87">87</option><option value="88">88</option><option value="89">89</option><option value="90">90</option><option value="91">91</option><option value="92">92</option><option value="93">93</option><option value="94">94</option><option value="95">95</option><option value="96">96</option><option value="97">97</option><option value="98">98</option><option value="99">99</option><option value="100">100</option></select>
  
</select>
				 
					 </div>
					 </td> 
					 <td width="30%">
					 <div class="dropdown">
                      
                       
                <select name="work_yoem1" style="width: 150px;" value"" class="form-control" >
  <option value="Days/Month/Years">Specify</option>
  
  <option value="Day/Days">Day/Days</option>
  <option value="Month/Months">Month/Months</option>
  <option value="Year/Years">Year/Years</option>
  
</select>
						
</div>
	
		</td>    
                    </tr>


		
					  </table>
		
		
				<!--End of Working Experience-->
					 	
		
		</div>
					 

                  
					
					
				  </section>
                
					
				 
		  
					  
   <section>

    <div>
      <!-- Button -->
		<div class="g-recaptcha" data-sitekey="6LdZQXEUAAAAAEJB3l2FscBrNzIBVRDfg-DLWEZY"></div>
		<br>
      <div class="form-group">
        <label class="col-md-4 control-label"></label>
       
        <input type="hidden" name="token" value="<?php if(function_exists('_token')) echo _token(); ?>">
          <button type="submit" name="signupBtn" class="btn btn-danger pull-right" style="float: left">Send <span class="glyphicon glyphicon-send"></span></button>
			
        
        </div>
          <p style="float: right"><a href="index.php">Back</a> </p>
		  </section>
      </div>
      </div>
    </fieldset>
  </form>
  
</div>
 </div>



            
        </div>
      </div> 
<!-- mainContent -->
               
           
  </section>
    
</div>
</div>
</div>
</div>
	</div>
</div>



Database Code
	 //collect form data and store in variables
        $firstname = $_POST['firstname'];
		$othername = $_POST['othername'];
		$lastname = $_POST['lastname'];
		$email = $_POST['email'];
        $username = $_POST['username'];
        $password = $_POST['password'];
		$gender = $_POST['gender'];
		$marital_status = $_POST['marital_status'];
		$computer_literate = $_POST['computer_literate'];
		$dept = $_POST['dept'];
		$keyword = $_POST['keyword'];
		
		$job_type = $_POST['job_type'];
		$edu1 = $_POST['edu1'];
		
		$qualifications1 = $_POST['qualifications1'];
		$org_name1 = $_POST['org_name1'];
		$pos_held1 = $_POST['pos_held1'];
		
		$work_yoe1 = $_POST['work_yoe1'];
		$work_yoem1 = $_POST['work_yoem1'];
		
	
		

        if(checkDuplicateEntries("users", "email", $email, $db)){
            $result = flashMessage("Email is already taken, please try another one");
        }
        else if(checkDuplicateEntries("users", "username", $username, $db)){
            $result = flashMessage("Username is already taken, please try another one");
        }
    //check if error array is empty, if yes process form data and insert record
    else if(empty($form_errors)){
       

        //hashing the password
        $hashed_password = password_hash($password, PASSWORD_DEFAULT);
        try{

			
		
           
            $sqlInsert = "INSERT INTO users (username, email, password,firstname, othername, lastname,gender,marital_status,computer_literate,dept,keyword,job_type,edu1,qualifications1,org_name1,pos_held1,work_yoe1,work_yoem1, join_date)
			
			VALUES (:username, :email, :password, :firstname, :othername, :lastname,:gender,:marital_status,:computer_literate,:dept,:keyword,:job_type,:edu1,:qualifications1,:org_name1,:pos_held1,:work_yoe1,:work_yoem1 , now())";
			

            //use PDO prepared to sanitize data
            $statement = $db->prepare($sqlInsert);

            
            $statement->execute(array(':username' => $username, ':email' => $email, ':password' => $hashed_password, ':firstname'=> $firstname, ':othername'=> $othername, ':lastname'=> $lastname,':gender'=> $gender,':marital_status'=> $marital_status ,':computer_literate'=>$computer_literate,':dept'=>$dept,':keyword' => $keyword,':job_type'=>$job_type,':edu1'=>$edu1,':qualifications1'=>$qualifications1,':org_name1'=>$org_name1,':pos_held1'=>$pos_held1,':work_yoe1'=>$work_yoe1,':work_yoem1'=>$work_yoem1));
			
			
			
			 //call sweet alert
			  if($statement->rowCount() == 1){
				  
				  
				 
$user_id = $db->lastInsertId();
$encode_id = base64_encode("encodeuserid{$user_id}");

//prepare email body
$mail_body = '<html>
<body style="background-color:#CCCCCC; color:#000; font-family: Arial, Helvetica, sans-serif;
                    line-height:1.8em;">
<h2>Place center Login System</h2>
<p>Dear '.$username.'<br><br>Thank you for registering, please click on the link below to to activate your account.Thank you</p>
<p><a href="http://localhost/Place center/Login/activate.php?id='.$encode_id.'"> Confirm Email</a></p>
<p><strong>&copy;2018 PC</strong></p>
</body>
</html>';

$mail->addAddress($email, $username);
$mail->Subject = " Message from Place center ";
$mail->Body = $mail_body;

//Error Handling for PHPMailer
if(!$mail->Send()){
$result = "<script type=\"text/javascript\">
                    swal(\"Error\",\" Email sending failed: $mail->ErrorInfo \",\"error\");</script>";
}
else{
echo $result = "<script type=\"text/javascript\">
                            swal({
                            title: \"Congratulations $username!\",
                            text: \"Registration Completed Successfully. We’ve sent you an email, open and click on activate Account. We’ll take it from there.\",
                            type: 'success',
                            confirmButtonText: \"Thank You!\" });
                        </script>";
}


			  }
			  
            //check if one new row was created
            if($statement->rowCount() == 1){
                $result = flashMessage("Registration Succsessful","Pass");
            }
        }catch (PDOException $ex){
            $result = flashMessage("An error occurred:" .$ex->getMessage());
        }
    }
    else{
        if(count($form_errors) == 1){
            $result = flashMessage("There was 1 error in the form<br>");
        }else{
            $result = flashMessage("There were " .count($form_errors). " errors in the form <br>");
        }
    }
	}else{
		///display error
		 //throw an error
            if(!$result){
        $result = "<script type='text/javascript'>
                      swal('Error','This request originates from an unknown source, posible attack'
                      ,'error');
                      </script>";
    	}
	}
	}
Screenshot--143-.png
Screenshot--144-.png
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 20 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros