0

Users give an input like "dog, cat" or "cat, dog, fish... etc". Then I need to loop through that input and query the database which has all types of animal food to get the search result respectively for dog and cat.

I want to show the result like this on my handlebars template engine:

Dog- Canine

  • Happy Dog Food: 10
  • Healthy Dog Food: 15

Cat- Feline

  • Kitty Fish Can: 8

List goes on based on user inputs.

router.get('/search', (req, res) => {
    let { term } = req.query;
    term = term.replace(" ", "").split(',');

    let results = {};
    parent_query_base = "SELECT * FROM animal_profiles WHERE code = :code"
    child_base_query =  "SELECT * FROM food f JOIN animal_profiles a ON f.animal = a.animal WHERE a.code = :code"
    for (i = 0; i < term.length; i++){
        results[db.query(parent_query_base, {replacements: {code: term[i]}, type: QueryTypes.SELECT})] = db.query(child_base_query, {replacements: {code: term[i]}, type: QueryTypes.SELECT});
    }
    res.render('my_view',  {results} );

Handlebars view:

    <h1>Seach Results</h1>    

    {{#each results}}
      <h4>{{animal}} - {{species}}</h4>
      {{#each food}}
        <div>
          <h3>{{food_name}}: {{food_price}}
        </div>
      {{/each}}
    {{else}}
      <p>No food available</p>
    {{/each}}
  • 1
    Why use two separate queries? You should use `JOIN` to combine them into a single query. – Barmar Aug 22 '21 at 18:32
  • Hi Barmar, using JOIN was my first option. However, I only want to show Dog and Species for one time, then show all the foods. If I use JOIN, I can't think about a way without showing Dog and Canine many times. – DesertKoala Aug 22 '21 at 18:43
  • Order the results by species. Then your loop can check whether the current species is the same as the previous row. If not, it can display the species header. – Barmar Aug 22 '21 at 18:45
  • Thanks a lot, Barmar! Would you mind show me a little bit of how to do it in both the router file and the template engine? – DesertKoala Aug 22 '21 at 22:09
  • I don't have a JavaScript example, but https://stackoverflow.com/questions/27575562/how-can-i-list-has-same-id-data-with-while-loop-in-php/27575685#27575685 shows how to do it in PHP. The basic idea is the same in any language. – Barmar Aug 23 '21 at 00:32

0 Answers0