Display existing results in form

[djs1971]

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>";

 

 

[Pikachu2000]

Is the purpose of this to be able to edit the record? If that's the case, the following applies: <option>" . $scorevalue . "</option> contains the result resource from your query, not a usable value. Also, none of the <option> tags have a value= attribute, so they are pretty much useless. And value= is not a valid attribute for a <select> tag. This should give you a basic idea of how to handle it.

 

If you just want to display the record, there's really no need to use a <select> field at all.

[djs1971]

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>";

 

[Pikachu2000]

Replace everything from echo "<td><select name='score"  . $row['admin'] . "' /> through the ending</td>"; above with this. As long as your $score variable is getting a correct value, this will preselect the value that corresponds to it.

 

echo "<td><select name=\"score{$row['admin']}\">\n";
$scores = array(
   '0' => 0,
   '1' => 1,
   '2' => 2,
   '2.5' => 2.5,
   '3' => 3,
   '3.5' => 3.5,
   '4' => 4,
   );
foreach( $scores as $k => $v ) {
   echo "<option value=\"$k\"";
   if( !empty($score) && $score == $k ) {
      echo ' selected="selected"';
   }
   echo ">$v</option>\n";
}
echo "</select>\n</td>";

 

P.S. This topic is PHP related, so maybe a mod can move it for you.

[djs1971]

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 :-)