Web Analytics Made Easy -
StatCounter How to retrieve data from two tables into one table - CodingForum

Announcement

Collapse
No announcement yet.

How to retrieve data from two tables into one table

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to retrieve data from two tables into one table

    I have two databases :

    1. employees_info
    2. employee_payroll

    I would like to create a table that has some info from the first table and the rest from the second table like the code bellow but it's not working. Any idea?

    PHP Code:
                        $query = mysql_query("
                        SELECT *
                        FROM `employee_payroll`
                        WHERE `branch` LIKE '%$search_keyword%'
                        ");
                        
                        $query2 = mysql_query("
                        SELECT *
                        FROM `employees`
                        WHERE `branch` LIKE '%$search_keyword%'
                        ");
                        
                        echo "<table id=\"search\">";
                        echo "<tr>
                                <th>م</th>
                                <th>Name</th>
                                <th>Job Title</th>
                                <th>Salary</th>
                                <th>Working Days</th>
                                <th>Fee</th>
                                <th>Overtime</th>
                                <th>Violations Deduction</th>
                                <th>Absent Days</th>
                                <th>Absent Deduction</th>
                                <th>Delay Deduction</th>
                                <th>Uniform Deduction</th>
                                <th>Loan / Advance</th>
                                <th>Net Salary</th>
                                <th>Sign</th>
                            </tr>";
                        
                        while($employee = mysql_fetch_array($query) && $employees_info = mysql_fetch_array($query2))
                        {
                            
                            ?>
                            <tr>
                                <td><?php echo $employee['employee_id']; ?></td>
                                <td><?php echo $employees_info['name']; ?></td>
                                <td><?php echo $employees_info['job_title']; ?></td>
                                <td><?php echo $employees_info['salary']; ?></td>
                                <td><?php echo $employee['attendance_total']; ?></td>
                                <td><?php echo $employee['attendance_total_fee']; ?></td>
                                <td><?php echo $employee['overtime_total_fee']; ?></td>
                                <td><?php echo $employee['violations_total_fee']; ?></td>
                                <td><?php echo $employee['absence_total']; ?></td>
                                <td><?php echo $employee['absence_total_fee']; ?></td>
                                <td><?php echo $employee['delay_total_fee']; ?></td>
                                <td><?php echo $employee['uniform']; ?></td>
                                <td><?php echo $employee['loan']; ?></td>
                                <td><?php echo $employee['total']; ?></td>
                                <td></td>
                            
                            </tr>
                    <?php        
                    
    }
                    
    ?>    
                        </table>

  • #2
    Can we have the table structures of those tables.
    "show create table employees" and "show create table employee_payroll"
    It would be easier to tell what is the best action to go forward.

    I hope this is a hobby project as the data is not safe and the code is vulnerable to basic scriptkiddie attacks.


    mysql_ functions are dead. Don't use them. Try PDO instead.
    Don't use old mysql library
    The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets
    When a white horse is not a horse

    Comment


    • #3
      Here are the structures


      Click image for larger version

Name:	employees.png
Views:	1
Size:	17.1 KB
ID:	2270490


      Click image for larger version

Name:	employees_payroll.png
Views:	1
Size:	5.8 KB
ID:	2270491

      Comment


      • #4
        Security is not an issue for me because it's going to be run locally, however I will enhance the script after I make sure it's functional. I just need that code to work.

        Comment


        • #5
          Don't create a table based on other existing tables - just join the tables to retrieve the data you need from both in the one call.

          You should use mySQLi or PDO for the database calls. The mysql_ interface was declared obsolete in July 2013 and removed in December 2015.
          Stephen
          Learn Modern JavaScript - http://javascriptexample.net/
          Helping others to solve their computer problem at http://www.felgall.com/

          Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

          Comment


          • #6
            You misunderstood felgall, I mean I want to create an HTML table out of two mysql database tables

            Comment


            • #7
              Originally posted by samiraljohani View Post
              I would like to create a table that has some info from the first table and the rest from the second table like the code bellow but it's not working. Any idea?
              No, we do not misunderstand. In a php forum when you start talking of creating tables, it is assumed that you mean database tables - or else you would be in the html forum. Felgalls understanding is the same as mine - I also thought you wanted to create another database table - a memory table drawing data in from two other tables was my understanding.
              "Tango says double quotes with a single ( ' ) quote in the middle"
              '$Name says single quotes with a double ( " ) quote in the middle'
              "Tango says double quotes ( \" ) must escape a double quote"
              '$Name single quotes ( \' ) must escape a single quote'

              Comment

              Working...
              X