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

Abraham Augustus Nortey used Ask the Experts™

on 2018-10-11

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

Open in new window

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>

Open in new window

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>";
    	}
	}
	}

Open in new window

Screenshot--143-.png
Screenshot--144-.png

Comment

Watch Question

Do more with

EXPERT OFFICE^® is a registered trademark of EXPERTS EXCHANGE^®

View Solution Only

kenfcamp

Verified Expert

Commented: 2018-10-11

Well, if each user is assigned a unique ID (UserID), you can use that identifier in your additional tables to match the user the information belongs to.

This can be a primary key with an auto-incremented value created when the account is setup

That is assuming I'm understanding your question properly

Abraham Augustus Nortey

Author

Commented: 2018-10-11

Yes

Abraham Augustus Nortey

Author

Commented: 2018-10-11

So i took out the following from the original users table i had from the beginning
keyword,computer_literate,edu1,qualifications1
My question is that how do i tag the id that is automatically created to userid i have in the new tables

 $sqlInsert = "INSERT INTO users (username, email, password,firstname, othername, lastname,gender,marital_status,dept,job_type,org_name1,pos_held1,work_yoe1,work_yoem1, join_date)
			
			VALUES (:username, :email, :password, :firstname, :othername, :lastname,:gender,:marital_status,:dept,:job_type,:org_name1,:pos_held1,:work_yoe1,:work_yoem1 , now())";
			
			
			$sqlInsert = "INSERT INTO keywords (userId,keyword) 
		VALUES (:userId,:keyword) SELECT users.id FROM users JOIN keywords ON users.id = keywords.userId";
			
			$sqlInsert = "INSERT INTO qualifications (userId,qualification) VALUES (:userId,:qualification) SELECT users.id FROM users JOIN qualifications ON users.id = qualifications.userId";
			
			$sqlInsert = "INSERT INTO educationtab (userId,education) VALUES (:userId,:education) SELECT users.id FROM users JOIN educationtab ON users.id = educationtab.userId";
			
			$sqlInsert = "INSERT INTO compliterate (userId,computer_literate) VALUES (:userId,:computer_literate) SELECT users.id FROM users JOIN compliterate ON users.id = compliterate.userId";
            
			
            $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,':education'=>$education,':qualification'=>$qualification,':org_name1'=>$org_name1,':pos_held1'=>$pos_held1,':work_yoe1'=>$work_yoe1,':work_yoem1'=>$work_yoem1));

Open in new window

Abraham Augustus Nortey

Author

Commented: 2018-10-11

Am sure the query above is wrong, Any help?

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2018

Commented: 2018-10-13

Hey Abraham,

The idea here is that you insert your record into the users table. Once you've done that, you retrieve the auto ID and use that to insert the records into the other tables. Something like this:

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

$statement = $db->prepare($user);
$statement->execute(array(':username' => $username, ':email' => $email, ':password' => $hashed_password, ':firstname'=> $firstname, ':othername'=> $othername, ':lastname'=> $lastname, ':gender'=> $gender, ':marital_status'=> $marital_status, ':dept'=>$dept, ':job_type'=>$job_type, ':org_name1'=>$org_name1, ':pos_held1'=>$pos_held1, ':work_yoe1'=>$work_yoe1, ':work_yoem1'=>$work_yoem1));

// Get the auto-generated ID of the new user
$userId = $statement->lastInsertId();

$keywords = $db->prepare("INSERT INTO keywords (userId, keyword) VALUES (:userId, :keyword)");
$keywords->execute(array(
    'userId' => $userId,
    'keyword' => $keyword,
));

$qual = $db->prepare("INSERT INTO qualifications (userId,qualification) VALUES (:userId, :qualification)"); 
$qual->execute(array(
    'userId' => $userId,
    'qualification' => $qualification,
));

...

Open in new window

This code will only insert a single keyword, and single qualification for each user. If you keywords or qualifications are an array, then you'd need to execute those 2 queries in a loop.

You might also want to look into transactions. This means that you start a transactions, run all your queries and then commit the transaction. It will give you some performance gain and also make sure that if any of the queries fail, none of the data will be inserted.

Abraham Augustus Nortey

Author

Commented: 2018-10-14

Thanks for the reply Chris

I now have the error message " number of bound variable does not match number of tokkens"

 $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'];
		$education = $_POST['education'];
		
		$qualification = $_POST['qualification'];
		$org_name1 = $_POST['org_name1'];
		$pos_held1 = $_POST['pos_held1'];
		
		$work_yoe1 = $_POST['work_yoe1'];
		$work_yoem1 = $_POST['work_yoem1'];

