SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    newbie help! - php how to retrieve and show mysql results by date

    newbie question - retrieve and show mysql results by date

    Hi - I'm working my way through the sitepoint book "building websites with PHP and MYSQL" by Kevin Yank.

    I'm a little stuck and really really would appreciate help on one aspect of retrieving and presenting data from a mysql table using PHP.

    I am sending a query to the database and want the results to be returned and formatted on the page, which is fine, but now I want this to be formatted in date order. The database parameters are simple:

    Jokeid
    joketext
    jokedate

    My current code is:

    -----------------------------

    // Request the ID and text of all the jokes
    $result = @mysql_query('SELECT id, joketext FROM joke');
    if (!$result) {
    exit('<p>Error performing query: ' .
    mysql_error() . '</p>');
    }

    // Display the text of each joke in a paragraph
    // with a "Delete this joke" link next to each.
    while ($row = mysql_fetch_array($result)) {
    $jokeid = $row['id'];
    $joketext = $row['joketext'];
    echo '<p>' . $joketext .
    ' <a href="' . $_SERVER['PHP_SELF'] .
    '?deletejoke=' . $jokeid . '">' .
    'Delete this joke</a></p>';
    }

    -----------------------------

    In the MYSQL table there is a parameter jokedate which is not called in the above PHP code - how would I go about getting the returned results in date order. Would I alter the above $jokeid variable to $jokedate instaed, or do I need to create a seperate piece of code with the PHP usort() function.

    Help!

  2. #2
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Hola SenorGonzalez bienvenido a Sitepoint

    You need to alter your query slightly and add an order by clause
    PHP Code:
    $result = @mysql_query('SELECT id, joketext FROM joke order by jokedate asc'); 
    this will order the results by date in an ascending order, you can also use DESC to swap it
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  3. #3
    SitePoint Wizard frank1's Avatar
    Join Date
    Oct 2005
    Posts
    1,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if that field is datetime it would be better to sort with taking post time as well..
    and normally you would want the latest joke to appear first for that
    do
    DESC instead of ASC in that query
    just, in case yo u need it

  4. #4
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cheers spikez and frank1 for this very quick help.

    so much to learn... so little time.

    - the answer then is a simply an addition to my sql query (order by) rather than any additional php functions outside the sql query. that seems logical, i'll try this out as soon as I'm back online later.

    thanks again!!

  5. #5
    SitePoint Wizard frank1's Avatar
    Join Date
    Oct 2005
    Posts
    1,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by senorgonzalez View Post
    cheers spikez and frank1 for this very quick help.

    so much to learn... so little time.

    - the answer then is a simply an addition to my sql query (order by) rather than any additional php functions outside the sql query. that seems logical, i'll try this out as soon as I'm back online later.

    thanks again!!
    never mind
    it seems like you are starting the things..
    so some of my suggestion are...
    get a good book on php and go in to it first before posting things online...
    second google on things...

    i am not saying not to post but it will save your time and effort in long run...u can post any time but i think it is wise to get answer directly rather than posting in forum and waiting for reply...
    and in books you will have better description...

    just a suggestion..


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
  •