Trying to perfomr update on data selected by mysql drop down list

[kdillon2]

Hi,

I am trying to allow a user to select data from a mysql table and then update that data. I need two selection criteria for - date and student_id, but I can's seem to get either to display the data after I perform the selection.

My code is below and I the web page shows the list of students and the date, but when this is selected the Select query doesn't show the data related to that drop down selection.

<?php
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
  if (isset($_POST['submit'])) {
  // Grab the profile data from the POST
	$student_id = mysqli_real_escape_string($dbc, trim($_POST['student_id']));
	$first_name = mysqli_real_escape_string($dbc, trim($_POST['first_name']));
	$last_name = mysqli_real_escape_string($dbc, trim($_POST['last_name']));
	$date = mysqli_real_escape_string($dbc, trim($_POST['date']));
	$break = mysqli_real_escape_string($dbc, trim($_POST['break']));
    $m_tea = mysqli_real_escape_string($dbc, trim($_POST['m_tea']));
	$lunch = mysqli_real_escape_string($dbc, trim($_POST['lunch']));
	$a_tea = mysqli_real_escape_string($dbc, trim($_POST['a_tea']));
	$la_tea = mysqli_real_escape_string($dbc, trim($_POST['la_tea']));
	$dinner = mysqli_real_escape_string($dbc, trim($_POST['dinner']));
	$error = false;

       // Update the profile data in the database
if (!$error) {
if (!empty ($date)){
$query = "UPDATE eating SET break = '$_POST[break]', m_tea = '$_POST[m_tea]', lunch = '$_POST[lunch]', a_tea = '$_POST[a_tea]',".	
 " la_tea = '$_POST[la_tea]', dinner = '$_POST[dinner]' WHERE date = '" . $_POST['date'] . "'AND student_id ='" . $_POST['student_id'] . "'";
 }
$data = mysqli_query($dbc, $query)
or die("Error: ".mysqli_error($dbc));



// Confirm success with the user
    echo '<p>Your daily programming sheet has been successfully updated.</p>';

    mysqli_close($dbc);
    exit();

}

 } // End of check for form submission
  else {
    // Grab the profile data from the database
    $query = "SELECT st.trainer_id, ea.student_id, ea.date, ea.break, ea.m_tea, ea.lunch, ea.a_tea, ea.la_tea, ea.dinner, st.first_name, st.last_name".
	" AS last_name FROM student AS st INNER JOIN eating AS ea USING (student_id) WHERE st.trainer_id = '" . $_SESSION['trainer_id'] . "' AND ea.date = '{$nt[date]}'";
    $data = mysqli_query($dbc, $query);
	 $row = mysqli_fetch_array($data);



if ($row != NULL) {
  $student_id = $row['student_id'];
  $first_name = $row['first_name'];
  $last_name = $row['last_name'];
  $date = $row['date'];
  $break = $row['break'];
  $m_tea = $row['m_tea'];
  $lunch = $row['lunch'];
  $a_tea = $row['a_tea'];
  $la_tea = $row['la_tea'];
  $dinner = $row['dinner'];
         }
else {
  echo '<p class="error">There was a problem accessing your profile.</p>';
}






  }

  echo "<select name='date'>";

// printing the list box select command

while($nt=mysqli_fetch_array($data)){//Array or records stored in $nt
echo "<option value='$nt[date]'>$nt[first_name] $nt[last_name] $nt[date]</option>";
/* Option values are added by looping through the array */
}

echo '</select><br />';// Closing of list box 

echo '<form method="post" action="' . $_SERVER['PHP_SELF'] . '">';
 echo '<fieldset><legend>DAILY MATTERS</legend>';





?>
<input type="text" id="studentid" name="studentid" value="<?php if (!empty($student_id)) echo $student_id; ?>" /><br />
 <label for="firstname">First name:</label>
      <input type="text" id="firstname" name="firstname" value="<?php if (!empty($first_name)) echo $first_name; ?>" /><br />
      <label for="lastname">Last name:</label>
      <input type="text" id="lastname" name="lastname" value="<?php if (!empty($last_name)) echo $last_name; ?>" /><br />
  <label for="date">Date:</label>
<input type="text" id="date" name="date" value="<?php if (!empty($date)) echo $date; else echo 'YYYY-MM-DD'; ?>" /><br />
  <label for="break">Breakfast:</label>
 <select id="break" name="break">
	<option value="" <?php if (!empty($break) &&$break == 'N') echo 'selected = "selected"'; ?>></option>
	<option value="W" <?php if (!empty($break) &&$break == 'W') echo 'selected = "selected"'; ?>>Ate Well</option>
	<option value="L" <?php if (!empty($break) &&$break == 'L') echo 'selected = "selected"'; ?>>Ate Little</option>
</select><br />
<label for="m_tea">Morning Tea:</label>
 <select id="m_tea" name="m_tea">
	<option value="W" <?php if (!empty($m_tea) &&$m_tea == 'W') echo 'selected = "selected"'; ?>>Ate Well</option>
	<option value="L" <?php if (!empty($m_tea) &&$m_tea == 'L') echo 'selected = "selected"'; ?>>Ate Little</option>
	<option value="N" <?php if (!empty($m_tea) &&$m_tea == 'N') echo 'selected = "selected"'; ?>>Did Not Eat</option>	
</select><br />	
<label for="lunch">Lunch:</label>
 <select id="lunch" name="lunch">
	<option value="W" <?php if (!empty($lunch) &&$lunch == 'W') echo 'selected = "selected"'; ?>>Ate Well</option>
	<option value="L" <?php if (!empty($lunch) &&$lunch == 'L') echo 'selected = "selected"'; ?>>Ate Little</option>
	<option value="N" <?php if (!empty($lunch) &&$lunch == 'N') echo 'selected = "selected"'; ?>>Did Not Eat</option>	