Open in new window

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

$statement = $db->prepare($user);
$statement->execute(array(':username' => $username, ':email' => $email, ':password' => $hashed_password, ':firstname'=> $firstname, ':othername'=> $othername, ':lastname'=> $lastname, ':gender'=> $gender, ':marital_status'=> $marital_status, ':dept'=>$dept, ':job_type'=>$job_type, ':org_name1'=>$org_name1, ':pos_held1'=>$pos_held1, ':work_yoe1'=>$work_yoe1, ':work_yoem1'=>$work_yoem1));

// Get the auto-generated ID of the new user
$userId = $statement->lastInsertId();

$keywords = $db->prepare("INSERT INTO keywords (userId, keyword) VALUES (:userId, :keyword)");
$keywords->execute(array(
    'userId' => $userId,
    'keyword' => $keyword,
));
			
			$qualifications = $db->prepare("INSERT INTO qualifications (userId,qualification) VALUES (:userId, :qualification)"); 
$qual->execute(array(
    'userId' => $userId,
    'qualification' => $qualification,
));


$educationtab = $db->prepare("INSERT INTO educationtab (userId,education) VALUES (:userId, :education)"); 
$qual->execute(array(
    'userId' => $userId,
    'education' => $education,
));
			
$compliterate = $db->prepare("INSERT INTO compliterate (userId,computer_literate) VALUES (:userId, :computer_literate)"); 
$qual->execute(array(
    'userId' => $userId,
    'computer_literate' => $computer_literate,
));

Open in new window

Screenshot--151-.png

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2018

Commented: 2018-10-14

Few things wrong. Your error message is because you have a space in the dept parameter name:

:marital_status, : dept, :job_type,

Also, your 3rd prepared statement is called $qualifications but then you try to execute $qual.

Your next 2 prepared statements are called $educationtab and $compliterate, but you're trying to execute $qual.

Abraham Augustus Nortey

Author

Commented: 2018-10-14

I get this now

Fatal error: Uncaught Error: Call to undefined method PDOStatement::lastInsertId() in C:\xampp\htdocs\project\Login\partials\parseRegister.php:112 Stack trace: #0 C:\xampp\htdocs\project\Login\Register.php(4): include_once() #1 {main} thrown in C:\xampp\htdocs\project\Login\partials\parseRegister.php on line 112

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

$statement = $db->prepare($user);
$statement->execute(array(':username' => $username, ':email' => $email, ':password' => $hashed_password, ':firstname'=> $firstname, ':othername'=> $othername, ':lastname'=> $lastname, ':gender'=> $gender, ':marital_status'=> $marital_status, ':dept'=>$dept, ':job_type'=>$job_type, ':org_name1'=>$org_name1, ':pos_held1'=>$pos_held1, ':work_yoe1'=>$work_yoe1, ':work_yoem1'=>$work_yoem1));

// Get the auto-generated ID of the new user
$userId = $statement->lastInsertId();

$keywords = $db->prepare("INSERT INTO keywords (userId, keyword) VALUES (:userId, :keyword)");
$keywords->execute(array(
    'userId' => $userId,
    'keyword' => $keyword,
));
			
			$qualifications = $db->prepare("INSERT INTO qualifications (userId,qualification) VALUES (:userId, :qualification)"); 
$qualifications->execute(array(
    'userId' => $userId,
    'qualification' => $qualification,
));


$educationtab = $db->prepare("INSERT INTO educationtab (userId,education) VALUES (:userId, :education)"); 
$educationtab->execute(array(
    'userId' => $userId,
    'education' => $education,
));
			
$compliterate = $db->prepare("INSERT INTO compliterate (userId,computer_literate) VALUES (:userId, :computer_literate)"); 
$$compliterate->execute(array(
    'userId' => $userId,
    'computer_literate' => $computer_literate,
));

Open in new window

  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>Project 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/project/Login/activate.php?id='.$encode_id.'"> Confirm Email</a></p>
<p><strong>&copy;2018 project</strong></p>
</body>
</html>';

$mail->addAddress($email, $username);
$mail->Subject = " Project 2018 ";
$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>";
    	}
	}
	}

