Retrieving an amount of rows depending on an entry?

[kenuk110]

Hi,

 

I'm pretty new to this but here goes. Thank you in advance for any replies.

 

I have 'blocks' that are labeled 0001, 0002, 0003, 0004,0005 for instance and within these blocks I have 'numbers', these go from 01-99, so 01,02,03,04...

 

Each block has a different amount of numbers within them. Say 0001 only has 4 numbers within it but 0002 has 38. I have to produce a query that allows me to say 'bring me back 400 distinct 'numbers' total' from blocks. These need to be distinct as in the block reference can only be used once. I use this information to issue instructions to our guys. They should have 400 'numbers' total to process each day but at the moment I have 7 queries bringing back static block references which total 400 numbers and this is about to increase as we employ more people.

 

This whole message may not make sense, if so, please get in touch anyway just to say it's rubbish! I'll try to write it in a better way.

 

Thanks again in advance for any assistance.

 

Regards

 

Ken

 

 

[phpknight]

Can you give a real world example?  That would help me think it through.

[kenuk110]

Hi,

 

Thanks for replying.

 

I work for a merchandising company, we basically put products on to shelves in supermarkets. Each section in a supermarket is called a 'Block', when you go down the cereal aisle you see Kellogg's for instance, this will pretty much be in it's own block in the supermarket. Each block can have multiple items on it, say Crunchy Nut Cornflakes, Regular Cornflakes, Special K etc. Each different product has a number assigned to it. By the way, 'we' have named/labelled the store like this, so we have BLOCK then INUMBER.

 

We have an amount of staff working in these stores each day and I wanted to divide the amount of INUMBER's between them on sheets of paper - just like a report really. Now the complicated bit. If we had say 200 products, I could just divide the amount by two and print 100 per page. What I can't have happen is that the INUMBER's from one block appear on the other guys sheet as they would both be working on the same block then and getting gin each others way. What I would like to do is say okay, this guy will actually get a few more than the other guy as the block he was allocated (randomly) has 7 more products on it and I'm not allowed to print any block onto two separate reports.

 

I really have no idea how to or if this is even possible but please reply if you need me to explain more.

 

Thanks

 

Ken

 

 

 

[phpknight]

Okay, is this something you need to accomplish only with mysql, or can you use PHP, too?

[phpknight]

BTW, if you can give me a sample structure with fake rows in there so I can see it, that would help a lot.  As long as you can think it through logically, though, there is not much you cannot do if you can get your head around the problem.

 

To clarify, the block is the space on the shelf, and the product has an inumber.  A person has to get 400 total inumbers to work with each day.  Two people can be shelving the same product (i number) in different locations in the store (like the front of the aisle and the middle or the top shelf and the bottom), but they cannot be working on the same block at all, right?  Please correct me if I am misunderstanding you.

 

 

[kenuk110]

Hi,

 

I'm just using MySQL at the moment, I use Navicat to produce the reports so I have no way at the moment of using PHP.

 

Here is some sample data:

 

COUNTRY  CITY  STORE  BLOCK  INUMBER  UPC                SAT  SUN  MON  TUE  WED  THU  FRI

44            01      002      0001    01            2331456423      Y      Y      Y        Y      Y        Y      Y

44            01      002      0002    02            7736253738      Y      Y      Y        Y      Y        Y      Y

44            01      002      0099    01            3948574837      Y      Y      Y        Y      Y        Y      Y

44            01      002      0143    01            8474837495      Y      Y      Y        Y      Y        Y      Y

44            01      002      0143    02            9893839282      Y      Y      Y        Y      Y        Y      Y

44            01      002      0143    03            2232345455      Y      Y      Y        Y      Y        Y      Y

 

Is that enough?? I can send the SQL if needed.

 

A block is the unit that contains shelves, the inumber is the product on the shelf. The way we work doesn't require the shelf number. I have forgotten what else you wrote, let me post this the reread it just in case I missed something.

 

Ken

 

 

[kenuk110]

"To clarify, the block is the space on the shelf, and the product has an inumber.  A person has to get 400 total inumbers to work with each day.  Two people can be shelving the same product (i number) in different locations in the store (like the front of the aisle and the middle or the top shelf and the bottom), but they cannot be working on the same block at all, right?  Please correct me if I am misunderstanding you."

 

Okay, so a block is the main unit in an aisle then the inumber is the product on the shelves within the block. As posted, we don't use shelves to organise things, just block and inumber. Each person has to get 400 inumbers each day yes but they must be on different blocks from each other, no two people should ever work on the same block - they would get in each others way! I think you have it correct above apart from the shelf bit.

 

Make sense??

 

Cheers

 

Ken

 

 

 

[phpknight]

Is there a way you can  code after and just increment numbers for each user's inumbers while making sure there are no duplicates?  I am not sure how to do this with just mySQL.