Update query problem

[gdp_87]

Hi i am new to php and am trying to sort out the update part of this script.

What should happen is the record is selected using the drop down box, changes are made to the required fields and changes are saved.

What current happens is i edit the record, and then click the save changes button and it then runs through the script successfully and echos the message in the head, but the school_name field is cleared and no changes are saved to the record.

Can anyone see anything obviously wrong?

Thankyou for your help in advance.

gdp

<?php

include'../includes/database.php';

	//print out and populate dropdown box
	$sql="SELECT school_id, name FROM schools";
	$result=mysql_query($sql);

	$options="";

	while ($row=mysql_fetch_array($result)) {

    $id=$row["school_id"];
    $thing=$row["name"];
    $options.="<OPTION VALUE=\"$id\">".$thing;
} 


//show school details
if(isset($_POST['submit']))
			{
			
			//select the school which is selected in the drop down box
			$row=mysql_query("SELECT * FROM schools WHERE school_id ='$_POST[gourl]'")or die('Error: ' . mysql_error());
			$output=mysql_fetch_array($row);
			
			//create editable table for school data 	
			$table= "<p><table width=\"95%\" class=\"stats\">
							<tr>
							 <td class=\"hed\" colspan=\"4\">School Details
							 </td>
							</tr>
							<tr>			
							    <th>School ID:</th>
								<td>$output[school_id]</td>
							</tr>
							<tr>
								<th>School Name:</th>
								<td><input type=\"text\" value=\"$output[name]\" name=\"name\" /></td>
							</tr>
							<tr>
								<th>Address:</th>
								<td><input type=\"text\" value=\"$output[address]\" name=\"address\" /></td>
							</tr>
							<tr>
								<th>City:</th>
								<td><input type=\"text\" value=\"$output[city]\" name=\"city\" /></td>
							</tr>
							<tr>
								<th>County:</th>
								<td><input type=\"text\" value=\"$output[county]\" name=\"county\" /></td>
							</tr>
							<tr>
								<th>Postcode:</th>
								<td><input type=\"text\" value=\"$output[postcode]\" name=\"postcode\" /></td>
							</tr>
							<tr>
								<th>Telephone:</th>
								<td><input type=\"text\" value=\"$output[telephone]\" name=\"telephone\" /></td>
							</tr>
							<tr>
								<th>Fax:</th>
								<td><input type=\"text\" value=\"$output[fax_number]\" name=\"fax_number\" /></td>
							</tr>
							<tr>
								<th>Email:</th>
								<td><input type=\"text\" value=\"$output[email]\" name=\"email\" /></td>
							</tr>
							<tr>
								<th>Contact Name:</th>
								<td><input type=\"text\" value=\"$output[contact_name]\" name=\"contact_name\" /></td>
							</tr>
							<tr>
								<th>Cheque Payable:</th>
								<td><input type=\"text\" value=\"$output[cheque_payable]\" name=\"cheque_payable\" /></td>
							</tr>
							<tr>
								<th>Bank Placed:</th>
								<td><input type=\"radio\" name=\"bank\"  id=\"radio\" value=\"$output[bank]\" />yes
     					       <input type=\"radio\" name=\"bank\" id=\"radio\" value=\"$output[bank]\" />
            					no</td>
							</tr>
							<tr>
								<th>Additional Notes:</th>
								<td><input type=\"text\" value=\"$output[note]\" name=\"note\" /></td>
							</tr>
							<tr>
								<th>Amount Raised:</th>
								<td><input type=\"text\" value=\"$output[amount_raised]\" name=\"amount_raised\" /></td>
							</tr>
							
							
					</table></p>"; 				
					
					//create edit and delete buttons when school is selected
					$edit= "<p><form action=\"search.php\" name=\"edit\" method=\"post\"><input type=\"submit\" name=\"edit\" value=\"Save Changes\"><input type=\"hidden\" value=\"$output[school_id]\" name=\"edit_school\"></form></p>";
					$delete= "<p><form action=\"search.php\" name=\"delete\" method=\"post\"><input type=\"submit\" name=\"delete\" value=\"Delete School\"><input type=\"hidden\" value=\"$output[school_id]\" name=\"delete_school\"> </form></p>";		
}

//update
if(isset($_POST['edit']))
{
 
$row3=("UPDATE schools SET name ='$_POST[name]' WHERE school_id ='$_POST[edit_school]'") or die('Error: ' . mysql_error());;
$query_edit = mysql_query($row3);
 
	if(!$query_edit)
{
  die('Could not edit data: ' . mysql_error());
}
 echo "record edited successfully";
}

//delete
if(isset($_POST['delete']))
{
 
$row2=("DELETE FROM schools WHERE school_id ='$_POST[delete_school]'") or die('Error: ' . mysql_error());;
$query_delete = mysql_query($row2);
 
	if(!$query_delete)
{
  die('Could not delete data: ' . mysql_error());
}

}


?>

HTML where php is located:

<div id="colOne">
    <h2 class="active">Search School</h2>
    <FORM 
     ACTION="search.php" 
     METHOD="post">
      <SELECT NAME="gourl">
        <OPTION VALUE="">Choose a School... <?php echo $options ?>
      </SELECT>
      <INPUT TYPE=SUBMIT VALUE="Go" name="submit">
    </FORM>
    <?php 
	
	echo "<br>";
	//print out table	
	if(isset($table))
	
    {echo $table;
	 echo $edit;
	 echo $delete;
	
	} //show success message if query delete is executed.
	if(isset($query_delete))
		{
		  echo "<br/><br/><span style=\"color:#FF0000\">School Deleted Successfully </span>";
		}	
	?>
    <br />
  </div>

[Atli]

Hey.

I would guess that it is because your fields, notably $_POST['name'] , are not inside the form.

If you do something like:

<input type="text" name="name" />

<form action="?" method="post">
    <input type="hidden" name="id" value="1" />
    <input type="submit" />
</form>

Once you submit, you will only get $_POST['id'] , not $_POST['name'] .

Also, that code is wide open to SQL Injection.
The short version of that is; never ever use any data you get from the outside (via forms or cookies or whatever else), in a MySQL query without verifying it. Easiest way to do that is to run all text through mysql_real_escape_string, and all numbers through intval or floatval.

[gdp_87]

Hi thank you very much for the quick reply.

I will have a go at sorting that tonight.

Many Thanks

gdp

[gdp_87]

Hi Atli,

I already have a hidden ID field on line 96

Am i missing something here?

Thankyou for your patience

gdp

[Atli]

My point there wasn't that there should be a hidden ID field, but that the "name" field should be inside the form as well.

Anything that is outside the <form> ... </form> elements will be ignored when the form is submitted, so if you want the name to go along with the hidden ID, it needs to be inside those elements.

<form action="?" method="post">
    <input type="text" name="name" />
    <input type="hidden" name="id" value="1" />
    <input type="submit" />
</form>

In this code the "name" field will go with the ID. In my other example, and in your code, it won't.