//activation
else if(isset($_GET['id'])) {
$encoded_id = $_GET['id'];
$decode_id = base64_decode($encoded_id);
$user_id_array = explode("encodeuserid", $decode_id);
$id = $user_id_array[1];

$sql = "UPDATE users SET activated =:activated WHERE id=:id AND activated='0'";

$statement = $db->prepare($sql);
$statement->execute(array(':activated' => "1", ':id' => $id));

if ($statement->rowCount() == 1) {
$result = '<h2 style="color:#1b9d95">Email Confirmed </h2>
<p class="lead">Your email address has been verified, you can now <a href="login.php">login</a> with your email and password.</p>';
	} else {
$result = "<p class='lead'>No changes made please contact site admin for possible solutions.</p>";

	}
}

Open in new window

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2018

Commented: 2018-10-14

Ahhh, sorry. You need to call lastInsertId on the DB connection and not on the statement. It should be

$userId = $db->lastInsertId();

Abraham Augustus Nortey

Author

Commented: 2018-10-14

should $userId = $db->lastInsertId(); be before

$keywords->execute(array(
'userId' => $userId,
'keyword' => $keyword,
));

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2018

Commented: 2018-10-14

Yes, otherwise the $userID variable won't exist when you try to use it in your query! Look at my original code and you'll see where it should be

Abraham Augustus Nortey

Author

Commented: 2018-10-14

Done but i get this error message now

"Recoverable fatal error: Object of class PDOStatement could not be converted to string"

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2018

Commented: 2018-10-14

OK. Post your code. It sounds like you're trying to use the prepared statement wrong!

Abraham Augustus Nortey

Author

Commented: 2018-10-14

<?php
//add our database connection script
include_once 'resource/Database.php';
include_once 'resource/utilities.php';
include_once 'resource/send-email.php';


//process the form
if(isset($_POST['signupBtn'], $_POST['token'])){
	 $config = require __DIR__ . '/../config/app.php';
    $secret = $config['recaptcha']['secret'];

    $recaptcha = [
        'secret' => $secret,
        'response' => $_POST['g-recaptcha-response']
    ];

    $response = verifyReCaptcha($recaptcha);

    if(isset($response->success) && !$response->success == true){
        $result = "<script type='text/javascript'>
                      swal('Error','ReCaptcha validation failed'
                      ,'error');
                      </script>";
    }

    else if(isset($response->hostname) && !$response->hostname == 'localhost'){
        $result = "<script type='text/javascript'>
                      swal('Error','Request originates from a different server'
                      ,'error');
                      </script>";
    }
	
	
    else if(validate_token($_POST['token'])){
$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'];
		$education = $_POST['education'];
		
		$qualification = $_POST['qualification'];
		$org_name1 = $_POST['org_name1'];
		$pos_held1 = $_POST['pos_held1'];
		
		$work_yoe1 = $_POST['work_yoe1'];
		$work_yoem1 = $_POST['work_yoem1'];
		
	
		$user= "INSERT INTO users (username, email, password,firstname, othername, lastname, gender, marital_status,dept, job_type, org_name1, pos_held1, work_yoe1, work_yoem1, join_date)
VALUES (:username, :email, :password, :firstname, :othername, :lastname, :gender, :marital_status, :dept, :job_type, :org_name1, :pos_held1, :work_yoe1, :work_yoem1 , now())";

$statement = $db->prepare($user);
$statement->execute(array(':username' => $username, ':email' => $email, ':password' => $hashed_password, ':firstname'=> $firstname, ':othername'=> $othername, ':lastname'=> $lastname, ':gender'=> $gender, ':marital_status'=> $marital_status, ':dept'=>$dept, ':job_type'=>$job_type, ':org_name1'=>$org_name1, ':pos_held1'=>$pos_held1, ':work_yoe1'=>$work_yoe1, ':work_yoem1'=>$work_yoem1));

// Get the auto-generated ID of the new user
$userId = $db->lastInsertId(); 


$keywords = $db->prepare("INSERT INTO keywords (userId, keyword) VALUES (:userId, :keyword)");
$keywords->execute(array(
    'userId' => $userId,
    'keyword' => $keyword,
));
			
			$qualifications = $db->prepare("INSERT INTO qualifications (userId,qualification) VALUES (:userId, :qualification)"); 
$qualifications->execute(array(
    'userId' => $userId,
    'qualification' => $qualification,
));


$educationtab = $db->prepare("INSERT INTO educationtab (userId,education) VALUES (:userId, :education)"); 
$educationtab->execute(array(
    'userId' => $userId,
    'education' => $education,
));
			
$compliterate = $db->prepare("INSERT INTO compliterate (userId,computer_literate) VALUES (:userId, :computer_literate)"); 
$$compliterate->execute(array(
    'userId' => $userId,
    'computer_literate' => $computer_literate,
));
			
  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>Project 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/project/Login/activate.php?id='.$encode_id.'"> Confirm Email</a></p>
<p><strong>&copy;2018 project</strong></p>
</body>
</html>';

$mail->addAddress($email, $username);
$mail->Subject = " Project 2018 ";
$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>";
    	}
	}
	}

