SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Jul 2005
    Location
    Oslo, Norway
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Writing wrong code in SELECT statement?

    This is probably very easy but I can not figure it out myself (I am sorry, you've heard that before).

    I am new to MySQL and PHP and have a little trouble when trying to program it correctly.

    The following code works as I want it to when entering it in Terminal (on Mac OS X).

    PHP Code:
    SELECT DATE_FORMAT(GameDate'%d.%m. %Y'), TeamNameHomeCheGoalOppGoalScorereTourID FROM CsnterminCsnteam WHERE TeamID=Csnteam.ID ORDER BY GameDate ASC
    However when entering it like this in my PHP document it will not work:

    PHP Code:
        $select 'SELECT DATE_FORMAT(GameDate, '%d.%m. %Y'), TeamName, Home, CheGoal, OppGoal, TourID, Scorere';
        
    $from   ' FROM Csntermin, Csnteam';
        
    $where  ' WHERE TeamID=Csnteam.ID';
        
    $order  ' ORDER BY GameDate ASC';

        
    $termin = @mysql_query($select $from $where $order); 
    I have tried surrounding it with every possible combination of 's and "s (I think) but nothing gives me the result I want.

    Can someone please tell me what I am doing wrong here? It surely is something simple regarding the punctuation around the date formatting because the SELECT statement works in PHP too when I am not trying to use DATE_FORMAT.

    Thanks!

    Jan Ove
    Last edited by Jan Ove; Aug 5, 2005 at 15:26. Reason: Trying to improve summary :)

  2. #2
    SitePoint Zealot CdeMky's Avatar
    Join Date
    Sep 2004
    Location
    United States
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $select = 'SELECT DATE_FORMAT(GameDate, '%d.%m. %Y'), TeamName,

    your problem probably lies there, in the DATE_FORMAT function. You have an improperly formated string.


    $select = "SELECT DATE_FORMAT('GameDate', '%d.%m. %Y'), TeamName,Home, CheGoal, OppGoal, TourID, Scorere";

  3. #3
    SitePoint Member
    Join Date
    Jul 2005
    Location
    Oslo, Norway
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, but I have tried that (and tried again now just to be sure), but it still does not work. Any other ideas?

    PS Sorry about posting in the wrong forum.

  4. #4
    SitePoint Guru augathra's Avatar
    Join Date
    Jul 2004
    Location
    united states
    Posts
    826
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $select = "SELECT DATE_FORMAT(GameDate, '%d %m %Y'), TeamName, Home, CheGoal, OppGoal, TourID, Scorere";

    EDIT: it's because he didn't take out the concats, it should be spaces i believe

  5. #5
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If your enclosing your query with 'query', then you need to escape the ['] in your MySQL DATE_FORMAT function!


    Code:
    	$select = 'SELECT DATE_FORMAT(GameDate, \'%d.%m. %Y\'), TeamName, Home, CheGoal, OppGoal, TourID, Scorere';
    	$from   = ' FROM Csntermin, Csnteam';
     	$where  = ' WHERE TeamID=Csnteam.ID';
     	$order  = ' ORDER BY GameDate ASC';
    
    	$termin = @mysql_query($select . $from . $where . $order);
    printf

  6. #6
    SitePoint Member
    Join Date
    Jul 2005
    Location
    Oslo, Norway
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to all who replied, but unfortunately it still does not work. Nothing prints on the screen in the date column even though I have tried all your suggestions.
    Could the problem be a problem somewhere else too? For instance maybe this does not work since the format for the date has changed:

    PHP Code:
          $gamedate = ($kamp['GameDate']); 

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you need to give this expression a column alias --
    Code:
    'SELECT DATE_FORMAT(GameDate, \'%d.%m. %Y\') as print_date, TeamName ...
    then refer to print_date in your script
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Jul 2005
    Location
    Oslo, Norway
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937! That worked!

    Now if I only could understand why, I might have learned something from this too. I've read about column aliases but unfortunately I can't see why it was needed here.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Jan Ove
    ...unfortunately I can't see why it was needed here.
    i don't do php but my understanding is that it's a lot easier to use an alias in your code than the real name of the column

    the real name of the column is DATE_FORMAT(GameDate, '%d.%m. %Y')

    okay, so let's say you did use the real name of the column in your script

    it would work!

    problem is, you now have a maintenance nightmare

    see, what if you decide you wanted the date displayed in a slightly different format, let's say with commas instead of periods

    your scripts falls down goes boom!

    you have to change the column name throughout the script, too

    that's why it was needed here
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Jul 2005
    Location
    Oslo, Norway
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! Great answer. I understand why now.

    Jan Ove


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
  •