Forum Moderators: open
<?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!
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";
Also did you see the other thing at the bottom? i hope it makes since.
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.
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]
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]
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.
