Jonatan Stenbacka Jonatan Stenbacka - 1 year ago 35
SQL Question

How to submit and handle values from a form inside a table

I have a HTML table with an arbitrary amount of rows, each with 4 columns. And the idea is to let the user edit any of these rows and then save the result. These changes should then be saved to a database. But I have several problems.

I have found some posts on stackoverflow indicating on how you should approach this, but none perfectly useful in my case. This is how it looks when the user is editing my table (although I only have one row now, there can be several).

my table

And of course there's a submit button, that takes my to a php file named something like "handle_edit.php".

Now another post on stackoverflow about this subject has indicated that I should use some kind of foreach-loop to edit one row in the SQL table per row in my HTML table. My version of this loop in "handle_edit.php" looks like this right now:

foreach( $_POST['appearances'] as $appearances ) {
$goals = $_POST['goals'];
$assists = $_POST['assists'];
$name = ??????? //How to I do this?

mysqli_query($connection, "UPDATE players
SET appearances=$appearances, goals=$goals, assists=$assists
WHERE name=$name");

First of all. The
loop needs to contain an array expression, and the compiler tells me that's not the case right now. But I have no idea on how to count the amount of rows. In the thread I linked above they are doing something similar to this, but I don't think I fully understand what they are doing since my
loop isn't working.

Secondly, since the name column in the table isn't an input field, I don't know how to send it to this page with the rest of the information. I need to do it somehow, since I want to determine which row to edit based on the name provided. Each of the players in this SQL table has an unique player id, could I use that somehow?

Am I maybe approaching this the wrong way? Is there a more viable solution?

Please tell me if you need any additional code.

Thanks in advance!

Answer Source

First of all, the name variable. you need to add <input type='hidden' name='name' value='<*YOUR WAY OF GETTING THE NAME*>'/> to your form. if you do so, you can get the value of the name in your foreach loop with $_POST['name']. For more info on how to make a form for php look here:

Secondly, the sql string u use is wrong. You cant input the variable names within a string. I suggest declaring the sql string first like this:

$sql = "UPDATE players SET appearances=" . $appearances . ", goals=" . $goals . ", assists=" . $assists . " WHERE name=". $name;

Then make the connection and execute the query like so:

mysqli_query($connection, $sql);

for the foreach problem, i suggest debugging using the var_dump() method and see if you really get an array back with $_POST['appearances'].

I hope this helps.