Page 1 of 1

A better Way? Or make this work?

Posted: Sun Jun 08, 2008 10:41 pm
by me!
Ok, I am trying to get an option to work with an existing db that has information in one table called campers, in that there are weeks, and each week is given a value based on how they are attending. I know this is not the best way to go about doing this but it is all I come up with without a lot of re-doing.

The problem is it don't work! :banghead:
The WHERE reg_year='".$reg_year."' is not doing anything and I am getting campers from the past 5 years.

Help... :(

Code: Select all

$result = mysql_query("SELECT * 
                        FROM campers 
                        WHERE reg_year='".$reg_year."' 
                        AND ".$_POST["week"]."='1' 
                        OR (".$_POST["week"]."='4') 
                        OR (".$_POST["week"]."='2')
                        OR (".$_POST["week"]."='5') 
                        ORDER BY first_name, last_name ASC");
            while ($mysql = mysql_fetch_array($result)) 
                {  

Re: A better Way? Or make this work?

Posted: Sun Jun 08, 2008 11:18 pm
by Kieran Huggins
You should have the stats in a second table - then you can query with a join.

Re: A better Way? Or make this work?

Posted: Mon Jun 09, 2008 3:02 am
by onion2k
Your SQL is wrong. Well, not wrong exactly, because it does work, it's just the wrong query for what you want.

Code: Select all

WHERE reg_year='".$reg_year."'
AND ".$_POST["week"]."='1'
OR (".$_POST["week"]."='4')
OR (".$_POST["week"]."='2')
OR (".$_POST["week"]."='5')
What that does is..

Get all the records where reg_year = 2008 AND post_week = 1
or any record where post_week = 4
or any record where post_week = 2
or any record where post_week = 5

The year bit is separate from the post_week bit because you're using OR without wrapping the entire block in brackets. What you need is more like...

Code: Select all

WHERE reg_year='".$reg_year."'
AND 
(
".$_POST["week"]."='1'
OR (".$_POST["week"]."='4')
OR (".$_POST["week"]."='2')
OR (".$_POST["week"]."='5')
)
Kieran is right though, a separate table would be a better approach. You shouldn't fear rewriting code in order to make it better. It'll save you a lot of work in the long term rather than trying to hack together a solution that's based on something that's wrong in the first place. Eventually you'll hit something that you simply can't do with the bad data structure and you'll have to rewrite it - that'll be a hundred times more work than rewriting it now.

Re: A better Way? Or make this work?

Posted: Mon Jun 09, 2008 3:09 pm
by me!
THANK YOU!

I understand exactly what you are saying about bad structure. The more I learn the more I need to re-do :lol:

Why use a separate table for the week attending information?
Advantages? just match it up by camper id?

As it is now the table has all campers and all there info in it, not exactly small... but we have low volume so it works.

Open to recommendations.