如何在mysql中隐藏记录
I have a job website and i want that when a user posted a job it should not be shown till approval in admin panel of the website.
This is my homepage that displays jobs:
<?php
$result = mysqli_query($conn,"SELECT * FROM job ORDER BY `CreatedTime` DESC");
echo "<table border='0' cellpadding='0' cellspacing='0' class='table-fill'>
<tr>
<th width='300px' position='fixed' overflow='hidden'>Job Title</th>
<th width='100px'>Company Name</th>
<th width='100px'>Location</th>
<th width='80px'>Closing Date</th>
</tr>";
while($row = mysqli_fetch_array($result) ) {
echo "<tr>";
echo "<td><a href='job.details.php?id=".$row['id']."' class='positiontitle-link'>" . $row['positiontitle'] . "</td>";
echo "<td>" . $row['companyname'] . "</td>";
echo "<td>" . $row['location'] . "</td>";
echo "<td>" . $row['closingdate'] . "</td>";
echo "</tr>";
}
echo "</table>";
This is my form that job are posted via:
<div class="body">
<form action="insert.php" method="post" class="ara-form">
<header>Enter Job Details</header>
<fieldset>
<div class="row">
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Job Title" name="positiontitle">
<span class="error"></span>
</label>
</section>
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Organization / Company Name" name="companyname">
</label>
</section>
</div>
<div class="row">
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Location" name="location" >
</label>
</section>
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Job Category e.g. IT" name="jobcategory">
</label>
</section>
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Employment Type e.g. Full Time" name="employmenttype">
</label>
</section>
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Salary e.g. 5000$" name="salary">
</label>
</section>
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Duration e.g. Permanent" name="duration">
</label>
</section>
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Timing e.g. 8 AM - 4 PM" name="timing">
</label>
</section>
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Nationality" name="nationality">
</label>
</section>
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Number of Vacancy e.g. 2 Post(s)" name="numberofvacancy">
</label>
</section>
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Experience e.g. 3 Years" name="experience">
</label>
</section>
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Closing Date" id="datepicker" name="closingdate">
</label>
</section>
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Gender" name="gender">
</label>
</section>
<section class="col col-6">
<label class="input">
<i class="icon-append icon-company"></i>
<input type="text" placeholder="Education e.g. Bachelor" name="education">
</label>
</section>
</div>
</fieldset>
<fieldset>
<section>
<label class="textarea">
Tell us about your company background
<textarea id="editor1" rows="10" cols="80" name="background" placeholder="Tell us about your company background"></textarea>
</label>
</section>
<section>
<label class="textarea">
Job Summary
<textarea id="editor2" rows="10" cols="80" name="summary" placeholder="Job Summary"></textarea>
</label>
</section>
<section>
<label class="textarea">
Job Duties and Responsibilities
<textarea id="editor3" rows="10" cols="80" name="duty" placeholder="Job Duties and Responsibilities"></textarea>
</label>
</section>
<section>
<label class="textarea">
Qualification Needed
<textarea id="editor4" rows="10" cols="80" name="qualification" placeholder="Qualification Needed"></textarea>
</label>
</section>
<section>
<label class="textarea">
Skills Needed
<textarea id="editor5" rows="10" cols="80" name="skill" placeholder="Skills Needed"></textarea>
</label>
</section>
<section>
<label class="textarea">
Submission Guideline
<textarea id="editor6" rows="10" cols="80" name="submission" placeholder="Submission Guideline"></textarea>
</label>
</section>
<section>
<label class="textarea">
Words for making search easy e.g IT | Officer | Manager | ...
<textarea id="editor7" rows="10" cols="80" name="search" placeholder="Words for making search easy e.g IT | Officer | Manager | ... "></textarea>
</label>
</section>
</fieldset>
<footer>
<p>Fill all fields to activate the submit button.</br>
Thanks</p><i class="fa fa-check" style="float: right; position: relative; right: 22px; color: white; z-index: 1; padding-top: 23px;"></i><input class="button" type="submit" id="exinput" value="Submit"/>
<div style="float: right; padding-right: 10px;"><?php
include "../module/back.php";
?></div>
</footer>
</form>
</div>
This is the page where job details are displayed:
<div style="width:900px;">
<div style="float:left; width:200px;"class="ara-form"><header style="font-size:12px; color:#666666; font:Arial, sans-serif; padding:7px;"><?php
$result = mysqli_query($conn,"SELECT * FROM job WHERE id = '".$_GET['id']."' ORDER BY `CreatedTime` DESC");
$jobdetails = mysqli_fetch_assoc($result);
echo '<strong>Job Title</strong></br> '.$jobdetails['positiontitle'].'<hr class="job">';
echo '<strong>Company Name</strong></br> '.$jobdetails['companyname'].'<hr class="job">';
echo '<strong>Location</strong></br> '.$jobdetails['location'].'<hr class="job">';
echo '<strong>Closing Date</strong></br> '.$jobdetails['closingdate'].'<hr class="job">';
echo '<strong>Number of Vacancy</strong></br> '.$jobdetails['numberofvacancy'].'<hr class="job">';
echo '<strong>Job Category</strong></br> '.$jobdetails['jobcategory'].'<hr class="job">';
echo '<strong>Duration</strong></br> '.$jobdetails['duration'].'<hr class="job">';
echo '<strong>Employment Type</strong></br> '.$jobdetails['employmenttype'].'<hr class="job">';
echo '<strong>Salary</strong></br> '.$jobdetails['salary'].'<hr class="job">';
echo '<strong>Timing</strong></br> '.$jobdetails['timing'].'<hr class="job">';
echo '<strong>Nationality</strong></br> '.$jobdetails['nationality'].'<hr class="job">';
echo '<strong>Gender</strong></br> '.$jobdetails['gender'].'<hr class="job">';
echo '<strong>Experience</strong></br> '.$jobdetails['experience'].'<hr class="job">';
echo '<strong>Education</strong></br> '.$jobdetails['education'].'<hr class="job">';
echo '<strong>Gender</strong></br> '.$jobdetails['gender'].'<hr class="job">';
echo '<strong>Gender</strong></br> '.$jobdetails['gender'].'<hr class="job">';
?></header>
</div>
<div style="float:left; width:600px;" class="ara-form">
<fieldset style="font-size:12px; color:#666666; font:Arial, sans-serif;">
<div style="width:100px; float:right; padding-left:2px;"><?php
include "../module/back.php";?>
<button class="button" style="margin-right: -20px; width: 120px; padding-left: 10px;" value="Go Back"><i class="fa fa-check-circle" style="float: right; position: absolute; right: 6px; color: white; z-index: 1; padding-top: 31px; margin-top: -20px;"></i><?php
$query = "select id from jobviews where job_id=".$job_id."";
$result = mysqli_query($conn, $query);
echo "Views ".mysqli_num_rows($result);?></button>
<?php
include "../module/back.php";?>
<?php
include "../module/back.php";?>
</div>
<?php
echo '<p><strong id="ara-form">Company Background</strong></br> '.$jobdetails['background'].'</p></br>';
echo '<p><strong id="ara-form">Job Summary</strong></br> '.$jobdetails['summary'].'</p></br>';
echo '<p><strong id="ara-form">Job Duties and Responsibilities</strong></br> '.$jobdetails['duty'].'</p></br>';
echo '<p><strong id="ara-form">Qualification</strong></br> '.$jobdetails['qualification'].'</p></br>';
echo '<p><strong id="ara-form">Skills</strong></br> '.$jobdetails['skill'].'<hr class="job"></p></br>';
echo '<p><strong id="ara-form">Submission Guideline</strong></br> '.$jobdetails['submission'].'</p></br>';
echo '<p><strong id="ara-form">Words to search this job</strong></br> '.$jobdetails['search'].'</p></br>';
?>
</fieldset></div>
<div style="float:left; width:33.4%; background:#ccc;">three</div>
<div style="clear:both;"></div>
</div>
</div>
This is my MySQL table structure
Add a field to your table called approved int 1
In your first code block change:
$result = mysqli_query($conn,"SELECT * FROM job ORDER BY `CreatedTime` DESC");
to
$result = mysqli_query($conn,"SELECT * FROM job WHERE approved=1 ORDER BY `CreatedTime` DESC");
and also change in your third code block:
$result = mysqli_query($conn,"SELECT * FROM job WHERE id = '".$_GET['id']."' ORDER BY `CreatedTime` DESC");
to
$result = mysqli_query($conn,"SELECT * FROM job WHERE approved=1 AND id = '".$_GET['id']."' ORDER BY `CreatedTime` DESC");
You will also need to add some code to approve new submissions to change approved from 0 to 1
Add a column in your table that determines whether or not a post has been verified and have it default to false
ALTER TABLE `job` ADD COLUMN `approved` BOOL NOT NULL DEFAULT '0';
Then when you validate job post is ok
Update job set approved=1 where id='<the_job_post_id>';
When you make your query to only see approved
SELECT * FROM job where approved=1 ORDER BY `CreatedTime` DESC;