djs1971

Please excuse what may be a simple question from a relative newbie! I have a table in my database which has the following fields/data types id - integer admin - integer date - date am - varchar pm - archer I am trying to work out what sort of MySQL query I could use to display the attendance marks for a complete week on each row of the table. I can currently achieve something close but by using a separate table for each day of the week and having the 5 tables displayed side by side - but I am sure there must be a way of having it displayed in a single table, just can't figure it out! Attachedis a screenshot of what I can achieve currently: Hopefully someone would be kind enough to point me in the right direction. The code I am currently to return data for single day is as follows: $student_attendance_monday_query = "SELECT am, pm FROM attendance_15_16 WHERE DATE_FORMAT(date,'%W')='Monday' AND admin = '" . $admin . "' "; $student_attendance_monday_results = mysqli_query($link, $student_attendance_monday_query); while ($row = mysqli_fetch_array($student_attendance_monday_results)) { $mon_am_new = $row['am']; $mon_pm_new = $row['pm']; echo "<tr> <td>" . $mon_am_new . "</td> <td>" . $mon_pm_new . "</td>"; };

Hi again MMDE, Quick update: I've got my head around arrays a little more thanks to your reply. I have set up the following: $grade_number=array(3=>"1a",2=>"1b",1=>"1c",6=>"2a",5=>"2b",4=>"2c",9=>"3a",8=>"3b",7=>"3c",12=>"4a",11=>"4b",10=>"4c",15=>"5a",14=>"5b",13=>"5c",18=>"6a",17=>"6b",16=>"6c",21=>"7a",20=>"7b",19=>"7c",25=>"X",0=>"N",2000=>"-",2010=>""); and then used: $sum8_value1=array_search($row['Sum8'],$grade_number); To grab the correct value. It has massively trimmed down my code as I can use the single array for each of the 3 columns per year whereas before I had a massive if, elseif list block of code for every column! In the process it seems to have solved the problem I had with the one column (Sum8) having the incorrect background colour to the cells. I'm now going to work trimming down further code based on this new bit of knowledge. Thanks again :-)

Hi MMDE, Thanks for the reply - as I said at the top of my post, I'm a real newbie to PHP & MYSQL so aware I'm likely to be using weird methods and odd code! As for the $row - yep I'm using a database. For a bit of context - the system is storing a grade (1a, 2a, etc etc) for a set of 63 students - 3 times per year, for 5 years (Year 7 to Year 11) I'm using some maths to work out if their grade is below, on or above target based on a start point and target grade and set a background colour to the table cell to indicate this. This screenshot hopefully shows what I mean and what the issue is in terms of the random column. I'll read through your suggestions and then do some google research to see if I can apply it. Doesn't make total sense at the moment but sure it will after a bit of reading up. Thanks again for taking the time to reply.

