Help combining data from 2 tables... I keep failing!

DBookatay · 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.

Barand · March 18, 2013

Which fields in the enrollment table contain student id and class id?

DaveyK · 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());

DBookatay · March 18, 2013

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.

Barand · March 18, 2013

(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?

DBookatay · March 18, 2013

class id is in the enrollment table in the "id" field.

Barand · 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?

Edited March 18, 2013 by Barand

DBookatay · March 19, 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?

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.

Jessica · 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?

DBookatay · 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?

B. No, when I paste the same into phpMyAdmin is also only returns 1 name

Barand · March 19, 2013

One query is usually suffucient.

Can you show us some sample data from the 2 tables?

DBookatay · 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?

Edited March 23, 2013 by fenway
next time, use an attachment

Barand · March 19, 2013

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;

Edited March 19, 2013 by Barand

DBookatay · March 19, 2013

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!

Barand · March 19, 2013

One last point, you really should normalize that enrollment table.

DBookatay · March 19, 2013

One last point, you really should normalize that enrollment table.

Meaning?

Barand · March 19, 2013

http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360

Sign In

Help combining data from 2 tables... I keep failing!

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information