//flex table opened by JP
PDA

Click to See Complete Forum and Search --> : Unique Display MYSQL Data

skyxliner
08-20-2005, 06:22 PM
Background: ok so i would like to collect data from students of their information and also their class scheldules.

Problem: I managed to display everything perfectly. The only problem is displaying the scheldules in a decent viewable way. If anyone knows a better way to display class scheldules please let me know.

http://img374.imageshack.us/img374/7537/current3cs.jpg
^ this is what I have so far that actually displays data from the database

http://img374.imageshack.us/img374/1967/ideal9bi.jpg
^ this is what i'd like it to look like

This is the code that I have of right now
<?PHP
$query = "SELECT * FROM _app ORDER BY id ";
$result = mysql_query($query) or die ("Query failed");
//let's get the number of rows in our result so we can use it in a for loop
$numofrows = mysql_num_rows($result);



?>


<?PHP
echo "<TABLE BORDER=\"1\" cellpadding=\"3\" >\n";

echo "<TR bgcolor=\"lightgreen\"><TD>ID:</TD><TD>IP Address:</TD><TD>Date:</TD><TD>Name:</TD><TD>Birthday:</TD><TD>Class Of:</TD><TD>Shirt Size:</TD><TD>GPA:</TD><TD>Periods:</TD><TD>Subject:</TD><TD>Teacher:</TD><TD>Room:</TD></TR>\n";

for($i = 0; $i < $numofrows; $i++) {

$row = mysql_fetch_array($result); //get a row from our result set

if($i % 2) { //this means if there is a remainder

echo "<TR bgcolor=\"lightblue\">\n";

} else { //if there isn't a remainder we will do the else

echo "<TR bgcolor=\"white\">\n";

}

echo "<TD>".$row['id']."</TD><TD>".$row['ip']."</TD><TD>".$row['date']."</TD><TD>".$row['fname']." ".$row['lname']."</TD><TD>".$row['bday']."</TD><TD>".$row['class']."</TD><TD>".$row['shirt']."</TD><TD>".$row['gpa']."</TD><TD>".$row['sdfsfd']."</TD></Tr>\n";

echo "</TR>\n";
}

//now let's close the table and be done with it
echo "</TABLE>\n";
?>

I'm having problem displaying it so that each ID person is displaying their full scheldule routinely one after another
ScaryBinary
08-21-2005, 07:03 PM
I'm not quite sure if you're just having trouble with the HTML required to generate your desired table, of if you also need help with the PHP.

Here's some HTML code for possible table layouts. Keep in mind that most of this would be generated dynamically by your PHP, but I've shown two sample students.

<html>
<head>
<title>Untitled</title>
</head>
<body>
<h1>Single Table Using Rowspan</h1>
<p>This method uses a single table, and alters the table cells using rowspans.</p>
<table border="1">
<!-- First pass through outer loop. -->
<tr>
<td rowspan="3">Student 1</td>
<td>Period 1</td>
</tr>
<tr>
<td>Period 2</td>
</tr>
<tr>
<td>Period 3</td>
</tr>

<!-- Second pass through outer loop. -->
<tr>
<td rowspan="3">Student 2</td>
<td>Period 1</td>
</tr>
<tr>
<td>Period 2</td>
</tr>
<tr>
<td>Period 3</td>
</tr>

</table>

<h1>Nested Tables</h1>
<p>This method uses two tables, one nested in the other, and lends itself more
to dynamic creation with two PHP loops.</p>
<table border="1">
<!-- First pass through outer loop. -->
<tr>
<td>Student 1</td>
<td>
<table border="1">
<!-- Inner loop fills in periods. -->
<tr>
<td>Period 1</td>
</tr>
<tr>
<td>Period 2</td>
</tr>
<tr>
<td>Period 2</td>
</tr>
</table>
</td>
</tr>

<!-- Second pass through outer loop. -->
<tr>
<td>Student 2</td>
<td>
<table border="1">
<!-- Inner loop fills in periods. -->
<tr>
<td>Period 1</td>
</tr>
<tr>
<td>Period 2</td>
</tr>
<tr>
<td>Period 2</td>
</tr>
</table>
</td>
</tr>

</table>
</body>
</html>

