Can't Update Database

[miss_indie]

Aww. I'm so sorry. I've been reading topics posted here about updating database using form. but i can't seem to find my error.

I want to update my database using HTML forms. where in after i click a button "Edit Record" A page containing forms will appear where i can update using input type as text. then after will click the button "Done Editing" and a message Successful will appear

here is my code for update: this will just print the "Successful" but then the database isn't updating. o.o

<?php
$a=$_POST['e'];
$b=$_POST['f'];

$con = mysql_connect("localhost","*****","****");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("*****", $con);

mysql_query("UPDATE Sample SET Fname='$a' AND Lname='$b'");

echo "Successful";

mysql_close($con);
?>

I'm a total noob. i'm self studying about this topic. thank you so much.

[hielo]

...SET Fname='$a' AND Lname='$b' you need to get rid of AND. To update multiple fields, you need to separate them with a comma, NOT with the keyword AND. Try the following:

<?php
if( isset($_POST) && !empty($_POST) )
{

	mysql_connect("localhost","*****","****") or die('Could not connect:<br />' . mysql_error());
	mysql_select_db("*****", $con) or die( 'Unable to select db:<br />' . mysql_error() );

	$a=mysql_real_escape_string($_POST['e']);
	$b=mysql_real_escape_string($_POST['f']);

	$sql="UPDATE `Sample` SET `Fname`='$a',`Lname`='$b'";

	mysql_query($sql) or die('Unable to execute query: <br />'.$sql.'<br />'.mysql_error()) ;

	echo "Successful";

	mysql_close($con);
}
else
{
	echo "No data was posted!";
}
?>

[miss_indie]

...SET Fname='$a' AND Lname='$b' you need to get rid of AND. To update multiple fields, you need to separate them with a comma, NOT with the keyword AND. Try the following:

<?php
if( isset($_POST) && !empty($_POST) )
{

	mysql_connect("localhost","*****","****") or die('Could not connect:<br />' . mysql_error());
	mysql_select_db("*****", $con) or die( 'Unable to select db:<br />' . mysql_error() );

	$a=mysql_real_escape_string($_POST['e']);
	$b=mysql_real_escape_string($_POST['f']);

	$sql="UPDATE `Sample` SET `Fname`='$a',`Lname`='$b'";

	mysql_query($sql) or die('Unable to execute query: <br />'.$sql.'<br />'.mysql_error()) ;

	echo "Successful";

	mysql_close($con);
}
else
{
	echo "No data was posted!";
}
?>

Thank you so much for your reply. hmm. tried your code and when i update the "No data was posted" appeared . then when i looked at my database it's empty.

here is my Edit this record.php code:

<?php
$r=$_POST['d'];
$con = mysql_connect("localhost","******","******");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("******", $con);

$result = mysql_query("SELECT * FROM Sample WHERE Lname='$r'");

while($row = mysql_fetch_array($result))
  {
  echo "Record <br />";
  echo "First Name: <input type='text' name='e' value="  . $row['Fname'] . ">"  .  "<br />";
  echo "Last Name: <input type='text' name='f' value="  . $row['Lname'] . ">" .  "<br />";
  }
echo "</table>";

mysql_close($con);
?>

<br />
<form action="editme1.php" method="POST">
<input type="Submit" Value="Done Editing">
</form>

Then i used your code in editme1.php but then it's empty database. aww.

[Borzoi]

You put the wrong type of single quotes on the line:

$sql="UPDATE `Sample` SET `Fname`='$a',`Lname`='$b'";

Here's the correction:

$sql="UPDATE 'Sample' SET 'Fname'='$a','Lname'='$b'";

You shouldn't need the quotes though so this should work:

$sql="UPDATE Sample SET Fname='$a',Lname='$b'";

You cannot update the database if there is no information in the database in the first place though!

If you're just trying to insert the data into the table, this is what you should be using:

$sql = "INSERT INTO Sample (Fname, Lname) VALUES ('$a', '$b')"

[miss_indie]

You put the wrong type of single quotes on the line:

$sql="UPDATE `Sample` SET `Fname`='$a',`Lname`='$b'";

Here's the correction:

$sql="UPDATE 'Sample' SET 'Fname'='$a','Lname'='$b'";

You shouldn't need the quotes though so this should work:

$sql="UPDATE Sample SET Fname='$a',Lname='$b'";

You cannot update the database if there is no information in the database in the first place though!

If you're just trying to insert the data into the table, this is what you should be using:

$sql = "INSERT INTO Sample (Fname, Lname) VALUES ('$a', '$b')"

aww. thank you so much for your reply. hmm. I have data in my database. i wanted to update for example the last name. I've tried your codes but still when i click the button "Done Updating" my database was empty. there's no data in it. can i ask whut's wrong with it? aww. i'm so sorry.

[miss_indie]

The flow of my system is like this. I will enter a student number. for example 01 then click the button "update"

the form code is this:

<form action="editme.php" method="POST">
Enter Student number:  <input type="text" name="z">
<input type="submit" value="Edit record">
</form>

then the editme.php looks like this:

<?php
$r=$_POST['z'];
$con = mysql_connect("localhost","-----","-----");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("-----", $con);
 
