CODEWITHSUNDEEP
phphtmlmysql
user6369806
user6369806

Reputation:

How do I get data from 2 SQL tables and put it in a single HTML table?

PHP is here:

 <?php
    $sth = $conn->prepare('SELECT employee.name, employee.type, employee.rate, work.overtime, work.leaves, work.ticket FROM employee, work');
   $sth->execute();
     $data = $sth->fetchAll();   
    foreach ($data as $row ){
        if($row['name']!=""){
    ?>

HTML:

<tr>
      <td>
        <input type="text" placeholder="Name" value="<?php echo $row['name']?>"/>
      </td>
      <td>
        <input type="text" placeholder="Type" value="<?php echo $row['type']?>"/>
      </td>
      <td>
        <input type="text" placeholder="Rate" value="<?php echo $row['rate']?>"/>
      </td>
      <td>
        <input type="text" placeholder="OT" value="<?php echo $row['overtime']?>"/>
      </td>
      <td>
        <input type="text" placeholder="Leaves" value="<?php echo $row['leaves']?>"/>
      </td>
      <td>
        <input type="text" placeholder="Total" value="<?php echo $row['ticket']?>"/>
      </td>
    </tr>

Snapshots of tables: enter image description here

And the other: enter image description here

Name is common between the two.

How do I display the data?

Upvotes: 0

Views: 69

Answers (2)

Niraj_Palange
Niraj_Palange

Reputation: 38

<?php
    $sth = $conn->prepare('SELECT employee.name, employee.type, employee.rate, work.overtime, work.leaves, work.ticket FROM employee left join work on employee.name=work.name');
   $sth->execute();
     $data = $sth->fetchAll();   
    foreach ($data as $row ){
        if($row['name']!=""){
    ?>

Then Your HTML Code will be like this (Make sure you are using <tr> under <table> tag.)

<tr>
      <td>
        <input type="text" placeholder="Name" value="<?php echo $row['name']?>"/>
      </td>
      <td>
        <input type="text" placeholder="Type" value="<?php echo $row['type']?>"/>
      </td>
      <td>
        <input type="text" placeholder="Rate" value="<?php echo $row['rate']?>"/>
      </td>
      <td>
        <input type="text" placeholder="OT" value="<?php echo $row['overtime']?>"/>
      </td>
      <td>
        <input type="text" placeholder="Leaves" value="<?php echo $row['leaves']?>"/>
      </td>
      <td>
        <input type="text" placeholder="Total" value="<?php echo $row['ticket']?>"/>
      </td>
    </tr>

Here I am using left join because you may require your all employee name. if you want only common records between these two tables then simply use inner join instead of left join.

Upvotes: 0

Tajmin
Tajmin

Reputation: 405

The term you are looking for is JOIN. Read the manual of MySQL Official Doc

Upvotes: 1

Related Questions