Jump to content

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

DBookatay

By DBookatay
in MySQL Help

 Share
Go to solution Solved by DBookatay,

Recommended Posts

DBookatay Advanced Member

DBookatay

Posted
Posted

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.

 

 

post-25613-0-43168800-1363582823_thumb.jpg

DBookatay Advanced Member

DBookatay

Posted
  • Author
Posted

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 Prolific Member

Barand

Posted
Posted

 

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

Barand Prolific Member

Barand

Posted
Posted (edited)

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 by Barand
DBookatay Advanced Member

DBookatay

Posted
  • Author
Posted

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.

DBookatay Advanced Member

DBookatay

Posted
  • Author
Posted

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

DBookatay Advanced Member

DBookatay

Posted
  • Author
Posted (edited)

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 by fenway
next time, use an attachment
Barand Prolific Member

Barand

Posted
Posted (edited)

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 by Barand
  • Solution
DBookatay Advanced Member

DBookatay

Posted
  • Author
  • Solution
Posted

 

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!

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.