Update database from dynamic table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
recker2015
Forum Newbie
Posts: 5
Joined: Fri Feb 06, 2015 7:30 pm

Update database from dynamic table

Post 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>';

User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Update database from dynamic table

Post 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?
recker2015
Forum Newbie
Posts: 5
Joined: Fri Feb 06, 2015 7:30 pm

Re: Update database from dynamic table

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Update database from dynamic table

Post 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.
recker2015
Forum Newbie
Posts: 5
Joined: Fri Feb 06, 2015 7:30 pm

Re: Update database from dynamic table

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Update database from dynamic table

Post 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.
recker2015
Forum Newbie
Posts: 5
Joined: Fri Feb 06, 2015 7:30 pm

Re: Update database from dynamic table

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Update database from dynamic table

Post 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?
recker2015
Forum Newbie
Posts: 5
Joined: Fri Feb 06, 2015 7:30 pm

Re: Update database from dynamic table

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Update database from dynamic table

Post 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.
Post Reply