Depending on how you've set up the MySQL tables, you might be able to grab all the info you need (student info plus their class info) in one query. Or, you can run a query to get the student info, and loop through that - then for each student, you'd run a query to get the class information. So you'd have an outer loop (to get each student) and an inner loop (to get each student's class info).

Sorry this is so general...let us know in more detail what you're having trouble with and we can help. I have minimal PHP skills (I could crank out some ugly code if my life depended on it :p ) but there are a few folks here who are pretty good at it.
skyxliner
08-21-2005, 10:28 PM
I have a pretty good knowledge on html but i'm more unfamiliar with mysql and php.

my database is setup as in subject1, teacher1, room1, subject2, teacher2, room2 and this follows for each person

I couldn't find how to set these up to display for each person and have it loop properly
ScaryBinary
08-21-2005, 11:34 PM
Well, I'd start with your table structure in MySQL first. Basically, you have three "groups" of information. You have all your students, and their personal info like shirt size and name. You have all your classes and their info like the teacher and room. And then you have to have something that tells you which classes each student is enrolled in. So, you might have the three tables described below.

Table: Students
studentID (primary key)
firstName
lastName
shirtSize


Table: Classes
classID
period
teacher
subject


Table: Enrollment
enrollID
classID
studentID


This last table is going to keep track of which students are enrolled in which classes by associating classID's with studentID's. Some example data might be:Students
+-----------+-----------+----------+-----------+
| studentID | firstName | lastName | shirtSize |
+-----------+-----------+----------+-----------+
| 1 | Bill | Gates | S |
+-----------+-----------+----------+-----------+
| 2 | George | Bush | M |
+-----------+-----------+----------+-----------+

Classes
+---------+--------+------------+-------------+
| classID | period | teacher | subject |
+---------+--------+------------+-------------+
| 1 | 1 | Prof. Plum | Astronomy |
+---------+--------+------------+-------------+
| 2 | 1 | Dr. Dufus | Government |
+---------+--------+------------+-------------+
| 3 | 2 | Prof. Klutz| Phys Ed. |
+---------+--------+------------+-------------+

Enrollment
+----------+---------+-----------+
| enrollID | classID | studentID |
+----------+---------+-----------+
| 1 | 1 | 1 |
+----------+---------+-----------+
| 2 | 2 | 2 |
+----------+---------+-----------+
| 3 | 3 | 1 |
+----------+---------+-----------+
| 4 | 3 | 2 |
+----------+---------+-----------+
This data indicates that Bill has Astronomy first period, and George has Government first period, while both have Phys Ed for second period.

Now, to pull out that data back out and display it in some meaningful fashion is the next step. In PHP, you'll have a query to find all the students. This will be your "outer loop" query. You'll step through those records, and for each record (row) you find, you'll run a second query to find all the classes that student is in. This code will look something like the following (you read my disclaimer about my crappy PHP skills above, right?):

$sSQL = "SELECT * FROM Students ORDER BY lastName;" ;
$hndStudents = mysql_query($strSQL) ;

/* Start drawing our main table. */
echo "<TABLE BORDER=\"1\" cellpadding=\"3\" >\n";

/* Step through the students until there aren't any left. */
while ($aStudent = mysql_fetch_assoc( $hndStudents ) )
{
/* Start the HTML block for this student. Display student's personal info.*/
echo "<TR><TD>" ;
echo $aStudent['firstName'] . $aStudent['lastName'] ;
echo "</TD>" ;

/* Here's where your "inner loop" to find the classes starts. We'll just draw */
/* another table to keep it simple. We're trying to get information on all the */
/* classes this student is enrolled in. Sort the results by period. */
$sSQL = "SELECT * FROM Classes INNER JOIN Enrollment WHERE studentID = " .
$aStudent['studentID'] . " ORDER BY period;" ;
$hndClasses = mysql_query($strSQL) ;

/* Start our nested table for the classes. */
echo "<table>" ;

/* Now step through the classes that were returned for this student. */
while ($aClass = mysql_fetch_assoc($hndClasses))
{
/* Display the periods in a nested table. */
echo "<tr><td>" . $aClass['period'] . "</td>" ;
echo "<td>" . $aClass['subject'] . "</td>" ;
echo "<td>" . $aClass['teacher'] . "</td>" ;
echo "</tr>"
}

/* We've stepped through all the classes now. Close this nested table. */
echo "</table>" ;

/* We're also done processing information for this student. Close the table row. */
echo "</td></tr>" ;
}

/* Now we're done processessing all students. Close the main table. */

echo "</table>" ;

....that's the general idea, at any rate. I have to admit that I have not tested this code in any way....I don't have the time right now, but I could probably get it hammered out in a few days. You can give it a shot if you want, or maybe CompGeek01 can debug this for me..... :p

Also, the FAQ has some links to PHP sites. The actual PHP documentation has some pretty good stuff on the MySQL interface.
skyxliner
08-22-2005, 01:35 AM
thanks for taking your time to explain that, i'll try this later tonight and let you know if it works out....

I was actually planning on trying out having two seperate arrays or something for the general data and the scheldules.

but i'll give yours a try also, the only thing I might find difficult is having to deal with 3 tables but i guess that makes it cleaner appeal
skyxliner
08-22-2005, 05:25 AM
I don't think having 3 tables will work out,

my data acquistion is basiclly from a form where people fill out their teachers and subjects and rooms themselves and is stored.

it seems like you are preseting the subjects and the teachers? sorry cause i am really confused right now
skyxliner
08-22-2005, 05:36 AM
sorry for posting so many seperate posts but i keep thinking of things

i don't quite understnad the enrollment table and how it works... i understand how your calling the data out, but how would you store it properly because it seems like its more complicated in linking data...

it seems like you are sharing class informations between students, but i'm fine with it being slightly repetive inhaving each student displaying their own teachers and rooms... ack dunnos
ScaryBinary
08-22-2005, 09:32 PM
This is going to be a lenghty post. :)

First, some database theory babbling.

You can set it up however it makes sense to you. Both ways (a single-table approach versus a multiple-table approach) have their advantages. For larger databases, the multiple-table apprach is pretty much required. For smaller projects, the simplicity of a single-table approach often outways the "neatness" and complexity of a multiple-table approach.

I typically tend to keep my data as "compartmentalized" as possible, as I did above. This lets me define the relationships between the data in multiple ways. For instance, any single student will be in a number of classes. And typically every class will have a number of students. For this reason, it made sense to me to keep all the student data in one table, and all the class data in another table, so I can reuse it without having to worry about redundant data. Then the third table, the Enrollment table, lists which students are in which classes. In this manner, I define each class only once - if the teacher ever changes, for instance, I only have to update the information in one row in the Classes table. You are right, though, this adds another level of complexity to the database. Any queries I make must join in the tables to "recombine" the data into something meaningful.

The other advantage to this approach is it reduces the number of fields (columns) we have to deal with. If we plop all of the data in a single table then you have to pre-define the number of "periods" each student can attend, because you have to have several columns for each period. In your example, you have 8 student fields, plus 3 fields for each of 8 periods, so your single table probably has 8 + 8 * 3 = 32 fields. My approach would have 8 student fields, 4 class fields, and 3 fields to indicate which students are in which class, for a total of 15 fields. Of course, that doesn't really mean too much, but my philosophy is that I can always perform JOINS and such to combine all the data back together - that's the beauty of SQL.

Let's pose this question, as an example. Suppose Mr. Martin teaches Algebra Periods 1 and 3, and Physics for Period 7. He has 20 kids in each of these classes. If we put all of our data in one table, that means we have 60 rows of students, some with Mr. Martin listed in the "teacher1" column, some with him listed in the "teacher3" column, and some with him listed in the "teacher7" column. Now let's suppose Mr. Martin quits because he's fed up with his crummy salary. Mrs. Jenkins takes his place. How would up update all those records to change "Mr. Martin" to "Mrs. Jenkins"? With my table layout, we could do it with the query "UPDATE Classes SET teacher='Mrs. Jenkins' WHERE teacher = 'Mr. Martin';" It will be a little more interesting with a single-table layout, but still achievable, I think.

Now, some user interface babble.

You could get your application to work even with multiple tables. You'll just have to split your queries up when you update your database with the user's form data. For instance, you'd run a query to add the user's personal info (shirt size, IP address, etc.) to the Students table. Then you'd add the class info to the Classes table. The last thing would be to update the Enrollment table linking the user to the classes he just entered.