</select><br />	
<label for="a_tea">Afternoon Tea:</label>
 <select id="a_tea" name="a_tea">
	<option value="W" <?php if (!empty($a_tea) &&$a_tea == 'W') echo 'selected = "selected"'; ?>>Ate Well</option>
	<option value="L" <?php if (!empty($a_tea) &&$a_tea == 'L') echo 'selected = "selected"'; ?>>Ate Little</option>
	<option value="N" <?php if (!empty($a_tea) &&$a_tea == 'N') echo 'selected = "selected"'; ?>>Did Not Eat</option>	
</select><br />	
<label for="la_tea">Late Afternoon Tea:</label>
 <select id="la_tea" name="la_tea">
	<option value="W" <?php if (!empty($la_tea) &&$la_tea == 'W') echo 'selected = "selected"'; ?>>Ate Well</option>
	<option value="L" <?php if (!empty($la_tea) &&$la_tea == 'L') echo 'selected = "selected"'; ?>>Ate Little</option>
	<option value="N" <?php if (!empty($la_tea) &&$la_tea == 'N') echo 'selected = "selected"'; ?>>Did Not Eat</option>	
</select><br />	
<label for="dinner">Dinner:</label>
 <select id="dinner" name="dinner">
	<option value="W" <?php if (!empty($dinner) &&$dinner == 'W') echo 'selected = "selected"'; ?>>Ate Well</option>
	<option value="L" <?php if (!empty($dinner) &&$dinner == 'L') echo 'selected = "selected"'; ?>>Ate Little</option>
	<option value="N" <?php if (!empty($dinner) &&$dinner == 'N') echo 'selected = "selected"'; ?>>Did Not Eat</option>	
</select><br />
      </fieldset>
    <input type="submit" value="Save Profile" name="submit" />
  </form>
  <?php
   mysqli_close($dbc);
   ?>

[Ashley_Sheridan]

You have a problem with your query string. You've gone to all the effort of creating database-safe variables and then you're trying to use the raw $_POST values directly in your query, but not even in the right way.

When you use double quotes, PHP will use variable values where you use them in your string. However, PHP is lazy in doing this, so stops at the square brackets. If you echo out your query, you'll see stuff like:

'UPDATE eating SET break = 'Array[break]', m_tea ='

which is probably where your problem is. Use the DB-safe variables you created and all will work.

In future, if you need to use array elements in a string like this, wrap the variable in curly braces like so:

$sql = "UPDATE tablename SET field1='{$safe['field1']}'";

One last thing to note, you shouldn't try to access array elements like this:

$array[element];

but you should use quotes to identify the element:

$array['element'];

Otherwise you'll fill your logs with tons of warnings, and your code may produce unexpected results.

[johanafm]

Ashley Sheridan;10982721 wrote:

If you echo out your query, you'll see stuff like:

'UPDATE eating SET break = 'Array[break]', m_tea ='

In future, if you need to use array elements in a string like this, wrap the variable in curly braces like so:

$sql = "UPDATE tablename SET field1='{$safe['field1']}'";

One last thing to note, you shouldn't try to access array elements like this:

$array[element];

Not necessarily true. Usually, $array[element] will indeed issue a warning, since element is a non-defined constant for which PHP issues a warning and then assumes should take the value 'element'.

However, in string interpolation, you can use either "{$array['element']}" or "$array[element]"

$_POST['break'] = '09:00';
$query = "UPDATE eating SET break = '$_POST[break]'";

echo $query;

which outputs

UPDATE eating SET break = '09:00'

which is indeed a perfectly valid SQL query (allthough you'd most likely want WHERE id=...).

As for the initial post. If you want help, I suggest you wrap php code in php tags, html code in html tags and other code in code tags, and also indendt it properly. I only took a quick glance at it, since it's too much of an effort to read unformatted code, but you should always need to check if a query is successful or not. Do this first, and you may just find your problem yourself.

From the php manual at [man]mysqli_query[/man]

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a result object. For other successful queries mysqli_query() will return TRUE.

Your code

$data = mysqli_query($dbc, $query);
$row = mysqli_fetch_array($data);

which means $data is either a result set or boolean false. If it's false, mysqli_fetch_array produces an error since [man]mysqli_fetch_array[/man]

Procedural style
mixed mysqli_fetch_array ( mysqli_result $result [, int $resulttype = MYSQLI_BOTH ] )

does not take a boolean value as its first argument. What you should do

if (!$data = mysqli_query(...))
{
	/* trigger_error will "create" a PHP error/warning which is dealt with like any
	 * other PHP error. If php.ini is set to display them, it will be displayed.
	 * If php.ini is set to log them, they will be logged. In a production
	 * environment, they should be logged, not displayed. In a development
	 * environment, they should be logged and/or displayed, whatever you prefer
	 */

# So, use one of ...
trigger_error();
error_log();
# or other handling if possible. Perhaps you can recover from the error?
# perhaps you need to inform the user that they couldn't save data etc

/* If the error was critical, such as a failed login atttempt,
 * exit/die may be what you want, but you never ever want
* to die('some kind of internal error message');
* since that gives a potential hacker/griefer information about your system that
* they should not have. Something like
* die('An error occurred. If the problem persists, contact ...');
* would be far better.
* And personally, I prefer showing a normal page, with navigation, styling etc
* but by replacing any content with an error message
*/
}