1

What I need to do

  • I have two tables, "chapters" and "pages".
  • Each chapter has many pages.
  • Chapters are sorted by a position column.
  • Pages are sorted by a position column relative to its Chapter. So the first page in each chapter has a position of 1.
  • I need to get the 3 pages before and after (if there are 3 pages before/after the current page. ie - on page 2 there is only 1 page before the current)
  • I only have the current chapter's 'name' & current page's 'name' attributes.
  • I'm using PHP and MySQL

"chapters" Table

  • id
  • name
  • position

"pages" Table

  • id
  • chapter_id
  • name
  • position

How do I construct a query that will return an associative array of pages so that I have something as follows (Note: I can use php to do a series of queries if needed):

  • pages[0]["id"]
  • pages[0]["name"]
  • pages[0]["chapter_name"]

Thanks!

yourfavorite
  • 517
  • 8
  • 21
  • the current page name might not be enough, if name is not defined as unique... where is your code so far? – mindandmedia Mar 06 '12 at 20:20
  • Sorry, updated. I also have the current chapters name attribute as well. My current code is being thrown out as I'm changing from getting every single chapter and page and storing them in a JSON object that I pull out the necessary content using Javascript to an AJAX approach that will return only the pages needed. – yourfavorite Mar 06 '12 at 20:23
  • if this is homework, you should tag it as such. if not, please show your efforts so far and what problems you are running into. thx – mindandmedia Mar 06 '12 at 20:25
  • what if you are on chapter 2, page 2 and you need to get 3 pages before. do you get c2p1, c1p4, c1p3 or do you just get c2p1? – mindandmedia Mar 06 '12 at 20:26
  • Sorry, kind of new to StackOverflow. This isn't homework, just a personal project. I don't have any code relevant to this specific issue currently as I'm not sure how to go about it. The only thing I can think of is to get all the chapters and pages from the database and limit it down to the necessary content from there. But that's what I'm trying to avoid. – yourfavorite Mar 06 '12 at 20:29
  • Assuming there are 2 pages in each chapter and you are on c2p2, you would get: c1p1, c1p2, c2p1, **c2p2**, c3p1, c3p2, c4p1 – yourfavorite Mar 06 '12 at 20:31

3 Answers3

0

I think that you are looking for such a query:

SELECT
    pages.id AS page_id,
    pages.name AS page_name,
    chapters.name AS page_chapter
FROM pages
INNER JOIN chapters ON (chapters.id = pages.chapter_id)
WHERE pages_id IN
    (page_id -3, page_id - 2, page_id - 1, page_id,
    page_id + 1, page_id + 2, page_id + 3)
ORDER BY page_id ASC
Mohammad Naji
  • 5,372
  • 10
  • 54
  • 79
0

if you can change the tables, why not add an ordering column to the pages table?

instead of using an int and having to move everything everytime you insert a new page, you could also use a double and just use the middle value between this and the next page.

mindandmedia
  • 6,800
  • 1
  • 24
  • 33
  • I think that this is probably the best way to go about it. I've changed the format of position on the pages table to be 1.001, 1.002, 2.001, 2.002, 3.001, etc. So [chapter position].[page position]. This means when reordering chapters I'll still have to modify each page of the chapter but at least it will be simpler as I can just increment all pages in a chapter by whole numbers. Thanks for the help everyone! – yourfavorite Mar 06 '12 at 22:18
  • i ment to leave the chapters position as is (ordered int), and give the pages a continuous ordering. chapters are just virtual groupings of pages and when you move them, you replace a block of page-numbers with the other one. finally, your select will be `between max(0,pageNr -3) and min(pageNr+3,totalPages)` – mindandmedia Mar 06 '12 at 22:31
0
$cChapter = 5; //Current chapter's id
$cPage = 4; //Current page's id

//$PagePos = the current's page position (when 1 is the first page , not 0)
//$MaxPosExists = the highest position value for the current chapter
$range = 3; //3 pages before and 3 pages after

$minPosition = $PagePos - $range;
$maxPosition = $PagePos + $range;

if($minPosition <= 0)
{
 $minPosition = 1; //the current page is the first,second or third page.
}

if($maxPosition > $MaxPosExists)
{
 $maxPosition = $MaxPosExists;
}


$getPages = mysql_query("SELECT * FROM pages WHERE chapter_id='$cChapter' AND position BETWEEN $minPosition AND $maxPosition ORDER BY position ASC");

now , just fetch that query and make sure you're ignoring the current page from that array.

Ofir Baruch
  • 10,323
  • 2
  • 26
  • 39