If the word is in two columns then priority in the results in mysql

-1

I have a table like these:

menu_id menu_name menu_description
menus table

i would like to order the results giving a priority if the word is in the menu_name and menu description. How can i achieve this?

UPDATE:
with this query i get the most relevant results on top:

    select menu_id, menu_name, menu_description from ( select menu_id, 

    menu_name, menu_description, case when menu_name like '%salame%' 

    then 1 else 0 end + case when menu_description like '%salame%' 

    then 1 else 0 end as order_value from z8upvan6w_menus) as t order 

    by order_value desc

results:

    menu_id | menu_name              | menu_description

    13      | Panino Salame e Noci   | Salame, Noci, Caprino e Insalata

    14      | Piadina Salame e Noci  | Salame, Noci, Caprino e Insalata

    11      | Panino Caprese         | Mozzarella, Pomodoro e Insalata

    12      | Panino Boscaiolo       | Speck, Brie e Salsa Boscaiola

my question is why the third and fourth record appear if there is no word "salame" in any field, how do i get rid of them?

edited Nov 22 '18 at 2:43

asked Nov 22 '18 at 1:12

Robert Falco

1

What word? What is the menu_name? What is the "menu description"?

– Gordon Linoff
Nov 22 '18 at 1:28

I updated the question, please have a look.

– Robert Falco
Nov 22 '18 at 2:42

Because the records are only ordered, not filtered. I'll update this in my answer below.

– Yatin
Nov 22 '18 at 22:50

add a comment |

-1

I have a table like these:

menu_id menu_name menu_description
menus table

i would like to order the results giving a priority if the word is in the menu_name and menu description. How can i achieve this?

UPDATE:
with this query i get the most relevant results on top:

    select menu_id, menu_name, menu_description from ( select menu_id, 

    menu_name, menu_description, case when menu_name like '%salame%' 

    then 1 else 0 end + case when menu_description like '%salame%' 

    then 1 else 0 end as order_value from z8upvan6w_menus) as t order 

    by order_value desc

results:

    menu_id | menu_name              | menu_description

    13      | Panino Salame e Noci   | Salame, Noci, Caprino e Insalata

    14      | Piadina Salame e Noci  | Salame, Noci, Caprino e Insalata

    11      | Panino Caprese         | Mozzarella, Pomodoro e Insalata

    12      | Panino Boscaiolo       | Speck, Brie e Salsa Boscaiola

my question is why the third and fourth record appear if there is no word "salame" in any field, how do i get rid of them?

edited Nov 22 '18 at 2:43

asked Nov 22 '18 at 1:12

Robert Falco

1

What word? What is the menu_name? What is the "menu description"?

– Gordon Linoff
Nov 22 '18 at 1:28

I updated the question, please have a look.

– Robert Falco
Nov 22 '18 at 2:42

Because the records are only ordered, not filtered. I'll update this in my answer below.

– Yatin
Nov 22 '18 at 22:50

add a comment |

-1

I have a table like these:

menu_id menu_name menu_description
menus table

i would like to order the results giving a priority if the word is in the menu_name and menu description. How can i achieve this?

UPDATE:
with this query i get the most relevant results on top:

    select menu_id, menu_name, menu_description from ( select menu_id, 

    menu_name, menu_description, case when menu_name like '%salame%' 

    then 1 else 0 end + case when menu_description like '%salame%' 

    then 1 else 0 end as order_value from z8upvan6w_menus) as t order 

    by order_value desc

results:

    menu_id | menu_name              | menu_description

    13      | Panino Salame e Noci   | Salame, Noci, Caprino e Insalata

    14      | Piadina Salame e Noci  | Salame, Noci, Caprino e Insalata

    11      | Panino Caprese         | Mozzarella, Pomodoro e Insalata

    12      | Panino Boscaiolo       | Speck, Brie e Salsa Boscaiola

my question is why the third and fourth record appear if there is no word "salame" in any field, how do i get rid of them?

edited Nov 22 '18 at 2:43

asked Nov 22 '18 at 1:12

Robert Falco

I have a table like these:

menu_id menu_name menu_description
menus table

i would like to order the results giving a priority if the word is in the menu_name and menu description. How can i achieve this?

UPDATE:
with this query i get the most relevant results on top:

    select menu_id, menu_name, menu_description from ( select menu_id, 

    menu_name, menu_description, case when menu_name like '%salame%' 

    then 1 else 0 end + case when menu_description like '%salame%' 

    then 1 else 0 end as order_value from z8upvan6w_menus) as t order 

    by order_value desc

