How to sort MySQL data using the last word/date

[Rob (SA)]

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

[X96 Web Design]

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:

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.

[Rob (SA)]

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

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($words, 0, $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

[james438]

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.

[Rob (SA)]

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

[Rob (SA)]

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.

<!------------------------------------------------------------------>
<!                  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)*100, 0);

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

[james438]

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:

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

$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>';
$row= str_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.

[Rob (SA)]

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

[Nile]

Can I see all of that code for just that one page please?

[Rob (SA)]

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

$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 > 0 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($words, 0, $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>';

?>