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.
I am new to PHP and someone else may tell you a different way or a shorter way, but this is where I would start. // Run the search. $query = "SELECT * FROM tbl_country,tbl_employees ORDER BY tbl_country.Country_Name DESC"; $result = mysql_query($query) or die (mysql_error()); // Display the results. while ($row = mysql_fetch_array($result)) { // Many different ways to show the results. Can use tables or div's, up to you. echo '<p>' . $row['tbl_column'] . '</p>'; } Code (markup): Again that is just where I would start.
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.
// Run the search. $query = "SELECT * FROM tbl_country,tbl_employees ORDER BY tbl_country.Country_Name DESC"; $result = mysql_query($query) or die (mysql_error()); // Display the results. while ($row = mysql_fetch_array($result)) { // Many different ways to show the results. Can use tables or div's, up to you. echo '<p>' . $row['Employee_name'] . '</p>'; } Code (markup): And like I said its just a place to start, you will need to adjust it for the purposes of you site.
Will it be something like this? <!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> Code (markup):
Rob's idea is good enough if you use a join : "select * from tbl_country join tbl_employees on tbl_country.id = tbl_employees.country_id"; with a join his code works fine. http://www.dreamdealer.nl/?action=viewTutorial&id=69