What is the problem with my substring query.......????

Discussion in 'PHP' started by junandya, Oct 25, 2007.

0
  1. #1
    Hello,...

    i have a mysql database called theMember and a table called news, inside news i have field called newsDateInsert.

    field date is integer(8), which 2 digits first are Date, nest 2 digits are Month and last 4 digits are Year,...

    And, i have a variable $month which taken from a form.

    my problem is,... i want to retrieve all data, which based on month value on newsDate field which taken from $month with this query....

    $strSQL1 = "SELECT * FROM theMember where SUBSTRING ('newsDateInsert',3,2) like '$month' order by newsDateInsert desc limit $limit, 10 ";

    but i have the error message like this:

    You have an error in your SQL syntax near '(newsDateInsert,3,2) like '11' order by newsDateInsert desc limit 0, 10 ' at line 1

    Does anyone here could help my problem,....which part of this query that has a mistake
    Thank you very very very much

    Best Regards
     
    junandya, Oct 25, 2007 IP
  2. theOtherOne

    theOtherOne Well-Known Member

    Messages:
    112
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    108
    #2
    Suppose you will have to remove the space between SUBSTRING and the bracket, i.e.
    SUBSTRING(newsDateInsert,3,2)
     
    theOtherOne, Oct 25, 2007 IP
  3. Kuldeep1952

    Kuldeep1952 Active Member

    Messages:
    290
    Likes Received:
    18
    Best Answers:
    0
    Trophy Points:
    60
    #3
    If you want to order by newsDateInsert desc, you should structure
    it as yyyymmdd to correct the date order.

    Also the way you are using it, '01012007' will become 1012007 when
    saved as Integer. newsDateInsert should be Varchar to save it as
    '01012007'
     
    Kuldeep1952, Oct 25, 2007 IP
  4. junandya

    junandya Member

    Messages:
    79
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    43
    #4
    hello, i already changed the query script like this, i delete the space after SUBSTRING

    $strSQL1 = "SELECT * FROM theMember where SUBSTRING('newsDateInsert',3,2) like '$month' order by newsDateInsert desc limit $limit, 10 ";

    it still is not work, but no error report. it seem the "newsDate Insert", not refer to the field in data base. how is that?
     
    junandya, Oct 26, 2007 IP