I am querying mysql database to return jobs from one table and the job meta data from another table. I have a form with two inputs, one is title
and second is location
so a user can query w.r.t title or location or both. When I type in title
input and leave the location
one blank then I retrieve results, efficiently, but when I type in the latter input as well, only the same results are retrieved again with no change. When I empty the former input, then I receive unexpected results. Total jobs in the table are 460+ and the results I retrieve are in thousands and when I check the json object received, all of the data is repeated after 460+, and then from start to end and so on. I don't know what's the problem.
My basic need is to query the database and get results w.r.t both inputs, or even when they are empty.
The Form
<form action="" id="search_form">
<input type="text" class="job-search-inp job-title-inp" autocomplete="off" name="job_title" placeholder="Job Title, Company name">
<input type="text" class="job-search-inp job-type-inp" autocomplete="off" name="job_loc" placeholder="City or Country . . . ">
<button type="submit" id="submit_btn"> <i class="fas fa-search"></i> </button>
</form>
Client Side
var searchForm = document.querySelector("#search_form");
searchForm.addEventListener("submit", function(e) {
e.preventDefault();
var json = new Globals().toJSONString(this);
fetch("/fetch_jobs", {
method: "POST",
headers: {
"Content-Type": "application/json"
},
body: json
}).then(function(t) {
t.text().then(function(res) {
console.log(JSON.parse(res));
populateJobs(JSON.parse(res));
})
})
})
Server Side Scripts with Query
app.post("/fetch_jobs", function(req, res) {
new AccessAPI(req.body, res).retData();
})
function AccessAPI(data, response) {
this.retData = function() {
console.log(data['job_title'], data['job_loc']);
ms_connect.query("
SELECT `companies_jobs`.*
, `companies`.`name`
, `companies`.`location`
, `companies`.`image`
FROM `companies`
, `companies_jobs`
WHERE `companies_jobs`.`comp_fk` = `companies`.`id`
AND CONVERT(`companies_jobs`.`job_title` USING utf8) LIKE '%" + data['job_title'] + "%'
OR CONVERT(`companies_jobs`.`job_loc` USING utf8) LIKE '" + data['job_title'] + "%' "
, function(err, rows) {
if (err) {
throw err;
} else {
response.send(rows);
}
})
}
}