Results 1 to 10 of 10
  1. #1

    sorting with php and mysql

    i need to sort a group of rows of data from mysql
    say i do:
    $i = select * from tablex where register=1

    and it comes back as an unsorted bunch of data. say there is a field called, age, and i would like to sort all the rows by age, and then display the rows in a simple table or the like.

    i am looking into using php sort(); but it seems like there may be a simpler way.

    anyone here have any suggestions for me?

    thanks, everyone on these forums is extremely helpful! thanks everyone

    EDIT btw upon further research it seems i can ORDER BY, however this will still not work, as the data is stored like this:
    XXXX_age
    and i usually just explode("_",$row["age"])
    Last edited by variable; 06-12-2005 at 02:30 AM.
    workstations&servers @
    http://www.advancedproprietarysystems.com

  2. #2
    Join Date
    Oct 2003
    Location
    Georgetown, Ontario
    Posts
    1,761
    ии Repeat after me... ProSupport is the best... Prosupport is... ии
    ProSupport Host Support System - OUT NOW! Grab a copy yourself and see what the hype is about!
    VertiHost Inc. - We run a quality business. Do you?

  3. #3
    Join Date
    May 2004
    Location
    Singapore
    Posts
    262
    EDIT btw upon further research it seems i can ORDER BY, however this will still not work, as the data is stored like this:
    XXXX_age
    and i usually just explode("_",$row["age"])
    What is the "XXXX"? You might look towards restructuring your database table a little so that you can sort by age in the SQL instead of having to sort in PHP.
    #include<cstdio>
    char*s="#include<cstdio>%cchar*s=%c%s%c;%cint main(){std::printf(s,10,34,s,34,10);}";
    int main(){std::printf(s,10,34,s,34,10);}

  4. #4
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    Actually, you can use ORDER BY here -- you just need to get a bit fancy with your SQL.

    This works :

    Code:
    CREATE TABLE string_test (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, tkey VARCHAR(8));
    
    INSERT INTO string_test (id,tkey) VALUES ('','XXX_10'), ('','XXX_15'), ('','XXX_30'),('','XXX_1');
    Note that I deliberately inserted values in a "random" order

    Now, to get the results ordered by the digit after XXX_ you use the following query :

    SELECT * FROM string_test ORDER BY CAST(SUBSTRING(tkey FROM 5) AS SIGNED);

    This will return the rows ordered by the digit after XXX_
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  5. #5
    i am gonna not sort by age, i am going to do by registration time instead.
    the way the data is stored is:

    second_minute_hour_day_month_year_dayofyear(out of 365)_unixtimestamp.

    i dont know enough about the use of date, but is it possible to convert that unixtimestamp into second_minute_hour_day_month_year_dayofyear data? then all i would need is to store the unixtimestamp, and could sort very easily with mysql order by command.
    workstations&servers @
    http://www.advancedproprietarysystems.com

  6. #6
    uh, wow, i am ignorant of the function of date. i am just going to use unixtimestamp, and use date to convert that into second_minute_hour_day_month_year_dayofyear.

    that way i can orgnaize everything with unixtimestamps.

    seems simply enough, cant beleive i overlooked that, i only thought date returned the current time.

    thanks everyone
    workstations&servers @
    http://www.advancedproprietarysystems.com

  7. #7
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    Why would you want to store the time, down to the second -- and the day of the year AND the timestamp is beyond me. Just use the TIMESTAMP field type.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  8. #8
    ya, i just didnt know you could use date to convert any time other then the current time. i know now though,
    workstations&servers @
    http://www.advancedproprietarysystems.com

  9. #9
    Join Date
    May 2005
    Posts
    38
    This may help with sorting the results after you've pulled them into PHP...

    http://us3.php.net/manual/en/functio...-multisort.php

  10. #10
    You can also easily switch into a timestamp by using strtotime(thedate);

Posting Permissions

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