Editing existing data

raustin

I am trying to get the following script to edit a selected row. The row loads into the form, but after 'submit' is pressed, nothing gets changed. Any ideas?

<?PHP

	$id = 0;
	$area = "";
	$address = "";

if ($_GET["edit"] <> "") {
	$edit = intval($_GET["edit"]);
	$strsql = "SELECT * FROM openhouse WHERE id = $edit LIMIT 1";
	$sql = mysql_query($strsql);
	while ($row = mysql_fetch_assoc($sql)) {
		$id = $row["id"];
		$area = $row["area"];
		$address = $row["address"];
	}
	mysql_free_result($sql);
}
if ($_POST["btnSubmit"] <> "") {
	$id = $_POST["id"];
	$title = $_POST["area"];
	$description = $_POST["address"];

if ($id == 0) {
	$strsql = "INSERT INTO openhouse (area, address) ".
		"VALUES ('".$area."','".$address."')";
	mysql_query($strsql);
	$id = mysql_insert_id();
}else{
	$strsql = "UPDATE openhouse SET ".
		"area='".$area."',".
		"address='".$address."',".
		"WHERE id = " . $id;
	mysql_query($strsql);
}
header("Location: adminhouse7.php");
}
?>
<title>open haus admin 2.0 :: Modify</title>
<style type="text/css">
<!--
body,td,th {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 12px;
}
.style1 {font-size: 9px}
-->
</style>
<table width="714" border="0" cellpadding="0" cellspacing="0" bordercolor="#FFFFCC">
<tr>
<td width="291" bgcolor="#FFFFCC"><H1>Modify an Entry</H1></td>
<td width="423" bgcolor="#FFFFCC"><a href="adminhouse7.php"><strong>[BACK]</strong></a></td>
</tr>
</table>
<br>
<form action="modify.php" method="post" enctype="multipart/form-data">
<table>
  <tr>
    <td>ID</td>
    <td><?PHP echo $id; ?><input type="hidden" value="<?PHP echo $id; ?>" name="id"></td>
  </tr>
  <tr>
    <td>Area:</td>
    <td><input type="text" maxlength="250" name="area" value="<?PHP echo $area; ?>"></td>
  </tr>
  <tr>
    <td>Address</td>
    <td><input name="address" type="text" value="<?PHP echo $address; ?>" size="100" maxlength="250"></td>
  </tr>
  <tr>
    <td colspan="2"><input type="submit" name="btnSubmit" value="Submit"> 
      &nbsp;<span class="style1">No more than 250 characters; do not use the ' symbol.</span> </td>
  </tr>
</table>
</form>

TobyRT

// hang on a sec - i'm just changing this

I could be wrong (and probably am) but I think your insert query syntax is out.

if ($id == 0) { 
        $strsql = "INSERT INTO openhouse (area, address) ". 
            "VALUES ('".$area."','".$address."')";

I think should read:

 $strsql = "INSERT INTO openhouse (area, address) VALUES '$area', '$address'";

Likewise, for the update clause I would use:

$strsql = "UPDATE openhouse SET area='$area', address='$address' WHERE id =  $id";

You seem to be using quotes & concatenations for an 'echo' function in the middle of an SQL query

raustin

Well, we made progress... That code cleared out the 2 fields, but did not insert the new data. Here is how it looks now:

if ($_POST["btnSubmit"] <> "") {
	$id = $_POST["id"];
	$title = $_POST["area"];
	$description = $_POST["address"];

if ($id == 0) {
	$strsql = "INSERT INTO openhouse (area, address) VALUES '$area', '$address'";
	mysql_query($strsql);
	$id = mysql_insert_id();
}else{
	$strsql = "UPDATE openhouse SET area='$area', address='$address' WHERE id =  $id";
	mysql_query($strsql);
}
header("Location: adminhouse7.php");
}

TobyRT

oops!

that query should end:

WHERE id='$id'";

I forgot the quotes :queasy:

raustin

Still no dice; that id shows empty tables still...

TobyRT

try changing this:

$id = $_POST["id"];

to this:
$id = $_POST['id']; - note the single quotes.

Do likewise with the other variables

Oh, and make sure the page before is actually sending an ID

Roger_Ramjet

Raustin, forget TobyRT's comment that your string concatenation is wrong. I use that form all the time (end-quote and dot concat) for 2 reasons:

for security I use a function to apply mysql_real_escape_string to all user input, and you can't embed a function call into a string. You should be doing the same to all user input - read the manual about it at that link
even with queries that do not have user input I use that form for clarity and legibility. I find it much easier to scan my code and understand what is going on, and consequently far easier to debug

I know the short-hand form is faster to type, but I get more silly syntax and typo errors with it and loose any time saved, and more. 🙂

Oh, and have a look at the mysql manual for 'INSERT .... ON DUPLICATE KEY UPDATE'
so you can run the thing with one query.

TobyRT

Roger:

Fair enough - I've just never come across that long-handed form before (I've only been php-ing for a few months, with a gap while i changed jobs)

