Updating values, not working

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ashrafzia
Forum Commoner
Posts: 37
Joined: Wed Sep 28, 2005 12:23 pm

Updating values, not working

Post by ashrafzia »

I am updating values, manually(using mysql from cmd) its working fine but on the web its not working.
I have a table in which student_id is primary key, and i want to update its value.

Heres the code:

$sql = " UPDATE $tbname SET
student_id = '$_POST[std_id]'
WHERE student_id = '$_POST[std_id]' ";

value is echoing, i have checked it, but not working in the query.
Any idea ???
User avatar
gregwhitworth
Forum Commoner
Posts: 53
Joined: Tue Oct 09, 2007 1:00 am
Location: Wasilla, Alaska

Post by gregwhitworth »

I know that this is the meat of your code, but could we see more of it or any error messages (PHP or MYSQL)?

Thanks.

--
Greg
ashrafzia
Forum Commoner
Posts: 37
Joined: Wed Sep 28, 2005 12:23 pm

Post by ashrafzia »

gregwhitworth wrote:I know that this is the meat of your code, but could we see more of it or any error messages (PHP or MYSQL)?
Thanks.
--
Greg
THere's no Error message and Heres the full code :

Code: Select all

if (isset($_GET['action'])){
	$action = $_GET['action'];
	$id = $_GET['id'];

	if ($action=="edit"){
		$sql = "SELECT * From $tbname WHERE student_id=$id";
		$result = @mysql_query($sql, $conn) or die (mysql_error());
		
		while ($row = mysql_fetch_array($result)){
		$id = $row['student_id'];
		$reg = $row['registration_no'];
		$name = $row['student_name'];
		$fname = $row['father_name'];
		$prog = $row['programe'];
		$pic = $row['picture'];
		
		$form = "<body>
				<p>&nbsp;</p>
				<p>&nbsp;</p>
				<form action='allstudents.php' method='post' enctype='multipart/form-data'>
			  	<table width='394' border='0' align='center' cellpadding='5' cellspacing='5'>
			    <tr>
			      <td width='135'>Studnet ID:</td>
			      <td width='218'><input name='std_id' type='text' size='5' value=$id></td>
			    </tr>
			    <tr>
			      <td>Registration No: </td>
			      <td><input name='registration_no' type='text' size='5' value=$reg></td>
    			</tr>
			    <tr>
			      <td>Student Name: </td>
			      <td><input name='student_name' type='text' value=$name></td>
			    </tr>
			    <tr>
			      <td>Father Name:</td>
			      <td><input name='father_name' type='text' value=$fname></td>
			    </tr>
			    <tr>
			      <td>Programe:</td>
			      <td><select name='programe'>
			        <option value='BBA'>BBA</option>
			        <option value='BBA-IT'>BBA-IT</option>
			        <option value='BCS'>BCS</option>
				      </select></td>
			    </tr>
			    <tr>
			      <td>Picture:</td>
			      <td><input name='picture' type='file' name='picture'></td>
			    </tr>
			    <tr>
			      <td colspan='2'><div align='center'><strong>
			        <input type='image' name='update' src='images/update.gif'>
			      </strong></div></td>
			    </tr>
			  </table>
			</form>
			</body>";
						
			echo "$form";
		}
	}
}

if (isset($_POST['update_x'])){

/*
$sql = " UPDATE $tbname SET 
		 student_id = '$_POST[std_id]',
		 registration_no = '$_POST[registration_no]',
		 student_name = '$_POST[student_name]',
		 father_name = '$_POST[father_name]',
		 programe = '$_POST[programe]'
		 WHERE student_id = '$_POST[std_id]'
		 		 
		  ";
*/
$id = "$_POST[std_id]";
echo "$id";

$sql = " UPDATE $tbname SET 
		 student_id = $id
		 WHERE student_id = '$_POST[std_id]'
		 		 
		  ";


		  
$result = mysql_query ($sql, $conn) or die (mysql_error());
echo "Record Updated Successufully....";
echo "$_POST[std_id]";

}


?>
Problem is all other fields are updating but only the Student_id isn't updating......
I have checked and echoed the value, its printing but not working in the query...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Echo the last SQL statement that you say is not working and post the exact SQL that the database is seeing.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

Updating the value of a primary key is hazardous to the integrity of your data. Ordinarily, you shouldn't do that. What version of MySQL are you using, and what data engine are you using (MyISAM or INNODB)?
Last edited by califdon on Fri Oct 19, 2007 2:32 pm, edited 1 time in total.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Re: Updating values, not working

Post by Oren »

ashrafzia wrote:$sql = " UPDATE $tbname SET
student_id = '$_POST[std_id]'
WHERE student_id = '$_POST[std_id]' ";
What the...?!?!

You have a query of the form:

Code: Select all

UPDATE tbl SET my_field = 'my_val' WHERE my_field = 'my_val';
Is there any good reason on earth to do that, or am I missing something here? :? :?: :? :?:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

There is also the issue of unfiltered data, but I was saving that for after we see what the poster is really doing and how the SQL query looks to the database.
ashrafzia
Forum Commoner
Posts: 37
Joined: Wed Sep 28, 2005 12:23 pm

Post by ashrafzia »

THankyou Gurus! for all your support.
Actually i was having a logical flaw.

What actually i was trying to do is this :
I have the following table, with some records:

