MYSQL Query help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
joshuamichaelsanders
Forum Newbie
Posts: 14
Joined: Thu Jul 21, 2011 4:48 pm

MYSQL Query help

Post by joshuamichaelsanders »

I'm trying to formulate the proper SQL query to pull a roster up on this database I was asked to produce for my kids' HS band. Arrghh. Any help would be great. I have 3 tables.

student
[text]
student_ID {PK}
first_name
last_name
email
family_ID {FK}

family
family_ID {PK}
family_phone

adult
adult_ID {PK}
first_name
last_name
family_ID {FK}
[/text]

I've worked out query

Code: Select all

SELECT student.first_name,
student.last_name,
student.email,
adult.first_name,
adult.last_name,
family.family_phone
FROM family
LEFT JOIN student on family.family_ID = student.family_ID
LEFT JOIN adult on family.family_ID = adult.family_ID
The only problem with this is I get two rows for the kids who have two parents listed in the database. Like this.

[text]
| first_name | last_name | email | first_name | last_name | family_phone |
+------------+-------------+-----------------------------------+------------+---
-------------+--------------+
| Spencer | Smith | spencer@gmail.com | Stan | Smith | 805-555-1212 |
| Spencer | Smith | spencer@gmail.com | Karen | Smith | 805-555-1212 |
| Katherine | Jones | katherine@hotmail.com | Wes | Broderick | 805-555-1313 |
[/text]

I was going to display the results using this code but can't figure out how to get the adult's data to display as one line, ie. Stan and Karen Smith.

Code: Select all

