[SOLVED] Update MySQL Database throught a PHP/HTML form

xx_princess_xx · May 9, 2009

I want the code to allow a user to update a MySQL database, at the moment the user can enter details but when the user submits the form an error message is displayed saying 'the query was empty'

<?php
if($_POST) //if page has been submitted
{
$errorID=0;
$aname=$_POST[ 'aname'];
$address=$_POST[ 'address'];
$post_code=$_POST[ 'post_code'];

  if(trim($aname) =="") //Is the value empty?
{
  $errorID=1; $errorMsg="Name cannot be an empty value!";
}
  elseif(trim($address) =="") //Is the value empty?
{
  $errorID=2; $errorMsg="Address cannot be an empty value!";
}
  elseif(trim($post_code) =="") //Is the value empty?
{
  $errorID=3; $errorMsg="Post Code cannot be an empty value!";
}
}      
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/template.dwt" codeOutsideHTMLIsLocked="false" -->
<head>
<!-- InstanceBeginEditable name="head" -->
<link rel="stylesheet" type="text/css" href="styles.css" media="all"/>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Angel Estates</title>
<!-- InstanceEndEditable -->
</head>

<body>

<div class="container">
<div class="shadow">
<div class="main">
<div class="banner">
<div class="title">Angel Estates</div> <!-- title -->
</div>
<!-- banner -->

<div class="menu_container">
<div class="menu"><!-- InstanceBeginEditable name="menu" -->
<ul class="menu">
  <li><a href="index.php"><span>Home</span></a></li>
  <li><a href="search.php"><span>Search</span></a></li>
  <li><a href="agents.php" class="active"><span>Agents</span></a></li>
  <li><a href="tenants.php"><span>Tenants</span></a></li>
  <li><a href="properties.php"><span>Properties</span></a></li>
</ul>
<!-- InstanceEndEditable --></div> 
<!-- menu -->
</div> <!--menu_container-->

<div class="content">
<div class="left_panel">
  <!-- InstanceBeginEditable name="left content" -->
<p><a href="agents_insert.php" class="link" class="link_hover">Insert Agent Details</a></p>

<p><a href="agents_update.php" class="link" class="link_hover">Update Agent Details</a></p>

<p><a href="#" class="link" class="link_hover">Delete Agent Details</a></p>
  <!-- InstanceEndEditable --></div>
<!-- left_panel -->
<div class="right_content">
  <!-- InstanceBeginEditable name="right content" -->
<p class="sub_title">Update Agent Details</p>
<form action=<?php echo $_SERVER[ 'PHP_SELF']; ?> method="post">
<frameset>
<table border="0">
<tr>
<td>Agent ID</td>
<td><input type="text" name="aid" value="<?php echo $aid; ?>" /></td>
</tr>
<tr>
<td>Name:</td>
<td><input type="text" name="aname" value="<?php echo $aname; ?>" /> <?php if($errorID==1) echo "* $errorMsg"; ?></td>
</tr>
<tr>
<td>Address:</td>
<td><input type="text" name="address" value="<?php echo $address; ?>" /> <?php if($errorID==2) echo "*$errorMsg"; ?></td>
</tr>
<tr>
<td>Post Code:</td>
<td><input type="text" name="post_code" value="<?php echo $post_code; ?>" /> <?php if($errorID==3) echo "*$errorMsg"; ?></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="submit" /></td>
</tr>
</table>
</frameset>
</form>  

