DBookatay Posted March 18, 2013 Share Posted March 18, 2013 I am trying to make a printable enrollment list of kids enrolled in each class, by joining the "enrollment" table and the students table. This code works: $id = $_GET['id']; $query = 'SELECT students.nmL, students.nmF FROM students INNER JOIN enrollment ON students.id=enrollment.s1_AM ORDER BY students.nmL'; $result = mysql_query($query); $numrows = mysql_num_rows($result); for($x = 0; $row = mysql_fetch_array($result); $x++) { $names .= '<p>'.$row['nmF'].' '.$row['nmL'].'</p>'; } but as soon as I add a "Where" clause: SELECT students.nmL, students.nmF FROM students INNER JOIN enrollment ON students.id = enrollment.s1_AM WHERE enrollment.s1_AM = '.$id; I get these errors: Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in/home/dbookatay/superscienceenrichment.com/Admin/print.php on line 212 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in/home/dbookatay/superscienceenrichment.com/Admin/print.php on line 213 I am trying to pull all the students id from the s1_AM fields in the enrollment table, then print their first and last names from the students table. (There are 20 different classes, so I need the where clause, or else (my working code) prints all the results from all the courses, not just the one I want.) (I've attached a screen cap of what I am trying to do) Can someone please help, I have been struggling with this for hours now. Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/ Share on other sites More sharing options...
Barand Posted March 18, 2013 Share Posted March 18, 2013 Which fields in the enrollment table contain student id and class id? Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419271 Share on other sites More sharing options...
DaveyK Posted March 18, 2013 Share Posted March 18, 2013 The error is there because mysql_query() is failing. Use this code to find out what the actual error is: $result = mysql_query($query) or die(mysql_error()); Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419306 Share on other sites More sharing options...
DBookatay Posted March 18, 2013 Author Share Posted March 18, 2013 On 3/18/2013 at 11:01 AM, Barand said: Which fields in the enrollment table contain student id and class id? The enrollment table is: id student s1_AM s1_PM s2_AM s2_PM and the student table id p_id nmF nmL sex dob tshirt school grade teacher notes When a student registers the have 4 time slot options, s1_AM, s1_PM, s2_AM, s2_PM, (s meaning "session,") and their student id gets saved in the time slot that they chose. So to answer your question, s1_AM would contain the student id. Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419323 Share on other sites More sharing options...
Barand Posted March 18, 2013 Share Posted March 18, 2013 Quote (There are 20 different classes, so I need the where clause, or else (my working code) prints all the results from all the courses, not just the one I want.) So, I ask again, where is the class id held? Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419376 Share on other sites More sharing options...
DBookatay Posted March 18, 2013 Author Share Posted March 18, 2013 class id is in the enrollment table in the "id" field. Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419377 Share on other sites More sharing options...
Barand Posted March 18, 2013 Share Posted March 18, 2013 so is this what you want SELECT students.nmL, students.nmF FROM students INNER JOIN enrollment ON students.id = enrollment.s1_AM WHERE enrollment.id = $id; edit: What about the students who didn't choose s1_AM? Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419418 Share on other sites More sharing options...
DBookatay Posted March 19, 2013 Author Share Posted March 19, 2013 On 3/18/2013 at 9:35 PM, Barand said: so is this what you want SELECT students.nmL, students.nmF FROM students INNER JOIN enrollment ON students.id = enrollment.s1_AM WHERE enrollment.id = $id; edit: What about the students who didn't choose s1_AM? Barand: The above it only displaying the first student, even though there are currently 22 enrolled in session 1, am class. Here is what I have: $query = 'SELECT students.nmL, students.nmF FROM students INNER JOIN enrollment ON students.id = enrollment.s1_AM WHERE enrollment.id = '.$id; $result = mysql_query($query); $result = mysql_query($query) or die(mysql_error()); $numrows = mysql_num_rows($result); for($x = 0; $row = mysql_fetch_array($result); $x++) { $s1_AM .= '<p>'.$row['nmF'].' '.$row['nmL'].'</p>'; } If their not enrolled in s1_AM then I don't want them to display, which was the problem with the original query that I posted, all the students (over 100) were being displayed, even though I know only 22 were supposed to. Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419448 Share on other sites More sharing options...
Jessica Posted March 19, 2013 Share Posted March 19, 2013 A. Use a while loop, look at the examples on the manual page for mysql_fetch_assoc B. what's the rest of the code? Does the query work okay in mysql? Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419451 Share on other sites More sharing options...
DBookatay Posted March 19, 2013 Author Share Posted March 19, 2013 On 3/19/2013 at 1:18 AM, Jessica said: A. Use a while loop, look at the examples on the manual page for mysql_fetch_assoc B. what's the rest of the code? Does the query work okay in mysql? B. No, when I paste the same into phpMyAdmin is also only returns 1 name Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419455 Share on other sites More sharing options...
Barand Posted March 19, 2013 Share Posted March 19, 2013 One query is usually suffucient. Can you show us some sample data from the 2 tables? Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419465 Share on other sites More sharing options...
DBookatay Posted March 19, 2013 Author Share Posted March 19, 2013 I did a sql export, here is what I got. (I did cut out some rows, just to save space) INSERT INTO `enrollment` (`id`, `student`, `s1_AM`, `s1_PM`, `s2_AM`, `s2_PM`, `balance`, `date`) VALUES (1, 1, 10, 0, 0, 0, 0, '2013-03-04'), (2, 2, 8, 0, 0, 0, 0, '2013-02-27'), (3, 3, 0, 0, 0, 12, 0, '2013-03-02'), (4, 4, 18, 0, 0, 0, 0, '2013-03-02'), (5, 5, 18, 0, 0, 0, 0, '2013-03-02'), (6, 6, 0, 0, 3, 5, 0, '2013-02-27'), (7, 7, 0, 0, 9, 14, 0, '2013-02-27'), (8, 8, 15, 0, 0, 0, 0, '2013-02-26'), (9, 9, 12, 0, 14, 0, 0, '2013-02-26'), (10, 10, 10, 3, 0, 0, 0, '2013-03-04'), (11, 11, 11, 17, 0, 0, 0, '2013-03-04'), (12, 12, 3, 0, 6, 0, 0, '2013-02-19'), (13, 13, 3, 0, 6, 0, 0, '2013-02-19'), (14, 14, 0, 8, 0, 0, 0, '2013-03-03'), (15, 15, 0, 1, 0, 2, 0, '2013-03-03'), (16, 16, 5, 4, 18, 0, 0, '2013-03-01'), (17, 17, 0, 4, 0, 5, 0, '2013-02-26'), (18, 18, 13, 17, 0, 0, 0, '2013-02-25'), (19, 19, 8, 10, 0, 0, 0, '2013-02-25'), (20, 20, 11, 0, 0, 0, 0, '2013-02-27'), (21, 21, 13, 7, 0, 9, 0, '2013-02-27'), (22, 22, 8, 6, 0, 3, 0, '2013-02-27'), (23, 23, 0, 8, 0, 0, 0, '2013-02-27'), (24, 24, 12, 14, 0, 0, 0, '2013-02-27'), (25, 25, 0, 0, 12, 14, 0, '2013-02-28'), (26, 26, 12, 0, 14, 0, 0, '2013-03-01'), (27, 27, 0, 10, 0, 0, 0, '2013-03-01'), (28, 28, 0, 0, 6, 0, 0, '2013-03-14'), (29, 29, 6, 3, 4, 5, 0, '2013-03-14'), (30, 30, 4, 0, 5, 0, 0, '2013-03-15'), (31, 31, 4, 0, 5, 0, 0, '2013-03-15'), (32, 32, 7, 0, 9, 0, 0, '2013-03-15'), (33, 33, 12, 0, 0, 0, 0, '2013-03-15'), (34, 34, 4, 0, 0, 0, 0, '2013-03-15'), (35, 35, 5, 0, 0, 0, 0, '2013-03-16'), (36, 36, 4, 0, 5, 5, 0, '2013-03-16'), (37, 37, 0, 0, 9, 0, 0, '2013-03-17'), (38, 38, 0, 0, 9, 0, 0, '2013-03-17'), (39, 39, 17, 0, 0, 0, 0, '2013-03-17'), (40, 40, 4, 0, 5, 0, 0, '2013-03-18'), (41, 41, 10, 0, 0, 0, 0, '2013-03-18'), (42, 42, 10, 0, 0, 0, 0, '2013-03-18'), (43, 43, 18, 0, 0, 0, 0, '2013-03-18'), (44, 44, 12, 14, 0, 0, 0, '2013-03-18'), (45, 45, 13, 15, 0, 0, 0, '2013-03-18'); INSERT INTO `students` (`id`, `p_id`, `nmF`, `nmL`, `sex`, `dob`, `tshirt`, `school`, `grade`, `teacher`, `notes`, `ped_name`, `ped_city`, `ped_phone`, `ext_care`) VALUES (1, 1, 'Nicholas', 'Titolo', 'M', '2005-01-28', 'YM', 'Huckleberry Hill Ele. School', '3', 'Mrs. Plue', '', '', '', '', ''), (2, 2, 'Dylan', 'Morris', 'M', '2005-05-26', 'YS', 'Hill & Plain Elementary', '3', 'Mrs. Molinaro', '', '', '', '', ''), (3, 3, 'Peter', 'Wirth', 'M', '2003-11-19', 'YL', 'Meeting House Hill School', '5', 'Wilkens', '', '', '', '', ''), (4, 4, 'Chase', 'Dietter', 'M', '2005-01-26', 'YL', 'Huckleberry Hill Elementary', '3', 'Mrs. Prizio', '', '', '', '', ''), (6, 5, 'Brandon', 'Zhang', 'M', '2006-10-10', 'YM', 'John Pettibine School', '2', 'Mrs. Gregory', '', '', '', '', ''), (5, 4, 'Brody', 'Dietter', 'M', '2007-09-05', 'YM', 'Center Elementary School', '1', 'Summerfeld', '', '', '', '', ''), (7, 5, 'Jason', 'Zhang', 'M', '2002-07-19', 'YL', 'Sarah Noble Intermediate', '6', 'Mrs. Klee', '', '', '', '', ''), (8, 6, 'Aidan', 'Johnston', 'M', '2002-04-14', 'AS', 'Pembroke Elementary', '6', 'John Merrill', '', '', '', '', ''), (9, 6, 'Madeline', 'Johnston', 'F', '2003-10-31', 'YL', 'Pembroke Elementary', '5', 'Edwin Pena', '', '', '', '', ''), (10, 7, 'Alexander', 'Rogg', 'M', '2005-05-25', 'YS', 'Hill & Plain Elementary', '3', 'Ms. Molinaro', '', '', '', '', ''), (11, 7, 'Tyler', 'Rogg', 'M', '2003-07-13', 'YM', 'Sarah Noble Intermediate', '5', 'Mrs. Gee', '', '', '', '', ''), (12, 8, 'Madelyn', 'Burgess', 'F', '2006-03-11', 'YM', 'Pembroke Elementary', '2', 'Miss Haynes', '', '', '', '', ''), (13, 9, 'Kaitlyn', 'Salvatore', 'F', '2006-03-21', 'YS', 'Great Plain Elementary School', '2', 'Mrs. Leppla', '', '', '', '', ''), (14, 10, 'Claire', 'Kuntze', 'F', '2004-04-12', 'YM', 'Meeting House Hill School', '4', 'Mrs. Quist', '', '', '', '', ''), (15, 10, 'Benjamin', 'Kuntze', 'M', '2007-04-03', 'YS', 'Consolidated School', '1', 'Mrs. Brown', '', '', '', '', ''), (16, 11, 'Maxwell', 'McCarthy', 'M', '2006-05-22', 'YM', 'AIS Magnet School', '2', 'Mcgettigan', '', '', '', '', ''), (17, 12, 'Gabrielle', 'Pomeroy', 'F', '2006-01-29', 'AS', 'Homeschool', '2', 'N/a', '', '', '', '', ''), (18, 13, 'Stephanie', 'Yee', 'F', '2002-03-01', 'AM', 'Pembroke Elementary', '6', 'Mr. Merrill', '', '', '', '', ''), (19, 13, 'Emily', 'Yee', 'F', '2006-04-01', 'YM', 'Pembroke Elementary', '2', 'Mrs. Gerhardt', '', '', '', '', ''), (20, 14, 'Grace', 'Oconnell', 'F', '2002-10-19', 'AS', 'Reed Intermediate', '6', 'Mr. Myhill', '', '', '', '', ''), (21, 15, 'Kevin', 'Tang', 'M', '2002-04-07', 'AS', 'Wms', '6', 'Mrs. Nanassy', '', '', '', '', ''), (22, 15, 'Rachel', 'Tang', 'F', '2005-10-14', 'YS', 'Hhes', '3', 'Mrs. Naiman', '', '', '', '', ''), (23, 16, 'Julianna', 'Fontanilla', 'F', '2005-10-07', 'YM', 'Hill And Plain School', '3', 'Mrs. Meeker', '', '', '', '', ''), (24, 17, 'Jacob', 'Finch', 'M', '2003-10-27', 'YM', 'Kent Center School', '4', 'Susan Makarewich', '', '', '', '', ''), (25, 18, 'Shruti', 'Kelkar', 'F', '2004-01-23', 'YM', 'Stadley Rough', '4', 'Mr Poliquin', '', '', '', '', ''), (26, 19, 'Sebastian', 'Tseng', 'M', '2003-05-03', 'YL', 'St. Joseph School Brookfield', '5', 'Mrs. Nancy Rubino', '', '', '', '', ''), (27, 20, 'Jake', 'Windas', 'M', '2003-08-08', 'YL', 'Home School', '4', 'N/a', '', '', '', '', ''), (28, 21, 'Mya', 'Cefaloni', 'F', '2006-07-02', 'YS', 'Great Plain', '2', 'Ms Owens', '', '', '', '', ''), (29, 22, 'Siri', 'Reddy', 'F', '2006-04-02', 'YM', 'Brookside ', '2', 'Ms. Bockhaus', '', '', '', '', ''), (30, 23, 'Emily', 'Lang', 'F', '2006-01-31', 'YL', 'Kent Center School', '2', 'Mrs Ackerman ', '', '', '', '', ''), (31, 23, 'Madison', 'Lang', 'F', '2006-01-31', 'YL', 'Kent Center School', '2', 'Mrs Ackerman ', '', '', '', '', ''), (32, 24, 'Patrick', 'Dwyer', 'M', '2002-10-21', 'AL', 'Sherman School', '6', 'Mrs. Rianhard', '', '', '', '', ''), (33, 25, 'Brennan', 'Wilkins', 'M', '2003-11-09', 'YL', 'Kent Center School', '4', 'Mrs. Adrienne Mcbrien', '', '', '', '', ''), (34, 25, 'Ashley', 'Wilkins', 'F', '2006-09-02', 'YM', 'Kent Center School', '2', 'Mrs. Nancy Ackerman', '', '', '', '', ''), (35, 26, 'Declan', 'Hsieh', 'M', '2005-11-16', 'YM', 'Sandy Hook Elementary', '3', 'Ford', '', '', '', '', ''), (36, 27, 'Marra', 'Elsesser', 'F', '2006-03-15', 'YS', 'Kent Center School', '2', 'Mrs. Dasilva', '', '', '', '', ''), (37, 28, 'Brooklynn', 'Carpenter', 'F', '2001-12-21', 'YL', 'Stadley Rough School', '6', 'Mills', '', '', '', '', ''), (38, 28, 'Roy ', 'Carpenter', 'M', '2001-12-21', 'YL', 'Stadley Rough School', '6', 'Henggeler', '', '', '', '', ''), (39, 29, 'David', 'Mercier', 'M', '2000-04-25', 'AS', 'St. Joseph School (brookfield)', '8', 'Kathy Benzing', '', '', '', '', ''), (40, 30, 'Mary', 'LaFond', 'F', '2006-04-09', 'YM', 'Sharon Center School', '2', 'Mrs. Ives', '', '', '', '', ''), (41, 31, 'Ella', 'Behling', 'F', '2005-09-19', 'YS', 'Huckleberry Hill', '3', 'Mrs. Laughlin', '', '', '', '', ''), (42, 32, 'Brayden', 'Tripler', 'M', '2004-05-08', 'YM', 'St Joseph', '4', 'Mrs Thompson ', '', '', '', '', ''), (43, 32, 'Samantha', 'Tripler', 'F', '2007-01-31', 'YM', 'St Joseph', '1', 'Mrs Tomaselli', '', '', '', '', ''), (44, 33, 'Tiffany', 'Chen', 'F', '2004-06-01', 'YM', 'John Pittebone Elementary School', '4', 'Ms. Picco', '', '', '', '', ''), (45, 33, 'Stephen', 'Chen', 'M', '2002-01-16', 'AM', 'Sarah Noble School', '6', 'Ms. Miller', '', '', '', '', ''); Does this help, or do you need the structure as well? Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419469 Share on other sites More sharing options...
Barand Posted March 19, 2013 Share Posted March 19, 2013 On 3/18/2013 at 6:38 PM, DBookatay said: class id is in the enrollment table in the "id" field. then there should be 22 rows with the same class id - those are all unique ie 1 student per class. So where is the class really held? I'll have a guess at at the class being in the s1_AM and the student id in student field $query = 'SELECT students.nmL, students.nmF FROM students INNER JOIN enrollment ON students.id = enrollment.student WHERE enrollment.s1_AM = '.$id; Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419546 Share on other sites More sharing options...
DBookatay Posted March 19, 2013 Author Share Posted March 19, 2013 On 3/19/2013 at 12:21 PM, Barand said: then there should be 22 rows with the same class id - those are all unique ie 1 student per class. So where is the class really held? I'll have a guess at at the class being in the s1_AM and the student id in student field $query = 'SELECT students.nmL, students.nmF FROM students INNER JOIN enrollment ON students.id = enrollment.student WHERE enrollment.s1_AM = '.$id; THANK YOU! This did the trick, now only the students that are signed up for s1_AM in that particular class ($id) are showing up. Much appreciation! Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419598 Share on other sites More sharing options...
Barand Posted March 19, 2013 Share Posted March 19, 2013 One last point, you really should normalize that enrollment table. Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419615 Share on other sites More sharing options...
DBookatay Posted March 19, 2013 Author Share Posted March 19, 2013 On 3/19/2013 at 4:55 PM, Barand said: One last point, you really should normalize that enrollment table. Meaning? Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419632 Share on other sites More sharing options...
Barand Posted March 19, 2013 Share Posted March 19, 2013 http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360 Link to comment https://forums.phpfreaks.com/topic/275800-help-combining-data-from-2-tables-i-keep-failing/#findComment-1419642 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.