$result = mysql_query("SELECT * FROM Sample WHERE Studid='$r'");
 
while($row = mysql_fetch_array($result))
  {
  echo "Record <br />";
  echo "First Name: <input type='text' name='e' value="  . $row['Fname'] . ">"  .  "<br />";
  echo "Last Name: <input type='text' name='f' value="  . $row['Lname'] . ">" .  "<br />";
  }
echo "</table>";
 
mysql_close($con);
?>
 
<br />
<form action="editme1.php" method="POST">
<input type="Submit" Value="Done Editing">
</form>

then after clicking the Done EDITING button.

the editme1.php looks like this:

<?php
$a=$_POST['e'];
$b=$_POST['f'];

 
$con = mysql_connect("localhost","----","----");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("----", $con);
 
$query="UPDATE Sample SET Fname='$a', Lname='$b'";
mysql_query($query) or die('mysql error ' . mysql_error() . ' in query : ' . $query);
 
echo "Successful";
 
mysql_close($con);
?>

Sorry for posting all my codes. i really don't know where's my error. it's my defense tomorrow and my system just lack of this update thingy. i'm so sorry.

[Borzoi]

With your update, it's updating all the entries as you haven't specified a WHERE in it.

I would suggest changing the form on editme.php to this:

while($row = mysql_fetch_array($result))
  {
  echo "Record <br />";
  echo "Studen id: <input type='text' name='s' disabled='disabled' value="  . $row['Studid'] . ">"  . "<br />";
  echo "First Name: <input type='text' name='e' value="  . $row['Fname'] . ">"  .  "<br />";
  echo "Last Name: <input type='text' name='f' value="  . $row['Lname'] . ">" .  "<br />";
  };

This way you could use it to make sure you only modify the right row in editme1.php like this:

<?php
$a=$_POST['e'];
$b=$_POST['f'];
$c=$_POST['s'];
 
$con = mysql_connect("localhost","----","----");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("----", $con);
 
$query="UPDATE Sample SET Fname='$a', Lname='$b' WHERE Studid='$c'";
mysql_query($query) or die('mysql error ' . mysql_error() . ' in query : ' . $query);
 
echo "Successful";
 
mysql_close($con);
?>

That should stop it completely emptying the database. Let me know if it still causes the line you're trying to edit to be empty.

[miss_indie]

With your update, it's updating all the entries as you haven't specified a WHERE in it.

I would suggest changing the form on editme.php to this:

while($row = mysql_fetch_array($result))
  {
  echo "Record <br />";
  echo "Studen id: <input type='text' name='s' disabled='disabled' value="  . $row['Studid'] . ">"  . "<br />";
  echo "First Name: <input type='text' name='e' value="  . $row['Fname'] . ">"  .  "<br />";
  echo "Last Name: <input type='text' name='f' value="  . $row['Lname'] . ">" .  "<br />";
  };

This way you could use it to make sure you only modify the right row in editme1.php like this:

<?php
$a=$_POST['e'];
$b=$_POST['f'];
$c=$_POST['s'];
 
$con = mysql_connect("localhost","----","----");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("----", $con);
 
$query="UPDATE Sample SET Fname='$a', Lname='$b' WHERE Studid='$c'";
mysql_query($query) or die('mysql error ' . mysql_error() . ' in query : ' . $query);
 
echo "Successful";
 
mysql_close($con);
?>

That should stop it completely emptying the database. Let me know if it still causes the line you're trying to edit to be empty.

OMAYGAAAD! your codes worked well. Omg. you're so great thankyou so much! I really appreciate it. somehow i like the idea of student id being disabled.

[miss_indie]

With your update, it's updating all the entries as you haven't specified a WHERE in it.

I would suggest changing the form on editme.php to this:

while($row = mysql_fetch_array($result))
  {
  echo "Record <br />";
  echo "Studen id: <input type='text' name='s' disabled='disabled' value="  . $row['Studid'] . ">"  . "<br />";
  echo "First Name: <input type='text' name='e' value="  . $row['Fname'] . ">"  .  "<br />";
  echo "Last Name: <input type='text' name='f' value="  . $row['Lname'] . ">" .  "<br />";
  };

This way you could use it to make sure you only modify the right row in editme1.php like this:

<?php
$a=$_POST['e'];
$b=$_POST['f'];
$c=$_POST['s'];
 
$con = mysql_connect("localhost","----","----");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("----", $con);
 
$query="UPDATE Sample SET Fname='$a', Lname='$b' WHERE Studid='$c'";
mysql_query($query) or die('mysql error ' . mysql_error() . ' in query : ' . $query);
 
echo "Successful";
 
mysql_close($con);
?>

That should stop it completely emptying the database. Let me know if it still causes the line you're trying to edit to be empty.

Really. Thank you so much for your help! :)

[Borzoi]

You're very welcome.

[hielo]

You put the wrong type of single quotes on the line:
Here's the correction:
      $sql="UPDATE 'Sample' SET 'Fname'='$a','Lname'='$b'";

WRONG! databases, tables names and field names may NOT be enclosed in apostrophes in MySQL. You need to use backticks. The problem was that the FORM tag was NOT around the fields she was editing!