I see your point how this can get complex pretty fast, especially since you're letting the users enter all the class information, such as the teacher and room. How do you prevent users from mispelling teacher names, or entering the wrong room number? If you know all of this information ahead of time, you should use drop-down SELECT boxes to provvide this info - that will ensure that user input errors such as those I mentioned above will be minimized. If you don't know these values ahead of time, then you'll just have to deal with what every software developer who ever lived has had to deal with: the End User. :p

From what it sounds like, the single-table approach is probably the easiest approach for your project. If you give me a little more time, I think I could work out some code to pull data from your single table. It's not going to be too much different from your original code, if you combine it with the example I posted.
ScaryBinary
08-22-2005, 09:45 PM
I think if you modify the for loop you had in your first post to something like the following, you'll be close....
for($i = 0; $i < $numofrows; $i++) {

$row = mysql_fetch_array($result); //get a row from our result set

if($i % 2) { //this means if there is a remainder

echo "<TR bgcolor=\"lightblue\">\n";

} else { //if there isn't a remainder we will do the else

echo "<TR bgcolor=\"white\">\n";

}

/* Start off with the basic student info. Not really different from
* what you originally had, except we'll leave the row incomplete for now.
*/
echo "<TD>" . $row['id'] ."</TD><TD>" . $row['ip'] .
"</TD><TD>" . $row['date'] . "</TD><TD>" . $row['fname']. " .
$row['lname'] . "</TD><TD>" . $row['bday'] ."</TD><TD>" .
$row['class'] . "</TD><TD>" . $row['shirt'] . "</TD><TD>" . $row['gpa'] .
"</TD><TD>" . $row['sdfsfd'] . " </TD>" ;

/* Now we can display the Class info. We'll use a nested table for now.
* You can try something else if you want. Here's where the single-table
* design becomes a pain. I've written the code for a single period. You'll
* have to cut and paste this an additional seven times to get all your
* class information for 8 periods, since you have to
* iterate through all your "teacherX", "subjectX", "roomX", etc. fields.
* Don't forget to change the number in the field name to match the Period.
*/
echo "<table>" ;

/* Here's the stuff you'll have to copy for each period. */
echo "<tr>" ;
echo "<td>" . $row['subject1'] . "</td>" ;
echo "<td>" . $row['teacher1'] . "</td>" ;
echo "<td>" . $row['room1'] . "</td>" ;
echo "</tr>" ;
/* End of period data.

/* Now end this inner "period" table. */
echo "</table>\n" ;

/* Now end our row, which is all the info for a single student. */
echo "</TR>\n";
}

//now let's close the table and be done with it
echo "</TABLE>\n";
skyxliner
08-23-2005, 05:00 AM
thank you so much for taking your time with my problem. I hope I am not making it seem like i'm asking you to do all the work.. I'm really trying to learn php and mysql... i just got tons of books yesterday to keep learning cause internet tutorials weren't really doing so well..

and as related to the question, i think for this project, I'm going to have to go with the single database and user submitted entries because 1) theres simply too many teachers to use a drop down 2) I don't know the periods in which these teachers will be teaching ahead of time.

i'll give the code a try and i'll let you know thanks
skyxliner
08-23-2005, 05:56 AM
i tried the code, the good part is that it displays the scheldule for one person, the bad part is that the table is within the outside table
skyxliner
08-23-2005, 06:02 AM
OMG IT WORKS!!! THANK YOU SOO MUCH ScaryBinary for taking your time out to write out your long but constructive analogies =D and helping me out with the coding

your minimum php skills have proved superior =D hahaha

its kind of funny because, the code is basiclly exactly the same as what i had but just inserting a table haha... but thanks for point that out for me... The code you gave me didn't exactly wrap inside the outer table but I modified it and it works PERFECT now. extracts data perfectly, just the way i wanted it haha i'm like over joyed right now. Thank you so much

I'm posting the actual code in case anyone in this forum ever wants to use it for learning experience cause it was one for me haha. stressed over this for nearly a week even though its simple =P


<?PHP
//selecting queries
$query = "SELECT * FROM _app ORDER BY id ";
$result = mysql_query($query) or die ("Query failed");
//let's get the number of rows in our result so we can use it in a for loop
$numofrows = mysql_num_rows($result);



?>


<?PHP
echo "<TABLE BORDER=\"1\" cellpadding=\"3\" >\n";

