SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Having a little problem understanding how
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Snickers123
Starting Member

USA
5 Posts

Posted - 06/26/2013 :  04:25:35  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 06/26/2013 :  04:34:40  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Snickers123
Starting Member

USA
5 Posts

Posted - 06/26/2013 :  14:40:51  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4347 Posts

Posted - 06/26/2013 :  14:56:29  Show Profile  Reply with Quote
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.
Go to Top of Page

Snickers123
Starting Member

USA
5 Posts

Posted - 06/26/2013 :  17:52:26  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/27/2013 :  00:51:25  Show Profile  Reply with Quote
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 joins

http://w3schools.com/sql/sql_join.asp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Snickers123
Starting Member

USA
5 Posts

Posted - 06/28/2013 :  17:52:22  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/29/2013 :  14:32:39  Show Profile  Reply with Quote
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)

or
SELECT ...
FROM... t
WHERE EXISTS(Your FirstSerach Query
             WHERE Col= t.LinkingCol)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Snickers123
Starting Member

USA
5 Posts

Posted - 07/03/2013 :  13:47:38  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/03/2013 :  14:56:26  Show Profile  Reply with Quote
Sorry not clear. Whats the query to which you need to use the above queries result as a filter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000