Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Unanswered: PHP Application - MySql Problem

    Hi, i have a sql problem; I have some records ordered by date, and using PHP i can view the contents of an item in detail in my browser. However, i need a previous and next link to be able to jump quickly to other items.

    However, since some of my items have the same date i have been having problems with selecting these previous and next items.

    Here is a sample Database output :

    SQL code to generate this :

    Code:
    SELECT * FROM jokes WHERE categoryID = '$categoryID' ORDER BY date DESC, title ASC LIMIT $start, $number
    Holiday Dinner Oct 29th
    Just Married Oct 26th
    The geography Oct 26th
    Top 10 slogans Oct 26th
    Pregnant Oct 25th
    Temptation Oct 25th

    Next Item SQL =

    Code:
    SELECT title FROM items WHERE date <= '$date' AND categoryID = $category AND itemID != $itemID order by date desc, title asc limit 1
    Previous Item SQL =

    Code:
    SELECT title FROM items wHERE date >= '$date' AND categoryID = $category AND itemID != $itemID order by date asc, title desc LIMIT 1
    the $.. are variables, collected from the item the user is looking at.

    When i look at an item in detail, here are the previous and next item suggestions:

    Previous = Newer item
    Next = Older item

    holiday dinner:
    NEXT -> just married
    PREVIOUS -> /

    just married
    NEXT -> The geography
    PREVIOUS -> top 10 slogans INCORRECT

    The geography
    PREVIOUS -> top 10 slogans INCORRECT
    NEXT -> just married INCORRECT

    top 10 slogans
    PREVIOUS -> the geography
    NEXT -> just married INCORRECT

    pregnant
    PREVIOUS -> tempation INCORRECT
    NEXT -> temptation

    Does anyone know how I can fix this? I'm using date at the moment, don't think datetime would make a difference, because i still want them grouped by day/month and then title.

    Thank you very much

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: PHP Application - MySql Problem

    I would say to turn the date column into datetime. This way, you could have an order specified within the days with more than one title, but still group by month / day.
    It might take some work to get the datetime column ordered right. You could also just add an order column and number the titles by the order you know.
    Does this help?

    Originally posted by Xia
    Hi, i have a sql problem; I have some records ordered by date, and using PHP i can view the contents of an item in detail in my browser. However, i need a previous and next link to be able to jump quickly to other items.

    However, since some of my items have the same date i have been having problems with selecting these previous and next items.

    Here is a sample Database output :

    SQL code to generate this :

    Code:
    SELECT * FROM jokes WHERE categoryID = '$categoryID' ORDER BY date DESC, title ASC LIMIT $start, $number
    Holiday Dinner Oct 29th
    Just Married Oct 26th
    The geography Oct 26th
    Top 10 slogans Oct 26th
    Pregnant Oct 25th
    Temptation Oct 25th

    Next Item SQL =

    Code:
    SELECT title FROM items WHERE date <= '$date' AND categoryID = $category AND itemID != $itemID order by date desc, title asc limit 1
    Previous Item SQL =

    Code:
    SELECT title FROM items wHERE date >= '$date' AND categoryID = $category AND itemID != $itemID order by date asc, title desc LIMIT 1
    the $.. are variables, collected from the item the user is looking at.

    When i look at an item in detail, here are the previous and next item suggestions:

    Previous = Newer item
    Next = Older item

    holiday dinner:
    NEXT -> just married
    PREVIOUS -> /


    just married
    NEXT -> The geography
    PREVIOUS -> top 10 slogans INCORRECT

    The geography
    PREVIOUS -> top 10 slogans INCORRECT
    NEXT -> just married INCORRECT

    top 10 slogans
    PREVIOUS -> the geography
    NEXT -> just married INCORRECT

    pregnant
    PREVIOUS -> tempation INCORRECT
    NEXT -> temptation

    Does anyone know how I can fix this? I'm using date at the moment, don't think datetime would make a difference, because i still want them grouped by day/month and then title.

    Thank you very much

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    But wouldn't i then be ordering on time if more than one item has the same date (day)? I don't quite understand, could you elaborate a bit :s

    Btw, don't think an order column would be good, with every new, changed or deleted item I'd have to re-order that.

    Thanks,
    Wesley

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    By changing the date to datetime, you can give an order to the titles that are on the same day, but it wouldn't cause problems when you insert or delete. You don't need to consider the time part of the column once the order is set. If you had two titles on the same day and you knew the ordering, then you could give them values like this:

    title 1 9/23/2003 01:01:01
    title 2 9/23/2003 02:01:01

    The time values do not need to mean anything. It would take care of the misordering and you wouldn't have to change the SQL statements that you have.

    Originally posted by Xia
    But wouldn't i then be ordering on time if more than one item has the same date (day)? I don't quite understand, could you elaborate a bit :s

    Btw, don't think an order column would be good, with every new, changed or deleted item I'd have to re-order that.

    Thanks,
    Wesley

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •