SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question Table Joing and Reporting

    Hello People,


    I designed this application keeping in mind the normalization technique. Let me give you a brief overview of my application and how have I designed it.

    My App is a form/webpage where in we save details about all countries. The structure of the DB is as follows:

    +++++++++++++++++++++++++++++
    Table Name: tbl_country

    ID [Autoincreament]
    Country_Name [varchar]
    Currency [varchar]
    Country_code [int]

    +++++++++++++++++++++++++++++

    I also have a second table which would keep records of employees of that particular country. The DB structure is as follows:

    +++++++++++++++++++++++++++++

    Table Name: tbl_employees

    ID [Autoincreament]
    country_ID [int]
    Employee_name [varchar]
    Employee_telephone [varchar]

    +++++++++++++++++++++++++++++

    The tbl_employees.country_ID column will contain the value of the tbl_country.ID column.


    Assuming there are 200 countries in the tbl_country table and each country has 5 employees. What is the best way to display the report in a tabular format. So that it lists all countries row by row and each row will display employees of that particular country in different columns.


    Also, what would the query for it be?


    I am using MySQL 5 with PHP 5

    Thanx a ton in advance for any help.

  2. #2
    SitePoint Member jmiletic's Avatar
    Join Date
    Oct 2008
    Location
    Ohio
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A partial solution:

    SELECT Country_Name, Employee_name, Employee_telephone, Currency, Country_code
    FROM tbl_country c, tbl_employees e
    WHERE country_ID=c.ID
    ORDER BY country_ID;

    Not sure about the tab delimited; could select and concat into out file...
    Joyce Miletic RHCT + Systems Engineer
    Navisite Dedicated Hosting
    http://dedicatedhosting.navisite.com
    "24/7/365" Myth or Reality?

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jmiletic View Post
    FROM tbl_country c, tbl_employees e


    Whats that c and e stand for?

  4. #4
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    They are just aliases for the tables - to make referring to them easier when using in field names. They can be anything really.

  5. #5
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jmiletic View Post
    A partial solution:

    SELECT Country_Name, Employee_name, Employee_telephone, Currency, Country_code
    FROM tbl_country c, tbl_employees e
    WHERE country_ID=c.ID
    ORDER BY country_ID;

    Not sure about the tab delimited; could select and concat into out file...
    Hi

    Thanx for the reply. I am sorry but you code does not tell me how do I populate all employees for each country under each rows.

  6. #6
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Can you give an example of the output you want?

  7. #7
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Something like this

    Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    </head>
    
    <body>
    <table width="312" border="1">
      <tr>
        <th width="49">Country</th>
        <th width="56">Currency</th>
        <th width="34">Code</th>
        <th width="64"> Employee name </th>
        <th width="75">Tel</th>
      </tr>
      <tr>
        <td>USA</td>
        <td>USD</td>
        <td>001</td>
        <td>Paul</td>
        <td>645674544</td>
      </tr>
      <tr>
        <td>India</td>
        <td>INR</td>
        <td>91</td>
        <td>John</td>
        <td>85785444</td>
      </tr>
    </table>
    </body>
    </html>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •