By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,631 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,631 IT Pros & Developers. It's quick & easy.

Sort after adding leading zero to date?

P: n/a
I have code numbers in 2 fields from a table which correspond to month
and date.

(Month, Code number)
Field name = ml_mna
1
2
3
etc up to 12
(Data is entered without a leading zero)
Field name = ml_dya
(Date, Code number)
1
2
3
etc up to 31
(Data is entered without a leading zero)

I can sucessfully add a leading zero if necessary to each number after
retrieving with select using the following....

$ml_mna=mysql_result($ml_upg,$i,"ml_mna");
$ml_mna++;
if (strlen($ml_mna) < 2) {
$ml_mna = '0' .$ml_mna;
}

for the month and

$ml_dya=mysql_result($ml_upg,$i,"ml_dya");
$ml_dya++;
if (strlen($ml_dya) < 2) {
$ml_dya = '0' .$ml_dya;
}

for the day

However, I need to display the data in date order.

Using "order by" the dates are sorted before I add this leading zero
and the result is an incorrect and confusing display

For instance the date of 30 5 (30 May) is displayed in the wrong place.
(Its sorted as 1,3,4, and not 01, 04, 30).

05 01 (1st May)
05 30 (30th May)
05 04 (4th May)

So how can I sort the date after selecting, after adding the necessary
leading zeros and before displaying?

Thankfull for any help in this matter

Garry Jones
Sweden

Dec 7 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
GarryJones wrote :
I have code numbers in 2 fields from a table which correspond to month
and date.

(Month, Code number)
Field name = ml_mna
1
2
3
etc up to 12
(Data is entered without a leading zero)
Field name = ml_dya
(Date, Code number)
1
2
3
etc up to 31
(Data is entered without a leading zero)

I can sucessfully add a leading zero if necessary to each number after
retrieving with select using the following....

$ml_mna=mysql_result($ml_upg,$i,"ml_mna");
$ml_mna++;
if (strlen($ml_mna) < 2) {
$ml_mna = '0' .$ml_mna;
}

for the month and

$ml_dya=mysql_result($ml_upg,$i,"ml_dya");
$ml_dya++;
if (strlen($ml_dya) < 2) {
$ml_dya = '0' .$ml_dya;
}

for the day

However, I need to display the data in date order.

Using "order by" the dates are sorted before I add this leading zero
and the result is an incorrect and confusing display

For instance the date of 30 5 (30 May) is displayed in the wrong place.
(Its sorted as 1,3,4, and not 01, 04, 30).

05 01 (1st May)
05 30 (30th May)
05 04 (4th May)

So how can I sort the date after selecting, after adding the necessary
leading zeros and before displaying?

Thankfull for any help in this matter

Garry Jones
Sweden
Try to put your dates in an array, and sort this array in PHP, since
the latter has so many sort functions ....

http://php.net/sort

--
Naixn
http://fma-fr.net
Dec 7 '06 #2

P: n/a
naixn skrev:
Try to put your dates in an array, and sort this array in PHP, since the latter has so many sort functions ....
How do I do that? I need to display the data with a different colour
background on alternate rows. Currently I have this page which sorts
everything according to another field.

http://www.scfmotion.se/index15ce.php

I want to be able to sort using the date (datum) field instead.

This is the nearest I can get.... (dont know how to use arrays for
this)

First I get the data from several tables with this

(This is the code that sorts it in the wrong order as it has no leading
zeros in its raw data format from the table).

I would like resort all the data once the leading zeros have been
added, but the way I am doing it is adding the leading zero on each row
one by one and each row is processed in its original sort order which
is wrong because of the fact there are no leading zeros.

$ml_collect='SELECT * FROM ml_lopp LEFT JOIN scfmforening ON
(scfmforening.scfmnum=ml_lopp.scfmnum) LEFT JOIN ml_tidplats ON
(ml_tidplats.loppnum=ml_lopp.loppnum) ORDER BY ml_mna,ml_dya';
$ml_upg=mysql_query($ml_collect);
$num_ml=mysql_numrows($ml_upg);

// set $i to 0 and print a row "a"

$i=0;

