Editing table content using forms in PHP

[sridharao]

I have a table with 30-40 records. I wish to present all the records in a html form (data in each column being displayed in a series of textbox elements of a single form). I have been able to do that successfully, but I have two requirements.

Lying next to data of each record is placed a delete button. When the user clicks on the button, corresponding record should be deleted. I can't get to do that.

The same form contains an update button, clicking on which each of the data element gets updated into the record. May be I should use foreach loop and update every record one by one. How can this be optimally done?

[kows]

you can use form arrays to do what you want easily. you could lay your form out like this:

  <input name="231[field1]" value="some value1" type="text" />
  <input name="231[field2]" value="some value2" type="text" />
  <input name="231[field3]" value="some value3" type="text" />

where 231 is the ID of the record, and "field1" through "field3" are your fields. this will produce the following POST array, which would be perfect for looping through:

Array
(
    [231] => Array
        (
            [field1] => some value1
            [field2] => some value2
            [field3] => some value3
        )

you could do something like:

foreach($_POST as $id => $fields){
  //skip non numerical ID fields, eg. fields that weren't an array
  if(!is_numeric($id)) continue;

  $sql = "UPDATE table SET field1='{$fields['field1']}', field2='{$fields['field2']}', field3='{$fields['field3']}' WHERE id='{$id}' LIMIT 1";
  mysql_query($sql);
} 

now, as far as deleting goes, it would be much easier if you used checkboxes, or plain links, than trying to build it into your form like the way you described. using the same method as above, you could add a checkbox:

<input type="checkbox" name="231[delete]" /> DEL

then in your foreach(), simply check if this field exists:

foreach($_POST as $id => $fields){
  if(isset($fields['delete'])){
    //this record is supposed to be deleted
    $sql = "DELETE FROM table WHERE id='{$id}' LIMIT 1";
  }else{
    //update it instead
    $sql = "UPDATE table ........";
  }
  mysql_query($sql);
} 

hope that helps and makes sense! ask questions if it doesn't :)

[sridharao]

Thank you so much for the reply. I had posted the same query in three different fora and only here it got replied.

Let us go slow on this, I am an absolute beginner. Let us look at that part which selects the records to delete. I had come up with my own solution to display the table's contents in a html form.

The PHP code:

<?php
//initializing connection
$con = mysql_connect('localhost',"root");
mysql_select_db("setenv",$con) or die(mysql_error());
$select_records = "SELECT * FROM invest";
$list = mysql_query($select_records) or die(mysql_error());
$num = mysql_numrows($list);
$ctr = 1;
//generating elements' name for use in form
while($record =mysql_fetch_array($list)){
$te= "test".$ctr;
$cd = "code".$ctr;
$as = "ass".$ctr;
$pr = "gen".$ctr;
$sp = "spl".$ctr;
?>
generating a complete html form output with data in respective elements
<tr>
<td width="50%"><input type="text" name='<?php echo $te; ?>' size="50" value="<?php echo $record['TEST']; ?>"></td>
<td width="13%"><input type="text" name="<?php echo $cd; ?>" size="3" value="<?php echo $record['COD']; ?>"></td>
<td width="12%"><input type="text" name="<?php echo $as; ?>" size="3" value="<?php echo $record['ASS']; ?>"></td>
<td width="9%"><input type="text" name="<?php echo $pr; ?>" size="3" value="<?php echo $record['PRICE']; ?>"></td>
<td width="9%"><input type="text" name="<?php echo $sp; ?>" size="3" value="<?php echo $record['SPRICE']; ?>"></td>
<td width="7%"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<?php echo $record['COD']; ?>"></td>
</tr>
<?php
$ctr+=1;
}
mysql_close($con);
?>

When the user clicks on delete button, the following code is executed:

if (isset($_POST['delEntry'])){
    if (isset($_POST['checkbox'])){
        $ctr = 0;
        foreach($_POST['checkbox'] as $val){
            $sql_del = "DELETE FROM invest WHERE COD ='".$val."' LIMIT 1";
            $del_query = mysql_query($sql_del);
            $ctr+=mysql_affected_rows();
        }
        if ($del_query){
            echo "<b><font color=\"#00CC00\">".$ctr." investigations successfully removed from the database.</font></b>";
        }else{
            echo "<b><font color=\"#FF0000\">Error: An unexpected error has occured in deleting investigations.</font></b>";
        }
    }else{
    echo "<b><font color=\"#FF0000\">Error: You have not selected any investigation to delete.</font></b>";
    }
} 

This might look a little silly (I am new to this), but this works. Any tweaks or suggestions?

As you can see from the above form, there are 6 columns in as many rows. I am not able to adapt your code to update the data from these forms. Please explain using the existing structure.

[kows]

I assume, from your example, that you have two submit buttons for your form; one for updating, one for deleting. in the method I described, you would just have an update button, but no delete button. it would just take a quick modification of your script. In general, your code looks fine.

First, I will assume that your database table has a unique primary key. From your example, I think this would be "COD." You can completely replace $ctr with the value of COD, and do something like this:

<table>
<?php
  while($result = mysql_fetch_assoc($list)){
    $id = $result['COD'];
?>
  <tr>
    <td><input type="text" name="<?php echo $id; ?>[TEST]" value="<?php echo $result['TEST']; ?>" /></td>
    <td><input type="text" name="<?php echo $id; ?>[COD]" value="<?php echo $result['COD']; ?>" /></td>
    <td><input type="text" name="<?php echo $id; ?>[ASS]" value="<?php echo $result['ASS']; ?>" /></td>
    <td><input type="text" name="<?php echo $id; ?>[PRICE]" value="<?php echo $result['PRICE']; ?>" /></td>
    <td><input type="text" name="<?php echo $id; ?>[SPRICE]" value="<?php echo $result['SPRICE']; ?>" /></td>
    <td><input type="checkbox" name="<?php echo $id; ?>[delete]" value="1" /></td>
  </tr>
<?php
  }
?>
</table>

after typing all that out, I realize that you also allow the user to edit COD! if this isn't a unique value, then you probably shouldn't be using it to delete records. if it is a unique value, you probably shouldn't let it be modified, either! if you have an "ID" field, define $id as it instead. If you don't have an ID field, then I highly suggest you make one. Just add a new field, name it "ID," add auto_increment to it, and make it a primary key.

now, you can get to processing the information. using the same example as above:

<?php
  $deleted = 0; //deleted records
  $updated = 0; //updated records

  //loop through our information
  foreach($_POST as $id => $fields){

    //skip any elements that might break our code
    if(!is_numeric($id) || !is_array($fields)) continue;

    //check if this field is to be deleted or not
    if(isset($fields['delete'])){

      //this record is supposed to be deleted
      $sql = "DELETE FROM invest WHERE COD='{$id}' LIMIT 1";
      $var = "deleted";

    }else{

      //update it instead
      $sql = "UPDATE invest SET ...."; //add all of your fields and stuff.
      $var = "updated";

    }
    //query
    mysql_query($sql);

    //update $$var ($deleted or $updated)
    $$var += mysql_affected_rows();
  }
?>
<h4>Successfully updated <?php echo $updated; ?> and deleted <?php echo $deleted; ?> records!

hopefully this all made sense! let me know if you have any more questions.

also, you don't need to bother calling mysql_close(). PHP will do this for you.

[sridharao]

Thanks, your reply is helpful and encouraging. I included a new id field and made its value auto_increment as you suggested.

Updating is not simple, I want all the records to be updated in one instance without user selecting them individually. Before updating the records, the data must be verified and some data (COD) must be unique. I am using javascript to perform few checks before the form is submitted.

The code that I now need to write is for the following actions:
a) I extract only the COD fields (which is unique) from the table into an array using select statement.
b) The form submission too produces a multi-dimensional array. I want to scan this array one by one for this form element $_POST['code1'] ..$_POST['code2'] etc...if it matches any in the previous array; if found - either abort update loop or skip it with an error message.
c) If no match found, update the contents of the form to the corresponding record in the table.

