Help in creating Table View

[bthalapathi]

I had a table called 'FruitSaleList' , that contains the following columns

sl.no, shop_id, fruitName, price, ....

I had also another table called 'FruitList' that contains the columns

shop_id, fruitName, salesManId, ....

the problem is 'FruitList' table doesn't contain a column like fruitId.

Let us assume there are following fruits are available in all shops

Apple
Banana
Cherry
Dates
Elderberry
Figs
Grapes
Jackfruit
Orange
(it is a lengthy list, around 50)

In some case I need to list the details of fruits after 'Grapes' from 'FruitSaleList' . That means I have to list the 'Jackfruit' and 'Orange' from 'FruitSaleList' .

I'm struggling to list these using a MySQL query in php.

If I create a table view(FruitDetai ls) like below then I will proceed as specified below

|FruitId|FruitN ame|
|1 |Apple |
|2 |Banana |
|3 |Cherry |
|4 |Dates |
|5 |Elderberry|
|6 |Figs |
|7 |Grapes |
|8 |Jackfruit |
|9 |Orange |

If it is possible then I will proceed by selecting the fruits after the fruitId 7. I can create virtual column or table but should avoid creating or alter table with real column or table.

If I haven't explained clearly, please let me know so that I will explain even more.

any help is appreciated. thanks in advance for your help.

[Atli]

Hi.

So, your "FruitList" table is the one that actually lists the available fruits, and the "FruitSaleL ist" is the one that logs each fruit that is sold?

What is the Primary Key in the "FruitList" table? The name of the fruit?
If so, you should consider adding a integer ID field and use that as a PK instead. Using strings as PKs and FKs can get very messy and eat up a lot more recourses than is needed.

If you simply want to select items after a given value in alphabetical order, you could simply do:
[code=mysql]
SELECT * FROM `FruitList`
WHERE `FruitName` > 'Grapes'[/code]
If the `FruitList` column is a string, this should give you every entry in the table that would be alphabetically sorted after 'Grapes'.

Is this what you are trying to do?

[bthalapathi]

Thanks for helping me! Atli.

First I will answer, your questions,

[Atli.] your "FruitList" table is the one that actually lists the available fruits, and the "FruitSaleL ist" is the one that logs each fruit that is sold?

[thala] Absolutely, you are correct. "FruitList" is the table having unique fruits. Sales logs are available in "FruitSaleL ist" table.

[Atli.] What is the Primary Key in the "FruitList" table? The name of the fruit?

[thala] Yes, fruitName is the Primary Key. It's my bad time to have this.
-------------------------------
For explanation purpose, I have listed the fruits in alphabetical order. Actually it will not be in alphabetical. But when we query the FruitList table, it will list the fruits in alphabetical order. Hence, I hope, I can solve this problem using your precious idea\help.

Currently, I'm out of reach of MyDesktop, once I back to my work, I'll update the status soon.

Still, I have a doubt,
Is it possible or Is there a anyway to create table VIEW as FruitDetails as i explained in my post.

Atli, Thanks Once again for your help

thala.