Ritika
-
Posts
5 -
Joined
-
Last visited
Ritika's Achievements
Newbie (1/5)
0
Reputation
-
Pivot Table or Cross Tab in PHP using MYSQL for attendance
Ritika replied to akshayhomkar's topic in PHP Coding Help
will you please give me example please... it will be grate help for me -
Pivot Table or Cross Tab in PHP using MYSQL for attendance
Ritika replied to akshayhomkar's topic in PHP Coding Help
<!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Q2220229 - Pivot table</title> <style> td { border-bottom: 1px solid grey; width: 10em; } </style> </head> <body> <?php // I am getting output like /* * Date |NAME | Roll_no | ATTEND ======================================== 01/02/14 |Musician | 1 | 1 01/02/14 |Leader | 2 | 1 01/02/14 |Singer | 3 | 0 08/02/14 |Musician | 4 | 0 08/02/14 |Leader | 5 | 1 08/02/14 |Singer | 6 | 1 * */ // DESIRED OUTPUT /* NEEDED sample output: * * Roll_no | NAME |01/02/14 |08/02/14 =============================== 1 |Musician | 0 | 1 2 |Leader | 1 | 1 3 | Singer | 1 | 0 */ $db = mysql_connect('localhost', 'test', 'test', 'testmysql'); // 1) Must return three columns only. // 2) Can return any number of 'roles' - one per row // 3) Any date range but beware you may need a wide page! // 4) Must sort by date! $query = mysql_query( "SELECT date, attend, name FROM atten ORDER BY date ASC, name ASC"); // i prefer to used named subscripts to make the code easier to read. // These MUST match up with column alias from the above query! define('THE_DATE', 'date'); // !important define('name', 'name'); // !imortant define('attend', 'attend'); // !important /* * Now, we need a complete array of Roles in the order that they are to be displayed. * * These names must match with the names of the roles in the input data. * They will be printed out in the order that they appear in the array. * * These are the only roles that will appear in the $outputDates array. * Add more and in any order to control which 'roles' are shown. * */ $allRoles = array('student1', 'student5', 'student6', 'student2' ); // !important /* * At some point we will need an output array that we can easily traverse and * print out as a row of dates. i.e. a 'page' of data. * * We will build it up as we go along... */ $outputDates = array(); // !important -- this is the 'pivoted' output array /* * Start to process the input data. * * To make my life easier, i will use the 'read ahead' technique to simplify the code. */ $currentInputRow = mysql_fetch_array($query); while (isset($currentInputRow[THE_DATE])) { // process all the input array... // must be a new day... $currentDay = $currentInputRow[THE_DATE]; // create an array to hold ALL the possible roles for this day... $theDayRoles = array(); // initialise the array with default values for all the requested roles. foreach ($allRoles as $name) { $theDayRoles[$name] = '--'; } // now we need to fill theDayRoles with what we actually have for the current day... while ($currentInputRow[THE_DATE] == $currentDay) { // loop around all records for the current day // set the appropiate DayRole to the current ATTEND $theDayRoles[$currentInputRow[name]] = $currentInputRow[attend]; // read the next input row - may be current day, new day or no more $currentInputRow = mysql_fetch_array($query); } // end of day on the input for whatever reason... /* we now have: * 1) Current Date * * 2) an array of members for ALL the roles on that day. * * We need to output it to another array ($outputDates) where we can print it out * by scanning the array line by line later. * * I will 'pivot' the array and produce an output array we can scan sequentially later. */ // to ensure that we are updating the correct $outputDates row i will use a subscript $currentOutputRowIdx = 0; // first add the current date to the output... $outputDates[$currentOutputRowIdx][] = $currentDay; $currentOutputRowIdx++; // next output row // we need to drive off the '$allRoles' array to add the name data in the correct order foreach ($allRoles as $outRole) { $outputDates[$currentOutputRowIdx][] = $theDayRoles[$outRole]; $currentOutputRowIdx++; // next output row } } // end of all the input data /* * Now we just need to print the outputDates array one row at a time... */ // need the roles as the first column... // so we need an index for which one we are currently printing $currentRoleIdx = -1; // increment each time but allow for the first row being the title 'Roles' echo '<table>'; foreach ($outputDates as $oneOutputRow) { echo '<tr>'; // this is the first column... if ($currentRoleIdx < 0) { echo '<td>'. 'NAME' .'</td>'; } else { echo '<td>'. $allRoles[$currentRoleIdx] .'</td>'; } // now output the day info foreach($oneOutputRow as $column) { echo '<td>'. $column .'</td>'; } echo '</tr>'; $currentRoleIdx++; // next output name to show... } echo '</table>'; ?> </body> </html> Thanks Barand sir for quick reply.. Sir here is my code for same problem but i have a issue please guide me little in line no: 66 how can i take dynamic array.. Thank you in advance please help me out -
Pivot Table or Cross Tab in PHP using MYSQL for attendance
Ritika replied to akshayhomkar's topic in PHP Coding Help
i do not know why but I am getting ERROR: Parse error: syntax error, unexpected '$sql' (T_VARIABLE) in C:\xampp\htdocs\att\creating_column\final3.php on line 3. Its in select query statement.. please help thanks -
I want to do it with PHP .. thanks
-
PLEASE help................ // I am getting output like /* * Date |NAME | Roll_no | ATTEND ======================================== 01/02/14 |Musician | 1 | 1 01/02/14 |Leader | 2 | 1 01/02/14 |Singer | 3 | 0 08/02/14 |Musician | 4 | 0 08/02/14 |Leader | 5 | 1 08/02/14 |Singer | 6 | 1 * */ /* NEEDED sample output: * * Roll_no | NAME |01/02/14 |08/02/14 =============================== 1 |Musician | 0 | 1 2 |Leader | 1 | 1 3 | Singer | 1 | 0 */ here is my code -- { <?php include('config.php'); $sql = ("SELECT roll_no,attend,date from atten ") ; $q1 = mysql_query($sql) or die("could not search!"); echo "<table border='1'>"; echo "<tr> <td>roll_no </td> <td> attendance</td> <td> Date </td> </tr>" ; while ($row=mysql_fetch_array($q1)) { echo "<tr> <td>". $row['roll_no'] ." </td> <td>". $row['attend'] ." </td> <td>". $row['date'] ." </td> </tr>"; } echo "</table>"; ?> }