Tables relationship issue - gathering info from second table

Question

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:

button of row1 table A shows info of row1 table B
button of row2 table A shows info of row2 table B

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: ----

The index.php page diplays table B
Every time a new row is inserted, a button gets created.
Every time i click on the button, a modal shows the referred row of table A.

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

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";

Tables are diplayed through an php page. Thank you to notice the unclear question — gigi, Nov 19 '15 at 23:19
rows in mysql tables have no (humanly discernible) inherent 'order'. You need some kind of key in order to relate them — Strawberry, Nov 19 '15 at 23:22
I dont understand how can I associate for each button of the row a different "action". — gigi, Nov 19 '15 at 23:35

Darwin von Corax · Accepted Answer · 2015-11-21T23:28:16.133

1

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.

edited Nov 21 '15 at 23:28

answered Nov 19 '15 at 23:42

Darwin von Corax

5,201
3
17
28

So, table Aa should have a PK, then table B shoud have a FK, the second part you explained sounds a bit complicated, I must try out to gather more info, – gigi Nov 19 '15 at 23:53
Exactly. You don't need to explicitly declare a foreign key constraint (and MySQL's enforcement of FKs can be problematic); the fact that the correspondence exists between the table columns is the very definition of a foreign key. Declaring a foreign key constraint on a database that supports them simply means that the dbms will do more of the work for you. (You may realise this already, but future readers may not.) – Darwin von Corax Nov 20 '15 at 00:30
Could you clarify or give me more details regarding the second part? – gigi Nov 20 '15 at 00:42
This answer to ["How can I prevent SQL-injection in PHP? "](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496?s=1|6.1088#60496 "Preventing SQL Injection Through Prepared Statements") covers prepared statements from a security standpoint, but they have significant efficiency benefits as well. Basically, instead of creating a new, almost-identical query each time, you prepare a "fill-in-the-blanks" query that you can reuse with different values in the blanks each time. (More detail would be best left to a separate thread.) – Darwin von Corax Nov 20 '15 at 00:57
If you name each of your row submit buttons something like `name="rowsub['row_key_1']"` PHP creates an associative array named `$rowsub`. When the button is clicked, the array element with that particular key is defined; you can use PHP's array-wrangling tools to get the key value. – Darwin von Corax Nov 20 '15 at 01:06
but the submit button get automatically created everytime I add a new row – gigi Nov 20 '15 at 01:12
` ` – gigi Nov 20 '15 at 01:12
You're using PHP to generate the table, aren't you? Use something like `''`. Again, the details of this would be better left to a separate thread. (Your original post is really two questions.) – Darwin von Corax Nov 20 '15 at 01:19
yes I do use php, ok I will try, thanks for pointing me in the right direction – gigi Nov 20 '15 at 01:20
I was actually wrong because I use this code to open the modal pop-up with the second table in it. ` Info` – gigi Nov 20 '15 at 10:23
please read my -- UPDATE 2 -- in the question, i am trying to be more clear to let you understand what I want to achive – gigi Nov 20 '15 at 11:30
I think I see what you're trying to do - let me wrangle some code and I'll get back to you. – Darwin von Corax Nov 21 '15 at 03:01
Its connected with this if can help: http://stackoverflow.com/questions/33468436/connecting-two-tables-row-by-row-association?noredirect=1#comment54807862_33468436 – gigi Nov 21 '15 at 14:46
I worken on it a little bit, not sure is right what i just did but i can see ispectioning the code that now all the button gets a different name with this code: ` Info` – gigi Nov 21 '15 at 15:06
In yuor exemple you are not using the WHERE clause. I am able to assign for each button name a different key ( name="[1]", name="[2]", name="[3]"...) but i am not able to handle it. In my case I start from table B, which has the button that should show only one row of table A, but at the moment i can only show the entire table. See update n.3 in my question – gigi Nov 23 '15 at 23:30

Tables relationship issue - gathering info from second table

1 Answers1