I can't figure out, how I can manage that.

[kows]

the user wouldn't need to select anything individually for the form to update. the checkboxes in my example are only for if the user wanted to delete those records. they don't do anything else.

I'm not sure why you want to take only the COD fields from the table and put them into an array, but you already have the code to do that.

$sql = "SELECT COD FROM table";
$query = mysql_query($sql);
$myarray = array();
while($cods = mysql_fetch_assoc($query)){
  $myarray[$cods['COD']] = $cods['COD'];
} 

and I've already given you code that lets you walk through this array one by one. did you not know that this was what that code did? you can simply loop through it before the update code I gave you and make it check against the array. this code will walk through the POST array and check if the current COD value in the form already exists in an array ($myarray). if it does exist (meaning there are duplicates), then it produces an error. if it does not exist, then that current COD value is added to the array (so that it can be checked for duplicates later on as well). then, it performs a check on if there were any errors or not before updating any records, to make sure that no update happens if there were any errors at all. now, if you were planning on using the $myarray variable that you created above to do this, then I wouldn't bother and I would just use this method instead.

<?php
  $myarray = array();
  $errors = array();
  foreach($_POST as $id => $fields){
    //skip bad stuff
    if(!is_numeric($id) || !is_array($fields)) continue;

    if(isset($myarray[$fields['COD']]))
      $errors[$id] = "COD field given ({$fields['COD']}) already exists";
    else
      $myarray[$fields['COD']] = $myarray[$fields['COD'];
  }

  if(count($errors)){
?>
<h2>there were some errors with your submission</h2>
<ul>
<?php foreach($errors as $id => $error): ?>
  <li>#<?php echo $id; ?> - <?php echo $error; ?></li>
<?php endforeach; ?>
</ul>
<?php
  }else{
    //no errors, so let's loop through our post array and update
    foreach($_POST as $id => $fields){

      //all that other stuff that goes here should go here

    }
  }
?>

[sridharao]

Thanks for the reply. Please excuse my wholesome ignorance, but I am really having difficulty in understanding.

When I mentioned that the codes from the table must be taken in an array, I did not mean a new array. The select command automatically places the result in an array.
$get_cod = mysql_query("SELECT TEST,COD FROM invest");
$cods =mysql_fetch_array($get_cod);

There are six columns in the table. A corresponding text element has been created on the fly in the form for all the columns (one is hidden) for every record in the table. If there are 10 records in the table, there are 60 form elements. I am interested only in the content of one field: COD. The elements names are cod1, cod2, cod3...cod10. Number of elements depends on the number of records in the table. When the form is submitted, the $_POST array will contains all these.

In your code, you have written "$myarray[$fields['COD']]", How do you make reference to it when the names change, as the array is scanned in foreach loop?

here is the print_r($_POST) of the first two rows:
[test1] => Afb Smear [code1] => AFB [ass1] => BAC [gen1] => 10 [spl1] => 10
[test2] => Afb Smear [code2] => AFC [ass2] => BAC [gen2] => 15 [spl2] => 30

I want to scan the $_POST array using foreach loop. Here, I want to check the value of $_POST['code1'], $_POST['code2'].......$_POST['code10'] etc and check it it already exists in the $cods array.

How do you refer to each element in $_POST and how to gets its value? You may say, $val = $myarray[$fields['COD']], but then 'COD' is not constant, it ranges from 'code1' to the last record in table. Any piece of code like $val = $_POST['code' . $i]; inside for loop generates an Undefined index error.

The point is, I have to update the fields to the table using the id, which is also passed in the form. How do I refer to these ids, which also range from 1 to many (depending on record numbers). I can't assume it to be in order because some of these records may been deleted.

[kows]

I don't think you're understanding my examples at all. for this to work, your form needs to look and operate exactly how I set it up in my post a few up. field names need to be in this format (on the form): "#ID#[#FIELD#]," where #ID# is the ID (that you are saying ranges from 1 to many, and say that you can't assume they are in order) and #FIELD# is the field name. the problem is, you are STILL using your previous method to name these fields (adding a 1, 2, 3 etc). you are dealing with a table being turned into a multidimensional array; this means the #FIELD# should represent the actual names of the fields in your table. not [code1] for the first record, and [code2] for the second record, and [code3] for the third record. the records are all separated by their corresponding ID. these should all be just "code." the same goes for your ass/gen/spl fields. the ID doesn't need to be passed in the form, because every time you need to reference an ID, you are looping through the $_POST array, and in my examples the foreach() loop defines $id (#ID# in "#ID#[#FIELD#]) and $fields (array of fields).

as for your questions; I never actually refer to $myarray[$fields['COD']] (to get its value or anything, that is), that array was only created to hold the values of every COD on the form, so that you can check for duplicates. why exactly do you want to define $val as the COD value? if you are inside of the foreach() loop, you could get the value of COD by defining it as $fields['COD']. the word "COD" definitely should be constant, I don't see why it wouldn't be.

The following code (that you posted) also only gets one record, so I'm not sure why you it would be useful to you. the $cods variable is a multidimensional array and would have two keys: "TEST" and "COD ($cods['TEST'] $cods['COD']). this doesn't seem to be what you would want. the only way to query the database and get every current COD value into an array is shown in my last post (looping through and adding to $myarray -- the first example in my last post).

$get_cod = mysql_query("SELECT TEST,COD FROM invest");
$cods =mysql_fetch_array($get_cod); 

post your code so that I can see what you've been doing, because I need to know whether or not you're using these examples correctly. I'll work on a live example to show you what I think you want to do.

[kows]

this is my example. I've created a table that mimics yours (I don't like your shortened titles/abbreviations, so I guessed at what they stood for):

 CREATE TABLE `formarrays` (
`id` INT( 2 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`test` VARCHAR( 15 ) NOT NULL ,
`code` VARCHAR( 15 ) NOT NULL ,
`assign` VARCHAR( 15 ) NOT NULL ,
`general` INT( 3 ) NOT NULL ,
`spell` INT( 3 ) NOT NULL
) ENGINE = MYISAM

with these records (taken from the print_r result you posted):

+----+-----------+------+--------+---------+-------+
| id | test      | code | assign | general | spell |
+----+-----------+------+--------+---------+-------+
| 1  | afb smear | afb  | bac    | 10      | 10    |
| 2  | afb smear | afc  | bac    | 15      | 30    |
+----+-----------+------+--------+---------+-------+

here is my script. let me know if this seems to do what you want. it only has two records, and I didn't make a delete function (since there is no insert, and this data is live). be sure to look at the HTML layout of the form I made by viewing the source! you can change both fields' values of "code" to the same value to get an error message (saying that there were duplicates).

also, in this code I fixed a bug with my duplicate checking script. in my post above, I had this code:

  foreach($_POST as $id => $fields){
    //skip bad stuff
    if(!is_numeric($id) || !is_array($fields)) continue;

    if(isset($myarray[$fields['COD']]))
      $errors[$id] = "COD field given ({$fields['COD']}) already exists";
    else
      $myarray[$fields['COD']] = $myarray[$fields['COD'];
  } 

which defines $myarray[$fields['COD']] as $myarray[$fields['COD']]. this will define $myarray[$fields['COD']] as a null value, making isset() always return false (also, there's a syntax error because I forgot a closing ] bracket!). you can fix it by changing the following line, to the one below it:

//this line
      $myarray[$fields['COD']] = $myarray[$fields['COD'];

//to this line
      $myarray[$fields['COD']] = $fields['COD']; 

let me know!

[sridharao]

Thank you so much for helping me out with your own live forms. I really appreciate it.

OK, after going through your second post, I got some idea about naming the elements. I think $id = $result['COD']; should now be rewritten as $id = $result['ID']; as COD field contain alphabets and ID field is numeric. This would produce the form with elements named the way you did:
<input name="1[test]" value="afb smear" type="text" />
<input name="1[code]" value="afb" type="text" />
By the way, are we creating an array of field names here?

I am curious to know the value of $id in the foreach loop:
foreach($_POST as $id => $fields){
will it be 1,23.... or 1[test], 2[test], 3[test]...

Next, I realised that the user need not have to edit the code field at all, and decided to restrict the user to edit test, general & spell fields only. With this, the whole process of matching the codes for duplicates is eliminated. A big relief!

With that issue settled, I now want to update the fields. Can you please edit and describe your update code with recent changes in mind?
$sql = "UPDATE table SET field1='{$fields['field1']}', field2='{$fields['field2']}', field3='{$fields['field3']}' WHERE id='{$id}' LIMIT 1";
mysql_query($sql);

[kows]

the form is creating an array of IDs, and each ID is an array that holds field values. when submitting the form on my script, your $_POST array looks like:

Array
(
    [1] => Array
        (
            [test] => afb smear
            [code] => afb
            [assign] => bac
            [general] => 10
            [spell] => 10
        )

    [2] => Array
        (
            [test] => afb smear
            [code] => afc
            [assign] => bac
            [general] => 15
            [spell] => 30
        )

)
<?php ?>

which means if you wanted to, you could call a variable like $_POST[1]['code'] to get the value "afb". form arrays work just like arrays in PHP or JavaScript; the square brackets just denote the key. so, in the foreach, $id will be equal to 1 for the first loop, and 2 for the second loop. $fields will be an array, illustrated like this:

    [fields] => Array
        (
            [test] => afb smear
            [code] => afb
            [assign] => bac
            [general] => 10
            [spell] => 10
        )

<?php ?>

this means that you can then reference these like you would a regular array. $fields['code'] produces "afb," and $fields['spell'] produces the number 10.

the update code you have there is exactly what you need, you just need to modify it to have your field names and field values. you just need to change the MySQL field names I reference (field1, field2, field3) and the keys the array references (field1, field2, field3 -- respectively). for example:

$sql = "UPDATE invest SET test='{$fields['test']}', general='{$fields['general']}', spell='{$fields['spell']}' WHERE id='{$id}' LIMIT 1"; 

if you were wondering what the query does, we can look at this SQL more clearly. I'm not sure if this is what you meant by describe, but I figured you might want to know:

UPDATE invest

SET
    test='{$fields['test']}',
    general='{$fields['general']}',
    spell='{$fields['spell']}'

WHERE
    id='{$id}'

LIMIT 1

translation: we are updating the invest table and setting the test field to $fields['test'], the general field to $fields['general'], and the spell field to $fields['spell'], but only if the id field matches $id. we will limit this to update only 1 record.

hope that helps. let me know if you have anymore questions.

[sridharao]

Thank you so much for the explanation, now I understand your concept. It makes coding a lot simpler and short. I am also using id for each element so that it can be accessed by document.getElementById('...').value in javascript for validation. How should they be named, just like the field names? If so, how can I access them in javascript?

Another point; in the script to check for suitable form elements in the foreach loop, you scripted thus:

if(!is_numeric($id) || !is_array($fields)) continue; 

and later in the page you stated, "so, in the foreach, $id will be equal to 1 for the first loop, and 2 for the second loop."
In that case $id will be numeric, so should it not be

if(is_numeric($id) || !is_array($fields)) continue; 

Your first post was all that I wanted, it was already there but it took me so much explanation to understand such a simple thing. I thank you for your patience and goodwill.

Here is my final script, spot anything wrong? coz records are not updating.

$con = mysql_connect('localhost',"root");
mysql_select_db("setenv",$con) or die ("<b><font color=\"#FF0000\">Error: Can not select database or database doesn't exist.</font></b>");
// common code to update as well as delete records
$deleted = 0; 
$updated = 0;
foreach($_POST as $id => $fields){
    if(is_numeric($id) || !is_array($fields)) continue;
    if(isset($fields['delEntry'])){
        $sql = "DELETE FROM invest WHERE ID='{$id}' LIMIT 1";
        $var = "deleted";
    }
    if(isset($fields['btnUpdate'])){
        $sql = "UPDATE invest SET TEST='{$fields['TEST']}', PRICE='{$fields['PRICE']}', SPRICE='{$fields['SPRICE']}' WHERE ID='{$id}' LIMIT 1";
        $var = "updated";
    }
    $query_result = mysql_query($sql) or die(mysql_error());
    if ($query_result){
    $var += mysql_affected_rows();
    }
}
if(isset($fields['delEntry']) || isset($fields['btnUpdate'])){
    echo "<b><font color=\"#00CC00\">Successfully updated ".$updated." and deleted ".$deleted." investigations!</font></b>";
} 

[kows]

In that case $id will be numeric, so should it not be

no. my code is correct. "continue" will continue to the next item in the array if those conditions are not met. you're effectively breaking the loop if you changed that. my code says, if $id is NOT numeric, or $fields is NOT an array, then continue on to the next item in $_POST. this lets you put non-array items in your form without it messing anything up. if you have made this change (which I see you have), then this is the BIGGEST reason for it to not be updating anymore.

and, as to why your code isn't updating, the only thing that you're not doing correctly is your call to mysql_affected_rows(). my double scalar ($) in the original code wasn't a typo ;). $var is set to either the text "deleted" or "updated" because I had made two variables named $deleted and $updated. if $var is set to "deleted," and you define $$var as the number 5, then PHP is essentially changing that line to: $"deleted" = 5;. so, you need to change the line calling mysql_affected_rows() to add to $$var and not $var.

also, you shouldn't check if $fields variables are set in order to display that text. and you don't want to run your foreach() code unless the page has been submitted, either. so, you can simply check to see if the script has been submitted via POST or not! you are also checking if "btnUpdate" is set in order to update the record, and this, I think, is wrong. you should only have one submit button, so the only thing that matters is whether or not that checkbox for deleting was checked. if it was checked, then you're deleting the record; if not, you're updating it. see below:

//has the form been submitted?
if($_SERVER['REQUEST_METHOD'] == "POST"){

  $deleted = 0;
  $updated = 0;

  foreach($_POST as $id => $fields){
    if(!is_numeric($id) || !is_array($fields)) continue;

    if(isset($fields['delEntry'])){
        $sql = "DELETE FROM invest WHERE ID='{$id}' LIMIT 1";
        $var = "deleted";
    }else{
        $sql = "UPDATE invest SET TEST='{$fields['TEST']}', PRICE='{$fields['PRICE']}', SPRICE='{$fields['SPRICE']}' WHERE ID='{$id}' LIMIT 1";
        $var = "updated";
    }

    mysql_query($sql) or die(mysql_error());

    $$var += mysql_affected_rows();
  }

  echo "<b><font color=\"#00CC00\">Successfully updated ".$updated." and deleted ".$deleted." investigations!</font></b>";
} 

I also cleaned up your code just a tad (by getting rid of $query_result). you're already going to kill the script if mysql_query() fails, so your script will either outright fail or will add to $$var.

let me know how that works out.

edit: actually, now that I think about it, for $fields[btnUpdate] to even be set, you would need a button for each and every row on your form. you should probably not do that. it's much simpler having just one button at the end of the form, and you don't need to name it:

<input type="submit" value="Update" />

and I forgot: to access individual rows via JavaScript, yes you would need to use the unique ID to be able to differentiate between rows. but, I would never personally use JavaScript form validation. you're already working with PHP, so validate your form with it instead.

[sridharao]

I had run into problems.

The page contains two other forms besides the one we are working on, hence I have to use

if(isset($_POST['delEntry']) || isset($_POST['btnUpdate'])) 

instead of

if($_SERVER['REQUEST_METHOD'] == "POST") 

as you suggested or else the codes will get executed whenever any of the three forms is submitted.

When I used if(isset($fields['delEntry'])) as you suggested, it aways returned false and the subsequent line

$sql = "DELETE FROM invest WHERE ID='{$id}' LIMIT 1"; 

never got executed resulting in $str remaining null. So, I changed it to if(isset($_POST['delEntry'])) and things worked.

After all the updating records worked smoothly, I tried deleting a single record...and the entire table was wiped out not just the checked ones. All the records were deleted. Something had gone wrong somewhere.

The reason why I have used two buttons is that I am using javascript for form validation, it is a lot quicker and interactive. I worked around to get a situation. Here is the script that successfully serves my purpose:

if(isset($_POST['delEntry']) || isset($_POST['btnUpdate'])){
    $deleted = 0; 
    $updated = 0;
    foreach($_POST as $id => $fields){
        if(!is_numeric($id) || !is_array($fields)) continue;
        if(isset($_POST['delEntry'])){
            if(isset($fields['delete']) && ($fields['delete'] == 1)){ 
                $sql = "DELETE FROM invest WHERE ID='{$id}' LIMIT 1";
                $var = "deleted";
                mysql_query($sql) or die(mysql_error());
                $$var += mysql_affected_rows();
            }
        }
        if(isset($_POST['btnUpdate'])){
            $nv = mysql_real_escape_string($fields['TEST']);
            $sql = "UPDATE invest SET TEST='{$nv}', PRICE='{$fields['PRICE']}', SPRICE='{$fields['SPRICE']}' WHERE ID='{$id}' LIMIT 1";
            $var = "updated";
            mysql_query($sql) or die(mysql_error());
            $$var += mysql_affected_rows();
        }
    }
    if($updated != 0){
        echo "<b><font color=\"#00CC00\">Successfully updated ".$updated." investigations!</font></b>";
    }else{
        cho "<b><font color=\"#00CC00\">Successfully deleted ".$deleted." investigations!</font></b>";
    }
} 

Some text in the field contains a single quote (Albert's stain) and the SQLfailed because of that. I used mysql_real_escape_string to avoid the error, is it the right way to do it?

I have another query here; The page is lengthy and has three forms, the current one is at bottom. When the form is posted and the same page is loaded, it shows the top of the page. Is there no way to direct the page to a defined bookmark to any location of our choice?

[kows]

yes, you should always be using mysql_real_escape_string() on -any- data collected by a user.

now, just because you have three forms doesn't mean you shouldn't actually check if the form has been submitted. you just need to identify the forms, and then you can process whichever one was submitted. the <a> tags here should also provide a "bookmark" to direct your page to once submitted (take note of the form's action, "thispage.php#1", as it should bring the page down to where ever you laid the <a> named "1"). your forms could look like this:

<a name="1"></a>
<form action="thispage.php#1" method="post">
  <input type="hidden" name="form" value="1" />
  <input type="submit" value="Submit Form #1" />
</form>

<a name="2"></a>
<form action="thispage.php#2" method="post">
  <input type="hidden" name="form" value="2" />
  <input type="submit" value="Submit Form #2" />
</form>

<a name="3"></a>
<form action="thispage.php#3" method="post">
  <input type="hidden" name="form" value="3" />
  <input type="submit" value="Submit Form #3" />
</form>

then, when processing the information:

<?php
  if($_SERVER['REQUEST_METHOD'] == "POST"){
    switch($_POST['form']){
      case 1:
        //process form 1 here
        break;

      case 2:
        //process form 2 here
        break;

      case 3:
        //process form 3 here
        break;
    }
  }
?>

personally, I just wouldn't put three forms on the same page -- especially if they are lengthy, like you're saying they are. it can get very, very messy.

now, my original script makes things much easier than you're making them. to illustrate that, let's say that the user is presented with a form which has 10 rows, each row containing several columns of data and one column with a checkbox labeled "delete." the idea is that the user can UPDATE data and DELETE records at the same time, without needing to press a specific button. even if you are using javascript validation (which is still a bad idea, and not any more interactive than PHP could be by the way, though it may not be immediate -- but I won't get into that), there is no need to have two buttons on your form. it would be much more simple if you just did this:

    $deleted = 0;
    $updated = 0;
    foreach($_POST as $id => $fields){
        if(!is_numeric($id) || !is_array($fields)) continue;

        if(isset($fields['delete'])){
          //checkbox was checked. we delete this record.

          $sql = "DELETE FROM invest WHERE ID='{$id}' LIMIT 1";
          $var = "deleted";
        }else{
          //checkbox was not checked. we update this record.

          $nv = mysql_real_escape_string($fields['TEST']);
          $sql = "UPDATE invest SET TEST='{$nv}', PRICE='{$fields['PRICE']}', SPRICE='{$fields['SPRICE']}' WHERE ID='{$id}' LIMIT 1";
          $var = "updated";
        }
        mysql_query($sql) or die(mysql_error());
        $$var += mysql_affected_rows();
    }
    echo "updated $updated records and deleted $deleted records."; 

the point of this is to not be repetitive with your code (you are defining $sql twice, calling mysql_query() twice, and adding to $$var twice. you instead could define $sql twice and do only one call to mysql_query() and mysql_affected_rows(), like in my original example). you're also skipping the entire point of $var and $$var being used; they were made to make the call to mysql_affected_rows() be dynamic, adding only to the variable that needed to be added to. there's no point in having $var defined or using $$var in the code you have there (though I made proper use of them in the example above). also, your checking of $fields['delete'] is redundant. a checkbox is either set or not set (set if it was checked, not set if it was not checked). it doesn't matter what the value of it is (at least not for what you're doing), so you can safely only check if $fields['delete'] is set and not check whether it is set to 1 or not. I changed this in the example above.

[sridharao]

I agree with you that having multiple forms in single page is not a good idea but then their functions are so closely related that putting them on separate pages is unwarranted and will be inconvenient to the user. The first form is for inserting records, the second one is just a list & button to display the records in order chosen by the user and third one is for editing the records.

The reason I chose to have two buttons is purely for javascript validation, which is a lot quicker and most importantly I can use confirm() function to decided the course. Since it has no equivalent in PHP, I am sticking to it. I understand that javascript validation is not good idea because either the javascript may be turned off in a browser or the script can be manipulated by the user. Unless, I am convinced of a better way to achieve the same in PHP, I am not confident of dropping it.

I mentioned in my last post, all the records were deleted even though a limit was set. I wonder why!

Using switch and case is excellant, I will incorporate it. Since the form action is set to action= "<?php echo $_SERVER['PHP_SELF']; ?>", how can I incorporate the <a> link? Upon submission, the same page is loaded, how can I direct it to the bookmark?

You have a sound programming etiquette quite unlike me. Most importantly, you have tremendous patience in dealing with "dumb" people. I have never come across anyone like you who has stuck around trying to "deal" with me for so long. You are indeed great!

[kows]

like I said in my previous post, just because you're validating your form with javascript doesn't mean that you need to have two buttons. your one and only button can validate the entire form (with a confirm box, if you wish).

you could make the user confirm if any checkboxes have been checked by doing something similar to this:

<script language="javascript">
  function validate_form(form){
    var deleted = 0;
    //submit starts true so that if the user is deleting no records, it will submit.
    var submit = true;
    for(var i = 0; i < form.elements.length; i++){
      if(form.elements[i].type == "checkbox" && form.elements[i].checked == true){
        deleted++;
      }
    }
    //if deleted is above 0 and submit is true, make the user confirm delete
    if(deleted > 0 && submit){
      submit = confirm("Are you sure you want to delete " + deleted + " records?");
    }
    return submit;
  }
</script>

though, since this one function would be validating your whole form you should also add in your own code. because I was already looping through all of the elements in the form, you can simply check if elements are empty within that loop (rather than needing to check them individually), assuming you aren't using a loop already! if you're adding your own code, then you can just set submit to false if there are any empty/incorrect values. you can implement it like:

<form name="form_name" action="whatever" method="post" onsubmit="return validate_form(document.thisForm)">
  <!-- blah blah blah -->
  <input type="submit" value="Update records" />
</form>

and, just a note: I totally didn't test that code after writing it out, but it looks to me like it should work. and I won't get into anymore javascript from now on.

when you're submitting your form using php_self, then you just need to add the hash and anchor's (<a>) name after you get out of php. like:

<form action="<?php echo $_SERVER['PHP_SELF']; ?>#1">

so, if all of the records are being deleted, then your code is always returning true. it doesn't matter if a limit is being set if every single time you're checking something it returns true. just in case you've changed anything, post the HTML of your form (when viewing source on your form's page -- but only the form, and just most of it, not all of the data), and post your processing stuff for deleting.

[sridharao]

Thank you so much, you have solved all my problems!

Off the topic, I experimented with sessions, before I post the code for your valuable opinion, I must know if that should be started in a new thread.

[kows]

it doesn't matter to me if you post it in this thread or not. go right ahead!

[sridharao]

I am in the "web-assisted-forum-supported-self-learning" process of PHP. I figured out a way of using sessions. I have created a simple form that asks for name and age. When the form is submitted the entered data is held in sessions variables and the script checks for the name, if it matches "Rao", session is destroyed and the form continues. Else, an error message is displayed. Usually when the form reloads the data doesn't persist, hence the use of session.

It must be "crude" but it apparently serves the purpose, please let me know how best the same can be achieved.

<?php session_start(); 
        $tn = "";
        $ta = "";
?>
<html></head></head><body>
<?php
    if(isset($_POST["submit"])){
        $_SESSION["Name"] = $_POST['naam'];
        $_SESSION["Age"] = $_POST['age'];
        if($_POST['naam']=="Rao"){
            echo "Welcome";
            $tn = "";
            $ta = "";
            session_destroy();
        }else{
            $tn = $_SESSION["Name"];
            $ta = $_SESSION["Age"];
            echo "Sorry wrong name, please re-enter";
        }
    }
?>    
<form name="try" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<input type="text" name="naam" size="10" value="<?php echo $tn;?>">
<input type="text" name="age" size="3" value="<?php echo $ta;?>">
<input type="submit" name="submit" value="Submit">
</form>

</body></html>

[kows]

I'm not entirely sure what your script does (aside from the obvious). I mean, you can just not set the session at all if the name is wrong?

<?php
  session_start();
  if($_SERVER['REQUEST_METHOD'] == "POST"){
    if($_POST['name'] == "Rao"){
      echo "welcome!";
    }else{
      $_SESSION['name'] = $_POST['name'];
      $_SESSION['age'] = $_POST['age'];
      echo "wrong name.";
    }
  }
?>
<form method="post" action="script.php">
  <input type="text" name="name" />
  <input type="text" name="age" />
  <input type="submit" value="Submit" />
</form>

but I really don't understand why you'd want to do this.

[sridharao]

A session is initialised when the page loads for the first time. As the page loads, the session still is empty. User enters name and age and submits the form. Both these are saved in session variables. If the name entered is not "Rao" (data verification fails), the same form is presented back to the user for correction; not blank but with the data that was entered before submisstion, that is the data was retained. Sessions variables are used to fill the data in the form elements if the condition is false. If the condition is true, elements are cleared and session destroyed.

Use of name is just a silly example here, the idea is to return the form back to the user with data intact in the form fields.

[kows]

uh.. you're using sessions wrong then. sessions are used to store temporary information on the server (rather than long term information on the client, with cookies), and are used to store things like login information, cart information for shopping website, or other things like that. they are -not- used to retain information on a form, unless your form spans multiple pages, and even in that case I would think it's better to just use hidden input fields to pass the information along. you can do what you want by doing this:

<?php
  //by default, we show the form
  $showform = true;

  //array for retaining information
  $retain = array();

  if($_SERVER['REQUEST_METHOD'] == "POST"){
    if($_POST['name'] == "Rao"){
      $showform = false;
    }else{

      //store the entire post array in $retain to show on the form
      //this is only really useful if you have lots of form fields.
      foreach($_POST as $key => $value){
        $retain[$key] = htmlentities($value);
      }

      echo "you entered the wrong information!";
    }
  }

  //should we show the form?
  if($showform):
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  <input type="text" name="name" value="<?php echo $retain['name']; ?>" />
  <input type="submit" value="Submit" />
</form>
<?php else: ?>
welcome!
<?php endif; ?>

[sridharao]

Thank you so much, it did not occur to me that this issue could be solved by a simple method like using an array to hold the values temporarily. I have this strange ability to get simple things complicated.

I am developing a database based application for my hospital laboratory. I had achieved it in past using foxpro 2.6. I feel I have to redesign the the table structure and their relations. I have the backbone ready, I need your valuable suggestion from you without disclosing the project to the whole world. Can I send you an email instead?

[kows]

sure. david [at] cowarama [dot] com works, or send me a PM on these forums.