Hi folks, relative newbie so my code wont be pretty, however... This one has me stumped. I have written some PHP to change the background colour of a cell in a table based on a variable. The code is as follows: // Convert Summer 8 Grade to a number value if ($row['Sum8']=='1a') { $sum8_value=3; } elseif ($row['Sum8']=='1b') { $sum8_value=2; } elseif ($row['Sum8']=='1c') { $sum8_value=1; } elseif ($row['Sum8']=='2a') { $sum8_value=6; } elseif ($row['Sum8']=='2b') { $sum8_value=5; } elseif ($row['Sum8']=='2c') { $sum8_value=4; } elseif ($row['Sum8']=='3a') { $sum8_value=9; } elseif ($row['Sum8']=='3b') { $sum8_value=8; } elseif ($row['Sum8']=='3c') { $sum8_value=7; } elseif ($row['Sum8']=='4a') { $sum8_value=12; } elseif ($row['Sum8']=='4b') { $sum8_value=11; } elseif ($row['Sum8']=='4c') { $sum8_value=10; } elseif ($row['Sum8']=='5a') { $sum8_value=15; } elseif ($row['Sum8']=='5b') { $sum8_value=14; } elseif ($row['Sum8']=='5c') { $sum8_value=13; } elseif ($row['Sum8']=='6a') { $sum8_value=18; } elseif ($row['Sum8']=='6b') { $sum8_value=17; } elseif ($row['Sum8']=='6c') { $sum8_value=16; } elseif ($row['Sum8']=='7a') { $sum8_value=21; } elseif ($row['Sum8']=='7b') { $sum8_value=20; } elseif ($row['Sum8']=='7c') { $sum8_value=19; } elseif ($row['Sum8']=='N') { $sum8_value=0; } elseif ($row['Sum8']=='X') { $sum8_value=25; } elseif ($row['Sum8']=='B') { $sum8_value=5; } elseif ($row['Sum8']=='-') { $sum8_value=150; } // Calculate progress for Summer 8 $sum8_progress = number_format(($ks3_target_value-$ks2_value)*0.75)+$ks2_value; $sum8_progress_final = $sum8_value-$sum8_progress; if ($sum8_progress_final< -1) { $progress_colour_sum8="#ac3845";} elseif ($sum8_progress_final==-1) { $progress_colour_sum8="#c5a751";} elseif ($sum8_progress_final==0) { $progress_colour_sum8="#93a336";} elseif ($sum8_progress_final>= 1 && $sum8_progress_final<= 34) { $progress_colour_sum8="#477dae";} elseif ($sum8_progress_final>= 35) { $progress_colour_sum8=$row_color; } If you can follow my code, the problem is that currently $sum8 = '-' for every entry in the table and therefore working everything through the cell should end up being $row_color which is basically grey. However it is showing up as #477dae This code works on every other column in my table (the only difference being sum8 is spr8, aut8, sum7, spr7 etc etc (for autumn, spring, summer year 7, 8, 9 etc etc. It is only the sum8 based column that does not work. I have echo'd the results to check and find the following: $sum8_progress_final is in the 100's for every result $progress_colour_sum8 however is #477dae for every result? Any suggestions or thoughts?

Genius - at first that didn't work - which highlighted to me that I wasn't getting a correct value for $score - stupid newbie error in that in my db table the field for 'lesson' wasn't long enough so was storing the first 11 characters of 'before school' making it 'before scho' which was causing the problem of never showing any existing scores. Thanks so much for the help - really appreciated! Also to the Mod who moved the post to the correct forum area :-)

