Welcome to WebmasterWorld Guest from 18.204.227.250

Forum Moderators: open

Message Too Old, No Replies

Query is Returning Duplicates

     
9:38 pm on May 13, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:May 7, 2008
posts:45
votes: 0


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!

9:44 pm on May 13, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2643
votes: 6


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";

9:58 pm on May 13, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:May 7, 2008
posts:45
votes: 0


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.

4:11 pm on May 14, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2643
votes: 6


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.

4:37 pm on May 14, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:May 7, 2008
posts:45
votes: 0


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]

6:43 pm on May 14, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2643
votes: 6


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]

1:46 pm on May 15, 2008 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 11, 2007
posts:774
votes: 3


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.