<?php
  if(isset($errorID) && $errorID==0)
{
  $con = mysql_connect("localhost", "root", "");
  if (!$con)
{
  die('Could not connect: ' . mysql_error());
}

  mysql_select_db("comreg098hassan", $con);
  
  mysql_query("UPDATE agents (aname, address, post_code)
  VALUES ('$_POST[aname]','$_POST[address]','$_POST[post_code]') 
  WHERE AID = '$_POST[aid]'");
  
  if (!mysql_query($sql,$con))
{
  die('Error: ' . mysql_error());
}
  echo "1 record has been updated";
  mysql_close($con);
}
?>
  <!-- InstanceEndEditable --></div>
<!-- right_content -->
</div><!-- content -->
</div><!-- main -->
</div><!-- shadow -->
</div><!-- container -->

</body>
<!-- InstanceEnd --></html>

gevans · May 9, 2009

Echo out the query to see what it actually looks like. Also why is the inside of your form in a framset?

semlabs · May 9, 2009

mysql_query("UPDATE agents (aname, address, post_code)
  VALUES ('{$_POST[aname]}','{$_POST[address]}','{$_POST[post_code]}') 
  WHERE AID = '[$_POST[aid]}'");

When inserting array values into a string, you should use the curly braces as per the example above. What you have done might have caused some errors.

I would advise you research PHP templating engines. They allow you to separate all your PHP and HTML output, rather than just stuffing it all in one file.

Also, I'd recommend using PDO for your database: http://uk.php.net/manual/en/book.pdo.php

xx_princess_xx · May 9, 2009

Echo out the query to see what it actually looks like. Also why is the inside of your form in a framset?

shall i take it out of a frameset? would that make a difference, how do i echo out the query, i am really a beginner

gevans · May 9, 2009

I'd lose the framset unless you have a reasoin for doing it (I can't imagine what that would be though);

mysql_select_db("comreg098hassan", $con);
  
$query = "UPDATE agents (aname, address, post_code)
  VALUES ('{$_POST[aname]}','{$_POST[address]}','{$_POST[post_code]}') 
  WHERE AID = '{$_POST[aid]}'";

  mysql_query($query) or die('Error: '.mysql_error()."\n".$query);
  
  if (!mysql_query($sql,$con))
{
  die('Error: ' . mysql_error());
}
  echo "1 record has been updated";
  mysql_close($con);
}

EDIT...

Post the error that gets printed

xx_princess_xx · May 9, 2009

I have taken out the frameset, the only reason i had it because of previous courseworks i done when we are tought to use them.

I changed the code

the error is now

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 '(aname, address, post_code) VALUES ('tara','flat','fr5 4rt') WHERE AID = '' at line 1 UPDATE agents (aname, address, post_code) VALUES ('tara','flat','fr5 4rt') WHERE AID = '1'

gevans · May 9, 2009

Ahhh, wrong syntax (should've seen taht anyway)

UPDATE agents (aname, address, post_code) VALUES ('tara','flat','fr5 4rt') WHERE AID = '1'

shoudl be

UPDATE agents

SET aname='tara' address='flat' post_code='fr5 4rt'

WHERE AID=1

so....

$query = "UPDATE agents
SET aname='{$_POST[aname]}' address='{$_POST[address]}' post_code='{$_POST[post_code]}'
WHERE AID = '{$_POST[aid]}'";

xx_princess_xx · May 9, 2009

i still get an error

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 'address='house' post_code='123abc' WHERE AID = '1'' at line 2 UPDATE agents SET aname='tara' address='house' post_code='123abc' WHERE AID = '1'

gevans · May 9, 2009

My bad, I forgot commas....

$query = "UPDATE agents
SET aname='{$_POST[aname]}', address='{$_POST[address]}', post_code='{$_POST[post_code]}'
WHERE AID = '{$_POST[aid]}'";

xx_princess_xx · May 9, 2009

i got an error saying

error: query was empty

gevans · May 9, 2009

I'm being slow today, bare with me;

<?php
if(isset($errorID) && $errorID==0)
{
$con = mysql_connect("localhost", "root", "");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("comreg098hassan", $con);
$query = "UPDATE agents
SET aname='{$_POST[aname]}', address='{$_POST[address]}', post_code='{$_POST[post_code]}'
WHERE AID = '{$_POST[aid]}'";

$result = mysql_query($query) or die('Error: '.mysql_error()."\n".$query);
echo "1 record has been updated";
mysql_close($con);
}

xx_princess_xx · May 9, 2009

sorrey it did work before, thank you so much it has been bugging me all day, since 7am so thanks

the only problem was that an error message was displayed

gevans · May 9, 2009

You're still getting an error message?

xx_princess_xx · May 9, 2009

its all sorted thanks that last post completed it

thanx again for your help

Sign In

[SOLVED] Update MySQL Database throught a PHP/HTML form

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information