Page 1 of 1

display multiple tables

Posted: Mon Sep 14, 2009 8:15 pm
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...

Re: display multiple tables

Posted: Mon Sep 14, 2009 8:35 pm
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?

Re: display multiple tables

Posted: Mon Sep 14, 2009 9:27 pm
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.

Re: display multiple tables

Posted: Mon Sep 14, 2009 9:40 pm
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.

Re: display multiple tables

Posted: Mon Sep 14, 2009 9:43 pm
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