Help displaying data

[jj.amonit]

I was wondering if anyone has the answer or can point me to a topic with the answer for this problem I have.

 

Let's say my users take inventory of grocery stores.  And for this example, every grocery store has the same three products (apples, watermelons, and plums).  But when it comes to oranges, they may have up to three different types of oranges.  Each time the user goes to take inventory, they count all of them up and enter them in. 

 

In order to accomodate all the inventory, I created a database that has two tables.  Table A records the three constants: apples, watermelons, and plums.  It stores the date of inventory, and the number of fresh and bruised of each fruit.

 

I then use the index for that day's inventory and enter it into Table B for each variety of oranges for that store.  For example, let's say the grocery store has two varieties of oranges.  So, for every date of inventory, two rows are created in Table B, one for each variety.  Each row contains the index from Table A and the number of fresh oranges and the number of bruised oranges for that variety.

 

Now, once the inventory is finished, I want to display the data for the user.  So, I create a table to show it.  I want it to look like this.

 

Food            |    Date

                        01/01/2009

apples fresh            6

apples bruised        8

watermelon fresh    10

watermelon bruised  12

Plums fresh            15

Plums bruised          10

Orange 1 fresh        5

Orange 1 bruised      7

Orange 2 fresh        10

Orange 2 bruised      15

 

To add to the complexity, this is an accumulative project.  As more inventories are taken, a new column is added to the right of the previous date.

 

Okay, so here is my results so far.  I can get the fixed "table a" to display properly.  I query the db for each row I want to display.  It's probably terrible code, but it works.

 

However, when I get to displaying the data from 'table b', I begin to have problems.  I can get the data to show up properly for Orange1.  But if that works, I can't get Orange2 to show up.

 

I'm new to php programming, and just don't know what functions to use.  I tried while, foreach, etc.  It may seem simple to experts, but I am just at a loss.  And I don't have a mentor to ask for help. 

 

So, did I design my database improperly?  Am I just missing something is my search for code?

 

I will be happy to answer any questions, if I didn't explain myself clearly enough.

 

Thanks

[hellonoko]

You should probably put you code up so we can see it?

 

You could also do something were rather than having two tables you had one table and a 'fruit type' column then you could:

 

SELECT * FROM fruit WERE fruittype = 'citrus' AND bruised = 'FALSE'

 

You could use a union to combine queries and then display the results.

[tracivia]

there are differently methodes of fetching mysql raw data. If you get more than one result, you have to fetch the data in a loop. There could be two reasons. 1, your query returns just one result, 2, you fetch raw data without a loop so you just get the first result.

 

Another point, if you put a common field in both tables, you can join them easily.

 

main

id name

1 orange

2 apple

 

sub

id main_id name

1 1          bruised

2 1          fresh

3 2          fresh

 

SELECT main.name AS mname, sub.name AS sname FROM main, sub WHERE main.id=sub.main_id;

 

this query gets data from both tables and returns the same data as you posted.