Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.
Creating different tables for a membership registration system (PhpMysql)(Register page)
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
Any help received is appreciated
Registration Form Code
Database Code
Screenshot--144-.png
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>©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--144-.png
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
So i took out the following from the original users table i had from the beginning
keyword,computer_literate,
My question is that how do i tag the id that is automatically created to userid i have in the new tables
keyword,computer_literate,
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));
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:
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.
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,
));
...
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.
Thanks for the reply Chris
I now have the error message " number of bound variable does not match number of tokkens"
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'];
$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,
));
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.
: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.
I get this now
Fatal error: Uncaught Error: Call to undefined method PDOStatement::lastInsertId
Fatal error: Uncaught Error: Call to undefined method PDOStatement::lastInsertId
$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,
));
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>©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>";
}
}
Ahhh, sorry. You need to call lastInsertId on the DB connection and not on the statement. It should be
$userId = $db->lastInsertId();
$userId = $db->lastInsertId();
should $userId = $db->lastInsertId(); be before
$keywords->execute(array(
'userId' => $userId,
'keyword' => $keyword,
));
$keywords->execute(array(
'userId' => $userId,
'keyword' => $keyword,
));
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
Done but i get this error message now
"Recoverable fatal error: Object of class PDOStatement could not be converted to string"
"Recoverable fatal error: Object of class PDOStatement could not be converted to string"
<?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>©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>";
}
}
?>
Couple of issues in your code.
Line 89 - you have a double dollar : $$compliterate->execute(ar
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 ?
Line 89 - you have a double dollar : $$compliterate->execute(ar
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 ?
Took Off line 94 and the page stopped working,So i took off the $userId variable on line 95
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
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>©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>";
}
}
?>
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
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?
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?
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
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:
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();
}
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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