0

Thank you in advance if you will help me out. i am trying to use whereBetween clause and why i am using it, let me explain the issue below.

i have multiple checkboxes for different services which are called treatments and user can select price from the price range slider which is fix price for that service, please see it herehere

and i am saving the records in database in 2 columns, one is treatments column and one is fixed_price column which i think is fine and i am saving it comma separated as you can see it in the below screenshots

enter image description here enter image description here

now if someone is doing search then he will select one of the treatment service and then he will select price range From and To and then clicking on search so here is the reason i am using whereBetween Clause but i think i won't be successful in it. here is my code for the whereBetween clause:

$members->whereBetween('fixed_price',[$request->price_from,$request->price_to]);

i know that i am doing it wrong but any help would be really appreciated.

thank you

Naveed Ali
  • 1,043
  • 7
  • 15
  • 1
    It is the storing of the data that you are doing wrong! You should normalise your data structure to have each treatment and its price as a separate record. Then you could use the between operator. This way you cannot use sql operators, you have to process each record in your application code with string functions. See the following SO question for details: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Shadow May 22 '20 at 12:19
  • can you give me a small demonstration of how the schema for that structure should be which you suggested? – Naveed Ali May 22 '20 at 12:23
  • I already gave you the schema: store each treatment and its price as a separate record. I do not have any information beyond that to suggest anything more. – Shadow May 22 '20 at 12:24
  • i will have issues in search functionality than because for the time being i am searching from one table and after normalization i will have to search from 2 tables and then i will join both maybe. but i am not sure yet. – Naveed Ali May 22 '20 at 12:35
  • i have one table member and from there i am doing the search functionality but normalizing the table is making me confuse of how to get only that members which have price range of for example $200 to $400 – Naveed Ali May 22 '20 at 12:36
  • Why would you have issues with the search functionality if you need to join two tables? Join is a standard sql operation, fully supported by laravel. – Shadow May 22 '20 at 12:37
  • If the treatments are offered by members, then you need to add a member id column to your treatments table next to the treatment and price. Treatment names should probably be in a different table. – Shadow May 22 '20 at 12:39
  • okay then i will have a table called treatments which will have 3 columns: member_id, treatment and their price. but now if i am doing search functionality i have multiple fields on front website in which on of them is: 1- select treatment 2- select price range from and too – Naveed Ali May 22 '20 at 12:45
  • now the members are found by searching through the members table, now i am confuse how i can do the search for all the members which resulted from other fields search and i will have multiple members to search in the treatments table now. – Naveed Ali May 22 '20 at 12:47

0 Answers0