// test to see if last row has been processed
while ($i < $num_ml){
// obtain info from the current row
$loppnmn=mysql_result($ml_upg,$i,"loppnmn");
$scfmfor=mysql_result($ml_upg,$i,"scfmfor");
$scfmnum=mysql_result($ml_upg,$i,"scfmnum");
$ml_mna=mysql_result($ml_upg,$i,"ml_mna");
$ml_dya=mysql_result($ml_upg,$i,"ml_dya");

// add a leading zero to the month if necessary
$ml_mna++;
if (strlen($ml_mna) < 2) {
$ml_mna = '0' .$ml_mna;

// add a leading zero to the day if necessary
$ml_dya++;
if (strlen($ml_dya) < 2) {
$ml_dya = '0' .$ml_dya;
$montest[$ml_mna]
}
// print with row a code

echo '<tr><td width="275" class="scfmotion_rowa">';
print $loppnmn;
echo '</td><td width="275" class="scfmotion_rowa">';
print $scfmfor;
echo '</td><td width="150" class="scfmotion_rowa">';
print "2007";
print "-";
print $ml_mna;
print "-";
print $ml_dya;
echo '</td><td width="50" class="scfmotion_rowa_right">';
print $scfmnum;
echo '</td></tr>';

// add one to $i
$i++;
// test to see if last row has been processed
while ($i < $num_ml){

// obtain info from the current row
$loppnmn=mysql_result($ml_upg,$i,"loppnmn");
$scfmfor=mysql_result($ml_upg,$i,"scfmfor");
$scfmnum=mysql_result($ml_upg,$i,"scfmnum");
$ml_mna=mysql_result($ml_upg,$i,"ml_mna");
$ml_dya=mysql_result($ml_upg,$i,"ml_dya");

// add a leading zero to the month if necessary
$ml_mna++;
if (strlen($ml_mna) < 2) {
$ml_mna = '0' .$ml_mna;
// add a leading zero to the day if necessary

$ml_dya++;
if (strlen($ml_dya) < 2) {
$ml_dya = '0' .$ml_dya;
$montest[$ml_mna]
}

// print with row b code

echo '<tr><td width="275" class="scfmotion_rowb">';
print $loppnmn;
echo '</td><td width="275" class="scfmotion_rowb">';
print $scfmfor;
echo '</td><td width="150" class="scfmotion_rowb">';
print "2007";
print "-";
print $ml_mna;
print "-";
print $ml_dya;
echo '</td><td width="50" class="scfmotion_rowb_right">';
print $scfmnum;
echo '</td></tr>';
$i++;

}
}

This loops until all rows printed, alternatly with a and b code
Any help appreciated

Garry Jones
Sweden

Dec 7 '06 #3

P: n/a
GarryJones wrote :
[...]
You can do your query like this :
SELECT
*, DATE(CONCAT('2007', ml_mna, '-', ml_dya)) AS thedate
FROM
ml_lopp
LEFT JOIN
scfmforening
ON
(scfmforening.scfmnum = ml_lopp.scfmnum)
LEFT JOIN
ml_tidplats
ON
(ml_tidplats.loppnum = ml_lopp.loppnum)
ORDER BY
thedate

This idea is to concatenate 2007, the month, and the day, then you get
2007-2-1 for the 1st of january, and the function :
DATE() receives it [ DATE('2007-2-1') ] and return :
2007-02-01
And this value is put in "thedate", which is used for the sorting ;)

Could you try and tell the result?

--
Naixn
http://fma-fr.net
Dec 8 '06 #4

P: n/a
"GarryJones" <mo****@algonet.sewrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
>I have code numbers in 2 fields from a table which correspond to month
and date.

(Month, Code number)
Field name = ml_mna
1
2
3
etc up to 12
(Data is entered without a leading zero)
Field name = ml_dya
(Date, Code number)
1
2
3
etc up to 31
(Data is entered without a leading zero)

I can sucessfully add a leading zero if necessary to each number after
retrieving with select using the following....

$ml_mna=mysql_result($ml_upg,$i,"ml_mna");
$ml_mna++;
if (strlen($ml_mna) < 2) {
$ml_mna = '0' .$ml_mna;
}

for the month and

$ml_dya=mysql_result($ml_upg,$i,"ml_dya");
$ml_dya++;
if (strlen($ml_dya) < 2) {
$ml_dya = '0' .$ml_dya;
}

for the day

However, I need to display the data in date order.

Using "order by" the dates are sorted before I add this leading zero
and the result is an incorrect and confusing display
Well the problem might be in the database structure rather than how yo use
the date. If the numbers are sorted like you claim - 1,30,4 - it indicates
that the data type of the field is a textual format such as varchar rather
than an integer. What you should be doing is use a datetime data type field
to store the date instead of a couple of varchar fields and you'll have no
worries at all with ORDER BY.

And for simpler leading zero formatting, just use sprintf:
echo sprintf('%02d', $m1_dya);

--
"Ohjelmoija on organismi joka muuttaa kofeiinia koodiksi" - lpk
http://outolempi.net/ahdistus/ - Satunnaisesti päivittyvä nettisarjis
sp**@outolempi.net | rot13(xv***@bhgbyrzcv.arg)
Dec 8 '06 #5

P: n/a
I tried that but its not working, I got Parse error: syntax error,
unexpected T_LNUMBER in
/customers/scfmotion.se/scfmotion.se/httpd.www/scfml_lst.php on line 6

I think this is because
SELECT
*, DATE(CONCAT('2007', ml_mna, '-', ml_dya)) AS thedate
FROM
ml_lopp
LEFT JOIN
scfmforening
ON
(scfmforening.scfmnum = ml_lopp.scfmnum)
LEFT JOIN
ml_tidplats
ON
(ml_tidplats.loppnum = ml_lopp.loppnum)
ORDER BY
thedate

This idea is to concatenate 2007, the month, and the day, then you get
2007-2-1 for the 1st of january, and the function :
DATE() receives it [ DATE('2007-2-1') ] and return :
2007-02-01
And this value is put in "thedate", which is used for the sorting ;)
ml_mna and ml_dya are in the table ml_tidplats and that info is
extracted with the left join function. So how can I refer to the fields
ml_mna and ml_dya ?

Dec 11 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.