The problem I generally have with the php manual though is that it makes my head hurt - the code there generally doesn't bear any resemblance to anything I can translate or utilise. It's getting better as I understand more!

TobyRT

try slinging this at the beginning, it'll tell you if you're getting your id passed:

	//check for record id
	if ((!isset($_GET['id']) || trim ($_GET['id']) == ''))
	{
	die('Missing Record ID!');
	}

TobyRT

}else{
$strsql = "UPDATE openhouse SET ".
"area='".$area."',".
"address='".$address."',".
"WHERE id = " . $id;
mysql_query($strsql)
or die (mysql_error()); //add this line in and see if you get an error

raustin

Missing Record ID!

raustin

here it is again:

<?PHP

  if ((!isset($_GET['id']) || trim ($_GET['id']) == ''))
    {
    die('Missing Record ID!');
    }  

	else{
$strsql = "UPDATE openhouse SET ".
"area='".$area."',".
"address='".$address."',".
"WHERE id = " . $id;
mysql_query($strsql)
or die (mysql_error()); //add this line in and see if you get an error
}
		$id = 0;
		$area = "";
		$address = "";

if ($_GET["edit"] <> "") {
	$edit = intval($_GET["edit"]);
	$strsql = "SELECT * FROM openhouse WHERE id = $edit LIMIT 1";
	$sql = mysql_query($strsql);
	while ($row = mysql_fetch_assoc($sql)) {
		$id = $row["id"];
		$area = $row["area"];
		$address = $row["address"];
	}
	mysql_free_result($sql);
}
if ($_POST["btnSubmit"] <> "") {
	$id = $_POST["id"];
	$title = $_POST["area"];
	$description = $_POST["address"];

if ($id == 0) {
	$strsql = "INSERT INTO openhouse (area, address) VALUES '$area', '$address'";
	mysql_query($strsql);
	$id = mysql_insert_id();
}else{
	$strsql = "UPDATE openhouse SET area='$area', address='$address' WHERE id='$id'";
	mysql_query($strsql);
}
header("Location: adminhouse7.php");
}
?>
<title>open haus admin 2.0 :: Modify</title>
<style type="text/css">
<!--
body,td,th {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 12px;
}
.style1 {font-size: 9px}
-->
</style>
<table width="714" border="0" cellpadding="0" cellspacing="0" bordercolor="#FFFFCC">
<tr>
<td width="291" bgcolor="#FFFFCC"><H1>Modify an Entry</H1></td>
<td width="423" bgcolor="#FFFFCC"><a href="adminhouse7.php"><strong>[BACK]</strong></a></td>
</tr>
</table>
<br>
<form action="modify.php" method="post" enctype="multipart/form-data">
<table>
  <tr>
    <td>ID</td>
    <td><?PHP echo $id; ?><input type="hidden" value="<?PHP echo $id; ?>" name="id"></td>
  </tr>
  <tr>
    <td>Area:</td>
    <td><input type="text" maxlength="250" name="area" value="<?PHP echo $area; ?>"></td>
  </tr>
  <tr>
    <td>Address</td>
    <td><input name="address" type="text" value="<?PHP echo $address; ?>" size="100" maxlength="250"></td>
  </tr>
  <tr>
    <td colspan="2"><input type="submit" name="btnSubmit" value="Submit"> 
      &nbsp;<span class="style1">No more than 250 characters; do not use the ' symbol.</span> </td>
  </tr>
</table>
</form>
<br> 
<p><img src="images/openmap.gif" width="450" height="352"></p>

and the link:

 <td><a href="modify.php?edit=<?PHP echo $row["id"]; ?>">Edit</a></td>

TobyRT

You haven't taken out the double quotes.

Anything that looks like this (in my experience):
$_POST["id"]

should look like this:
$_POST['id']

I suggest changing the link to <a href="modify.php?id=<?php echo $row['id'];?>">

I notice that you're also adding an ID to the record - do you not have it set to autoincrement in the db?

TobyRT

Here's some useful chunks of one of my update forms - it works - only got it going this week, so I'm sharing the things i've learnt this week!

This chunk checks whether anything has been posted from the previous page (ie, clicked "edit")

if (!$_POST['submit']) 
{
	//check for record id
	if ((!isset($_GET['id']) || trim ($_GET['id']) == ''))
	{
	die('Missing Record ID!');
	}

//Generate & Execute query
// Get the PHP file containing the DbConnector class
require_once('../sypphp/sypcms/DbConnector.php');

// Create an instance of DbConnector
$connector = new DbConnector();

$id = $_GET['id'];
$query = "SELECT company, jobtitle, jobdesc, contact, closingdate from jobs where id = '$id'";
$result = $connector->query($query) or die("Error in query $query.".mysql_error());

//IF A RESULT IS RETURNED
if (mysql_num_rows($result)>0){ //this if statement is closed after the form, which I see you already have OK!)

//TURN IT INTO AN ARRAY
$row = mysql_fetch_assoc($result);

?>
<!-- PRINT FORM WITH JOB VALUES -->
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
<input type="hidden" name ="id" value="<?php echo $id; ?>">
} //closes the if statement
//no result returned - return error message
else
{
echo 'Sorry, cannot find that job in the database';
} //end else

Up to this point you are telling it what to do when NOTHING has been submitted
Here comes the bit where you HAVE submitted something, covered by the following else statement. Of course, feel free to amend my syntax according to Roger's instructions

}else{
$company = $_POST['company'];
$jobtitle = $_POST['jobtitle'];
$jobdesc = $_POST['jobdesc'];
$contact = $_POST['contact'];
$closingdate = $_POST['closingdate'];

//validate the fields
if (trim($_POST['company']) == '') {$errorlist[] = 'Invalid entry: company';}
if (trim($_POST['jobtitle']) == '') {$errorlist[] = 'Invalid entry: jobtitle';}
if (trim($_POST['jobdesc']) == '') {$errorlist[] = 'Invalid entry: jobdesc';}
if (trim($_POST['contact']) == '') {$errorlist[] = 'Invalid entry: contact';}
if (trim($_POST['closingdate']) == '') {$errorlist[] = 'Invalid entry: closingdate';}

//check for errors, if none found:

if (sizeof($errorlist) == 0)
{
$connector = new DbConnector();

$insertQuery="UPDATE jobs SET company='$company', jobtitle='$jobtitle', jobdesc='$jobdesc', contact='$contact', closingdate='$closepost' WHERE id = '$id' ";
$result = $connector->query($insertQuery) or die ("Error in query: $insertQuery.".mysql_error());

//print result
echo 'Update Successful<br><a href=jobsadmin.php>Click here to return to the jobs admin menu</a>';

}else{

//errors found: show them in a list
echo 'The following errors have been encountered:<br>';
for ($x=0; $x<sizeof($errorlist); $x++)
{
echo $errorlist [$x];
}
}
}
?>

This is the link (having performed a select query) from the start page

<a href="jobsfull.php?id=<?php echo $row['id'];?>"><?php echo $row['jobtitle'];?></a>

raustin

"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 'WHERE id = 4' at line 1" displays now...

raustin

and the id is set to auto increment; the goal is to have the id stay the same, while only changing the other 2 fields...

TobyRT

Can you repost the code you're using please - I'm a bit lost!

TobyRT

Unless you actually have a form entry to change and repost the ID, how is it going to change?

The only thing you're using the ID for is a reference, you post it from the selection page to the editing page only so that the latter knows which entry to edit.

raustin

This code is so far hacked that i'm considering starting all over...

The link:

<a href="modify.php?id=<?php echo $row['id'];?>">Edit</a>

The modify.php page:

<?PHP

  if ((!isset($_GET['id']) || trim ($_GET['id']) == ''))
    {
    die('Missing Record ID!');
    }  

	else{
$strsql = "UPDATE openhouse SET ".
"area='".$area."',".
"address='".$address."',".
"WHERE id = " . $id;
mysql_query($strsql)
or die (mysql_error()); //add this line in and see if you get an error
}
		$id = 0;
		$area = "";
		$address = "";

if ($_GET["edit"] <> "") {
	$edit = intval($_GET["edit"]);
	$strsql = "SELECT * FROM openhouse WHERE id = $edit LIMIT 1";
	$sql = mysql_query($strsql);
	while ($row = mysql_fetch_assoc($sql)) {
		$id = $row['id'];
		$area = $row['area'];
		$address = $row['address'];
	}
	mysql_free_result($sql);
}
if ($_POST["btnSubmit"] <> "") {
	$id = $_POST['id'];
	$title = $_POST['area'];
	$description = $_POST['address'];

if ($id == 0) {
	$strsql = "INSERT INTO openhouse (area, address) VALUES '$area', '$address'";
	mysql_query($strsql);
	$id = mysql_insert_id();
}else{
	$strsql = "UPDATE openhouse SET area='$area', address='$address' WHERE id='$id'";
	mysql_query($strsql);
}
header("Location: adminhouse7.php");
}
?>
<title>open haus admin 2.0 :: Modify</title>
<style type="text/css">
<!--
body,td,th {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 12px;
}
.style1 {font-size: 9px}
-->
</style>
<table width="714" border="0" cellpadding="0" cellspacing="0" bordercolor="#FFFFCC">
<tr>
<td width="291" bgcolor="#FFFFCC"><H1>Modify an Entry</H1></td>
<td width="423" bgcolor="#FFFFCC"><a href="adminhouse7.php"><strong>[BACK]</strong></a></td>
</tr>
</table>
<br>
<form action="modify.php" method="post" enctype="multipart/form-data">
<table>
  <tr>
    <td>ID</td>
    <td><?PHP echo $id; ?><input type="hidden" value="<?PHP echo $id; ?>" name="id"></td>
  </tr>
  <tr>
    <td>Area:</td>
    <td><input type="text" maxlength="250" name="area" value="<?PHP echo $area; ?>"></td>
  </tr>
  <tr>
    <td>Address</td>
    <td><input name="address" type="text" value="<?PHP echo $address; ?>" size="100" maxlength="250"></td>
  </tr>
  <tr>
    <td colspan="2"><input type="submit" name="btnSubmit" value="Submit"> 
      &nbsp;<span class="style1">No more than 250 characters; do not use the ' symbol.</span> </td>
  </tr>
</table>
</form>
<br> 
<p><img src="images/openmap.gif" width="450" height="352"></p>

TobyRT

try editing these lines:

if ($_GET["edit"] <> "") { 
    $edit = intval($_GET["edit"]); 

//change to

if ($_GET['edit'] <> "") { 
    $edit = intval($_GET['edit']);

Change:

$strsql = "SELECT * FROM openhouse WHERE id = $edit LIMIT 1";

//to

$strsql = "SELECT * FROM openhouse WHERE id = '$edit' LIMIT 1";

You also have a query to get 'edit' from somewhere - where is that coming from?