//creating top labels of the display table
// bgcolor is the top label background color
echo "<TR bgcolor=\"lightgreen\"><TD>ID:</TD><TD>IP Address:</TD><TD>Date:</TD><TD>Name:</TD><TD>Birthday:</TD><TD>Class Of:</TD><TD>Shirt Size:</TD><TD>GPA:</TD><TD>Periods:</TD><TD>Subject:</TD><TD>Teacher:</TD><TD>Room:</TD></TR>\n";

/* what this does is provide an alternating color rows
* I found this on some online tutorial so credit to them.
*/

for($i = 0; $i < $numofrows; $i++) {

$row = mysql_fetch_array($result); //get a row from our result set

if($i % 2) { //this means if there is a remainder

//color choice 1 to alternate
echo "<TR bgcolor=\"lightblue\">\n";

} else { //if there isn't a remainder we will do the else

//color choice 2 to alternate
echo "<TR bgcolor=\"white\">\n";

}

//Start off with the basic student info. Pulling normal data from the database in rows
echo "<TD>" . $row['id'] ."</TD><TD>" . $row['ip'] .
"</TD><TD>" . $row['date'] . "</TD><TD>" . $row['fname'] . " ". $row['lname'] . "</TD><TD>" . $row['bday'] ."</TD><TD>" .
$row['class'] . "</TD><TD>" . $row['shirt'] . "</TD><TD>" . $row['gpa'] .
"</TD>" ;

/* Now we can display the Class info. We'll use a nested table for now.
* You can try something else if you want. Here's where the single-table
* design becomes a pain. I've written the code for a single period. You'll
* have to cut and paste this an additional seven times to get all your
* class information for 8 periods, since you have to
* iterate through all your "teacherX", "subjectX", "roomX", etc. fields.
* Don't forget to change the number in the field name to match the Period.
*/

echo "<TD><table border=\"0\">" ;

/* Here's the stuff you'll have to copy for each period. */
echo "<TR><TD>1</TD></TR>" ;
echo "<TR><TD>2</TD></TR>" ;
echo "<TR><TD>3</TD></TR>" ;
echo "<TR><TD>4</TD></TR>" ;
echo "<TR><TD>5</TD></TR>" ;
echo "<TR><TD>6</TD></TR>" ;
echo "<TR><TD>7</TD></TR>" ;
echo "<TR><TD>8</TD></TR>" ;

/* End of period data.

/* Now end this inner "period" table. */
echo "</table>\n" ;

echo "<TD><table border=\"0\">" ;

/* Here's the stuff you'll have to copy for each subject. */
echo "<TR><TD>" . $row['subject1'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['subject2'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['subject3'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['subject4'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['subject5'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['subject6'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['subject7'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['subject8'] . "</TD></TR>" ;

/* End of subject data.

/* Now end this inner "subject" table. */
echo "</table>\n" ;

echo "<TD><table border=\"0\">" ;

/* Here's the stuff you'll have to copy for each teacher. */
echo "<TR><TD>" . $row['teacher1'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['teacher2'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['teacher3'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['teacher4'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['teacher5'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['teacher6'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['teacher7'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['teacher8'] . "</TD></TR>" ;


/* End of teacher data.

/* Now end this inner "teacher" table. */
echo "</table>\n" ;

echo "<TD><table border=\"0\">" ;

/* Here's the stuff you'll have to copy for each room. */
echo "<TR><TD>" . $row['room1'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['room2'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['room3'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['room4'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['room5'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['room6'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['room7'] . "</TD></TR>" ;
echo "<TR><TD>" . $row['room8'] . "</TD></TR>" ;

/* End of room data.

/* Now end this inner "room" table. */
echo "</table>\n" ;

/* Now end our row, which is all the info for a single student. */
echo "</TD></TR>\n";
}

//now let's close the table and be done with it
echo "</TABLE>\n";
?>

I even added some comments cause i heard thats what all good php coders do =P

the best thing about this is that i was still able to keep the script that gave alternating colors on each row

http://img377.imageshack.us/img377/9646/untitled7ac.jpg

once again thanks so much ScaryBinary :)
ScaryBinary
08-23-2005, 11:45 PM
Cool. Glad my crummy PHP skills spawned some ideas! :t