PHP - Moved: Left Join Help. Displaying Tables From Mysql Database.

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=323935.0

Here is what I am trying to accomplish:

I have a students table with a studentID
I also have a notes table and a sched table with studentID

The sched table is working as planned where if the student is scheduled for more than one time he is displayed twice.
However if there are multiple notes per student the student is display for each individual note where I would like only the most current note to be displayed

Here is what the output is:

1- 10:30:00 - 10:50:00   student3 Three   FST   Teacher One   Special1 One   Writing Ratios   09/10
2- 10:30:00 - 10:50:00   student3 Three   FST   Teacher One   Special1 One   Needs to work on fractions and decimals   09/10
3- 13:00:00 - 14:00:00   student3 Three   FST   Teacher One   Special1 One   Writing Ratios   09/10
4- 13:00:00 - 14:00:00   student3 Three   FST   Teacher One   Special1 One   Needs to work on fractions and decimals   09/10

As you can see I have two notes and two schedule times for this student. what I want it to display is only line 1 & 3. which is the newest note in the system.

Below is my code that I am using. Any help would be greatly appreciated.


Code: [Select]
<?php 
$result = mysql_query("SELECT * FROM students 
  LEFT JOIN teachers ON students.teacherId = teachers.teacherId
  LEFT JOIN course ON students.courseId = course.courseId
  LEFT JOIN specialEd ON students.specialId = specialEd.specialId
  LEFT JOIN sched ON students.studentId = sched.studentId 
  ORDER BY start
  ");
echo "<table>";
while ($row = mysql_fetch_array($result)){
$id = "?id=" . $row['studentId'];
echo "<tr>";
echo "<td>" . $row['start'] . " - " . $row['stop'] . "</td>";
echo "<td>" . "<a href='student.php$id'>" . $row['fName'] . " " . $row['lName'] . "</td>";
echo "<td>" . $row['courseName'] . "</td>";
echo "<td>" . $row['teachers_fName'] . " " . $row['teachers_lName'] . "</td>";
echo "<td>" . $row['special_fName'] . " " . $row['special_lName'] . "</td>";
echo "<td>" . $row['note'] . "</td>";
echo "<td>" . date("m/d", strtotime($row['started'])) . "</td>";
echo "</tr>";
}
echo "</table>";
?>

This topic has been escorted to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=354341.0

Alright this is going to be a doozy:

I have 3 tables: Collectibles, CollectiblesMethods and CollectionsCollectibles

Collectibles contains: ID, Photo, Collectible, Value, Resale, Rarity
CollectiblesMethods contains: ID, Collectible, Method
CollectionsCollectibles contains: ID, Collectible, Collection, Amount

Needless to say, this is a many to many relationship model. (many methods to one collectible, many collectibles to one collection).

The page collectibles.php is contained inside collspec.php

collspec.php shows information relating to a specific collection called by GET.
collectibles.php shows all collectibles belonging to the same specfic collection in collpec.php, thus why it is contained inside it.

I want collectibles.php to output the following:
Photo, Collectible, Amount, Resale, Rarity, Method

I already have collectibles.php outputting:
Photo, Collectible, Amount, Resale, Rarity

Method is proving to be difficult. I can either get it to show the word None for every collectible with this code:
<table width="99%">
<tr>
<th colspan="6"><h2>Collectibles</h2></th>
</tr>
<tr>
<td>Photo</td>
<td>Name</td>
<td># Needed</td>
<td>Sells For</td>
<td>Rarity</td>
<td>Obtained</td>
</tr>
<?php 
$sql2 = "SELECT * FROM CollectionsCollectibles LEFT JOIN Collectibles ON CollectionsCollectibles.Collectible = Collectibles.Collectible WHERE Collection = '" . mysql_real_escape_string($item) . "'";
$collectible = mysql_query($sql2);
if (trim($row['Collectible']) != '') 
while($row2 = mysql_fetch_array($collectible)){
?>
<tr>
<td width="14%" valign="top"><img src="<?php echo "$img{$row2['Photo']}"; ?>"></td>
<td><?php echo $row2['Collectible']; ?></td>
<td><?php echo $row2['Amount']; ?></td>
<td><?php echo $row2['Sell']; ?></td>
<td><?php echo $row2['Rarity']; ?></td>
<td>
<?php
$sql3 = "SELECT CollectiblesMethods.Method FROM CollectiblesMethods WHERE Collectible = '" . $row2['Collectible'] . "'";
$obtain = mysql_query($sql3);
if (trim($row['Method']) != '') 
while($row3 = mysql_fetch_array($obtain)){
?>
<tr>
<td><?php echo $row3['Method']; ?></td>
</tr>
<?php
} else 
  echo 'None';
?>
</td>
<?php
} else 
  echo 'None';
?>
</tr>
</table>

or I can get it to show the Methods (of which certain collectibles have more than one method) using this code but with a catch; it repeats the photo, name, value and resale if there is more than one method for a given collectible:
<table width="99%">
<tr>
<th colspan="6"><h2>Collectibles</h2></th>
</tr>
<tr>
<td>Photo</td>
<td>Name</td>
<td># Needed</td>
<td>Sells For</td>
<td>Rarity</td>
<td>Obtained</td>
</tr>
<?php 
$sql2 = "SELECT * FROM CollectionsCollectibles LEFT JOIN Collectibles ON CollectionsCollectibles.Collectible = Collectibles.Collectible LEFT JOIN CollectiblesMethods ON Collectibles.Collectible = CollectiblesMethods.Collectible WHERE Collection = '" . mysql_real_escape_string($item) . "'";
$collectible = mysql_query($sql2);
if (trim($row['Collectible']) != '') 
while($row2 = mysql_fetch_array($collectible)){
?>
<tr>
<td width="14%" valign="top"><img src="<?php echo "$img{$row2['Photo']}"; ?>"></td>
<td><?php echo $row2['Collectible']; ?></td>
<td><?php echo $row2['Amount']; ?></td>
<td><?php echo $row2['Sell']; ?></td>
<td><?php echo $row2['Rarity']; ?></td>
<td><?php echo $row2['Method']; ?></td>
<?php
} else 
  echo 'None';
?>
</tr>
</table>

So Let's say I have a collection called Toys. This is how it outputs using my above codes:

1st Code example:
__________________________________________
|Photo|Name |Amount|Sells For   |Rarity       |Obtained|
|Pic1  |Marble| x5        | 20 cents | Common | None     |
|Pic2  |Train   | x1       | 5 $          | Rare       |  None    |

2nd Code example:
__________________________________________
|Photo|Name |Amount|Sells For   |Rarity       |Obtained|
|Pic1  |Marble| x5        | 20 cents | Common | Barter   |
|Pic1  |Marble| x5        | 20 cents | Common | Trade    |
|Pic2  |Train   | x1       | 5 $          | Rare       |  Gift      |

How it should output:
__________________________________________
|Photo|Name |Amount|Sells For   |Rarity       |Obtained|
|Pic1  |Marble| x5        | 20 cents | Common | Barter   |
|        |          |             |               |               | Trade    |
|Pic2  |Train   | x1       | 5 $          | Rare       |  Gift      |

As you can see, I want it to output all methods obtained in the same box, increasing the rowspan as necessary.

I don't want it to output None (since this is not true) as in the first example.
I don't want it to repeat the same information simply to add a line for the next method as in the second example.

Can someone help me figure out what I'm doing wrong?

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=308855.0