Many thanks for the help so far! Very much appreciated. The idea is to create new/update existing records. I've made some alteration - think I've tidied up two of the points you made - what I'm unclear on is how to display a score as the default if it exists in the first <option></option> tag. Any ideas on this? (Apologies for such a newbie question!) Here's the updated code: <form action="savescores.php" method="get" name="sampleform"> <input type="hidden" name="tutor" value="<?php echo $_GET["tutor"]; ?>" /> <input type="hidden" name="date" value="<?php echo $_GET["date"]; ?>" /> <input type="hidden" name="lesson" value="<?php echo $_GET["lesson"]; ?>" /> <?php error_reporting(-1); $con = mysql_connect("localhost","root",""); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("lakeside", $con); $result = mysql_query("SELECT students.admin, students.fname, students.surname, students.tutor FROM students WHERE tutor='$_GET[tutor]' ORDER BY students.tutor, students.surname"); echo "<table class='scores'> <tr> <th>Admin</th> <th>Firstname</th> <th>Surname</th> <th>Tutor</th> <th>Score</th> <th>Code</th> <th>Comment</th> </tr>"; while($row = mysql_fetch_array($result)) { $sqlstatement = "SELECT * FROM scores where admin = '" . $row['admin'] . "' and lesson = '" . $_GET[lesson] . "' and date = '" . $_GET[date] . "' "; $studentsvalue = mysql_query($sqlstatement); while($rowvalue = mysql_fetch_array($studentsvalue)) { $score = $rowvalue['score']; $comment = $rowvalue['comment']; $code = $rowvalue['code']; } echo "<tr>"; echo "<td>" . $row['admin'] . "</td>"; echo "<td>" . $row['fname'] . "</td>"; echo "<td>" . $row['surname'] . "</td>"; echo "<td>" . $row['tutor'] . "</td>"; echo "<td><select name='score" . $row['admin'] . "' /> <option></option> <option value=0>0</option> <option value=1>1</option> <option value=2>2</option> <option value=2.5>2.5</option> <option value=3>3</option> <option value=3.5>3.5</option> <option>4</option> </select> </td>";

EDIT: sorry - new to posting guidelines - using XAMPP with mysql 5.1.44 (I think this is correct!!) Newbie question here: I have successfully created a form that that displays students (based on a selected lesson/group/date) with the ability to enter scores. I'm now trying to set the form up so that if data already exists then it is displayed in the form. Eg: if a member of staff choses group 7AS, lesson 1, 24/09/2010 and another member of staff has entered a score already for one student it shows up in the <select> drop down by using <option>" . $scorevalue . "</option> I had this working at one point but have made a change somewhere and cannot get it working again! can anyone spot an obvious mistake I am making? The code I am using is as follows: <input type="hidden" name="tutor" value="<?php echo $_GET["tutor"]; ?>" /><input type="hidden" name="date" value="<?php echo $_GET["date"]; ?>" /><input type="hidden" name="lesson" value="<?php echo $_GET["lesson"]; ?>" /><?phperror_reporting(-1);$con = mysql_connect("localhost","root","");if (!$con) { die('Could not connect: ' . mysql_error()); }mysql_select_db("lakeside", $con);$result = mysql_query("SELECT students.admin, students.fname, students.surname, students.tutorFROM studentsWHERE tutor='$_GET[tutor]' ORDER BY students.tutor, students.surname");echo "<table class='scores'><tr><th>Admin</th><th>Firstname</th><th>Surname</th><th>Tutor</th><th>Score</th><th>Code</th><th>Comment</th></tr>";while($row = mysql_fetch_array($result)) { $sqlstatement = "SELECT * FROM scores where admin = '" . $row['admin'] . "' and lesson = '" . $_GET[lesson] . "' and date = '" . $_GET[date] . "' ";$scorevalue = mysql_query($sqlstatement); echo " . $score . ";while($rowvalue = mysql_fetch_array($scorevalue)){$score = $rowvalue['score'];$comment = $rowvalue['comment'];$code = $rowvalue['code'];} echo "<tr>"; echo "<td>" . $row['admin'] . "</td>"; echo "<td>" . $row['fname'] . "</td>"; echo "<td>" . $row['surname'] . "</td>"; echo "<td>" . $row['tutor'] . "</td>"; echo "<td><select name='score" . $row['admin'] . "' value='{$row['score']}' /> <option>" . $scorevalue . "</option> <option>0</option> <option>1</option> <option>2</option> <option>2.5</option> <option >3</option> <option>3.5</option> <option>4</option> </select> </td>";

Complete newbie - have battled with this all day and going nowhere - completely stumped so throwing myself on your mercy!! I have a simple form which has a select drop down populated from my sql database. When I hit submit nothing seems to get passed to the hidden field in the next page. My code for the form is as follows: <form action="selectday.php" method="get"> <select> <?php //connect to MySQL $connect = mysql_connect("localhost","root","") or die ("Could not connect to database."); //choose the database mysql_select_db("lakeside"); //Query from database $sql="SELECT year_id, year FROM years"; $result=mysql_query($sql); $options=""; while ($row=mysql_fetch_array($result)) { $year_id=$row["year_id"]; $year=$row["year"]; $options.="<OPTION VALUE=\"$year_id\">".$year.'</option>'; } ?> <SELECT NAME=year_id> <OPTION VALUE=0>Choose Year Group <?=$options?> </SELECT> <input type="submit" /> </select> </form> Drop down works fine but does not seem to pass any data across. I believe by using GET I should see the data passed in the url?? Any advice would be great!