Author |
Topic |
Snickers123
Starting Member
5 Posts |
Posted - 2013-06-26 : 04:25:35
|
I am not new to programing but I am new to mySQL. I have written the programing for a cookbook site. The recipes are marked by the person submitting the recipe by selecting yes or no to 11 different criteria including most food allergies. When a person uses the cookbook to search for recipes they set these switches for their dietary needs.The Select * From recipe Where switchname=yes or no and switchname=yes or no... will be long but I think necessary.The info is in a $_Post[$switchname=yes or no] array.I figure I will need at least one loop but do not know how to use variables in the statement to change the switchname= to a yes or no which ever is needed.Can anyone help guide me in the right direction |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-26 : 04:34:40
|
the problem is with the table design i guess. rather than having yes, no,yes,no for each criteria you should have stored the critieria in different table with referenceid,criteria and valueset as fields.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Snickers123
Starting Member
5 Posts |
Posted - 2013-06-26 : 14:40:51
|
I do not understand how putting the criteria in a different table would help as I would still need to search the db. for the recipes that match the search criteria. I would still need to use variables in order to change the yes's and no's to match the search criteria. Is there a special way to mark a variable so the statement recognize it as a variable. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-06-26 : 14:56:29
|
If you have a one to many; as in a person can zero, one or more food allergies. Then doing that as a de-normalized structure (many bit columns) is, in general, more difficult to work with. If you normalize the data more you can simply join the Person to anotehr table that contains rows of all the things they are allergic to (PersonAllergy for example). This can also be done a similar fashion for the recipes. Then you can join it all together to and if you have any rows then you have a posssible allergy issue. The additional advantage of this is that you can add or remove allergies at any time and you don't ahve to change any code. |
|
|
Snickers123
Starting Member
5 Posts |
Posted - 2013-06-26 : 17:52:26
|
Is there any place where I can learn more about joining tables how it works. I am just starting so the basics first. I have tried to search mySQL/SQL sintax references where I could learn by seeing what is available have not found one that just explains what things are/how they work. Any advice would be really helpful. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-27 : 00:51:25
|
quote: Originally posted by Snickers123 Is there any place where I can learn more about joining tables how it works. I am just starting so the basics first. I have tried to search mySQL/SQL sintax references where I could learn by seeing what is available have not found one that just explains what things are/how they work. Any advice would be really helpful.
here you go with basics of joinshttp://w3schools.com/sql/sql_join.asp------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Snickers123
Starting Member
5 Posts |
Posted - 2013-06-28 : 17:52:22
|
Having the two tables joined is what I had in mind. Now I know how to do it. My problem now is how do I get the results of a search into the Where statement of a second search? Can you please direct me in the right direction. I have been looking and reading for a couple of weeks but just not asking the right questions. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-29 : 14:32:39
|
quote: Originally posted by Snickers123 Having the two tables joined is what I had in mind. Now I know how to do it. My problem now is how do I get the results of a search into the Where statement of a second search? Can you please direct me in the right direction. I have been looking and reading for a couple of weeks but just not asking the right questions.
SELECT ...FROM...WHERE Field IN (Your First Search Query)orSELECT ...FROM... tWHERE EXISTS(Your FirstSerach Query WHERE Col= t.LinkingCol) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Snickers123
Starting Member
5 Posts |
Posted - 2013-07-03 : 13:47:38
|
Please explain just a little to help me understand.SELECT * FROM recipes WHERE User=(whatever user name) This will give me a list of switches that are used to filter the recipes to your dietary needs. This list needs to be the WHERE statement to search the recipes. There are a total of 10 switches at present will be adding more when the need arises.The or (second SELECT) Has me a little confused. Seems it has to do with Joining the two db's and eliminates the need to do the first search. Still not sure how to get the switch settings from the db into the WHERE of the search of the recipes. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-03 : 14:56:26
|
Sorry not clear. Whats the query to which you need to use the above queries result as a filter?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|