A better Way? Or make this work?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
me!
Forum Contributor
Posts: 133
Joined: Sat Nov 04, 2006 8:45 pm

A better Way? Or make this work?

Post 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)) 
                {  
Last edited by Weirdan on Mon Jun 09, 2008 4:34 pm, edited 1 time in total.
Reason: php tags
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: A better Way? Or make this work?

Post by Kieran Huggins »

You should have the stats in a second table - then you can query with a join.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: A better Way? Or make this work?

Post 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.
me!
Forum Contributor
Posts: 133
Joined: Sat Nov 04, 2006 8:45 pm

Re: A better Way? Or make this work?

Post 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.
Post Reply