0

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);
      }
    })
  }
}
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Nadeem Ahmad
  • 665
  • 3
  • 17
  • 40
  • 1
    This is unrelated to answering your question, but I noticed that you are not using a paramaterized query, nor are you sanitizing the input that you say is coming from a form, and thus I would assume is user entered...this can lead to bad things - [obligatory XKCD](https://xkcd.com/327/) – pstrjds Dec 10 '18 at 17:53
  • Related to your question. Is that a typo in what you copied to SO, or did you repeat `data['job_title']` in your actual code. You are using it when comparing the job_title and the location – pstrjds Dec 10 '18 at 17:57
  • Put parentheses around the two `LIKE` conditions: `AND (CONVERT(...) LIKE ... OR CONVERT(...) LIKE ...)` – Barmar Dec 10 '18 at 17:58
  • **WARNING**: Be sure to use *prepared statements with placeholder values* to avoid [SQL injection bugs](http://bobby-tables.com/). This is necessary to ensure any values are properly escaped and won't cause problems. Most drivers have this capability, and those like [Sequelize](http://sequelizejs.com) make it easy so there's really no excuse to not do it. – tadman Dec 10 '18 at 17:58
  • @tadman The first comment already warns about this. – Barmar Dec 10 '18 at 17:59
  • @Barmar This comment mentions Sequelize as an example of a driver that allows you to do this easily, something the first comment didn't. – tadman Dec 10 '18 at 18:00
  • @Barmar that worked for now, the maximum results are now 460+, but still not efficient data. – Nadeem Ahmad Dec 10 '18 at 18:07
  • 1
    @tadman I will try it now ! – Nadeem Ahmad Dec 10 '18 at 18:08
  • @NadeemAhmad MySQL is usuallly not able to optimize `OR` conditions efficiently. It's often better to split it into two queries that you combine with `UNION`. – Barmar Dec 10 '18 at 19:13
  • BTW, it doesn't affect efficiency, but you should learn to write ANSI JOINs instead of cross-products. – Barmar Dec 10 '18 at 19:13
  • I got the perfect solution, thank you dude ! – Nadeem Ahmad Dec 10 '18 at 20:12

0 Answers0