display multiple tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
gracepinas
Forum Newbie
Posts: 15
Joined: Sat Aug 15, 2009 1:04 am

display multiple tables

Post by gracepinas »

Hello!

I am making an online payroll system. In one of my form i need to pull 3 tables from the database

1. table for Department
2. table for Cost Center
3. table for Payroll Location
i have this as my code to display my table in php

paycon.php

Code: Select all

<?php
$host = "localhost";
$user = "root";
$pass = "";
$port = "3036";
$db = "onlinepayroll";
$connection = mysql_connect($host,$user,$pass);
$db = mysql_select_db($db,$connection);
 
?>
 

Code: Select all

<?php
 
 include("common/payroll_con.php");
 
    $TableName = "tblemployee";
    $SQLstring = mysql_query("SELECT * from $TableName");
 
    //$Row = mysql_fetch_row($SQLstring);
        //echo  "<div id=\"users\">";
        echo "<div id=\"lblUser\">Employee Code</div>";
        echo "<div id=\"lblName\">Employee   Name</div>";
        echo "<div id=\"userlist\" >&nbsp;";
        echo "<table style=\"margin:0 auto;\" width:\"100%\" margin-top:\"-15px\">";
       while ($Row = mysql_fetch_array($SQLstring)) {
        echo "<tr style=\"background:white;\">";
        echo "<td><a href=\"\"><span id=\"liUser\">{$Row['empcode']}</span><span id=\"liName\">{$Row['ename']}</span>/a></td>";
        echo  "</tr>";
        }
 
        echo "</table>";
      echo "</div>";
    
 
 ?>
 
how about my other 3 tables?

can you help me please...
Griven
Forum Contributor
Posts: 165
Joined: Sat May 09, 2009 8:23 pm

Re: display multiple tables

Post by Griven »

In order to display all of the data in a table, with each piece linked together, we'll need to perform a JOIN on the tables.

I have a few questions, first.

1. What are your primary keys for each table?
2. Can the Cost Center table be merged with the Departments table? In most organizations I've been in, each department had a unique cost center code (which could serve as your department's primary key value). If you're storing more data besides the cost center's code, then merging may not be a good idea.
3. How do you want your data to be displayed once all is said and done? What is the goal here?
gracepinas
Forum Newbie
Posts: 15
Joined: Sat Aug 15, 2009 1:04 am

Re: display multiple tables

Post by gracepinas »

Griven wrote:In order to display all of the data in a table, with each piece linked together, we'll need to perform a JOIN on the tables.

I have a few questions, first.

1. What are your primary keys for each table?
2. Can the Cost Center table be merged with the Departments table? In most organizations I've been in, each department had a unique cost center code (which could serve as your department's primary key value). If you're storing more data besides the cost center's code, then merging may not be a good idea.
3. How do you want your data to be displayed once all is said and done? What is the goal here?
my form will look like the attach pic.
answers to your question:
1. the primary keys are costCODE for Cost Center, deptcode for Department and paylocCODE for pay location
2. no these tables cannot be merged
3. my objective is for these tables to be displayed and later on it these tables will be use in another table to seperate employees by cost center, by department and by payroll location. But for now i Would like to display first the tables before proceeding.
Attachments
createpref.JPG
createpref.JPG (29.57 KiB) Viewed 159 times
Griven
Forum Contributor
Posts: 165
Joined: Sat May 09, 2009 8:23 pm

Re: display multiple tables

Post by Griven »

Okay--displaying the data like this is simple enough.

Since the data here isn't joined together, you'll need to run three separate queries to get the three separate result sets. Then, use a WHILE loop to display them in tabular format.

Code: Select all

   //Structure the queries
$query1 = "SELECT * FROM departments";
$query2 = "SELECT * FROM paylocation";
$query3 = "SELECT * FROM costcenter";
 
  //Run the queries
$deptresult = mysql_query($query1);
$paylocresult = mysql_query($query2);
$costresult = mysql_query($query3);
 
  //Display the data
echo '<table>';
echo '<tr><th>Code</th><th>Description</th></tr>';
while($row = mysql_fetch_assoc($deptresult)){
  echo '<tr><td>', $row['deptcode'] ,'</td><td>', $row['deptdescription'] ,'</td></tr>';
}
echo '</table>'; 
Repeat the "Display the data" portion, substituting the $deptresult variable with the $paylocresult and $costresult variables, respectively.

Also, if you're not going to use all of the data from your tables, it may benefit your code's performance to specify which columns you want returned, rather than using the * wildcard.
Last edited by Griven on Mon Sep 14, 2009 9:43 pm, edited 1 time in total.
gracepinas
Forum Newbie
Posts: 15
Joined: Sat Aug 15, 2009 1:04 am

Re: display multiple tables

Post by gracepinas »

Griven wrote:Okay--displaying the data like this is simple enough.

Since the data here isn't joined together, you'll need to run three separate queries to get the three separate result sets. Then, use a WHILE loop to display them in tabular format.

Code: Select all

   //Structure the queries
$query1 = "SELECT * FROM departments";
$query2 = "SELECT * FROM paylocation";
$query3 = "SELECT * FROM costcenter";
 
  //Run the queries
$deptresult = mysql_query($query1);
$paylocresult = mysql_query($query2);
$costresult = mysql_query($query3);
 
  //Display the data
echo '<table>';
echo '<tr><th>Code</th><th>Description</th></tr>';
while($row = mysql_fetch_assoc($deptresult)){
  echo '<tr><td>', $row['deptcode'] ,'</td><td>', $row['deptdescription'] ,'</td></tr>';
}
echo '</table>'; 
Repeat the "Display the data" portion, substituting the $deptresult variable with the $paylocresult and $costresult variables, respectively.
thanks for the help :) i'll try the code and inform you afterward
Post Reply