concat problem

I have two fields in a table with values defined as varchar that are
actually numbers for months and days. I need to collect the data with a
select statemet and sort by date.

My first attempt at sorting did not work because the leading zeros are
missing. I added leading zeros after collecting the data but it still
sorted incorrectly (ie 01, 20, 03) for first, twentieth and third.

The fields with the numbers for month and day numbers are ml_mna and
ml_dya. (in table ml_tidplats)

In my latest attempt I try to format the numbers into a date form with
concat but getting a syntax errror

$ml_collect='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 ';

.... but that gives this...
Parse error: syntax error, unexpected T_LNUMBER in
/customers/scfmotion.se/scfmotion.se/httpd.www/scfml_lst.php on line 4

(Its on line 4)

Any help appreciated.

Garry Jones
Sweden

GarryJones wrote:
> $ml_collect='SELECT *, DATE(CONCAT('2007', ml_mna, '-', ml_dya)) AS
....
> Parse error: syntax error, unexpected T_LNUMBER in
> /customers/scfmotion.se/scfmotion.se/httpd.www/scfml_lst.php on line 4

This is a PHP error, not a MySQL error. You have quotes inside your
quoted string.

Read about how to put literal quote characters inside a quoted string here:
http://www.php.net/manual/en/languag....syntax.single

Regards,
Bill K.

Thanks for your answer. Its still not working

I tried this

$ml_collect='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 ';

And got this error
Parse error: syntax error, unexpected T_LNUMBER in
/customers/scfmotion.se/scfmotion.se/httpd.www/admin/cent_adm3.php on
line 6

The people at php sent me to this newsgroup. The problem is either or,
I am not sure yet. Need help, appreciate any I can get.

Garry Jones
Sweden

Thanks

This works

$ml_collect='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 ';

But now I am getting an error... because it does not return any data,
any ideas?

Garry Jones
Sweden

GarryJones wrote:
> Thanks
>
> This works
>
> $ml_collect='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 ';
>
> But now I am getting an error... because it does not return any data,
> any ideas?

You've only shown me that you set $ml_collect to a string, which
resembles SQL.

Are you executing this string with mysql_query()? Does that function
return any errors?

Does the table ml_lopp contain any rows?

Regards,
Bill K.

In article <em2ml3011p@enews4.newsguy.com>,
Bill Karwin <bill@karwin.com> wrote:
>GarryJones wrote:
>> Thanks
>>
>> This works
>>
>> $ml_collect='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 ';

You forgot to put a hyphen between the year and the month, unless ml_mna
already has it. You're probably making "200701-02", not "2007-01-02".
--
Steve Watt KD6GGD PP-ASEL-IA ICBM: 121W 56' 57.5" / 37N 20' 15.3"
Internet: steve @ Watt.COM Whois: SW32-ARIN
Free time? There's no such thing. It just comes in varying prices...