PHP MYSQL Jobs Portal

0-PHP-JOBS.MD

PHP MYSQL JOBS PORTAL

https://code-boxx.com/simple-jobs-portal-php-mysql/

IMAGES

NOTES

1. Create a database and import 1-database.sql.
2. Change the database settings in 2-lib-jobs.php.
3. Run X-dummy.php if you want to create dummy entries.
4. Access 3-demo-jobs.php in your browser.

LICENSE

Copyright by Code Boxx

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

1-database.sql

-- (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;

-- (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;

2-lib-jobs.php

<?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);
  }

  // (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();
  }
}

// (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();

3-demo-jobs.css

/* (A) ENTIRE PAGE */
* {
  font-family: Arial, Helvetica, sans-serif;
  box-sizing: border-box;
}
body {
  padding: 0;
  margin: 0;
  background: #f2f2f2;
}

/* (B) HEADER */
#pgHead {
  background-image: url(X-header.jpg);
  background-size: cover;
  height: 250px;
  display: flex;
  flex-direction: column;
  align-items: center;
  justify-content: center;
  color: #fff;
}
#pgHead h1 {
  font-size: 3em;
  margin: 0 0 10px 0;
}

/* (C) SEARCH */
#pgSearch {
  position: relative;
  bottom: 30px;
  padding: 0 20px;
  max-width: 600px;
  margin: 0 auto;
}
#pgSearch form {
  padding: 10px;
  display: flex;
  border: 1px solid #dbdbdb;
  background: #fff;
}
#pgSearch input, #pgSearch select {
  border: 0;
  padding: 10px;
  outline: none;
}
#pgSearch input[type=text] { flex-grow: 1; }
#pgSearch select {
  margin: 0 10px;
  border-left: 1px solid #e1e1e1;
}
#pgSearch input[type=submit] {
  color: #fff;
  background: #d5301f;
}

/* (D) JOBS LIST */
#pgList {
  max-width: 1000px;
  margin: 0 auto;
  display: grid;
  grid-template-columns: repeat(3, 1fr);
}
@media screen AND (max-width: 768px) {
  #pgList { grid-template-columns: repeat(2, 1fr); }
}
.jCard {
  margin: 10px;
  padding: 15px;
  border: 1px solid #e1e1e1;
  background: #fff;
  text-decoration: none;
}
.jCoTitle {
  display: flex;
  align-items: center;
  font-weight: 700;
}
.jCoTitle img {
  width: 40px;
  padding: 1px;
  margin-right: 5px;
  border-radius: 50%;
  background: #f00;
}
.jCo {
  font-size: 1.2em;
  color: #000;
}
.jTitle { color: #d51515; }
.jDesc {
  font-size: 0.9em;
  padding: 20px 0;
  color: #363636;
}
.jTypeDate {
  padding-top: 5px;
  font-size: 0.8em;
  line-height: 1.4em;
  border-top: 1px solid #e1e1e1;
  color: #b5b5b5;
}

3-demo-jobs.php

<!DOCTYPE html>
<html>
  <head>
    <title>Jobs Demo Page</title>
    <meta charset="utf-8">
    <link rel="stylesheet" href="3-demo-jobs.css">
  </head>
  <body>
    <?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>
  </body>
</html>

4-demo-job.css

/* (A) ENTIRE PAGE */
* {
  font-family: Arial, Helvetica, sans-serif;
  box-sizing: border-box;
}
body {
  padding: 0;
  margin: 0;
  background: #f2f2f2;
}

/* (B) HEADER */
#pgHead {
  background: #231c93;
  height: 250px;
  display: flex;
  flex-direction: column;
  align-items: center;
  justify-content: center;
  color: #fff;
}
#pgHead h1 {
  font-size: 3em;
  margin: 0 0 10px 0;
}

/* (C) BODY */
#pgBody {
  max-width: 1000px;
  padding: 20px;
  margin: 0 auto;
}
#jTitle, #jType { text-transform: uppercase; }
#jTitle {
  font-size: 1.5em;
  color: #d51515;
  margin: 0 0 5px 0;
}
#jType {
  font-weight: 700;
}
#jDetail {
  margin: 20px 0;
  padding: 20px;
  border: 1px solid #dbdbdb;
  background: #fff;
}
#jDate {
  font-size: 0.9em;
  color: #545454;
}

4-demo-job.php

<?php
// (A) LOAD LIBRARY & GET JOB
require "2-lib-jobs.php";
$job = $_JOB->getJob($_GET["j"]);
if (!is_array($job)) { exit("Invalid job listing"); } ?>
<!DOCTYPE html>
<html>
  <head>
    <title>Job Demo Page</title>
    <meta charset="utf-8">
    <link rel="stylesheet" href="4-demo-job.css">
  </head>
  <body>
    <!-- (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>
  </body>
</html>

X-dummy.php

<?php
// (A) LOAD LIBRARY
require "2-lib-jobs.php";

// (B) DUMMY ENTRIES
echo $_JOB->saveCo("Test Co", "A test company") ? "OK" : $_JOB->error ;
echo $_JOB->saveJob(1, "Accountant", "Sit on a chair and count money.", "Full page long <strong>job</strong> description.", "F") ? "OK" : $_JOB->error ;
echo $_JOB->saveJob(1, "Manager", "Sit on a chair and scream at people.", "Full page long <strong>job</strong> description.", "F") ? "OK" : $_JOB->error ;
echo $_JOB->saveJob(1, "Supervisor", "Walk around and scream at people.", "Full page long <strong>job</strong> description.", "F") ? "OK" : $_JOB->error ;
echo $_JOB->saveJob(1, "Worker", "Walk around and get screamed at.", "Full page long <strong>job</strong> description.", "P") ? "OK" : $_JOB->error ;