How to edit and insert dynamicaly created form textboxes values to MySQL DB?

Question

I'm having a bunch of features table in MySQL DB in my PHP project. I'm fetching through php and creating a table with textboxes named by column name and id of record in photos table using code:

functions.php Code:

function m_html_editPhotos($id) {
$result = "<table class=\"tabelka\" id=\"tbl_photos\"><thead><tr><th>Miniaturka</th><th>Duże zdjęcie</th></tr></thead><tbody>";
$mysqli = m_db_Init();
$qry = "SELECT ID, Img_Min, Img_Nrm FROM tbl_Zdjecie WHERE ID_Grzyb = ?";
if($stmt = $mysqli -> prepare($qry)) {
          $stmt -> bind_param("i", $id);
          mysqli_stmt_bind_result($stmt, $img_id, $img_min, $img_nrm);
          $stmt->execute();
          $i =0;
          while (mysqli_stmt_fetch($stmt)) {
              $i = $i+1;
              $result .= "<tr><td><!--<label>Link do miniaturki:<label>--><input type=\"text\" class=\"required\" name=\"photo[$i][min]\" value=$img_min></td><td><!--<label>Link do zdjęcia pełnego rozmiaru:</label>--><input type=\"text\" class=\"required\" name=\"photo[$i][nrm]\" value=$img_nrm></td><td style=\"display:none;\"><input type=\"text\" name=\"photo[$i][id]\" value=$img_id /></td></tr>";
          }
          $stmt -> close();      
}
mysqli_close($mysqli);
$result .= "</tbody></table><div class=\"link\" onclick=\"AddPhotoEditRow();\">Dodaj kolejne zdjęcie</div>";
return $result;
}

Now what I'd like is to edit photos table iterating through each row of generated as above table with textboxes for thumbnail_url (img_min) and full size link (img_nrm) Additionally I'd like to add new ones to the table from dynamically created rows by function AddPhotoEditRow();

functions.js Code:

function AddPhotoEditRow(){
 $('#tbl_photos > tbody:last').append('<tr><td><input type="text" name="photo[0][min]"></td><td><input type="text" name="photo[0][nrm]"></td><td style=\"display:none;\"><input type=\"text\" name=\"photo[0][id]\" value=\"0\" /></td></tr>');
}

edit.php Code:

include 'functions.php';
if(isset($_POST["change"])) m_db_updateAllFeatures($_GET["id"]);
if (m_db_isAdmin("")){
if (!isset($_GET["id"]) || !is_numeric($_GET["id"]))
    header("Location: ../index.php");
else {
if (isset($_POST["Nazwa_PL"]))
    m_db_UpdateName("PL", $_GET["id"],$_POST["Nazwa_PL"]);
if (isset($_POST["Nazwa_Lac"]))
    m_db_UpdateName("Lac", $_GET["id"],$_POST["Nazwa_Lac"]);
render_edit_body();
    }
}

I'd like to iterate somehow through photos links and update existing records by parsing textbox names passed through $_POST, additionally would be good to insert new photo links (that with id=0). I'm setting new rows' id to 0 because I need to distinguish if I'm inserting to table, or updating, right? I've assumed that all 0-indexed fields should be added, rest of them should be inserted. I might have my conception wrong, if there is a better way to do full functionality to that table "control" then I'm very open to suggestions.

Answer 1

If you use 0 for all newly created HTML elements, the PHP $_POST will contains just the information of the last item.

This is my solution using the same approach as yours with a bit of modification, supposed that we have a form wrap outside out loop and an array named $data contains the seeding information:

PHP code to create the table

// the $data array contains a list of picture object, these object will be updated later.
foreach($data as $num => $row)
{
    echo "<tr>\n";
    echo sprintf("<td><input type='text' name='name[%d]' value='%s' /></td>\n", $num, $row['name']);
    echo "</tr>\n";
}
echo "<tr><td>\n";
echo "<a id='add-item'>Add new item</a>\n";
echo "<input type="hidden" name='num-created' value='0' />";
echo "</td></tr>\n"

JavaScript code to add new element (using jQuery)

$(document).ready(function(){
    $('#add-item').click(function(){
        var currentCreatedNum = $("input[name=num-created]").val() * 1;
        var html = "<tr>";
        html += "<td><input type='text' name='newname["+ (currentCreatedNum + 1) +"]' value='' /></td>";
        html += "</tr>";            
        $("input[name=num-created]").val(currentCreatedNum + 1);
    });
});

The PHP code to manipulate the POST request

# check contrains ...

# update old records
foreach($_POST['name'] as $id => $name)
{
    // get information of existing item and update this record

}

# create new record
foreach($_POST['newname'] as $order => $name)
{
    // get information of new item and create a new record

}

As you can see, the name attribute of manual created elements must be different from the existing one.

Answer 2

If I'm understanding, you're wanting to access each dynamically created table item? You can do this by simply creating a dynamic array. As each new TD element (or whatever element) is generated, make the name like name=Something[] (with empty brackets). This will automatically assign each item generated as a member of the array called "Something". You can of course generate key values through a counter as well.

Here's a simplified example from a project I did, where an "Update" button (not shown here) posts back to the same page:

echo "<td class='grid'>
<input type='checkbox' name='chkDel[]' />
</td>";

Then the PHP would be as follows:

if (isset($_POST['chkDel'])) {

    // Make a MySQL Connection
    $con = mysql_connect(host,user,pwd) or die(mysql_error());
    mysql_select_db("db_name"); 

    $rows = 0;

    foreach($_POST['chkDel'] as $chkdel)  {

        //query to delete post
        $delQuery = "DELETE FROM table WHERE ".$chkdel;

        //run the query 
        mysql_query($delQuery) or die(mysql_error());

        //Get rows affected
        $i = mysql_affected_rows();
        $rows = $rows + $i;
    }

    mysql_close($con);
}

Is that what you're looking for?

Answer 3

You've got the basics, except

$('#tbl_photo snip snip name="photo[0][min]"> snip snip snip
                                    ^---

the 0 should not be hardcoded. You're telling php to create an array of 'photo' values in the $_POST array, but then you force the index keys to be 0 for all entries, causing later ones to overwrite earlier ones.

Keep a count in Javascript as to how many of these fields you've inserted, and use that count to increment the index key, e.g.

$('#tbl_photo snip snip name="photo[' + i + '][min]"> snip snip snip

instead, where i is the field count. Then it's a simple matter of:

foreach($_POST['photo'] as $i => $photo) {
    echo $photo['min'];
}

to get that min value for each photo field.