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
LVL 1
LB1234Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SStoryCommented:
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:
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
0
Julian HansenCommented:
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.
0
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">

<table>
  <tr>
<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>

 
  </tr>
 
<?php

$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
?>

<?php
//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)) {
?>

 <tr>
 <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>
 
 </tr>
 
 
<?php
}
?>

      
</table>

<input type="submit">
</form>
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

COBOLdinosaurCommented:
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

Cd&
0
LB1234Author Commented:
Ah, thanks COBOL!

Can you please shed some light on the PHP code  and SQL statement I'd use?
0
SStoryCommented:
>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.
0
Chris StanyonWebDevCommented:
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'];
endforeach;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Julian HansenCommented:
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'" ;
    mysqli_query($query);
}

Open in new window

The primary key will be the index of the array.
0
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!
0
Ray PaseurCommented:
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!
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.