student_id | reg_id | std_name | father_name | picture etc......
1 reg_1 abc zxc
2 reg_2 abc zxc
3 reg_3 abc zxc
40 reg_40 abc zxc
5 reg_5 abc zxc

Now suppose if an admin wants to change the student_id of that student which has 40 as the student_id.
He will write certain query in mysql:
UPDATE student_info SET student_id = 4 WHERE student_id = 40;
It works fine in mysql.

I was trying to do the same thing but on web.
My mistake was: I was not getting the current value of the student_id to put inside the WHERE clause.
I simply stored the current value of student_id in a hidden form value by $_GET method.
When the form is submitted the New Value of student_id goes to the query and the previous actual value goes inside the Where Clause.

Its working Cooool!
Here's the Code:

Code: Select all

if (isset($_GET['action'])){
	$action = $_GET['action'];
	$id_value = $_GET['id'];

	if ($action=="edit"){
	
		//echo "$id_value";
	
		$sql = "SELECT * From $tbname WHERE student_id=$id";
		$result = @mysql_query($sql, $conn) or die (mysql_error());
		
		while ($row = mysql_fetch_array($result)){
		$id = $row['student_id'];
		$reg = $row['registration_no'];
		$name = $row['student_name'];
		$fname = $row['father_name'];
		$prog = $row['programe'];
		$sem = $row['semester'];
		$pic = $row['picture'];
				
		$form = "<body>
				<p>&nbsp;</p>
				<p>&nbsp;</p>
				<form action='allstudents.php' method='post' enctype='multipart/form-data'>
				<input type='hidden' value=$id_value name='h1'>
			  	<table width='394' border='0' align='center' cellpadding='5' cellspacing='5'>
			    <tr>
			      <td width='135'>Studnet ID:</td>
			      <td width='218'><input name='std_id' type='text' size='5' value=$id></td>
			    </tr>
			    <tr>
			      <td>Registration No: </td>
			      <td><input name='registration_no' type='text' size='5' value=$reg></td>
    			</tr>
			    <tr>
			      <td>Student Name: </td>
			      <td><input name='student_name' type='text' value=$name></td>
			    </tr>
			    <tr>
			      <td>Father Name:</td>
			      <td><input name='father_name' type='text' value=$fname></td>
			    </tr>
			    <tr>
			      <td>Programe:</td>
			      <td><select name='programe'>
			        <option value='BBA'>BBA</option>
			        <option value='BBA-IT'>BBA-IT</option>
			        <option value='BCS'>BCS</option>
				      </select></td>
			    </tr>
				<tr>
				  <td>Semester:</td>
				  <td><select name='semester'>
			        <option value='1st'>1st</option>
			        <option value='2nd'>2nd</option>
			        <option value='3rd'>3rd</option>
					<option value='4th'>4th</option>
			        <option value='5th'>5th</option>
			        <option value='6th'>6th</option>
					<option value='7th'>7th</option>
			        <option value='8th'>8th</option>
			      </select></td>
			  </tr>
			    <tr>
			      <td>Picture:</td>
			      <td><input name='picture' type='file' name='picture'></td>
			    </tr>
			    <tr>
			      <td colspan='2'><div align='center'><strong>
			        <input type='image' name='update' src='images/update.gif'>
			      </strong></div></td>
			    </tr>
			  </table>
			</form>
			</body>";
					
			echo "$form";
		}
	}
}

if (isset($_POST['update_x'])){
$id_value = $_POST['h1'];

$sql = " UPDATE $tbname SET 
		 student_id = '$_POST[std_id]',
		 registration_no = '$_POST[registration_no]',
		 student_name = '$_POST[student_name]',
		 father_name = '$_POST[father_name]',
		 programe = '$_POST[programe]',
		 semester = '$_POST[semester]'
		 WHERE student_id = $id_value
		 		 
		  ";
	  
$result = mysql_query ($sql, $conn) or die (mysql_error());
echo "Record Updated Successufully....";
}
?>
User avatar
gregwhitworth
Forum Commoner
Posts: 53
Joined: Tue Oct 09, 2007 1:00 am
Location: Wasilla, Alaska

Post by gregwhitworth »

That's it wrap this one up, another day in the life of a php...cough, cough - not sure?

--
Greg
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Ok, now that the SQL is working for you, have you considered the following:
  • Why are you changing ID value of the student? Record ID should not be changed like this.
  • You have no SQL Injection protection all on your code.
  • You have not input validation on your code.
  • Your only error catching is done via die().
  • Your error messages are database error messages, which you want to not show to the user.
ashrafzia
Forum Commoner
Posts: 37
Joined: Wed Sep 28, 2005 12:23 pm

Post by ashrafzia »

Everah wrote:Ok, now that the SQL is working for you, have you considered the following:
  • Why are you changing ID value of the student? Record ID should not be changed like this.
  • You have no SQL Injection protection all on your code.
  • You have not input validation on your code.
  • Your only error catching is done via die().
  • Your error messages are database error messages, which you want to not show to the user.
Wow! these are really very good points you have mentioned but i am a newbie and still learning.

How can i put ON SQL Injection protection on my code ?
By input validation do you mean? checks like, if someone enters integer value in the student_name field etc...??
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

ashrafzia wrote:How can i put ON SQL Injection protection on my code ?
As a starting point:

1) Google
2) mysql_real_escape_string()
ashrafzia wrote:By input validation do you mean? checks like, if someone enters integer value in the student_name field etc...??
Yes, something like that... you are on the right direction :wink:
Post Reply