Displaying Data from mysql table

Question

I have created this form using the while loop so that i dont have to make 28 text field ... but when i submit the data into my mysql database it works well but how to display the data back to my form for edit and update .. when i type a value to a text field (EX - submitted data from mysql in emp_name field) then it repeated 4 times in the text field .... i know it is happening because of loop but is there any way that i can display multiple data in each text field for updating after submitting data by the user as normal ....

my form.php

<form action="userdata.php" name="frmAdd" method="post">

<table width="80%" border="0" cellpadding="3" cellspacing="3" class="forms">
  <tr>
    <td width="5"> <div align="center">NO</div></td>
    <td width="91"> <div align="center">Employer's NAME</div></td>
    <td width="160"> <div align="center">COUNTRY</div></td>
    <td width="198"> <div align="center">POSITION</div></td>
    <td width="70"> <div align="center">FROM</div></td>
    <td width="70"> <div align="center">TO</div></td>
    <td width="70"> <div align="center">SALARY</div></td>
    <td width="70"> <div align="center">REASONS FOR LEAVING</div></td>
  </tr>

  <?php for($i=1;$i<=4;$i++) { ?>

  <tr>
    <th width="5"> <div align="center"><? echo $i . "."; ?></div></th>
    <td><input type="text" name="emp_name<?=$i;?>" size="25" value="submitted data from mysql"></td>
    <td><input type="text" name="emp_country<?=$i;?>" size="10"></td>
    <td><input type="text" name="emp_pos<?=$i;?>" size="10"></td>
    <td><input type="text" name="emp_frm<?=$i;?>" size="5"></td>
    <td><input type="text" name="emp_to<?=$i;?>" size="5"></td>
    <td><input type="text" name="emp_sal<?=$i;?>" size="5"></td>
    <td><input type="text" name="emp_lev<?=$i;?>" size="25"></td>
  </tr>
  <?php } ?>
  </table>
  </br>
  <input type="submit" name="doHis" value="Save Employment History">
  <input type="hidden" name="hdlfrm" value="<?=$i;?>">
  </form>

and my userdata.php

    if($_POST['doHis'] == 'Save Employment History')  
    {
        try{
                $conn = new PDO("mysql:host=localhost;dbname=dbname", "user", "pass");
            }
            catch(PDOException $pe)
                {
                    die('Connection error, because: ' .$pe->getMessage());
                }

                for($i=1;$i<=$_POST["hdlfrm"];$i++){

                        if($_POST["emp_name$i"] != ""){

                            $sql = "INSERT INTO emp_table (emp_name, emp_country, emp_pos, emp_frm, emp_to, emp_sal, emp_lev) 
                                    VALUES (:emp_name, :emp_country, :emp_pos, :emp_frm, :emp_to, :emp_sal, :emp_lev)";
                            $stmt = $conn->prepare($sql);
                            $stmt->bindParam(':emp_name', $_POST["emp_name$i"]);
                            $stmt->bindParam(':emp_country', $_POST["emp_country$i"]);
                            $stmt->bindParam(':emp_pos', $_POST["emp_pos$i"]);
                            $stmt->bindParam(':emp_frm', $_POST["emp_frm$i"]);
                            $stmt->bindParam(':emp_to', $_POST["emp_to$i"]);
                            $stmt->bindParam(':emp_sal', $_POST["emp_sal$i"]);
                            $stmt->bindParam(':emp_lev', $_POST["emp_lev$i"]);
                            $stmt->execute();

                                        echo "Save Done.  Click <a href='phpMySQLListRecord.php'>here</a> to view.";
                                }
}



    }

and here is the snapshot

enter image description here

You are pretty much asking for a SQL injection attack, use [prepared statements](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php), and don't use the `mysql_*` extensions, [they are deprecated](http://php.net/manual/en/changelog.mysql.php) — doublesharp, May 14 '13 at 20:39
yes the code has security issue but at this moment this is lets truse the user, me ... — fahim74, May 14 '13 at 20:47
This is not a trust issue, this is a case of doing it wrong. If you don't develop a discipline for escaping **any** and **all** user data, you will inevitably get yourself into very serious trouble. You'll also be battling escaping problems, missing quotation marks, and encoding issues that are solved by switching to PDO or another database layer with [proper SQL escaping](http://bobby-tables.com/php). You'll have to re-write every last bit of this when the `mysql_query` method is removed from PHP, as well, which is the plan. — tadman, May 14 '13 at 20:53
^ and it's not worth debugging code that has this many fundamental issues. — doublesharp, May 14 '13 at 20:54
@doublesharp .... i forgot mysql_* ..... tadman .... issue is gone .... and can i have any solution now .... ? !! — fahim74, May 14 '13 at 21:37
@KiroConeski the source of the output http://pastebin.com/z2Q1q5v3 and also a snapshot added ... — fahim74, May 14 '13 at 21:44

score 0 · Accepted Answer · answered May 14 '13 at 22:01

The problem you have is your data model - you are saving employment history, but for who? It needs to have an employee_id somewhere to say who this data belongs to, and join it to a table that saves the employee_table data. Next you need to have a unique ID on the emp_table to identify each row, which you can use instead of your $i index.

TABLE

history_id INT autoincrement | employee_id INT | emp_name VARCHAR | your other fields....

SELECT

SELECT history_id, emp_name, ... FROM emp_history where employee_id = ?

You can then loop over the results and use the history_id to identify the row that the data belongs to to update it. Submitting multiple fields with the same name will be an array you can iterate over, so you don't need to use a unique field name for each.

As a side note, you probably want to use isset($_POST["key"]) instead of $_POST["key"] != "" to check if a field was submitted.

well little bit of confused here .... emp_table will have a unique id `history_id` like 1 then the emp_id for the each 4 rows will be the same like 54 .. am i right and then ... ? — fahim74, May 14 '13 at 22:15
No, the ID would be per row in table, so it will be a unique identifier for that row which you can use do do an update. — doublesharp, May 15 '13 at 00:02
well your advised worked well .. thx ..bro but still have some problem ... and still working on it ... — fahim74, May 16 '13 at 19:57

Displaying Data from mysql table

1 Answers1