CODEWITHSUNDEEP
phphtmlmysql
user5358888
user5358888

Reputation: 207

Dispaly all the data from a mysql table with additional information from second table

I have two tables in mysql db.Table_1 and Table_2 I am displaying the whole table in HTML using the following code :

<table>
<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "database";
$results = null;
try {
     $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $stmt = $conn->prepare("SELECT * FROM Table_1"); 
     $stmt->execute();
     $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
     $results = $stmt->fetchAll();
}
catch(PDOException $e) {
     echo "Error: " . $e->getMessage();
}
$conn = null;
?> 
<table>
      <thead>
        <tr>
          <th>Name</th>
          <th>Address</th>
          <th>Email</th>
        </tr>
      </thead>
      <tbody>
         <?php foreach($results as $key=>$row) { ?>
        <tr>
      <td><?php echo $row['Name'];?></td>
      <td><?php echo $row['Address'];?></td>
      <td><?php echo $row['Email'];?></td>
    </tr>
     <?php } ?>
       </tbody>
</table>
</table>

Now i want to add one more column to this HTML table where the data will be coming from Table_2.In this Table_2 there will be multiple comments inserted for a single post in Table_1.

Iam querying the latest comment for the post using the query statement :

SELECT comment FROM Table_2 WHERE id = (SELECT id 
FROM Table_2 WHERE post_id = 10 ORDER BY id DESC LIMIT 1)

Here we are specifying the post_id = 10 but when iam usiong SELECT * FROM Table_1" to display all the data from table_1 how should i specify to display a latest comment for that particular post in table_2

Table-1 Structure:

+-------+---------+---------+---------+
|  id   |  Name   | Address | Email   |
+-------+---------+---------+---------+
|   1   |   ABC   |   ABC   |   ABC   |
|   2   |   DEF   |   DEF   |   DEF   |
+-------+---------+---------+---------+

Table-2 Structure:

+-------+-------------+---------+
|  id   |  Table_1_id | Comments|
+-------+-------------+---------+
|   1   |     1       |   X     | 
|   2   |     1       |   Y     | 
|   3   |     2       |   Z     | 
+-------+-------------+---------+

HTML to be displayed:

+---------+---------+---------+---------+
|  Name   | Address | Email   | Comments|
+---------+---------+---------+---------+
|   ABC   |   ABC   |   ABC   |    Y    |
|   DEF   |   DEF   |   DEF   |    Z    | 
+---------+---------+---------+---------+

Thanks in advance.

Upvotes: 2

Views: 68

Answers (3)

Mirko Brombin
Mirko Brombin

Reputation: 1012

You can do this with MySql JOIN to retrieve data from multiple tables with the same query. I see that you are using php PDO, you can use join like this:

$stmt = $conn->prepare(" SELECT Table1.*, Table_2.Comment FROM Table_2 
    LEFT JOIN Table_1 ON ( Table_2.Table_1_id=Table_1.id)
    WHERE id = (SELECT id 
    FROM Table_2 WHERE post_id = 10 ORDER BY id DESC LIMIT 1)");

if ($stmt->execute(array($getVars['Comment']))) {
    while ($row = $stmt->fetch()) {
        print_r($row);
    }
}

Upvotes: 0

1000111
1000111

Reputation: 13519

If you want to get all the posts along with its latest comment then you can use the following query :

SELECT 
Name,
Address,
Email,
finalTable.comment
FROM 
Table_1
INNER JOIN 
(

        SELECT 
        comment,
        Table_1_id
        FROM Table_2
        INNER JOIN 
        (   SELECT 
             MAX(id) latest_comment_id
            FROM Table_2
            GROUP BY Table_1_id ) latestComments
        ON Table_2.id = latestComments.latest_comment_id
) finalTable
ON finalTable.Table_1_id = Table_1.id;

It will give an output result like below:

+---------+---------+---------+---------+
|  Name   | Address | Email   | Comments|
+---------+---------+---------+---------+
|   ABC   |   ABC   |   ABC   |    Y    |
|   DEF   |   DEF   |   DEF   |    Z    | 
+---------+---------+---------+---------+

Note: If you want all the posts i mean those posts too which don't have any comment yet then replace the INNER JOIN by LEFT JOIN

Demo Here

Upvotes: 1

Drudge Rajen
Drudge Rajen

Reputation: 7985

you can use joins as below 

 select Table_1.*
    from Table_1
    left join Table_2  on 
    (Table_1.id=Table_2.Table_1_id)
    and 
    Table_2.id>
    (select id from Table_2  
        where Table_1_id='10'
        order by id DESC LIMIT 2,1)

Upvotes: 0

Related Questions