trying to build page to edit sql

turpentyne

I've used the script from a book, I've rewritten this 3 different ways, posted it on forums, and gotten nowhere. I cannot figure out how to write a dang scipt to do a simple edit of entries in my sql database.

I wrote this absolutely stripped-down version, where it takes the variable from a search page. I got it to show the results on the page, so I added in the code to update the database and still it doesn't work:

<html>
<body>

<?php


if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) 
     {
     $id = $_GET['id'];
     // that was the part to check for passed variables. This is what does something with it
     require ('databaseconnect.php');
     $query = "SELECT * FROM table WHERE $id = item_name";
     $result = mysql_query($query);
     if (mysql_num_rows($result) == 1){

      While ($row = mysql_fetch_array($result)) {

           echo '<form action="edit.php" method="post"><table cellpadding="10"><tr><td align="left">' . $row['item_name'] . '</td>
           <td align="left"> <input type="text" value="' . $row['2nd_name'] . '"></td>
           <td align="left"><input type="text" value="' . $row['3rd_name'] . '"></td></tr></table><br>
           <input type="hidden" value="' . $row['item_name'] . '">
           <input type="hidden" value="submitted">  <input type="submit" name="submit" value="Submit change"></form>';
           }

      }


 }  else {
 echo 'you have reached this page in error';
}
// up to this point i had a working script. when I tried to set up the ability to edit, it broke.
if (isset($_POST['submitted'])){

  $pn = escape_data($_POST['item_name']);
  $sn = escape_data($_POST['2nd_name']);
  $cne = escape_data($_POST['3rd_name']);
  $query = "UPDATE table SET item_name='$sn', 2nd_name='$cne' WHERE item_name=$pn";

  $result = mysql_query ($query);
  if (mysql_affected_rows() == 1) {
        echo 'your plant has been updated';
        }  
 }

?>

</body>
</html>

Krik

Your html is in error in particular the <input> fields.

$_POST is structured just like an array. You must have a key and a value. And you use the keys to retrieve the value. In your input field you have the values set but you have not set the keys, which in this case is the "name" attribute.

Also if you use "$row['2nd_name']" for the name. it does not mean that $_POST['2nd_name'] will work. The "value" of $row['2nd_name'] would be the name of the field not 2nd_name.

Here is your code with several parts reworked. I can not say it will work as some parts were not included in your sample. But if everything else is as it should be it will do the job.

<?php
if ((isset($_GET['id'])) && (is_numeric($_GET['id']))) {

$id = $_GET['id'];
// that was the part to check for passed variables. This is what does something with it
require ('databaseconnect.php');
$query = "SELECT * FROM table WHERE $id = item_name";
$result = mysql_query($query);

if (mysql_num_rows($result) == 1){

	while ($row = mysql_fetch_array($result)) {
?>
			<form action="edit.php" method="post">
			<table cellpadding="10">
			<tr>
			<td align="left"><?php echo $row['item_name']; ?></td>
			<td align="left"><input type="text" name="2ndname" value="<?php echo $row['2nd_name']; ?>"></td>
			<td align="left"><input type="text" name="3rdname" value="<?php echo $row['3rd_name']; ?>"></td>
			</tr>
			</table>
			<input type="hidden" name="itemname" value="<?php echo $row['item_name']; ?>">
			<input type="submit" name="submit" value="Submit Change">
			</form>
<?php
		}
	}
}
else {
	echo 'you have reached this page in error';
}
// up to this point i had a working script. when I tried to set up the ability to edit, it broke.
if (isset($_POST['submit'])){

$pn = escape_data($_POST['itemname']);
$sn = escape_data($_POST['2nd_name']);
$cne = escape_data($_POST['3rdname']);
$query = "UPDATE table SET item_name='$sn', 2nd_name='$cne' WHERE item_name=$pn";

$result = mysql_query ($query);
if (mysql_affected_rows() == 1) {
echo 'your plant has been updated';
}
}
?>

Lastly I should add that your database structure is very odd and may eventual come back to haunt you. In your first query your "where" clause seems to indicate that "item_name" is a value in one of the records but later you indicate that he same table has a column named "item_name". It looks like the value of the "item_name" is "item_name". But also some other column, that is a number, has a value of "item_name". If your confused I won't be surprised I was also for almost an hour.

But worse yet is that in the second query you use the "item_name" to determine which record to change and then change the value of the "item_name" column. Every record should have a unique identifier that under no condition changes that you can always use to refer to that record. So if your records have a unique identifier your not using it and if they don't you should give them all one.

turpentyne

Ah... part of the confusion is from me trying to change some of the information so that it was generic for posting on here. My table's item_id is an auto increment key for each item in the database. The 2nd name is also unique, and I'm using it to find the item_id, then using item_id as an identifier in hidden fields. (or at least trying to)

Other than that, I'm turned around, upside down, inside out and lost.

I've dropped in the code rework that you gave me, fixed the confusing spots and it still doesn't work.

The page loads, with the passed variable $id populating the two boxes with info. But when I hit submit, it echos my error ' you have reached this page in error' and gives me this error:

Fatal error: Call to undefined function escape_data() in /data/21/2/40/160/2040975/user/2235577/htdocs/edit_user10.php on line 39

