How to update 2 independent HTML tables on same page with one button click?

[Yury]

I am trying to build Web based PHP driven application with MySQL database. I need to have several tables on the same web page and dynamically reload them on button click event. Unfortunately I don't know what approach should I take, reloading entire web page from server side using PHP or reload table from client side with JavaScript? I used to do similar task on Java and used onclick event to get data from SQL and rebuild necessary table and then display it on screen. Is it possible to achieve same approach with PHP or JavaScript? On attached picture there are two tables. I am trying to achieve the following:

1. Click "Show All" for Customers

•       - show result on customer table (I know how to do it)
•       - clear result on Sites table

2. versus versa when click "Show All" for Sites

3. some other operations but idea is the same - dynamically update necessary tables (several) on same page for my needs.

 

[Barand]

The technique you want is AJAX

      +-----------------+                                  +-----------------+
      | browser page    |                                  |      Server     |
      |-----------------|      AJAX request                |-----------------|
      |          Send   | ----------------------------->   |    Query DB     |
      |                 |      with search data            |        |        |
      |                 |                                  |        |        |
      |                 |                                  |        |        |
      |     Process     |  <----------------------------   | return response |
      |     response    |                                  |                 |
      |                 |                                  |                 |
      |                 |                                  |                 |
      |                 |                                  |                 |
      +-----------------+                                  +-----------------+

 

[Yury]

I looked at Ajax and looks like I can update only one table at the time and also has to specify URL for php file and not function or several functions. That is where I am stuck, I don't understand how to update 2 tables at the same time with one button click.

[Barand]

It's just a matter of organising your ajax response.

Send it back as an array containing two arrays, one for table A and one for table B

[Barand]

Here's an example (using the database from my SQL tutorials)

Output

Code

<?php 
define("HOST",'localhost');
define("USERNAME",'????');
define("PASSWORD",'????');
define("DATABASE", 'jointute');                 // uses DB from my SQL tutorials

$db = pdoConnect();

#################################################################################
## handle AJAX request
##
    if (isset($_GET['ajax'])) {
        if ($_GET['ajax']=='classdata') {
            $response = [ 'tablea' => [], 'tableb' => [] ];  // keys are the ids of the destination tbody elements
            $res = $db->prepare("SELECT s.subject
                                      , concat(t.fname, ' ', t.lname) as name
                                FROM teacher_subject ts
                                     JOIN teacher t USING (teacherid)
                                     JOIN subject s USING (subjectid)
                                     JOIN (
                                            SELECT DISTINCT subjectid
                                            FROM choice c 
                                                 JOIN pupil p ON c.pupilid = p.pupilid
                                                              AND p.classid = ?
                                          ) subj USING (subjectid)
                                     WHERE ts.classid = ?
                                ORDER BY ts.subjectid
                                ");
            $res->execute( [ $_GET['cid'], $_GET['cid'] ] );
            $response['tablea'] = $res->fetchAll();
            
            $res = $db->prepare("SELECT concat(p.fname, ' ', p.lname) as name
                                      , GROUP_CONCAT(s.subject SEPARATOR ', ') as subjects
                                 FROM choice c
                                      JOIN pupil p USING (pupilid)
                                      JOIN subject s USING (subjectid)
                                 WHERE p.classid = ?
                                 GROUP BY p.pupilid     
                                ");
            $res->execute( [ $_GET['cid'] ] );
            $response['tableb'] = $res->fetchAll();
            
            exit(json_encode($response));
        }
    }
#################################################################################

$buttons = '';
for ($i='A'; $i<'G'; $i++) {
    $buttons .= "<button class='classbtn' value='$i'>$i</button>&emsp;";
}
    
function pdoConnect($dbname=DATABASE) 
{
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    return $db;
}


?>
<!doctype html>
<html>
<head>
    <meta charset="utf-8">
    <title>County Selection</title>
    <script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
    <script type='text/javascript'>
        $().ready(function() {
            
            $(".classbtn").click( function() {
                var cid = $(this).val()
                $(".classbtn").removeClass("selectbtn")
                $(this).addClass("selectbtn")
                $.get(
                    "",                                            // request sent to self
                    {"ajax":"classdata", "cid":cid},
                    function(resp) {                            
                        $.each(resp, function(k,v) {
                            var tbody = $("#"+k)
                            $(tbody).html("")
                            $.each(v, function(k1, r){
                                var row = $("<tr>")
                                $.each(r, function(k2, c) {
                                    $(row).append($("<td>", {text:c}))
                                })
                                $(tbody).append(row)
                            })
                        })
                    },
                    "JSON"
                )
            })
        })    
    </script>
    <style type='text/css'>
        body {
            font-family: arial, sans-serif;
        }
        table {
            border-collapse: collapse;
            width: 600px;
            margin: 16px;
        }
        td, th {
            padding: 8px;
        }
        th {
            background-color: #EEE;
            text-align: left;
        }
        .buttons {
            padding: 16px;
            border-bottom: 1px solid gray;
        }
        .classbtn {
            background-color: #EEE;
            cursor: pointer;
        }
        .selectbtn {
            background-color: #008000;
            color: white;
        }
    </style>
</head>
<body>
    <div class='buttons'>
        Select a class &emsp; <?=$buttons?>
    </div>
    
    <h3>Pupils</h3>
    <table border='1'>
        <tr><th>Name</th><th>Subjects</th></tr>
        <tbody id='tableb'></tbody>
    </table>
    
    <h3>Teachers</h3>
    <table border='1'>
        <tr><th>Subject</th><th>Teacher</th></tr>
        <tbody id='tablea'></tbody>
    </table>
</body>
</html>

 

[Yury]

Thank you very much.

I will take a look later to see if I can figure it out.

Can you suggest any good resources to learn PDO PHP together with MySQL. 

Thanks.

[Barand]

phpdelusions.net

[Yury]

Thanks

 

[gizmola]

I saw your other post looking for some guidance.  I highly advise using one of the 2 best PHP MVC frameworks as the starting point for your back office application.  Since you mentioned some prior Java experience, they are both dependency injection frameworks and have much in common with the Spring framework.

• Symfony Framework
• Laravel Framework

There is excellent documentation as well as paid and free resources that will help you learn either one.   They also both come with ORM's like Java's hibernate, so when using either one, you won't be writing PDO code directly in a lot of cases.

You will be doing a lot to embrace the DRY and avoid needless reinvention of the wheel, but the most important thing is that you won't waste time dealing with basic request response, routing, templating or database classes, and more time working on your actual business logic.  You will also benefit from having an application that is written in a way that you'll be able to find others to help you add to or extend.