Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: How to sort MySQL data using the last word/date

  1. #1
    Join Date
    May 2007
    Location
    South Africa
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default How to sort MySQL data using the last word/date

    Hi Folks,

    I have a page that presents properly.

    I do however want the page to sort by using the date following the text, that is "BRYANSTON CC - 31/07/2011"

    I would like to sort using the date portion.

    The date is not set in the MySql as data but as a heading for a tournament.

    Any help in this regard would be appreciated.

    Regards
    Rob

  2. #2
    Join Date
    Feb 2009
    Posts
    303
    Thanks
    18
    Thanked 36 Times in 36 Posts

    Default

    I don't think there's any way to do that (easily). I recommend upgrading your database structure to be more fitted to the content.

    If you have a column named "date" setup as a date field, you could just use:
    Code:
    mysql_query("SELECT ... ORDER BY `date`");
    Otherwise there's probably a way to use PHP to strip out just the last 10 characters, but I think it'd be better in the long-run to upgrade your database structure.
    Alex Blackie, X96 Design
    My Website
    I specialize in: HTML5, CSS3, PHP, Ruby on Rails, MySQL, MongoDB, Linux Server Administration

  3. #3
    Join Date
    May 2007
    Location
    South Africa
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default

    Hi,

    Thanks for the reply.

    To change the database at this stage is rather complicated.

    The point of entry has been simplified to cater for children to enter the information and thus to have many inputs only leads to errors and junk sometimes being submitted.

    No matter the other option of striping the last 10 characters is an idea.

    I have treid this currently

    PHP Code:
    if($row['i_wish_to_play'] != $last_wish_to_play)
    {
    // the first n words to extract
    $n 6;
    // extract the words
    $words explode(" "$row['i_wish_to_play']);
    // chop the words array down to the first n elements
    $firstN array_slice($words0$n-1);
    // glue the 3 elements back into a spaced sentence
    $firstNAsAString implode(" "$firstN);
    }
    if(!isset(
    $counter[$firstNAsAString]))
    $counter[$firstNAsAString] = 1;
    else
    $counter[$firstNAsAString]++;
    $last_wish_to_play $row['i_wish_to_play'];

    $i++;
     echo 
    '</td></tr>'
    Please can you advise how to change this and thus get the desired result

    Regards
    Rob

  4. #4
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,413
    Thanks
    101
    Thanked 115 Times in 113 Posts

    Default

    If your date is in different formats a query would not always work. MySQL does have a regular expression functionality, which would allow you to search by the last 10 characters, however, I do not think that your date is in the correct format. You could write a script to take the last 10 characters in column a and insert it into column date while reformatting it to the correct format in the process. This would be handy if you have a lot of data to work with.

    You may also want to create a script where the date is entered automatically or where only the correct data can be inserted into the database, for example using a drop down menu with numbers for the day, month, and year.
    To choose the lesser of two evils is still to choose evil. My personal site

  5. #5
    Join Date
    May 2007
    Location
    South Africa
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default

    Hi,

    Thanks for the reply.

    In no form of disrespect but in terms of learning. I have found this link and if I am not mistaken the date format will be Ok as presented. Please correct me if I am wrong in my understanding as so much is presented on the web which is not always correct.

    If the format of dd/mm/yyyy be ok then I need to get a script that will firstly extract the date portion like you say and then submit it to a date field in the database. Can you help with this?

    If I can do it this way then the query becomes easy as it would then be a sort by date.

    I hope you can help.

    Regards
    Rob
    Regards
    Rob
    Last edited by Rob (SA); 01-01-2011 at 07:48 AM.

  6. #6
    Join Date
    May 2007
    Location
    South Africa
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default

    Hi James,

    I have managed to make the changes as you proposed - thank you

    I do now have one small challenge and was wondering if you or another could assist.

    PHP Code:
    <!------------------------------------------------------------------>
    <!                  THIS SECTION CHANGES THE MAIN HEADING           > 
    <!------------------------------------------------------------------>
    <table>
    <td colspan="1" width="15%" align="center" bgcolor="#376091"><font style="font-size:11px" color="#FFFFFF" face="Arial"><b>TOURNAMENT ENTRIES</b></font></td>
    </table>

    <table>
    <td width="8%" align="center" bgcolor="#376091"><font style="font-size:11px" color="#FFFFFF" face="Arial"><b>No</b></font></td>
    <td width="24%" align="center" bgcolor="#376091"><font style="font-size:11px" color="#FFFFFF" face="Arial"><b>TOURNAMENT</b></font></td>
    <td width="10%" align="center" bgcolor="#376091"><font style="font-size:11px" color="#FFFFFF" face="Arial"><b>DATE</b></font></td>
    <td width="8%" align="center" bgcolor="#376091"><font style="font-size:11px" color="#FFFFFF" face="Arial"><b>%</b></font></td>
    </table>
    <!-------------------------------------------------------------------->
    <!  THIS SECTION COUNTS THE ROWS THAT ARE UNIQUE AND PROVIDES RESULT  >  
    <!-------------------------------------------------------------------->
    <?php

    echo '<table border=1px bgcolor=#B5CCE4 bordercolor="#FFFFFF" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF" cellspacing=0>';
    foreach(
    $counter as $key => $value)

    {
    $percentage round(($value/96)*1000);

    echo 

    '<tr>
    <td align="center" width="8%" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial"><b>'
    .$value.'</b></font></td>
    <td width="24%" align="left" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial">'
    .$key.'</font></td>
    <td align="left" width="10%" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial">'
    .'Date'.'</font></td>
    <td align="center" width="8%" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial"><b>'
    .$percentage.'</b></font></td>
    </tr>'
    ;

    }
    echo 
    '</table>';
    ?>
    I need for the word Date in this link to be replaced by the actual date that can be found in MySQL and correspond with the relavent tournament.

    This PHP script represents the left hand side of the page concerned.

    I look forward to any reply

    Regards
    Rob
    Last edited by Rob (SA); 01-03-2011 at 05:09 AM.

  7. #7
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,413
    Thanks
    101
    Thanked 115 Times in 113 Posts

    Default

    You are missing some php code in the sample code you posted. In particular the code that relates to foreach($counter as $key => $value). Knowing nothing else, I would replace:

    Code:
    echo
    
    '<tr> 
    <td align="center" width="8%" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial"><b>'.$value.'</b></font></td> 
    <td width="24%" align="left" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial">'.$key.'</font></td> 
    <td align="left" width="10%" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial">'.'Date'.'</font></td> 
    <td align="center" width="8%" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial"><b>'.$percentage.'</b></font></td> 
    </tr>';
    with

    PHP Code:
    $row='<tr> 
    <td align="center" width="8%" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial"><b>'
    .$value.'</b></font></td> 
    <td width="24%" align="left" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial">'
    .$key.'</font></td> 
    <td align="left" width="10%" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial">'
    .'Date'.'</font></td> 
    <td align="center" width="8%" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial"><b>'
    .$percentage.'</b></font></td> 
    </tr>'
    ;
    $rowstr_replace('Date'$value$row);
    echo 
    "$row"
    This assumes that $value is the corresponding Date entry.

    Are you saying that you created a column set with one of MySQL's date formats? That would be very good and useful.
    To choose the lesser of two evils is still to choose evil. My personal site

  8. #8
    Join Date
    May 2007
    Location
    South Africa
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default

    Hi,

    Thanks for the reply.

    I have made the changes proposed however no noticable effect.

    The problem that I see is that it does not know where to get the "Date" from.

    The $value actually represents the amount of players in each event and $key is the name of the tournament, $percentage is the tournament saturation - 96 being a fulll tournament.

    In terms of the foreach($counter as $key => $value). There is another HTML which does the calculations of the table seen on th eright hand side of the page

    Regards
    Rob

  9. #9
    Join Date
    Jan 2008
    Posts
    4,168
    Thanks
    28
    Thanked 628 Times in 624 Posts
    Blog Entries
    1

    Default

    Can I see all of that code for just that one page please?
    Jeremy | jfein.net

  10. #10
    Join Date
    May 2007
    Location
    South Africa
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default

    Hi,

    Thanks for the reply.

    Some of this information may be obsolete as it has really been a cut and paste exercise.

    I look forward to your help.

    PHP Code:
    <?php

    $connect 
    mysql_connect("localhost""e****_*****""r*******") or

    die (
    "Hey loser, check your server connection.");

    mysql_select_db("eghfya_Members");

    $quey1="select * from _Form_Nr_4 ORDER BY `Date` ASC, `my_sajgf_division:` ASC, `surname` ASC ";
    $result=mysql_query($quey1) or die(mysql_error());


    ?>
    <!------------------------------------------------------------------>
    <!           THIS SECTION CHANGES WHAT THE TABLE LOOKS LIKE         >
    <!------------------------------------------------------------------>
    <table border=1 width="85%" bordercolor="#D3E2FE" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF" style="font-size:11px" cellspacing=0>
    <!------------------------------------------------------------------>
    <!                  THIS SECTION CHANGES THE MAIN HEADING           > 
    <!------------------------------------------------------------------>
    <caption><b><font style="font-size:20px" color="#376091" face="Arial">
    </font><b><caption>
    <!------------------------------------------------------------------->
    <!    THIS SECTION FETCHES THE INFORMATION FROM THE MYSQL TABLE      >  
    <!------------------------------------------------------------------->
    <?php
    $counter 
    = array();
    $last_wish_to_play "";
    $firstNAsAString "";
    while(
    $row=mysql_fetch_array($result)){

    //----------------------------------------------------------------------------------------------------
    // THIS SECTION ADDS 3 EMPTY LINES IF THERE IS A SPLIT BUT ONLY AFTER SOME RECORDS HAVE BEEN DISPLAYED
    //----------------------------------------------------------------------------------------------------
    if($i AND $row['i_wish_to_play'] != $last_wish_to_play)
    {
    echo 
    '<tr>
    <td colspan="4">&nbsp;</td>
    </tr>'
    ;
    }
    //------------------------------------------------------
    //            THIS SECTION ADDS HEADINGS                
    //------------------------------------------------------
    if ($row['i_wish_to_play'] != $last_wish_to_play) {

    echo 
    '<tr>
    <td colspan="3" align="center" bgcolor="#376091"><font style="font-size:11px" color="#FFFFFF" face="Arial"><b>'
    .$row['i_wish_to_play'].'</b></font></td>
    <td colspan="1" align="center" bgcolor="#376091"><font style="font-size:11px" color="#FFFFFF" face="Arial"><b>'
    .$row['Date'].'</b></font></td>
    </tr>'
    ;

    echo 
    '<tr>
    <td bgcolor="#376091"><font style="font-size:11px" color="#FFFFFF" face="Arial"><b>FULL NAME</b></font></td>
    <td align="center" bgcolor="#376091"><font style="font-size:11px" color="#FFFFFF" face="Arial"><b>DIVISION</b></font></td>
    <td align="center" bgcolor="#376091"><font style="font-size:11px" color="#FFFFFF" face="Arial"><b>HANDICAP</b></font></td>
    <td align="center" bgcolor="#376091"><font style="font-size:11px" color="#FFFFFF" face="Arial"><b>CELL NUMBER</b></font></td>
    </tr>'
    ;
    }

    echo 
    '<tr><td bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial">';
    echo 
    '<option value="'.strtoupper($row['surname']).', '.strtoupper($row['name']).'">'.strtoupper($row['surname']).', '.strtoupper($row['name']).'</option>';

    echo 
    '</td><td align="left" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial">';
    echo 
    $row['my_sajgf_division:'];

    echo 
    '</td><td align="center" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial">';
    echo 
    $row['my_handicap_is'];

    echo 
    '</td><td align="center" bgcolor="#B5CCE4"><font style="font-size:10px" color="#000000" face="Arial">';
    echo 
    $row['cell_phone'];




    if(
    $row['i_wish_to_play'] != $last_wish_to_play)
    {
    // the first n words to extract
    $n 6;
    // extract the words
    $words explode(" "$row['i_wish_to_play']);
    // chop the words array down to the first n elements
    $firstN array_slice($words0$n-1);
    // glue the 3 elements back into a spaced sentence
    $firstNAsAString implode(" "$firstN);
    }
    if(!isset(
    $counter[$firstNAsAString]))
    $counter[$firstNAsAString] = 1;
    else
    $counter[$firstNAsAString]++;
    $last_wish_to_play $row['i_wish_to_play'];

    $i++;
     echo 
    '</td></tr>';


    }
    echo 
    '</table>';

    ?>

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
  •