Welcome to a tutorial on how to create a simple jobs portal with PHP and MYSQL. Have some available job positions? Want to add a jobs section to your website? Let us walk through a simple jobs portal in this guide – Read on!
TABLE OF CONTENTS
PHP MYSQL JOBS PORTAL
All right, let us now get into more details on building a jobs portal with PHP and MYSQL.
PART 1) THE DATABASE
1A) COMPANIES TABLE
-- (A) COMPANIES
CREATE TABLE `companies` (
`company_id` bigint(20) NOT NULL,
`company_name` varchar(255) NOT NULL,
`company_desc` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `companies`
ADD PRIMARY KEY (`company_id`),
ADD KEY (`company_name`);
ALTER TABLE `companies`
MODIFY `company_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
company_id
Primary key, auto-increment.company_name
Name of the company.company_desc
A description of the company.
This one should be self-explanatory. Feel free to add more fields as required – Email, webpage, telephone, industry, etc…
1B) JOBS TABLE
-- (B) JOBS
CREATE TABLE `jobs` (
`job_id` bigint(20) NOT NULL,
`company_id` bigint(20) NOT NULL,
`job_title` varchar(255) NOT NULL,
`job_desc` text NOT NULL,
`job_detail` longtext NOT NULL,
`job_type` varchar(1) NOT NULL DEFAULT 'F',
`job_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `jobs`
ADD PRIMARY KEY (`job_id`),
ADD KEY (`job_title`),
ADD KEY (`company_id`),
ADD FULLTEXT(`job_desc`),
ADD FULLTEXT(`job_detail`),
ADD KEY (`job_type`),
ADD KEY (`job_date`);
ALTER TABLE `jobs`
MODIFY `job_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
job_id
Primary key, auto-increment.company_id
The company offering this job. Foreign key.job_title
Title of the job.job_desc
A short description of the job.job_detail
Full page description of the job.job_type
Full time, part-time, contract, freelance, temporary, etc…job_date
Date when the job is posted.
Once again, feel free to add more fields as required.
PART 2) PHP JOBS LIBRARY
2A) INITIALIZE
<?php
class Jobs {
// (A) CONSTRUCTOR - CONNECT TO THE DATABASE
private $pdo = null;
private $stmt = null;
public $error;
function __construct () {
$this->pdo = new PDO(
"mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
DB_USER, DB_PASSWORD, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);
}
// (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
function __destruct () {
if ($this->stmt !== null) { $this->stmt = null; }
if ($this->pdo !== null) { $this->pdo = null; }
}
// (C) EXECUTE SQL QUERY
function query ($sql, $data=null) : void {
$this->stmt = $this->pdo->prepare($sql);
$this->stmt->execute($data);
}
// ...
}
// (H) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
// (I) JOB TYPE DEFINITION
define("JOB_TYPE", [
"F" => "Full Time", "P" => "Part Time", "L" => "Freelance"
]);
// (J) JOBS OBJECT
$_JOB = new Jobs();
The library looks massive at first, but keep calm and look closely.
- (A, B, J) When
$_JOB = new Jobs()
is created, the constructor will connect to the database. The destructor closes the database connection. - (C)
query()
is a helper function to run an SQL query. - (H) Database settings. Remember to change these to your own.
- (I) The job types. Feel free to add more of your own.
2B) JOBS FUNCTIONS
// (D) SAVE COMPANY
function saveCo ($name, $desc, $id=null) {
$sql = $id==null
? "INSERT INTO `companies` (`company_name`, `company_desc`) VALUES (?,?)"
: "UPDATE `companies` SET `company_name`=?, `company_desc`=? WHERE `company_id`=?";
$data = [$name, $desc];
if ($id!=null) { $data[] = $id; }
$this->query($sql, $data);
return true;
}
// (E) SAVE JOB
function saveJob ($co, $title, $desc, $detail, $type, $id=null) {
$sql = $id==null
? "INSERT INTO `jobs` (`company_id`, `job_title`, `job_desc`, `job_detail`, `job_type`) VALUES (?,?,?,?,?)"
: "UPDATE `companies` SET `company_id`=?, `job_title`=?, `job_desc`=?, `job_detail`=?, `job_type`=? WHERE `job_id`=?";
$data = [$co, $title, $desc, $detail, $type];
if ($id!=null) { $data[] = $id; }
$this->query($sql, $data);
return true;
}
// (F) GET JOBS
function getJobs ($search=null, $type=null) {
// (F1) SQL & DATA
$sql = "SELECT j.`job_id`, j.`job_title`, j.`job_desc`, j.`job_type`, j.`job_date`, c.`company_name`
FROM `jobs` j
LEFT JOIN `companies` c USING (`company_id`)";
$data = [];
if ($search != null) {
$sql .= " WHERE `job_title` LIKE ? OR MATCH(`job_desc`) AGAINST (? IN NATURAL LANGUAGE MODE)";
$data[] = "%$search%";
$data[] = $search;
}
if ($type != null) {
$sql .= $search == null
? " WHERE `job_type`=?"
: " AND `job_type`=?" ;
$data[] = $type;
}
$sql .= " ORDER BY `job_id` DESC";
// (F2) FETCH JOBS
$this->query($sql, $data);
return $this->stmt->fetchAll();
}
// (G) GET JOB
function getJob ($id) {
$this->query(
"SELECT * FROM `jobs`
LEFT JOIN `companies` USING (`company_id`)
WHERE `job_id`=?", [$id]);
return $this->stmt->fetch();
}
There are only 4 “actual job library functions”.
- (D)
saveCo()
Save a company. - (E)
saveJob()
Save a job. - (F)
getJobs()
Get/search for jobs. - (G)
saveJob()
Get the specified job.
PART 3) JOB SEARCH PAGE
<?php
// (A) LOAD LIBRARY & GET JOBS
require "2-lib-jobs.php";
$jobs = $_JOB->getJobs(
isset($_POST["search"]) ? $_POST["search"] : null,
isset($_POST["type"]) ? $_POST["type"] : null,
); ?>
<!-- (B) PAGE HEADER -->
<div id="pgHead"><div>
<h1>PHP Jobs Portal</h1>
<div>Lorem ipsum dolor sit amet, consectetur adipiscing elit.</div>
</div></div>
<!-- (C) SEARCH -->
<div id="pgSearch"><form method="post">
<input type="text" placeholder="Find a job" name="search" value="<?=isset($_POST["search"])?$_POST["search"]:""?>">
<select name="type">
<option value="">Everything</option>
<?php foreach (JOB_TYPE as $t=>$j) {
printf("<option %svalue='%s'>%s</option>",
(isset($_POST["type"]) && $_POST["type"]==$t) ? "selected " : "" , $t, $j
);
} ?>
</select>
<input type="submit" value="Search">
</form></div>
<!-- (D) LISTING -->
<div id="pgList"><?php
if (is_array($jobs)) { foreach ($jobs as $j) { ?>
<a class="jCard" target="_blank" href="4-demo-job.php?j=<?=$j["job_id"]?>">
<div class="jCoTitle">
<img src="X-potato.png">
<div>
<div class="jCo"><?=$j["company_name"]?></div>
<div class="jTitle"><?=$j["job_title"]?></div>
</div>
</div>
<div class="jDesc"><?=$j["job_desc"]?></div>
<div class="jTypeDate">
<?=JOB_TYPE[$j["job_type"]]?><br>
Posted: <?=$j["job_date"]?>
</div>
</a>
<?php }} ?></div>
This page is easier to understand in this order:
- (B)
<div id="pgHead">
Nothing much, just a “nice header” to make things look… nice. - (C)
<div id="pgSearch">
The jobs search bar. - (A) When the search form is submitted, we will do a search for the jobs. If not, just fetch all jobs.
- (D)
<div id="pgList">
List of available jobs.
PART 4) JOB DETAILS PAGE
<?php
// (A) LOAD LIBRARY & GET JOB
require "2-lib-jobs.php";
$job = $_JOB->getJob($_GET["j"]);
if (!is_array($job)) { exit("Invalid job listing"); } ?>
<!-- (B) COMPANY HEADER -->
<div id="pgHead">
<h1><?=$job["company_name"]?></h1>
<div><?=$job["company_desc"]?></div>
</div>
<!-- (C) JOB DETAILS -->
<div id="pgBody">
<h2 id="jTitle"><?=$job["job_title"]?></h2>
<div id="jType"><?=JOB_TYPE[$job["job_type"]]?></div>
<div id="jDetail"><?=$job["job_detail"]?></div>
<div id="jDate">Posted: <?=$job["job_date"]?></div>
</div>
Lastly, just a simple page to show the job details.
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
SUPPORT
600+ free tutorials & projects on Code Boxx and still growing. I insist on not turning Code Boxx into a "paid scripts and courses" business, so every little bit of support helps.
Buy Me A Meal Code Boxx eBooks
EXAMPLE CODE DOWNLOAD
Click here for the source code on GitHub gist, just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
EXTRA BITS & LINKS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
IMPROVEMENT IDEAS
Before the trolls start singing crazy tunes, this is only a simple example. There is a lot that can be done.
- Proper pagination, so the jobs don’t “overflow”.
- Maybe add a “job status” flag. Give “sponsored” or “highlighted” jobs search priority, and hide jobs that are “obsolete”.
- Maybe add more search options – Search by company, search by region, or search by salary range (add your own fields).
- A proper admin panel and a text editor to manage entries (see links below).
- If you want to open up for company and user registrations, see the links below.
- If you want to let companies enter their own jobs, or allow users to upload their CV/resume – You will have to deal with possible spam.
- Add a payment processor – Paypal, Stripe, Apple Pay, Google Pay, etc…
The possibilities are endless, and I cannot provide free consultations – It is up to you to complete your own system now.
LINKS & REFERENCES
- TinyMCE – WYSIWYG Text Editor
- Simple Admin Panel – Code Boxx
- Simple User Registration With PHP MYSQL – Code Boxx
- Simple User Role Management System With PHP MySQL – Code Boxx
THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!