Page 1 of 1

Update database from dynamic table

Posted: Fri Feb 06, 2015 7:39 pm
by recker2015
I have created this dynamic table which contains my invoice line items. I
have two JavaScript functions which will add or delete lines. I didn't include
the JavaScript here but I can if it is needed to resolve my issue. Now I want
to be able to make changes to the lines items and save them back to the
database. This is where my trouble begins. I can't figure out how get each of
the values from $_POST so I can process them and update my database table.

Code: Select all

    $titles = array('invlineid','invoiceid','quantity','unitprice','itemdesc');
    $headers = array('invlineid','invoiceid','quantity','unitprice','itemdesc');
 
    $sql = "SELECT ";

    foreach(array_combine($headers, $titles) as $header => $title)
    {
        $sql .= "$header as $title,";
    }

    $sql .= "linetotal as linetotal";
    $sql .= " FROM invoicelineitem";
    $sql .= " WHERE invoiceid = 1096";

    try
    {
        $result = $pdo->query($sql);
    }
    catch (PDOException $e)
    {
        $error = 'Error getting invoice line items.---' . $e . '----' . $sql;
        include $_SERVER['DOCUMENT_ROOT'] . '/mincludes/error.html.php';
        exit();
    }

    if ($result !== false)
    {
        $html_table = '<table>';
        $html_table .= '<thead><tr>';

        foreach($titles as $title)
        {
            $html_table .= "<th> $title </th>";
        }

        $html_table .= '</tr> </thead>';
        $html_table .= '<tbody id="dataTable">';

        foreach($result->fetchAll(PDO::FETCH_ASSOC) as $row)
        {
            $html_table .= '<tr>' . "\n";

            foreach($row as $col)
            {
                $html_table .= '<td>';
                $html_table .= '<input type=text name=' . $title;
                $html_table .= ' value=' . $col . '>';
                $html_table .= '</td>' . "\n";
            }

            $html_table .= '</tr>' . "\n";
        }
    }

    $html_table .= '</tbody> <tr> </table>';


Re: Update database from dynamic table

Posted: Fri Feb 06, 2015 8:42 pm
by Celauran
Sorry, it's not clear where $_POST factors into this at all. The code you posted grabs rows from a database and displays them in a table. Where are you running into trouble?

Re: Update database from dynamic table

Posted: Fri Feb 06, 2015 9:03 pm
by recker2015
Ya this is the code I'm using to build the table. But once have have it loaded, I need to be able to save any changes back to the database. That is where I'm stuck. I don't know how to read the information and save the changes to the database. To put it another way, I don't know how to loop through all the rows/fields and update the database.

Thanks

Re: Update database from dynamic table

Posted: Fri Feb 06, 2015 9:11 pm
by Celauran
How are you hoping to achieve that? Update the whole table at once? You could use a form. Update just one row via AJAX call? That's more of a JS problem.

Re: Update database from dynamic table

Posted: Fri Feb 06, 2015 9:19 pm
by recker2015
If need be I could update the whole table. While not ideal from an efficiency standpoint, I will be dealing with a small amount of rows.

Perhaps there is a better way to build this html table that would make things easier. I wide open to suggestions.

Re: Update database from dynamic table

Posted: Fri Feb 06, 2015 9:20 pm
by Celauran
Perhaps the easiest would be to have an edit link at the end of each table row which goes to a separate page that loads just the one record into a form.

Re: Update database from dynamic table

Posted: Fri Feb 06, 2015 9:38 pm
by recker2015
I have given that some consideration, but I would really like to be able to edit them on the same page as it would seem to be a lot of bouncing between screens. Also, this remove any value of the dynamic part of the table, such as adding and removing entire rows. All in all having to go to separate pages for each action doesn't seem very user friendly.

Re: Update database from dynamic table

Posted: Fri Feb 06, 2015 9:55 pm
by Celauran
This is why I was asking about use cases. You're still going to need the form input fields to allow the user to edit values. Perhaps a save or update button at the end of each row to trigger an AJAX call to update the values of just that one row?

Re: Update database from dynamic table

Posted: Fri Feb 06, 2015 10:07 pm
by recker2015
Okay, I guess I will look into AJAX. I was trying to stick with just php and maybe a very minimal amount of JavaScript. I don't have much time left tonight so it will be a few days before I can dig into that. I appreciate your responses.

Re: Update database from dynamic table

Posted: Fri Feb 06, 2015 10:10 pm
by Celauran
You won't need more than a minimal amount of JS, but if you want it done asynchronously, you'll need some. Take a look at this. http://api.jquery.com/jquery.ajax/ Should be all you need.