Setting up unique names (for MySQL functionality) for each entry in a table pulled from a database

Please look at the attached image.  Each row in this table is being pulled from a database.  But in order to save the entered information (various notes entered, whether something has been checked "done" or not, and the entered value for the drop down list shown here) each element on each line must have a unique name for the processing page right?  So the first line, would have a form name of "note1" and "note2" -- they cannot both be called "note" correct?  Conceptually, how do I accomplish this?  A foreach statement or something?

table picture
Who is Participating?
Chris StanyonConnect With a Mentor Commented:
As a follow on from COBOL, if you are using IDs in your Database (which you should be), then include that in your input names and use a slightly different format. For each table row:

<?php $ID = $expenses["ID"]; ?>
<textarea name="record[<?php echo $ID ?>][notes]"></textarea>
<input type="text" name="record[<?php echo $ID ?>][done]">

Open in new window

This will effectively give you an array of arrays in the POST data

record[1][notes] = "something"
record[1][done] = true

record[2][notes] = "something else"
record[2][done] = true

And you can loop through it like so:

foreach ($_POST['record'] as $recordId => $recordDetails) :
    //now update your database, record by record.
    echo $recordId;
    echo $record['notes'];
    echo $record['done'];

Open in new window

SStoryConnect With a Mentor Commented:
You need a unique primary key and then it does not matter. An auto incrementing number field would do. If this database is to be replicated or merged with other copies in various other sites, then using a GUID for the key field will ensure absolute uniqueness.

Check out the id field in the following:
Julian HansenConnect With a Mentor Commented:
What does the target table in MySQL look like - does it have a primary key defined?

Are you updating or adding

If adding then you can create the primary key (or have the DB do it for you in case of an autonumber) on the server side.

In terms of laying out your form consider using arrays for the form element names then you don't have to worry about duplicate names - each additional row is added to the array on POST

We will need more information on your setup.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LB1234Author Commented:
This table is actually an HTML form populated with info being pulled from an MYSQL database.  I'd like people to update the form info, hit submit and then the data for each row of the form is saved back into the database.

Here's the code for the entire thing.  

<form action="process_table.php" method="post">

<th scope="col">Date</th>
<th scope="col">Amount</th>
<th scope="col">Vendor</th>
<th scope="col">Description</th>
<th scope="col">Type</th>
<th scope="col">Notes</th>
<th scope="col">Trip</th>
<th scope="col">Last Updated</th>
<th scope="col">Last Updated By</th>
<th scope="col">Done</th>


$get_transactions = "SELECT * FROM transactions order by date ASC LIMIT 10";
$transactions_result_set = mysqli_query($connection, $get_transactions);

$get_types = "SELECT * FROM type";
$type_result_set = mysqli_query($connection, $get_types);


<?php // this section creates the entire table
//the line below creates each record and puts it in a table

//this opens the tag
$type = "<select>";
//this while statement turns each entry from the database into value
while ($row = mysqli_fetch_assoc($type_result_set)) {
//this statement appends to the existing variable    
       $type .= sprintf("<option value='%s'>%s</option>", $row["type_name"], $row["type_name"]);
$type .= "</select>";

<?php while ($expenses = mysqli_fetch_assoc($transactions_result_set)) {

 <td><?php echo date("F d, Y", strtotime($expenses["date"]));?></td>
 <td><?php echo number_format($expenses["amount"], 2);?></td>
 <td><?php echo $expenses["vendor"];?></td>
 <td><?php echo $expenses["description"];?></td>
 <td><?php echo $type; ?></td>
 <td><textarea name="notes" id="notes"></textarea></td>
 <td nowrap="nowrap">Trip place holder</td>
 <td><?php echo date("m/d/Y")?></td>
  <td>User Name</td>
<!--  <td><?php// date("F d, Y");?></td>-->
 <td><input name="done" type="checkbox" id="done"></td>


<input type="submit">
It looks like the textarea is the field you need the array for so do it this way:

<textarea name="name[]"...

Then when it is sunmited you will have an array in the $_POST variables and can reference as:

$_POST['name[0]'], $_POST['name[1]'], etc

LB1234Author Commented:
Ah, thanks COBOL!

Can you please shed some light on the PHP code  and SQL statement I'd use?
SStoryConnect With a Mentor Commented:
>I'd like people to update the form info, hit submit and then the data for each row of the >form is saved back into the database.

For this you need a unique identifier called a Primary key. It could be an auto incrementing number. That could be shown or a hidden field on the form. Then when they submit the form you'd get the data and use SQL to do an update...something like

UPDATE thetablename
SET fieldx=valuex,fileldy=valuey
WHERE IDField=idfieldvalue

Whatever the IDfieldvalue was when submitted would help this only update the record in question.
Julian HansenConnect With a Mentor Commented:
What does the table in the database look like - can you give us a full description of the fields and types.

Specifically we need to see if there is a primary key defined on the table. You will need to include this in the row identifier - one option is to do it like this -
<textarea name="note[<?php echo $row['id'];?>]"></textare>

Open in new window

Assumes that your table has a primary key id - which results in this
<textarea name="note[1]"></textare>
<textarea name="note[2]"></textare>
<textarea name="note[3]"></textare>

Open in new window

When you get the data back from the post
$note = $_POST['note'];

Open in new window

You can cycle through the notes like so
foreach($note as $key => $n) {
    $query = "UPDATE transactions  SET note = '$n' WHERE id = '$key'" ;

Open in new window

The primary key will be the index of the array.
LB1234Author Commented:
The only time during any given day when I feel like a complete idiot is when I'm trying to code/learn  PHP.  I'm a network administrator learning this stuff on my own and PHP is my first language.  I should be compensated for the ongoing battery of my ego!  Guys, I will pore over the helpful code provided and try to understand it. Will get back to you soon, thanks!
Ray PaseurConnect With a Mentor Commented:
Give yourself a little time with this article, following the links and maybe buying one or two of the recommended books (they teach exactly what you're doing here, called "table maintenance").  It will help keep you away from the many bad code examples that litter the internet!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.