PDA

View Full Version : Script Ideas



boothyuk123
10-14-2011, 10:45 PM
What I am actually trying to do is have the following. The ENGLISH courses would be listed in a English section and then the MATH courses would be in a Mathematics section and then if I had say ART and MUSIC courses I could have them listed in a Fine Arts section.

E.g.

English
John Doe ENG 1003
John Doe ENG 1013

Mathematics
John Doe MATH 1023
John Doe MATH 2054

Fine Arts
John Doe ART 3023
John Doe MUS 4206

Do you see what I am trying to do? I am trying to start from the beginning to get this to work? Any suggestions?

Here is the code I have right now. I am thinking I may need to have multiple while loops to get this to work correctly but I can't see to get them functioning correctly:


<?php session_start();
error_reporting(0);

$idCheck=$_POST['primaryID'];

$dkServerConn = mysql_connect("localhost", "dbuser", "dbpassword") or die("no way");
mysql_select_db("registrar", $dkServerConn) or die("Cannot connect to the DB!");
$sql6 = "SELECT * from transferdatafile where cwid='".$idCheck."' order by subj asc, term desc";

$sql7 = "SELECT distinct cwid, lastname, firstname from transferdatafile where cwid='".$idCheck."'";
$sql8 = "SELECT distinct cwid, lastname, firstname from transferdatafile where cwid='".$idCheck."'";
$sql9 = "SELECT distinct cwid, lastname, firstname from transferdatafile where cwid='".$idCheck."'";
$dkResultSet2 = mysql_query($sql6,$dkServerConn) or die(mysql_error());
$dkResultSet4 = mysql_query($sql8,$dkServerConn) or die(mysql_error());
$dkResultSet5 = mysql_query($sql9,$dkServerConn) or die(mysql_error());
$dkResultSet = mysql_query($sql7,$dkServerConn) or die(mysql_error());
?>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Transfer Course Information</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
.style1 {
font-family: Arial, Helvetica, sans-serif;
font-size: 16px;
}
body {
background-color: #333333;font-family:Arial, Helvetica, sans-serif;font-size:16px;
}
-->
</style>
</head>

<body>




<table width='600' cellpadding='5' border="0" align="center" bgcolor="#FFFFFF">
<tr>
<td><a href='http://google.com' target='blank'><img src='6.jpg' border='0'></a>
<p />
<div style='text-align:center;font-weight:bold'>Transfer Course Tracker</div>
<p />


<?php

echo "Name of Student: </span>";

while ($dkROWrecord = mysql_fetch_array($dkResultSet,MYSQL_BOTH))

{



$lastname1=$dkROWrecord['lastname'];



$firstname1=$dkROWrecord['firstname'];
echo "<span style='color:#003300;font-weight:bold'>";
echo $firstname1." ".$lastname1."</span><p />";
$cwid1=$dkROWrecord['cwid'];

}

if ($lastname1=="")
{
echo "<p />There is no information regarding your General Education requirements. ";
die;
}


echo "Student ID Number: <span style='color:#003300;font-weight:bold'>";
echo $cwid1;
echo "</span><p />";
echo "<hr />";

$keepScore36=0;
$keepScore37=0;
echo "<b>Communications segment consists of Composition I (ENG 1003) AND Composition II (ENG 1013)</b><p />";
while ($dkROWrecord2 = mysql_fetch_array($dkResultSet2,MYSQL_BOTH))

{
$countIt=$countIt+1;
$subj=$dkROWrecord2['subj'];
$course=$dkROWrecord2['course'];
$section=$dkROWrecord2['section'];
$crsecode=$dkROWrecord2['crsecode'];
$crsegrade=$dkROWrecord2['crsegrade'];
$term=$dkROWrecord2['term'];
$loc=$dkROWrecord2['loc'];
$misc=$dkROWrecord2['misc'];


// echo "Test: ".$test."<br />";



if ($crsecode =="ENG1013" || "ENG1003")
{


echo "Subject ".$subj." Course: ".$course.": <span style='color:black;font-weight:bold'>".$crsegrade."</span><p />";
echo "You have not yet fulfilled all the requirements for the Communication general education area";
}





}


echo "<b><p />Mathematics segment consists of College Algebra (MATH 1023)</b><p />";
while ($dkROWrecord4 = mysql_fetch_array($dkResultSet4,MYSQL_BOTH))

{

if ($crsecode =="MATH1023")
{


echo "Subject ".$subj." Course: ".$course.": <span style='color:black;font-weight:bold'>".$crsegrade."</span><p />";
echo "You have fulfilled all the requirements for the Communication general education area";
}



}

?>

</td>
</body>
</html>

Thanks

Chris

mlseim
10-14-2011, 10:53 PM
You only have to do one query, to group them by subject.
I don't have the query in my mind right now, but maybe someone
can come up with the correct query for you.

Just so you know, you don't have to do 3 queries, like you're doing now.

gooney0
10-15-2011, 07:43 AM
I'd take a step back and clean up the logic a bit.

Here is how I'd go about this:

query the DB once and save results in an array so that you have something that makes sense. The array keys should be the same as your field names for simpicity.

You want to wind up with something like:

$students[0]['firstname']
$students[0]['lastname']

Now you can process the students and make decisions and add keys with extra data.




foreach($students as $num => $student) {
if ($student['firstname'] == "John" ) {
$students[$num]['isajerk'] = 1;
}
}



When it's time for output we can foreach through the students again. Hopefully our data is laid out well so this code is simple.




foreach($students as $student) {
// Output student info
echo $student['firstname'];

// Is this guy a jerk?
if($student['isajerk']) {
echo " is a jerk. ";
}

echo "<br />";

}




If it helps you can also create additional arrays with prepared data. In this example I could've created an array of jerks. It would then be even easier to output each jerk.

To recap:

Get the data, calculate / change the data, output the data

Even better if you can move code into functions such as get_students. This will also cut down on troubleshooting.

DJCMBear
10-15-2011, 02:29 PM
Ok here is the break down, I have used PDO for connecting to the database but it is easy to convert to MySQL as the query is the same just the '?' will change to the user ID.

Here is the SQL dump


--
-- Table structure for table `college_classes`
--

CREATE TABLE IF NOT EXISTS `college_classes` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Course_ID` int(11) NOT NULL,
`Subject_ID` int(11) NOT NULL,
`Room_ID` int(11) NOT NULL,
`Student_ID` int(11) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `college_classes`
--

INSERT INTO `college_classes` (`ID`, `Course_ID`, `Subject_ID`, `Room_ID`, `Student_ID`) VALUES
(1, 1, 1, 1, 1),
(2, 1, 1, 2, 1),
(3, 2, 2, 3, 1),
(4, 2, 2, 4, 1),
(5, 3, 3, 5, 1),
(6, 3, 4, 6, 1);

-- --------------------------------------------------------

--
-- Table structure for table `college_courses`
--

CREATE TABLE IF NOT EXISTS `college_courses` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `college_courses`
--

INSERT INTO `college_courses` (`ID`, `Name`) VALUES
(1, 'English'),
(2, 'Mathematics'),
(3, 'Fine Art');

-- --------------------------------------------------------

--
-- Table structure for table `college_rooms`
--

CREATE TABLE IF NOT EXISTS `college_rooms` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Number` varchar(10) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `college_rooms`
--

INSERT INTO `college_rooms` (`ID`, `Number`) VALUES
(1, '1003'),
(2, '1013'),
(3, '1023'),
(4, '2054'),
(5, '3023'),
(6, '4206');

-- --------------------------------------------------------

--
-- Table structure for table `college_students`
--

CREATE TABLE IF NOT EXISTS `college_students` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Forename` varchar(50) NOT NULL,
`Surname` varchar(50) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `college_students`
--

INSERT INTO `college_students` (`ID`, `Forename`, `Surname`) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Doe');

-- --------------------------------------------------------

--
-- Table structure for table `college_subjects`
--

CREATE TABLE IF NOT EXISTS `college_subjects` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `college_subjects`
--

INSERT INTO `college_subjects` (`ID`, `Name`) VALUES
(1, 'ENG'),
(2, 'MATH'),
(3, 'ART'),
(4, 'MUS');


After that is in place you can begin the PHP coding


# Setting test user id
$USER_ID = 1;

# Setting 'list' as array
$list = array();

# PDO Connect Here #

# Prepare Query
$SQL = $PDO -> prepare("SELECT `college_courses`.`Name` As `Course`, `college_subjects`.`Name` As `Subject`,
`college_students`.`Forename`, `college_students`.`Surname`,
`college_rooms`.`Number` As `Room`
FROM `college_classes`
JOIN `college_courses` ON `college_classes`.`Course_ID` = `college_courses`.`ID`
JOIN `college_subjects` ON `college_classes`.`Subject_ID` = `college_subjects`.`ID`
JOIN `college_students` ON `college_classes`.`Student_ID` = `college_students`.`ID`
JOIN `college_rooms` ON `college_classes`.`Room_ID` = `college_rooms`.`ID`
WHERE `Student_ID` = ?");

# Execute Query Including User_ID
$SQL -> execute(array($USER_ID));
if ($SQL -> rowCount()) {
while ($row = $SQL -> fetch(PSDO::FETCH_ASSOC)) {
# Below will create a readable array for use in a foreach loop
$Grouper = $row['Course'];
unset($row['Course']);
if (!$list[$Grouper]) $list[$Grouper];
$list[$Grouper][] = array(
"Student" => array("Forename" => $row['Forename'], "Surname" => $row['Surname']),
"Subject" => $row['Subject'],
"Room" => $row['Room']
);
}
}

# Creating the final list using the array from above
foreach ($list As $course => $classes) {
print "<strong>{$course}</strong><br />\n";
foreach ($classes As $class) {
print "{$class['Student']['Forename']} {$class['Student']['Surname']} ".
"{$class['Subject']} {$class['Room']}<br />\n";
}
print "<br />\n";
}


All this will output this: (Plain Text)


English
John Doe ENG 1003
John Doe ENG 1013

Mathematics
John Doe MATH 1023
John Doe MATH 2054

Fine Art
John Doe ART 3023
John Doe MUS 4206


All this will output this: (HTML)


<strong>English</strong><br />
John Doe ENG 1003<br />
John Doe ENG 1013<br />
<br />
<strong>Mathematics</strong><br />
John Doe MATH 1023<br />
John Doe MATH 2054<br />
<br />
<strong>Fine Art</strong><br />
John Doe ART 3023<br />
John Doe MUS 4206<br />
<br />


Let me know how you get on :)

- DJCMBear