current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 1 down vote favorite

Alright I am still a little rusty at php/mysql. So this may not be the best method of achieving what I am trying so let me know what or how I should go about achieving this:

I am working on a small project for a storage unit list. In my database I have the following:

Unit id |  Area  |   Row   | Unit | Status         | 
        |  1-9   | 1 - 200 | 1-20 | Empty / Renter |

Unit Id is made of sector.section.unit

Now what I would like to do is display a list of the 20 unites from each sector and section on a page.

1.  1.300.1  - Empty
2.  1.300.2  - John Doe - $75 Monthly - 10x10
...
19. 1.300.19 - Jane Evens - $40 Bi-Monthly - 10x10
20. 1.300.20 - Empty

What I am thinking is after grabbing all mysql data that is in area 1, and in row 300 convert the data to a php assosiative array by it's unit id. Then simply do a for() statement for the 20 units of a row. Displaying the array data if any follows it. How can I go about doing this?

Or should I just go ahead and just have every single unit in the database as empty till otherwise filled?

Thank you in advanced,

share|improve this question
    
Please describe better your table structure and what are you trying to do. – Alex Amiryan Mar 25 '12 at 15:21
    
Sorry I tried formatting it but the filters removed it. What I want is a vertical list counting each unit of a row (1-20) Then display empty if nothing is in the database or the information about it if there is anything. I am thinking if there is any information connected with a storage unit. If there is information place it into an associative array in php and pass it back so that I can easily do an explode on the array if information is contained. my table has: UNIT ID | AREA | SECTION | UNIT – benjaminj Mar 25 '12 at 15:27
    
any chance you are looking for this – Kishor Kundan Mar 25 '12 at 15:43
    
possibly, my only concern is will I be able to list the units prior that have no entry in the database? Some storage units are empty and do not have renters, so A list may be : unit 1: empty, unit 2: empty, unit 3: (array of information) John Doe, $75 a Month, 10x10 etc – benjaminj Mar 25 '12 at 15:47

1 Answer 1

up vote 0 down vote

Here the code that works. It creates temporary tables fills it with sequence of numbers then joins that table with your units table. You will get NULLs on the rows where you don't have any record.

CREATE TEMPORARY TABLE `sequence` (
  `Id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

DELIMITER //
CREATE PROCEDURE fill_sequence()
BEGIN
  DECLARE v1 INT DEFAULT 20;
  WHILE v1 > 0 DO
INSERT sequence VALUES (NULL);
SET v1 = v1 - 1;
  END WHILE;
END //
DELIMITER ;

CALL fill_sequence();
SELECT * FROM sequence LEFT JOIN units ON (sequence.id = units.unit_id);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.