$num=mysql_numrows($roster_result);

			$i = 0;
			while ($i < $num) {
				$student-first=mysql_result($roster_result,$i,"student.first_name");
				$student-last=mysql_result($roster_result,$i,"student.last_name");
				$student-mobile=mysql_result($roster_result,$i,"student.mobile_phone");
				$student-email=mysql_result($roster_result,$i,"student.email");
                                $family-phone=mysql_result($roster_result,$i,"family.family_phone");
				$adult-first=mysql_result($roster_result,$i,"student.mobile_phone");
				$adult-last=mysql_result($roster_result,$i,"studentemail");
				echo "<tr>";
				echo "<td>";
				echo $student-last.", ".$student-first;
				echo "</td>";
				echo "<td>";
				echo $student-mobile;
				echo "</td>";
				echo "<td>";
				echo $student-email;
				echo "</td>;
				echo "<td>";
				echo $family-mobile;
				echo "</td>";
				echo "<td>";
				echo $adult-first.", ".$adult-last;
				echo "</td>"</tr>";
				$i++;
I'm hoping this is posted in the right category. I was assuming the problem is with my SQL query. Any help would be great.
-J
Last edited by Benjamin on Mon Jul 25, 2011 1:21 pm, edited 2 times in total.
Reason: Added [syntax=php|sql|css|javascript] and/or [text] tags.
southofsomewhere
Forum Newbie
Posts: 2
Joined: Sun Jul 24, 2011 8:32 pm

Re: MYSQL Query help

Post by southofsomewhere »

sql:

Code: Select all

SELECT
  s.first_name,
  s.last_name,
  s.email,
  s.mobile_phone,
  f.family_phone,
  a.first_name,
  a.last_name
FROM student AS s
JOIN family AS f
  ON s.family_ID = f.family_ID
JOIN adult AS a
  ON a.family_ID = s.family_ID
php:

Code: Select all

echo "<table>";
echo "<tr>";
echo "<td>Student Name</td>";
echo "<td>Student Mobile</td>";
echo "<td>Student Email</td>";
echo "<td>Adult Name</td>";
echo "<td>Adult Mobile</td>";
echo "</tr>";

while($row = mysql_fetch_array($sql))
{

  echo "<tr>";
  echo "<td>" . $row["s.last_name"] . ", " . $row["s.first_name"] . "</td>";
  echo "<td>" . $row["s.mobile_phone"] . "</td>";
  echo "<td>" . $row["s.email"] . "</td>";
  echo "<td>" . $row["a.last_name"] . ", " . $row["a.first_name"] . "</td>";
  echo "<td>" . $row["f.family_phone"] . "</td>";
  echo "</tr>";

}

echo "</table>";
I didn't test it, but that should work as requested. It seems like there's some discrepancies with your variable assignments and your select statements in your queries (I see you refered mobile_phone for student, but it's not listed in your table design). Regardless, hopefully this puts you on the right track!
Last edited by Benjamin on Mon Jul 25, 2011 1:21 pm, edited 3 times in total.
Reason: Added [syntax=php|sql|css|javascript] and/or [text] tags.
joshuamichaelsanders
Forum Newbie
Posts: 14
Joined: Thu Jul 21, 2011 4:48 pm

Re: MYSQL Query help

Post by joshuamichaelsanders »

I'm getting an "Notice: Undefined index: s.last_name in ensemble.php on line 49". That's the first <TD> with the s.last_name & s.first_name.

Code: Select all

$roster_query="SELECT s.first_name,s.last_name,s.email,s.mobile_phone,f.family_phone,
		a.first_name,a.last_name FROM student AS s JOIN family AS f 
		ON s.family_ID = f.family_ID JOIN adult AS a ON a.family_ID = s.family_ID";
	$roster_result=mysql_query($roster_query);

	//$num=mysql_numrows($roster_result);

	mysql_close();

		while($row = mysql_fetch_array($roster_result))
		{
			echo "<tr>";
			echo "<td>" .$row["s.last_name"] . "," . $row["s.first_name"]. "</td>";
			echo "<td>" .$row["s.mobile_phone"]."</td>";
			echo "<td>" .$row["s.email"]."</td>";
			echo "<td>" .$row["a.last_name"].",".$row["a.first_name"]."</td>";
			echo "<td>" .$row["f.family_phone"]."</td>";
			echo "</tr>";
		}
It doesn't like the s.last_name and others in the php code building the table. If I change it to $row["last_name"] instead of a.last_name I get a list of the adult names.
I'm also not seeing how this query will work. If there are two adults (sometimes 1,3) with the same family code as a student, it should display on the same <tr>. This looks like each adult with land on a different <tr>. My apologies if I'm reading it incorrectly.
Last edited by Benjamin on Mon Jul 25, 2011 1:22 pm, edited 2 times in total.
Reason: Added [syntax=php|sql|css|javascript] and/or [text] tags.
joshuamichaelsanders
Forum Newbie
Posts: 14
Joined: Thu Jul 21, 2011 4:48 pm

Re: MYSQL Query help

Post by joshuamichaelsanders »

I updated

Code: Select all

$roster_result=mysql_query($roster_query);
to

Code: Select all

$roster_result=mysql_query($roster_query) or die("Josh sucks at SQL and query failed with error: ".mysql_error());
so I could see if the Query was the problem and got.

Josh sucks at SQL and query failed with error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM student AS s JOIN family AS f ON s.family_ID = f.family_ID JOIN adu' at line 2

current code I'm using is

Code: Select all

<?php

$roster_query="SELECT s.first_name, s.last_name, f.family_phone, 
FROM student AS s JOIN family AS f ON s.family_ID = f.family_ID 
JOIN adult AS a ON a.family_ID = s.family_ID";

$roster_result=mysql_query($roster_query) or die("Josh sucks at SQL and query failed with error: ".mysql_error());

mysql_close();
	
		while($row = mysql_fetch_array($roster_result))
		{
			echo "<td>" .$row['first_name']. "-". $row['last_name']. "</td>";
			echo "</td></tr>";
		}
?>
</table><br></div>
</body>
</html>
Last edited by Benjamin on Mon Jul 25, 2011 1:23 pm, edited 1 time in total.
Reason: Added [syntax=php|sql|css|javascript] and/or [text] tags.
joshuamichaelsanders
Forum Newbie
Posts: 14
Joined: Thu Jul 21, 2011 4:48 pm

Re: MYSQL Query help

Post by joshuamichaelsanders »

nevermind on my last. My query had a , after f.family_phone. I've restored my original query and stopped creating additional problems. My original problem still exists however. I'm getting an Undefined index for s.first_name at the echo"<td>" .$row['s.first_name'] line. Updated code is.

Code: Select all

<?php
$roster_query="SELECT s.first_name, s.last_name, s.email, s.mobile_phone, f.family_phone, a.first_name, a.last_name
FROM student AS s JOIN family AS f ON s.family_ID = f.family_ID 
JOIN adult AS a ON a.family_ID = s.family_ID";

$roster_result=mysql_query($roster_query) or die("Josh sucks at SQL and query failed with error: ".mysql_error());

mysql_close();
	
		while($row = mysql_fetch_array($roster_result))
		{
			echo "<td>" .$row['s.first_name']. "-". $row['s.last_name']. "</td>";
			echo "</td></tr>";
		}
?>
</table><br></div>
</body>
</html>
joshuamichaelsanders
Forum Newbie
Posts: 14
Joined: Thu Jul 21, 2011 4:48 pm

Re: MYSQL Query help

Post by joshuamichaelsanders »

I tried GROUP BY with a number of different fields and all I get is it just eliminates the second parent from the list all together. I appreciate the suggestion though.
joshuamichaelsanders
Forum Newbie
Posts: 14
Joined: Thu Jul 21, 2011 4:48 pm

Re: MYSQL Query help

Post by joshuamichaelsanders »

Code: Select all

SELECT GROUP_CONCAT(a.first_name
                    SEPARATOR ' & '), a.last_name
FROM adult AS a
GROUP BY last_name;
I think this is what I wanted. Thanks.
Last edited by Benjamin on Tue Jul 26, 2011 12:59 pm, edited 1 time in total.
Reason: Added [syntax=php|sql|css|javascript] and/or [text] tags.
joshuamichaelsanders
Forum Newbie
Posts: 14
Joined: Thu Jul 21, 2011 4:48 pm

Re: MYSQL Query help

Post by joshuamichaelsanders »

Finished query with student information for anyone coming to this thread later.

Code: Select all

SELECT s.first_name, s.last_name, s.email, s.mobile_phone, 
f.family_phone, GROUP_CONCAT(a.first_name SEPARATOR ' & '), a.last_name

FROM student AS s 
JOIN family AS f ON s.family_ID = f.family_ID
JOIN adult AS a ON a.family_ID = s.family_ID
GROUP BY a.family_ID;
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MYSQL Query help

Post by Benjamin »

@joshuamichaelsanders: We have

Code: Select all

 tags for a reason.  Use them.
Post Reply