//activation
else if(isset($_GET['id'])) {
$encoded_id = $_GET['id'];
$decode_id = base64_decode($encoded_id);
$user_id_array = explode("encodeuserid", $decode_id);
$id = $user_id_array[1];

$sql = "UPDATE users SET activated =:activated WHERE id=:id AND activated='0'";

$statement = $db->prepare($sql);
$statement->execute(array(':activated' => "1", ':id' => $id));

if ($statement->rowCount() == 1) {
$result = '<h2 style="color:#1b9d95">Email Confirmed </h2>
<p class="lead">Your email address has been verified, you can now <a href="login.php">login</a> with your email and password.</p>';
	} else {
$result = "<p class='lead'>No changes made please contact site admin for possible solutions.</p>";

	}
}

?>

Open in new window

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2018

Commented: 2018-10-14

Couple of issues in your code.

Line 89 - you have a double dollar : $$compliterate->execute(array(

Lines 94-96. This won't work. The $statement query is an INSERT, so there won't be a rowCount. Also, the lastInsertId() will retrieve the Auto ID from the last query you ran, so in your case you are trying to get the last ID from the compliterate table. You already have the $userId from the $userId variable set on line 66, so lines 94 and 95 are not needed.

I can't see anything in your code that would generate the error you mentioned. Does the error give you a line number ?

Abraham Augustus Nortey

Author

Commented: 2018-10-14

Took Off line 94 and the page stopped working,So i took off the $userId variable on line 95

	
  if($statement->rowCount() == 1){

$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>Project 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/project/Login/activate.php?id='.$encode_id.'"> Confirm Email</a></p>
<p><strong>&copy;2018 project</strong></p>
</body>
</html>';

$mail->addAddress($email, $username);
$mail->Subject = " Project 2018 ";
$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>";
    	}
	}
	}

//activation
else if(isset($_GET['id'])) {
$encoded_id = $_GET['id'];
$decode_id = base64_decode($encoded_id);
$user_id_array = explode("encodeuserid", $decode_id);
$id = $user_id_array[1];

$sql = "UPDATE users SET activated =:activated WHERE id=:id AND activated='0'";

$statement = $db->prepare($sql);
$statement->execute(array(':activated' => "1", ':id' => $id));

if ($statement->rowCount() == 1) {
$result = '<h2 style="color:#1b9d95">Email Confirmed </h2>
<p class="lead">Your email address has been verified, you can now <a href="login.php">login</a> with your email and password.</p>';
	} else {
$result = "<p class='lead'>No changes made please contact site admin for possible solutions.</p>";

	}
}

?>

Open in new window

Now i get repeated entry for keywords,education and qualifications but "compliterate" table has a single entry
Screenshot--152-.png
Screenshot--153-.png
Screenshot--154-.png
Screenshot--155-.png

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2018

Commented: 2018-10-14

Yeah - I already told you that you didn't need line 94-95.

I don't see what problem you know have. The tables screenshots you've shown look right to me. You have 2 records in some of the tables that relate to 2 different users. What is it you expect?

Abraham Augustus Nortey

Author

Commented: 2018-10-14

Well My bad, I was of the view that if the insert query fails for the users table, then the rest of the query should fail but it seems those times i was geting the error message, the data for the other tables were submitted

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2018

Commented: 2018-10-14

Hey Abraham,

Yeah - as you currently have it, each query will execute independently of each other. If you remember earlier, I suggested you wrapped all the queries in a transaction - then if any of the queries fail, none of them will insert data. The general approach to this is as follows:

try {

    $db->beginTransaction();

    $user = $db->prepare("...");
    $user->execute(...);

    $userId = $db->lastInsertId();

    $keywords = $db->prepare("...");
    $keywords->execute(...);

    $qualifications = $db->prepare("...");
    $qualifications->execute(...);

    $db->commit();

} catch (Exception $e) {

    // we have an error, so let's roll back the transaction:
    $db->rollBack();

}

Open in new window

Now your queries will execute within the scope of a single transaction. If any of them fail an throw an exception, the entire transaction is rolled back. If none of them fail, then the transaction is committed to the DB

Abraham Augustus Nortey

Author

Commented: 2018-10-14

Thanks Chris and kenfcamp,Its working now

Do more with

Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial

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

Premium Content

Premium Content

Explore our Suite of Services