Query is Returning Duplicates

[Spiceydog]

So I am not sure why it is happening but for some reason the following MySQL Query is returning about 15 of each of the correct replies. I am not sure why but I am guessing it's pretty simple.

<?php
$searchclass = $_POST['searchclass'];
$classperiod = $_POST['classperiod'];
include 'login.php';
$query = "SELECT name,
period
FROM schedule, classes
WHERE '$searchclass'=first OR '$searchclass'=second OR '$searchclass'=third OR '$searchclass'=fourth OR '$searchclass'=fifth OR '$searchclass'=sixth OR '$searchclass'=seventh OR '$searchclass'=eighth
ORDER BY name DESC";
$result = mysql_query($query) or die("Couldn't execute query because: ".mysql_error());
while($data = mysql_fetch_array($result, MYSQL_ASSOC)){
$name = $data['name'];
?>
<center><?php echo "$name"; ?></center>
<?php
}
?>

Also I was wondering if something was possible.. What this script does is it finds everyone who is taking a specific class by looking in all of the class period rows (ex. first, second, third, etc.) and then posts them. Unfortunately some classes are taught more than one period per day. I have a second table within the database that has only the name of the class and the period at which it is taught. Is it possible to make it so it not only finds everyone taking the class with a specific name but also only finds the ones that according to a different table are taught during a specific period? I really hope that made since!

Thanks in advance!

[Demaestro]

trying using distinct and group by

SELECT distinct name,
period
FROM schedule, classes
WHERE '$searchclass'=first OR '$searchclass'=second OR '$searchclass'=third OR '$searchclass'=fourth OR '$searchclass'=fifth OR '$searchclass'=sixth OR '$searchclass'=seventh OR '$searchclass'=eighth
ORDER BY name DESC group by name";

[Spiceydog]

Thanks! that worked great! Except you cant ORDER BY and DESC AND GROUP at the same time... so i just grouped.. works great!

Also did you see the other thing at the bottom? i hope it makes since.

[Demaestro]

I have to think some more about the question you are asking.

Can you show me 2 rows from each table.

You should be able to group by and order by I think you just have to change the order... use "group by" first then "order by" last.

[Spiceydog]

Ok here is two rows from the table "schedule":
name year adviser first second third fourth fifth sixth seventh eighth student_id

Rick James Senior Rob_LaVelle Organizational_Behavior Chemistry null null American_Fiction null null null 68
AND
Ice-T Freshmen Jamie Hysjulien Chemistry Advanced_digging American_Fiction Muscle_Flexing Gettin_Jiggy_wit_it Nit_Picking Principles_of_Evolution 64

And here is two rows from the table "classes":
class_id class tname period
36 Chemistry Bob 1
32 American Fiction Colin 3

So the "class" field in the table classes is exactly the same as some of the periods (first, second, third, etc.) in the table schedule.

[edited by: Spiceydog at 4:40 pm (utc) on May 14, 2008]

[Demaestro]

You can probibly get what you want into a query the way things are but it would be better if you altered the schema of the tables a bit to normalize them.

I would make the table like this.

TABLE:
student
student_id ¦ student_first_name ¦ student_last_name

TABLE
classes
class_id ¦ class_name ¦ etc...

TABLE
time_block
block_id ¦ block_time ¦ block_desc

TABLE
student_schedule
student_id ¦ class_id ¦ block_id ¦ school_year

For the student_schedule table you can make a complex key on student_id + block_id + school_year to enforce referential integrity, that way no student can have more then one record for each block in any school year.. also this way multi years will be easy to back track.

So tables info would like

STUDENT
student_id ¦ student_first_name ¦ student_last_name
1 ¦ John ¦ Doe
2 ¦ Jane ¦ Doe

CLASSES
class_id ¦ class_name ¦ etc...
100 ¦ Math 100 ¦ etc...
101 ¦ Science 100 ¦ etc...

TIME_BLOCK
block_id ¦ block_time ¦ block_desc
200 ¦ 8:00am ¦ first
201 ¦ 10:00am ¦ second

STUDENT_SCHEDULE
student_id ¦ class_id ¦ block_id ¦ school_year
1 ¦ 100 ¦ 200 ¦ 2008
1 ¦ 101 ¦ 201 ¦ 2008
2 ¦ 101 ¦ 200 ¦ 2008
2 ¦ 100 ¦ 201 ¦ 2008

This way you don't need nulls in all those "first", "second".... fields also if you ever need to add or remove time blocks all you need to do is add a new time block to the time_block table... in your schema you would have to alter table add column to that table every time.. Trust me the queries you want to write will be so much easier with the data organized this way.

If redoing the tables isn't an option and you still can't get it I will try to help but it might be ugly.

[edited by: Demaestro at 6:49 pm (utc) on May 14, 2008]

[ZydoSEO]

The reason you are getting duplicates (which 99% of the time is the reason) is that you are missing part of your WHERE clause.

If you JOIN table A that has 10 rows w/ table B that has 15 rows and do NOT have a WHERE clause that indicates how the tables are related, you will get the cartesion product of the 2 tables... i.e. you will get 10x15 = 150 rows back. You have essentially the same problem with your query.

You are basically saying JOIN Schedule with Classes, but you don't tell the DB how they two tables are related. You are missing something like WHERE Schedule.ClassID = Classes.ClassID.

Any time you JOIN two tables, your WHERE clause should have something to the affect of: Table1.field = Table2.field.