How to sort MySQL data using the last word/date [Archive]

View Full Version : How to sort MySQL data using the last word/date

Rob (SA)

12-30-2010, 08:12 PM

Hi Folks,

I have a page (http://www.gnjgf.co.za/Glacier_watflip.php) 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

12-31-2010, 01:29 AM

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)

12-31-2010, 04:54 AM

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

12-31-2010, 06:09 PM

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)

12-31-2010, 10:04 PM

Hi,

Thanks for the reply.

In no form of disrespect but in terms of learning. I have found this link (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_get-format) 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)

01-02-2011, 07:59 PM

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">TOURNAMENT ENTRIES</td>
</table>

<table>
<td width="8%" align="center" bgcolor="#376091">No</td>
<td width="24%" align="center" bgcolor="#376091">TOURNAMENT</td>
<td width="10%" align="center" bgcolor="#376091">DATE</td>
<td width="8%" align="center" bgcolor="#376091">%</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">'.$value.'</td>
<td width="24%" align="left" bgcolor="#B5CCE4">'.$key.'</td>
<td align="left" width="10%" bgcolor="#B5CCE4">'.'Date'.'</td>
<td align="center" width="8%" bgcolor="#B5CCE4">'.$percentage.'</td>
</tr>';

}
echo '</table>';
?>

I need for the word Date in this link (http://www.gnjgf.co.za/Glacier_watflip.php) 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

01-03-2011, 10:42 AM

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">'.$value.'</td>
<td width="24%" align="left" bgcolor="#B5CCE4">'.$key.'</td>
<td align="left" width="10%" bgcolor="#B5CCE4">'.'Date'.'</td>
<td align="center" width="8%" bgcolor="#B5CCE4">'.$percentage.'</td>
</tr>';

with

$row='<tr>
<td align="center" width="8%" bgcolor="#B5CCE4">'.$value.'</td>
<td width="24%" align="left" bgcolor="#B5CCE4">'.$key.'</td>
<td align="left" width="10%" bgcolor="#B5CCE4">'.'Date'.'</td>
<td align="center" width="8%" bgcolor="#B5CCE4">'.$percentage.'</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)

01-03-2011, 12:38 PM

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 (http://www.gnjgf.co.za/Glacier_watflip.php)

Regards
Rob

Nile

01-03-2011, 01:08 PM

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

Rob (SA)

01-03-2011, 01:21 PM

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>
<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"> </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">'.$row['i_wish_to_play'].'</td>
<td colspan="1" align="center" bgcolor="#376091">'.$row['Date'].'</td>
</tr>';

echo '<tr>
<td bgcolor="#376091">FULL NAME</td>
<td align="center" bgcolor="#376091">DIVISION</td>
<td align="center" bgcolor="#376091">HANDICAP</td>
<td align="center" bgcolor="#376091">CELL NUMBER</td>
</tr>';
}

echo '<tr><td bgcolor="#B5CCE4">';
echo '<option value="'.strtoupper($row['surname']).', '.strtoupper($row['name']).'">'.strtoupper($row['surname']).', '.strtoupper($row['name']).'</option>';

echo '</td><td align="left" bgcolor="#B5CCE4">';
echo $row['my_sajgf_division:'];

echo '</td><td align="center" bgcolor="#B5CCE4">';
echo $row['my_handicap_is'];

echo '</td><td align="center" bgcolor="#B5CCE4">';
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>';

?>

Nile

01-03-2011, 01:29 PM

Can I also see the code that does the left side of the page (all of it)? The right wont help me very much.. :\

Rob (SA)

01-03-2011, 01:44 PM

Hi,

The most recent submission is for the right hand side and everything in post #6 of this thread is for the left hand side.

I have now posted "All of it"

Nile

01-03-2011, 04:58 PM

Where does the $counter variable come from?

Rob (SA)

01-03-2011, 05:03 PM

Hi,

I am not a person who knows anything about php or script in this regard.

I have basically used a cut and past system to get it to where it is , with the help of folk like yourself.

The counter is used I understand to count the rows for each speific tournament and then place the respective number no of entrants next to the name of the respective tournament.

sorry I cant help much further with this

james438

01-03-2011, 05:40 PM

I can look at it some more later, but I think there is enough code for us to work with now. His sql query is a little odd in that it is ordered by multiple columns, which makes no sense.

select * from _Form_Nr_4 ORDER BY `Date` ASC, `my_sajgf_division:` ASC, `surname` ASC
should be:

SELECT * FROM _Form_Nr_4 ORDER BY Date ASC
Also, the variable for date has not been assigned.

$counter should probably be looked at as well.

Rob (SA)

01-03-2011, 06:01 PM

Hi James,

Thanks for taking a look at the file.

In tackling the first section :

select * from _Form_Nr_4 ORDER BY `Date` ASC, `my_sajgf_division:` ASC, `surname` ASC

I founfd that by assigning it like this I managed to get the table on the right to sort in a fashion I found favourable:

First by the DATE of tournament, then by the division the player belongs to and lastly in alphabeticla order of surname in the division the play belongs to. Maybe there is a better way of presenting this -I am not sure - That sections works perfectly.

The assigning of the DATE variable is the challenge of immediate need as once the Date can be retreived for the right hand side of the page then it will be easy to allocate it.

Thanks for all you inputs

Regards
Rob

james438

01-04-2011, 03:52 AM

I stand corrected. Your date is defined. I did not see it there the first time. I usually assign $row['date'] to $date, which is probably why I missed it. From what I can see things look pretty good, but clearly there is something we are missing.

I notice that several of your initial values are empty. For example, in the first loop of:

if($i > 0 AND $row['i_wish_to_play'] != $last_wish_to_play)

$i and $last_wish_to_play always have an empty value, so it will always fail the first time through. Also, $counter[$firstNAsAString] will always be true since it is empty as well.

Just letting you know. I suspect you already knew though. As to the main problem. Try inserting this line at various places in your code one at a time:

$date=$row['Date']; echo "$date";

to see where the date value is either not being assigned or lost.