Professional Web Applications Themes

concat problem - MySQL

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 ...

  1. #1

    Default 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...
    P 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 Guest

  2. #2

    Default Re: concat problem

    GarryJones wrote: 
    .... 

    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/language.types.string.php#language.types.string.sy ntax.single

    Regards,
    Bill K.
    Bill Guest

  3. #3

    Default Re: concat problem

    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
    P 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

    GarryJones Guest

  4. #4

    Default Re: concat problem

    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 Guest

  5. #5

    Default Re: concat problem

    GarryJones wrote: 

    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.
    Bill Guest

  6. #6

    Default Re: concat problem

    In article <newsguy.com>,
    Bill Karwin <com> wrote: [/ref]

    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...
    Steve Guest

Similar Threads

  1. where concat is case sensitive!?
    By Karsten in forum MySQL
    Replies: 4
    Last Post: November 24th, 03:39 PM
  2. concat with nulls
    By rmorgan in forum Coldfusion Database Access
    Replies: 3
    Last Post: November 13th, 03:12 PM
  3. Concat two fields in SQL
    By thomascraig in forum Coldfusion Database Access
    Replies: 7
    Last Post: June 9th, 01:54 PM
  4. Concat problem with SQL
    By Shivaan Keldon in forum Coldfusion Database Access
    Replies: 4
    Last Post: October 24th, 12:14 PM
  5. php string concat
    By anybody in forum PHP Development
    Replies: 2
    Last Post: October 14th, 03:04 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139