Can't update record

klinmy · February 14, 2009

I've been trying it for the whole day, but i just can't figure what's the problem with the code..

It's a form to record the city name, where the table (City) contains only 2 fields which are ID (auto_increment) and CityName.

Below is the code for the form. There'r no problem on 'search', 'save', and 'delete', but the conditions for the functions have to be set as, for example $query="DELETE FROM City where CityName='$CityName' rather than $query="DELETE FROM City where ID='$ID'

So there's where the "update" function comes.

if(isset($_POST['ID']))
{
$ID=0;
}

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


$CityName=$_POST["CityName"];


$errors=0;


if($errors==0)
{			


	$query="INSERT INTO city" . "(  CityName) VALUES ('$CityName')";
	$result=mysql_query($query);
	check_mysql();
	$message = "<script>"
	    		." alert('Record Saved');"

				."</script>";
	$CityName="";
	$ID=mysql_insert_id();
}
}

elseif(isset($_POST['search']))
{

$ID=0;
$CityName=$_POST["CityName"];


$query5="SELECT  CityName FROM City ". "WHERE CityName= '$CityName' AND ID > $ID";
$result5=mysql_query($query5);
check_mysql();
$row5=mysql_fetch_row($result5);
check_mysql();

if($row5>0)
{

	$CityName=$row5[0];


	$message = "<script>"
	    		." alert('Found $IDfound');"

				."</script>";
				$IDfound='$IDfound';
}
else
{
$message = "<script>"
	    		." alert('No Record Found');"

				."</script>";
	//$CityName="";

}
}
elseif(isset($_POST['update']))
{
$query="UPDATE City SET CityName='$CityName'" . "WHERE ID=$ID";
$result=mysql_query($query);
check_mysql();
$message="<script>"
	    		." alert('Record Update');"

				."</script>"; 

}

elseif(isset($_POST['delete']))
{
$CityName=$_POST["CityName"];


$error1=0;


	if($error1==0)
	{

		$query="DELETE FROM City where CityName='$CityName'" ;
		$result=mysql_query($query);
		check_mysql();



		$message="<script>"
	    		." alert('Record deleted');"

				."</script>"; 
	$CityName="";
	}



}

thanks in advance. I tried looking at other codes too, but still can't able to find the solution for it..

blueman378 · February 14, 2009

well dude here:

if(isset($_POST['ID']))

{

$ID=0;

}

your saying if someone has entered a value into the url then make it zero

i think you want

if(!isset($_POST['ID']))

{

$ID=0;

}

klinmy · February 14, 2009

Thanks blueman378, my mistake. But after changing it, it shows this error when i click the update button.

"MySQL error1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

Any possible answer for this? Dun find anything wrong with the update function.

Thanks.

klinmy · February 14, 2009

Tried this as well, yet the record is not updated.

elseif(isset($_POST['update']))

{

$error=0;

$ID=$_POST["ID"];

if($error==0)

{

$query="UPDATE City SET CityName='$CityName'" . "WHERE ID=$ID";

$result=mysql_query($query);

check_mysql();

$message="<script>"

." alert('Updated');"

."</script>";

}

What seems to be wrong?

blueman378 · February 14, 2009

see thats where ordie comes in, after all your mysql queries put or die(mysql_error());

(after your query but before the ;

then try running it and it will tell you whats wrong

klinmy · February 14, 2009

thanks blueman378, but it shows the same error after changed it to

$result=mysql_query($query) or die(mysql_error());

"You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

<html>
<head>
<title>City</title>

</head>

<body>

<form name="frm" form action="" method="post" >
<?PHP

function check_mysql()
{
if(mysql_errno()>0)
{
	die("MySQL error". mysql_errno() . ":" . mysql_error());
}
}

$db=mysql_connect("localhost","root","");
if(!$db)
{
die("Failed to open connection to MySQL server.");
}

mysql_select_db("TPS");
check_mysql();

mysql_select_db("TPS");
check_mysql();

if(!isset($_POST['ID']))
{
$ID=0;
}

if(isset($_POST['save']))
{  	
$CityName=$_POST["CityName"];	
$errors=0;	
if($errors==0)
{				
	$query="INSERT INTO city" . "(  CityName) VALUES ('$CityName')";
	$result=mysql_query($query);
	check_mysql();
			$ID=mysql_insert_id();
}
}

elseif(isset($_POST['update']))
{
$error=0;	
	if($error==0)
	{					

$query="UPDATE City SET CityName='$CityName'" . "WHERE ID=$ID ";
$result=mysql_query($query) or die(mysql_error());

$message="<script>"
	    		." alert('Record Updated');"

				."</script>"; 

}	
}
?>
</div>

<div id="Layer1" style="position:absolute; left:238px; top:105px; width:662px; height:131px; z-index:4">
  <p>City Name : 
    <input type="text" name="CityName" <?php echo "VALUE=\"$CityName\""?> >
  </p>
  <p><span class="style16">.</span>  </p>
	  
    
      <input name="update" type="submit" id="update" value="Update "style=" width:8em;" />
         <input name="save" type="submit" id="save2"  value="Save " style=" width:7em;" /   >
     <input type="HIDDEN" name="ID" <?php echo "VALUE=\"$ID\""?>>
    <?PHP
if(isset($message))
{
echo "<BR><BR>$message";
}
?> 
    
</body>
</html>

this is the simplify code which is from A-Z just in case there's other possibility that causes the error. i've tried every way i know to solve the problem, killed my whole day.

thanks in advance.

Philip · February 14, 2009

$query="UPDATE City SET CityName='$CityName'" . "WHERE ID=$ID ";

Should have single quotes around $ID, as with all variables, and a space between cityname var and WHERE:

$query="UPDATE City SET CityName='$CityName'" . " WHERE ID='$ID'";

klinmy · February 14, 2009

Thanks KingPhilip, i tried this. it popped up "record updated" but in fact it didn't. It did nothing to the edition.

allworknoplay · February 14, 2009

Hi,

when i run into issues like this, you need to get away from the complexity of the code and try to do it manually to see if your statements even work.

For example, forget all that you wrote, can you even update your DB if you manually coded it?


$query="UPDATE City SET CityName='New York' WHERE ID=0";

klinmy · February 15, 2009

yea, i tried this,

$query="UPDATE City SET CityName='$CityName'" . " WHERE ID='7'";

where for example the auto_increment ID for New York is 7. It updated New York with no problem.

thanks

Sign In

Can't update record

Recommended Posts

klinmy

Link to comment

Share on other sites

blueman378

Link to comment

Share on other sites

klinmy

Link to comment

Share on other sites

klinmy

Link to comment

Share on other sites

blueman378

Link to comment

Share on other sites

klinmy

Link to comment

Share on other sites

Philip

Link to comment

Share on other sites

klinmy

Link to comment

Share on other sites

allworknoplay

Link to comment

Share on other sites

klinmy

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information