Hi guys, I'm fairly new to PHP and mySQL, using a book to help me learn. I want to create a small site to help me improve my skills, but I'm a bit stuck on the database design part.

I want to be able to list various public companies and show the profits of the companies from the last 10 years.

For example:

Company Name // CEO // Founded // 2009 Profit // 2008 Profit // ... // 10 Year Average Profit

Apple // Steve Jobs // 1981 // $10.3 bn // $7.8 bn // ... // $3.58 bn

// = column separators :)

As far as I can tell, I need to have two tables in the database, one for the company data and one for the earnings:

Table 1: Company

  • company_id
  • Company Name
  • CEO
  • Founded
  • Website
  • Share Price

Table 2: Earnings

  • earnings_id
  • company_id
  • Year
  • Earnings

Bold = primary key for that table

My question is, is this the best setup for my tables? I guess I then need to learn a lot more about table joins to actually display the data in the format I gave above.

Member Avatar for rajarajan2017

Yeah its fine, and you make that company_id as foreign key in Earnings r8.

To me it seems to be fine. You may continue using this design. You may join tables using following query

select a.company_id, a.company_name ,a.CEO, a.Founded, a.Website,  a.Share_Price ,
b.year, b.earning
from company a left outer join earnings b on a.company_id=b.company_id

Hey, thanks a lot for the input, the foreign key and the SQL query worked well. Here's the demo:

http://www.peterclaridge.com/company_demo/index.php

I modified the SQL query to sort by date founded. I put the actual query as a comment in the source code if you want to view it.

Instead of listing each company 4 times and to get the correct data in the 2008/7/6 earnings columns, do I have to do a new SQL query for each year or modify the existing one?

Member Avatar for rajarajan2017

Little bit confused, why it comes 4 records for the same company? only the earnings is the difference. Whether it shows irrelevant output?

YOU may try following query

SELECT a.b_id, a.company_name AS name, a.ceo, a.founded, a.website, b.year, 
sum(case when b.year=2006 then b.earnings else 0 end ) y2006,
sum(case when b.year=2007 then b.earnings else 0 end ) y2007,
sum(case when b.year=2008 then b.earnings else 0 end ) y2008,
sum(case when b.year=2009 then b.earnings else 0 end ) y2009
FROM business a left outer JOIN earnings b on a.b_id=b.b_id 
group by a.b_id, a.company_name, a.ceo, a.founded, a.website ORDER BY (founded)

Great! Check it out, it worked, thanks. I will read up on the SUM and GROUP BY functions, I just need pushing in the right direction sometimes. I guess instead of hard coding the years 2009/8/7/6 I could include them as variables?

sum(case when b.year=$year1 then b.earnings else 0 end ) y2006,

The next job I'm going to tackle is to build a form to add, edit and delete records from the database. Maybe let the visitor sort the columns themselves at a later date - but that looks a little bit complex.

Thanks for the help!

Member Avatar for rajarajan2017

Good Work urtrivedi!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.