results:

    menu_id | menu_name              | menu_description

    13      | Panino Salame e Noci   | Salame, Noci, Caprino e Insalata

    14      | Piadina Salame e Noci  | Salame, Noci, Caprino e Insalata

    11      | Panino Caprese         | Mozzarella, Pomodoro e Insalata

    12      | Panino Boscaiolo       | Speck, Brie e Salsa Boscaiola

my question is why the third and fourth record appear if there is no word "salame" in any field, how do i get rid of them?

jquery mysql

edited Nov 22 '18 at 2:43

asked Nov 22 '18 at 1:12

Robert Falco

edited Nov 22 '18 at 2:43

asked Nov 22 '18 at 1:12

Robert Falco

edited Nov 22 '18 at 2:43

asked Nov 22 '18 at 1:12

Robert Falco

asked Nov 22 '18 at 1:12

Robert Falco

asked Nov 22 '18 at 1:12

Robert Falco

1

What word? What is the menu_name? What is the "menu description"?

– Gordon Linoff
Nov 22 '18 at 1:28

I updated the question, please have a look.

– Robert Falco
Nov 22 '18 at 2:42

Because the records are only ordered, not filtered. I'll update this in my answer below.

– Yatin
Nov 22 '18 at 22:50

add a comment |

1

What word? What is the menu_name? What is the "menu description"?

– Gordon Linoff
Nov 22 '18 at 1:28

I updated the question, please have a look.

– Robert Falco
Nov 22 '18 at 2:42

Because the records are only ordered, not filtered. I'll update this in my answer below.

– Yatin
Nov 22 '18 at 22:50

What word? What is the menu_name? What is the "menu description"?

– Gordon Linoff
Nov 22 '18 at 1:28

I updated the question, please have a look.

– Robert Falco
Nov 22 '18 at 2:42

Because the records are only ordered, not filtered. I'll update this in my answer below.

– Yatin
Nov 22 '18 at 22:50

add a comment |

2 Answers
2

active

oldest

votes

You are not filtering out any records. For that, you need a where clause:

select menu_id, menu_name, menu_description

from from z8upvan6w_menus

where (menu_name like '%salame%') or

      (menu_description like '%salame%' )

order by ( (menu_name like '%salame%') +

           (menu_description like '%salame%')

         ) desc;

Notes:

The where clause does the filtering.

No subquery is necessary. You can order by an expression.

I revised the expression so it uses the fact the MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.

answered Nov 22 '18 at 4:11

Gordon Linoff

778k35307410

add a comment |

I'd think you can use a dynamic numeric order with value 1 when the name is in the menu_name and 1 when the name is in menu_description. Sum them up when you select and order the results by the summed up value.
Like this:

It's written according to the SQL Server syntax, but I think you should be able to convert it the way you want.

UPDATE: Adding where clause to the query below to filter out the records which don't match both the menu_name and menu_description.

Assuming @input_val is the value you're trying to find in the columns.

select menu_id, menu_name, menu_description

from (

 select menu_id, menu_name, menu_description, 

 case 

    when menu_name like '%' + @input_val + '%' then 1

    else 0

 end

    +

 case

    when menu_description like '%' + @input_val + '%' then 1

    else 0

 end

 as order_value

 from menu_table) as t

 where t.order_value > 0

 order by order_value desc, menu_name asc

edited Nov 22 '18 at 22:53

answered Nov 22 '18 at 1:53

Yatin

978712

thank you @Yatin, please read the update.

– Robert Falco
Nov 22 '18 at 2:41

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53422605%2fif-the-word-is-in-two-columns-then-priority-in-the-results-in-mysql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

You are not filtering out any records. For that, you need a where clause:

select menu_id, menu_name, menu_description

from from z8upvan6w_menus

where (menu_name like '%salame%') or

      (menu_description like '%salame%' )

order by ( (menu_name like '%salame%') +

           (menu_description like '%salame%')

         ) desc;

Notes:

The where clause does the filtering.

No subquery is necessary. You can order by an expression.

I revised the expression so it uses the fact the MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.

answered Nov 22 '18 at 4:11

Gordon Linoff

778k35307410

add a comment |

You are not filtering out any records. For that, you need a where clause:

select menu_id, menu_name, menu_description

from from z8upvan6w_menus

where (menu_name like '%salame%') or

      (menu_description like '%salame%' )

order by ( (menu_name like '%salame%') +

           (menu_description like '%salame%')

         ) desc;

Notes:

The where clause does the filtering.

No subquery is necessary. You can order by an expression.

I revised the expression so it uses the fact the MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.

answered Nov 22 '18 at 4:11

Gordon Linoff

778k35307410

add a comment |

You are not filtering out any records. For that, you need a where clause:

select menu_id, menu_name, menu_description

from from z8upvan6w_menus

where (menu_name like '%salame%') or

      (menu_description like '%salame%' )

order by ( (menu_name like '%salame%') +

           (menu_description like '%salame%')

         ) desc;

Notes:

The where clause does the filtering.

No subquery is necessary. You can order by an expression.

I revised the expression so it uses the fact the MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.

answered Nov 22 '18 at 4:11

Gordon Linoff

778k35307410

You are not filtering out any records. For that, you need a where clause:

select menu_id, menu_name, menu_description

from from z8upvan6w_menus

where (menu_name like '%salame%') or

      (menu_description like '%salame%' )

order by ( (menu_name like '%salame%') +

           (menu_description like '%salame%')

         ) desc;

Notes:

The where clause does the filtering.

No subquery is necessary. You can order by an expression.

I revised the expression so it uses the fact the MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.

answered Nov 22 '18 at 4:11

Gordon Linoff

778k35307410

answered Nov 22 '18 at 4:11

Gordon Linoff

778k35307410

answered Nov 22 '18 at 4:11

Gordon Linoff

778k35307410

answered Nov 22 '18 at 4:11

Gordon Linoff

778k35307410

add a comment |

It's written according to the SQL Server syntax, but I think you should be able to convert it the way you want.

UPDATE: Adding where clause to the query below to filter out the records which don't match both the menu_name and menu_description.

Assuming @input_val is the value you're trying to find in the columns.

select menu_id, menu_name, menu_description

from (

 select menu_id, menu_name, menu_description, 

 case 

    when menu_name like '%' + @input_val + '%' then 1

    else 0

 end

    +

 case

    when menu_description like '%' + @input_val + '%' then 1

    else 0

 end

 as order_value

 from menu_table) as t

 where t.order_value > 0

 order by order_value desc, menu_name asc

edited Nov 22 '18 at 22:53

answered Nov 22 '18 at 1:53

Yatin

978712

thank you @Yatin, please read the update.

– Robert Falco
Nov 22 '18 at 2:41

add a comment |

It's written according to the SQL Server syntax, but I think you should be able to convert it the way you want.

UPDATE: Adding where clause to the query below to filter out the records which don't match both the menu_name and menu_description.

Assuming @input_val is the value you're trying to find in the columns.

select menu_id, menu_name, menu_description

from (

 select menu_id, menu_name, menu_description, 

 case 

    when menu_name like '%' + @input_val + '%' then 1

    else 0

 end

    +

 case

    when menu_description like '%' + @input_val + '%' then 1

    else 0

 end

 as order_value

 from menu_table) as t

 where t.order_value > 0

 order by order_value desc, menu_name asc

edited Nov 22 '18 at 22:53

answered Nov 22 '18 at 1:53

Yatin

978712

thank you @Yatin, please read the update.

– Robert Falco
Nov 22 '18 at 2:41

add a comment |

It's written according to the SQL Server syntax, but I think you should be able to convert it the way you want.

UPDATE: Adding where clause to the query below to filter out the records which don't match both the menu_name and menu_description.

Assuming @input_val is the value you're trying to find in the columns.

select menu_id, menu_name, menu_description

from (

 select menu_id, menu_name, menu_description, 

 case 

    when menu_name like '%' + @input_val + '%' then 1

    else 0

 end

    +

 case

    when menu_description like '%' + @input_val + '%' then 1

    else 0

 end

 as order_value

 from menu_table) as t

 where t.order_value > 0

 order by order_value desc, menu_name asc

edited Nov 22 '18 at 22:53

answered Nov 22 '18 at 1:53

Yatin

978712

It's written according to the SQL Server syntax, but I think you should be able to convert it the way you want.

UPDATE: Adding where clause to the query below to filter out the records which don't match both the menu_name and menu_description.

Assuming @input_val is the value you're trying to find in the columns.

select menu_id, menu_name, menu_description

from (

 select menu_id, menu_name, menu_description, 

 case 

    when menu_name like '%' + @input_val + '%' then 1

    else 0

 end

    +

 case

    when menu_description like '%' + @input_val + '%' then 1

    else 0

 end

 as order_value

 from menu_table) as t

 where t.order_value > 0

 order by order_value desc, menu_name asc

edited Nov 22 '18 at 22:53

answered Nov 22 '18 at 1:53

Yatin

978712

edited Nov 22 '18 at 22:53

answered Nov 22 '18 at 1:53

Yatin

978712

answered Nov 22 '18 at 1:53

Yatin

978712

answered Nov 22 '18 at 1:53

Yatin

978712

thank you @Yatin, please read the update.

– Robert Falco
Nov 22 '18 at 2:41

add a comment |

thank you @Yatin, please read the update.

– Robert Falco
Nov 22 '18 at 2:41

thank you @Yatin, please read the update.

– Robert Falco
Nov 22 '18 at 2:41

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu