CODEWITHSUNDEEP
phpmysqlsqldatabasehtml-table
gigi
gigi

Reputation: 181

Tables relationship issue - gathering info from second table

Considering I am using PHP and MySql, I have 2 table in my database named: table A and table B. I use a php page to populate both of them, and another php page for diplay them. Table A contains for each row a clickable button that open a modal pop-up. What I would like to do is show in this modal some info from table B for the respective row.

This is what I mean:

I am stuck to the point where each button in table A show me the entire the table B, and I cannot figure out how to relate this rows. Any tips?

I dont understand how can I associate for each button of the row a different "action".

---- additional info: ----

----- UPDATE 2 ------ Table B can be cosider an extension of table A

Table A has this column:
id | name | mail | number | device | price | payment | status

Table B has this column:
id | device | model | problem | status | priority | BUTTON1 | BUTTON2

button1 -> change the status and the change is reflected in table A

button2 -> should gather the respecive row of table A with the information name | mail | number | price | payment

----- UPDATE 3 ------

In table B:

  $custid = $row['id'];
 <td> <a class='btn btn-primary btn-sm'  data-toggle='modal' 
  data-target='#myModal'   name='[$custid]' >  Info</a></td>

In table A:

$sql = "SELECT name,mail,number,price,paymenttype,faktura,date from TABLE_A";

Upvotes: 0

Views: 129

Answers (1)

Darwin von Corax
Darwin von Corax

Reputation: 5246

Table A needs to have a primary key to uniquely identify each row. Clicking a button should invoke a query on table B with a where clause matching the PK value from the row in table A. (This is how foreign keys work.)

In PHP you can make that PK value part of the button's name. Extract the key value from the name of the button that was clicked, and pass it into the query on table B. (This would be an excellent use for a prepared-statement query.)

Here's an example, using the movies database from PostgreSQL 2/e by Douglas & Douglas:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<?php
    require_once("connect_params.php");
    $dbh = new PDO($DSN);

    // Use a prepared statement here if your query takes any parameters
    $query = "SELECT customer_id, customer_name, phone, balance FROM customers ORDER BY customer_id";
    $result = $dbh->query($query);

    // php.net says this is the only reliable way to force-close a PDO connection
    $dbh->query('SELECT pg_terminate_backend(pg_backend_pid())');
    $dbh = null;
?>
<html>
    <head>
        <meta content="text/html; charset=ISO-8859-1" http-equiv="content-type">
        <title>Customers</title>
    </head>
    <body>
        <form onsubmit="return false;">
            <table style="text-align: left;" border="1" cellpadding="2" cellspacing="0">
                <tbody>
                    <tr>
                        <td style="vertical-align: top;">
                            Customer Name
                        </td>
                        <td style="vertical-align: top;">
                            Phone
                        </td>
                        <td style="vertical-align: top;">
                            Current Balance
                        </td>
                        <td style="vertical-align: top;">
                            Rentals
                        </td>
                    </tr>

<!-- ********** Here's where we start building the individual rows ********** -->
<?php
    foreach ($result as $row)
    {
        $custid = $row['customer_id'];
?>
                    <tr>
                        <td>
                            <?php echo $row['customer_name']; ?>
                        </td>
                        <td>
                            <?php echo $row['phone']; ?>
                        </td>
                        <td>
                            <?php echo $row['balance']; ?>
                        </td>
                        <td>
                            <button name="show_rentals[<?php echo $custid; ?>]"
                                value="<?php echo $custid; ?>"
                                onclick="showDetail(<?php echo $custid; ?>);">
                                Show
                            </button>
                        </td>
                    </tr>
<?php
    }
?>
<!-- ********** We're finished building the individual rows ********** -->

                </tbody>
            </table>
        </form>
        <br>
    </body>
</html>

The button's onclick handler calls a JavaScript function showDetail(int custid) which I have not included in this sample, but it act to generate your popup window with the details from the second table.

Upvotes: 1

Related Questions