Inline Edit HTML Table With PHP

[rosstafarian]

Hi guys,
Im going to properly learn PHP by doing a little project that ive made up.
I know the basics of PHP but i havent really done much of it.

I need to know how to go about doing what im going to outline below. Please note, i dont want any code doing, i just want to know the logic behind it so i can code it.

Basically, im want to pull information from a database and display it in a table (THIS BIT I CAN DO).
The table the data goes into will be structed something similar to:

FIRST NAME - LAST NAME - AGE - SCORE

The score changes every week. I want to be able to have a table where the last column (score) is a textbox.
I can add in a textbox myself but i want to know how to then update that data in the database.
How will i know which person to update the data of?

From reading ive been seeing people mention having "ID's" but how would this come into play?

Some Psuedo code or general logic behind it would be great so i can start thinking and coding.

Cheers guys

[kaizokupuffball]

If i understood right, you should take a look at the while loop. ^^
You would do the SQL query, then while the row is fetched, echo it out. :) Im not very good at explaining, but read some of the articles on the site below :)
http://php.net/manual/en/control-structures.while.php

[kaizokupuffball]

Also, you can order the rows in SQL query, the highest ID number is the lateste inserted row. So you could make the SQL query like this:

SELECT blabla FROM table ORDER BY id DESC

Desc = Descending
Asc = Ascending

[rosstafarian]

ahh i see, that makes sense. Should have been obvious lol
Cheers

[rosstafarian]

One thing i dont get is even when the data is ordered in the list, how do you make it so it knows that that specific textbox updates that row?
Cant get my head around it

[broj1]

Give each textbox a name that has and ID of the record in it (hopefully you have a primary key in the table, you can use it for this purpose). If you have any code yet I can show you what I mean. Or if I put it in a made-up example:

// reading the rows from database
while($row = $result->fetch_row()) {

    $input_name = $row['id'];
    $input_value = $row['score'];

    ...
    echo "</td><input type=\"text\" name=\"input_name\" value=\"$input_value\" /></td>";
    ...
}

[rosstafarian]

ahhhhhh i see now.

No code at the moment, just trying to plan it all out at the moment.
One thing i learnt from school is to always have a plan first lol

[broj1]

Sure. It is good to have a firm plan before you start coding. Some of us do not have this privilege and have to code for living without a detailed plan (or specifications). These days they call it agile programming :-).

Anyway, come back if you are stuck with coding.

[rosstafarian]

cheers mate, much appreciated. You might regret saying that over the coming days ;) hehe

[rosstafarian]

Quick question mate,
All the tutorials ive seen tell me to use the following:

mysql_fetch_array($result))

Is there any reason that you use:

while($row = $result->fetch_row()) {

?

Cheers

[broj1]

Sorry to not point it out:

In the example I have used mysqli extension which is newer than mysql extension and is preffered over mysql. See this topic.

mysqli extension comes in two favours: procedural style and object oriented style in other words all functions have both variants and it is up to you to choose which one to use. Here you can see examples how to use mysqli to query a database procedural way and object oriented way. I used the later in my post above.

[rosstafarian]

Cheers for the clarification! More reading for me then! haha

[rosstafarian]

Im trying to add the data back into the database after being changed by the text box.
I know the SQL syntax (been doing vb.net for a few years) but i cant figure out how to make it put the data in the right place.
Heres the code so far:

while($row = mysql_fetch_array($result)){

$input_name = $row['id'];
$input_value = $row['handicap'];

echo"<tr>";
echo"<td>" . $row['ID'] . "</td>";
echo"<td>" . $row['First'] . "</td>";
echo"<td>" . $row['Last'] . "</td>";
echo"<td>" . $row['handicap'] . "</td>";
echo "<td><input type=\"text\" name=\"$input_name\" value=\"$input_value\" /></td>";
}
echo"</table>";

echo"<input type=\"submit\" name=\"submit\"/>";
//endwhile;
if (isset($_POST['submit'])) {

mysql_query("UPDATE members SET handicap='$POST['$input_name']' WHERE $_POST['$input_name'] = '$row['ID']'");

}

mysql_close($con);

?>

Now obviously i cant use the $input_name for both.
How do i do it so it knows which row to update?

Cheers

[rosstafarian]

Had a blonde moment.

Got this kind of working:

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

mysql_query("UPDATE members SET handicap='$POST[$input_name]' WHERE $input_name = '$row[ID]'");

}

mysql_close($con);

Now off to fix the submit button which doesnt want to actually do anything

[broj1]

This is a bit tricky. Simple thing would be to update all displayed rows (either changed or not changed) but that is a bit redundant:

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

    // ditch the submit element from $_POST
    unset($_POST['submit']);

    // loop through values in $_POST and shoot the query for each value
    // (it would be more efficient to use prepared queries here)
    foreach($_POST as $key => $val)
        mysql_query("UPDATE members SET handicap='$val' WHERE id=$key LIMIT 1"); 
    }
}

Above code does to mich work for nothing.

So you have to know which row has been changed. You can do that with a hidden field added to each row. The hidden field would contain the id of the row in it's name and would have a value of the existing textbox. Then when processing form you could compare the text input value with the hidden field value and if they differ update that row. Another approach would be using ajax to update a row upon leaving textbox (using an onblur javascript event). To give you examples I would need more time (and is half past midnight here). So if any other posters do not respond I'll be back tomorrow.

[rosstafarian]

cheers for all your help mate.
It is much appreciated.

Times like these i wish i wouldve started php a while ago lol
Decided on vb.net on my college course instead haha.

Yeah its midnight here, but im going to power on see what i can figure out lol

[broj1]

cheers for all your help mate.
It is much appreciated.

No worries.

One way of doing the updating only the changed values would be as follows (see comments in the code). I added a hidden field in each row to hold the value (same as input) and which can be then compared to see if changed.

<?php
while($row = mysql_fetch_array($result)){

    $text_name = 't-' . $row['id'];
    $hidden_name = 'h-' . $row['id'];
    $value = $row['handicap'];

    echo"<tr>";
    echo"<td>" . $row['ID'] . "</td>";
    echo"<td>" . $row['First'] . "</td>";
    echo"<td>" . $row['Last'] . "</td>";
    echo"<td>" . $row['handicap'] . "</td>";
    echo "<input type=\"text\" name=\"$text_name\" value=\"$input_value\" />";

    // hidden field added here; the value is the same as the input field's value
    echo "<td><input type=\"hidden\" name=\"$hidden_name\" value=\"$input_value\" /></td>";
}
echo"</table>";

echo"<input type=\"submit\" name=\"submit\"/>";
//endwhile;

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

    // initialize arrays for the text input values
    // the $text_values array will contain all the values from text input boxes
    // the $hidden_values array will contain all the values from hidden input boxes
    // these values were initially equal for each row; if user changes the value
    // in the input box, the corresponding value in the hidden input will differ
    // which will trigger the update query for that row
    $text_values = array();
    $hidden_values = array(); 

    // now let's go through the $_POST array where:
    // $key is the name attribute of the input box
    // $val is the value of that input box
    foreach($_POST as $key => $val) {

        // if $key starts with 't-' it is the value of the text input box
        if(substr($key, 0, 2) == 't-') {

            // the value will be added to the $text_values array and
            // the key will be the name of the input field stripped of 't-'
            $new_key = substr($key, 2);
            $text_values[$new_key] = $val;

            // no need to process this iterration so might as well skip the rest
            // of the foreach loop
            continue;
        }

        if(substr($key, 0, 2) == 'h-') {

            // the value will be added to the $hidden_values array and
            // the key will be the name of the input field stripped of 'h-'
            $new_key = substr($key, 2);
            $hidden_values[$new_key] = $val;

            // no need to process this iterration so might as well skip the rest
            // of the foreach loop
            continue;
        }

        // if there are no values in either of arrays something must have gone wrong
        if(empty($text_values) || empty($hidden_values)) {

            die('Unknown error');
        }
    }

    // now we have two equaly structured arrays: $text_values with values 
    // (changed or unchanged) and $hidden_values with unchanged values
    // both arrays have same associative indexes
    // we will go through each of the value in $text_values array and compare 
    // it with the value of the same index in the $hidden_values array
    // if there is a difference we will run the update query for that row
    foreach($text_values as $key => $val) {

        // to avoid errors also check for the existence of array elements
        // and then compare them
        if(isset($hidden_values[$key]) && ($val != $hidden_values[$key])) {

            mysql_query("UPDATE members SET handicap='$val' WHERE id=$key");
        }
    }
}

mysql_close($con);
?>

I haven't actually tested the code above since lack of other information. It is just a concept thing so you can have look at how I would do it. Nevertheless I would still prefer to use ajax and update each row immediately upon change.

[rosstafarian]

Hi mate,
Sorry about the delayed reply, been very busy at work the past couple of days so im going to have to put my little side project on hold for a week or so.

The code looks great, the comments are amazing, very good job on helping me understand it all lol.

As soon as i get a bit more time ill give it a shot and tinker around with it and let you know how i get on.

A big thankyou though mate, been very helpful!