SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2004
    Location
    Rochester, NY
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Sorting Display Questions

    Hey fellas, how's it going? I have two questions pertaining to sorting results from a mysql database using PHP. The first:

    The search engine that a prior web dev co-op (my current position) built for the site was designed to do daily indexing time jobs so that when you searched for an article for a certain keyword, it would sort from newest to oldest. Well, for whatever reason shortly after that co-op left (sabotage!) the indexing stopped and it had to be fixed again by RIT's ITS department. The result, is this:

    http://www.rit.edu/~930www/search/in...tronics&path=1

    As you can see, it begins sorting the newer stuff properly (post-ITS fix), but then the dates get screwed up where you can see older results ahead of newer results (the articles that were added while the indexing was not working).

    Basically, I'm wondering if there is a way to fix it so that I can get all of the results displaying properly from newest to oldest, and not just the ones post-indexing fix.

    I'm assuming this information is still too vague to receive much help on, so please, tell me what you need and I will provide it! I really need some help on this one, can't disappoint my first web dev boss

    Second:

    I want to be able to view 6 records from a mysql database, all of which are either the most recently updated or a completely new record. This is the page that they are to be displayed on (page has buttons that show the tables that I have to pull this information from):

    http://www.rit.edu/~930www/experts/

    I also haven't a clue as to how to implement this, and any help would be much appreciated!

    Thanks a bunch for your time and help guys!

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your sorting problem:
    as I understand it, some records have the correct sorting info, some don't.
    Please post some DDL so I can see the table structure.

    If it is no longer possible to recreate the correct sorting information based on time (I am flying blind here) then can you assume that the record order in the table represents the order in which the records were inserted? i.e. newest records at the top of the table, oldest records at the bottom?

    If so, I would create an autoincement field on the table, the ordering is highly likely to be correct. Then just sort the returned records on your field, descending (Sort by AUToFIELD DESC).

    About the 6 records, what do you mean comletely new records? The newest record *must* be in the table, you should use some field on which to sort based on sequential entry to the table (autoincrement field). Then you just take the top six records:

    select TOP 6 FIELD1, FIELD2,....
    from MYTABLE
    sort by AUTOFIELD desc

    HTH
    Last edited by asterix; Jan 4, 2005 at 16:32. Reason: incorrect sql :)

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2004
    Location
    Rochester, NY
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey asterix, thanks for the reply! I'll try to get you some DDL information, but unfortunately this site is massive, the work was done by someone else (with no documentation), and my boss's web design skills don't encompass anything in this area so I barely know where to start, heh. I'll check into it but for now I think I can clear a few things up that I didn't do a good job of explaining.

    The first thing that I meant by the updating part of my 2nd question, is that sometimes old records are updated with new information. Say that four new record entries were added, and two real old entries were updated. I would want those four entries displayed at the top of the page, along with the two real old entries that had their records recently updated, totalling six visible records.

    Your suggestion for the first part may indeed work though now that I think about it! I'll give it a spin and keep ya posted!

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, you want to get the 6 most recently modified documents (updated or inserted).

    The solution:
    Add a TIMESTAMP(14) field to the table. Each record is now timestamped and can be sorted on that stamp. Unfortunately, I don't see a reliable way of keeping the current data in any particular order, the timestamp only helps for changes made to rows after the timestamp was added...

    -- Add the field
    ALTER TABLE tname ADD timestampfield TIMESTAMP(14)

    -- Set it to the 01.01.1970
    Update tname
    set timestampfield="19700101000000"

    And then to sort...
    Select fieldlist
    from tname
    order by timestampfield DESC, somefieldwithdate DESC

    But as I said, all of the fields will initially have the the same timestamp value, only making it useful for sorting for modifications occuring from now on. Thats why you need to also sort on some other field containing maybe the publication date of the article.


    HTH

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2004
    Location
    Rochester, NY
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool!

    That's some pretty interesting and useful information asterix, thanks! Now, where should I go about doing this, in PHP or from the MySQL prompt itself? I'm assuming the prompt.

    If it's not too much trouble, I'd like to walk through what I think this is doing, and you can correct me if I am wrong.

    PHP Code:
     -- Add the field
    ALTER TABLE tname ADD timestampfield TIMESTAMP
    (14
    This adds a new TIMESTAMP column named timestampfield.

    PHP Code:
     And then to sort...
    Select fieldlist
    from tname
    order by timestampfield DESC
    somefieldwithdate DESC 
    Not sure what fieldlist is representing, but this first orders everything based on the timestamp, and then by the field that has the publication date.

    Did I do OK? I'm real sorry for my lack of knowledge, I unfortunately haven't hit my database classes in college yet and don't have a whole lot of time to read my sitepoint book I just know a bit from my spare time dabbling here and there. I really do appreciate your help again, thanks!

    PS - What is the significance of setting it to 01-01-1970?

  6. #6
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dannov
    That's some pretty interesting and useful information asterix, thanks! Now, where should I go about doing this, in PHP or from the MySQL prompt itself? I'm assuming the prompt.
    That's right, the mySQL client. Although, more commonly, people install phpMyAdmin and query the database through their web browser.

    Quote Originally Posted by dannov
    If it's not too much trouble, I'd like to walk through what I think this is doing, and you can correct me if I am wrong.

    PHP Code:
     -- Add the field
    ALTER TABLE tname ADD timestampfield TIMESTAMP
    (14
    This adds a new TIMESTAMP column named timestampfield.
    Yes. You will need to change the name of the table; tname, to suit your table.

    Quote Originally Posted by dannov
    PHP Code:
     And then to sort...
    Select fieldlist
    from tname
    order by timestampfield DESC
    somefieldwithdate DESC 
    Not sure what fieldlist is representing, but this first orders everything based on the timestamp, and then by the field that has the publication date.
    Select field1, field2, field3, field4.... from ....
    Specifies the columns you want to retrieve.

    Quote Originally Posted by dannov
    Did I do OK? I'm real sorry for my lack of knowledge, I unfortunately haven't hit my database classes in college yet and don't have a whole lot of time to read my sitepoint book I just know a bit from my spare time dabbling here and there. I really do appreciate your help again, thanks!
    No Problem. Everyone begins learning somewhere.
    Quote Originally Posted by dannov
    PS - What is the significance of setting it to 01-01-1970?
    Lets say you add the timestamp field tomorrow. Each row will have a new field with the following time in it:

    20050108093000

    if the column was added at exactly 09:30 on Jan. 08th 2005. This is not really useful for you, since it will tell you that this row was last modified on Jan 08th. I would set the date in the timestamp to its minimum possible date: 01.01.1970.

    Hopefully you already have another field in your database which you are currently using to sort on. Lets call that field "somefieldwithdate". Now what you want is, to retrieve the top 6 records based on

    a) The last modification date stored in the TIMESTAMP
    b) If several records have the same timestamp value, sort on a second field (somefieldwithdate)

    So:

    Code:
     Select TOP 6 field1, field2, field3 
    from MYTABLE
    Order By timestampfield DESC, somefieldwithdate DESC
    This says: return a result set with 3 fields in it coming from MYTABLE.
    Sort the results descendingly by the timestamp field, and if that contains the same value then sort on someotherfield.

  7. #7
    SitePoint Zealot
    Join Date
    Oct 2004
    Location
    Rochester, NY
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Geez that's easier than I thought! Man DB stuff is real cool, gives you so much control over how content is displayed. Thanks again Asterix and I'll get to work on this asap and will get back to ya if I encounter any hurdles. Thanks again, and I'm glad that people like yourself are willing to invest the time required into helping us newbies


Bookmarks

Posting Permissions

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