I can get rid of the escape_data() but still it doesn't do what I want.

the entire code with actual table/column names is here:


<html>
<body>

<?php
if ((isset($_GET['id'])) && (is_numeric($_GET['id']))) {

$id = $_GET['id'];
// that was the part to check for passed variables. This is what does something with it
require ('databaseconnect.php');

$query = "SELECT * FROM plantae WHERE $id = plant_name";
$result = mysql_query($query);

if (mysql_num_rows($result) == 1){

    while ($row = mysql_fetch_array($result)) {
?>
            <form action="edit_user10.php" method="post">
            <table cellpadding="10">
            <tr>
            <td align="left"><?php echo $row['plant_name']; ?></td>
            <td align="left"><input type="text" name="scientific_name" value="<?php echo $row['scientific_name']; ?>"></td>
            <td align="left"><input type="text" name="common_name_english" value="<?php echo $row['common_name_english']; ?>"></td>
            </tr>
            </table>
            <input type="hidden" name="plant_name" value="<?php echo $row['plant_name']; ?>">
            <input type="submit" name="submit" value="Submit Change">
            </form>
<?php
        }
    }
}
else {
    echo 'you have reached this page in error';
}
// up to this point i had a working script. when I tried to set up the ability to edit, it broke.
if (isset($_POST['submit'])){

$pn = escape_data($_POST['plant_name']);
$sn = escape_data($_POST['scientific_name']);
$cne = escape_data($_POST['common_name_english']);
$query = "UPDATE plantae SET scientific_name='$sn', common_name_english='$cne' WHERE plant_name=$pn";

$result = mysql_query ($query);
if (mysql_affected_rows() == 1) {
echo 'your plant has been updated';
}
}
?>

johanafm

<?php
/* This varible is only set if a plant has been selected.
 * E.g. the url is [url]http://example.com/?id=13[/url]
 * See comment for the else block below */
if ((isset($_GET['id'])) && (is_numeric($_GET['id']))) {

# I'd add (int) to make sure this is an integer
$id = (int) $_GET['id'];

require ('databaseconnect.php');

/* This query tries to retrieve a row in the database where some integer,
 * e.g. 13, is equal to plant_name. I'd expect plant_name to be something like 'Lily'
 * rather than 13. */
 # "SELECT * FROM plantae WHERE id = 13";
 # "SELECT * FROM plantae WHERE plant_name = 'Lily'";
$query = "SELECT * FROM plantae WHERE $id = plant_name";
$result = mysql_query($query);

if (mysql_num_rows($result) == 1){
	/* No reason to have a while loop when you know that you will run the code once...
	 * There is only one row after all. Simply $row = mysql_fetch... is enough */
    while ($row = mysql_fetch_array($result)) {
?>
			<-- Since you set the action to edit_user10.php, the url will contain
				no query string (?id=13) after form post. To retain the query string
				you could leave the action attribute empty: action="" -->
            <form action="edit_user10.php" method="post">
            <table cellpadding="10">
            <tr>
            <td align="left"><?php echo $row['plant_name']; ?></td>
            <td align="left"><input type="text" name="scientific_name" value="<?php echo $row['scientific_name']; ?>"></td>
            <td align="left"><input type="text" name="common_name_english" value="<?php echo $row['common_name_english']; ?>"></td>
            </tr>
            </table>
            <input type="hidden" name="plant_name" value="<?php echo $row['plant_name']; ?>">
            <input type="submit" name="submit" value="Submit Change">
            </form>
<?php
        }
    }
}
/* Is it really an error if you havn't selected a plant? Or should this part perhaps
 * display a list of plants instead? */
else {
    echo 'you have reached this page in error';
}
// up to this point i had a working script. when I tried to set up the ability to edit, it broke.
/* I'd run this code before the other, at least if the code is supposed to both let
 * the user edit and save data, and show the new results to the user. Otherwise you
 * risk either displaying data as it was before the new data is saved, or you risk
 * displaying what the user provided only to have your update query fail for some reason
 * which means that the user thought he had saved new information only to later find out
 * that nothing happened. */
if (isset($_POST['submit'])){
	/* I'm not sure what you'd want escape_data to do, but you should most definitely
	 * use mysql_real_escape_string() on any string data coming through post or get
	 */
    $pn = escape_data($_POST['plant_name']);
    $sn = escape_data($_POST['scientific_name']);
    $cne = escape_data($_POST['common_name_english']);
    /* Assuming plant_name is a string, such as 'Lily', it also needs to be properly
     * quoted:
     	WHERE plant_name='$pn'
     */
    $query = "UPDATE plantae SET scientific_name='$sn', common_name_english='$cne' WHERE plant_name=$pn";

$result = mysql_query ($query);
if (mysql_affected_rows() == 1) {
    echo 'your plant has been updated';
}
}
?>

turpentyne

I think the biggest problem I was having was the form posting to file name. I didn't realize it was dropping the passed variable. SO dang obvious.

You were right on about not showing the change after submitting it. I'll have to try and switch the code around later. I tried quickly by just switching the two blocks of code and got errors. I'll have to sit and work with it.