SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Query in PHP

    Hello All,

    I have a site I am working on for a band and I would like to display their next upcoming gig/event on the home page. I have all the events/gigs in a MySQL database in the following columns,

    database: events
    table: gigs
    col.1 col.2 col.3 col.4
    id | location | time | date

    These are all viewable on a seperate Gigs/Events page I have. What I am after though is to display the next gig/event on the home page, so I am wondering, is there a SQL query I can write in PHP on the Home Page to grab and display just the next gig/event? This will make updating the next gig very easy and it would constantly look for the next event/gig without any need for updating the site manually.

    If there is an easier / better way of doing this I am open for suggestions.

    Hope I have explained myself clearly :-) and any help would me buch appreciated

    Regards,
    Bernie

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    You seem to have a field called date, which is a reserved word. datetime would be better, or then again quote it with backticks like this `date` (unless you are saying col.4 is really your field name?).

    We'd need to know what kind of date you keep in there, give an example of what is stored under `date`.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no i have named the col. date, but I will change this now. An example of the data in the whole table would be;

    id | venue | time | datetime

    1 | The Tavern | 18:00 | 10-05-09

    I have put the column types all as text - apart from id where i have done;

    INT NOT NULL AUTO_INCREMENT PRIMARY KEY (think this is correct?)

    I was thinking of using a;

    SELECT venue, time, datetime FROM events WHERE CURRENTDATE < datetime

    but then only display one result? Am I angling towards the right sort of answer or not

    Thanks for your help and response

    Regards,
    Bernie

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Exactly right, just add on ..." LIMIT 1" ;

    SELECT venue, time, datetime FROM events WHERE CURRENTDATE < datetime LIMIT 1;

    if you have a time field and a date field you may as well simplify things by using the combined datetime (e.g. "05-12-2009 18:45:00" ) you can still select the date and/or the time using the same select statement like this;

    "select date_format('%a %e %b', daydate ) as day
    , venue
    , date_format('%l %i %p', daydate ) as time
    from events"

    Will give you the likes of:

    "Tues 12 May", "The venue", "6.45 PM"

    http://dev.mysql.com/doc/refman/5.1/...on_date-format

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahhhhh excellent thank you very much for your help! :-) really appreciate that.

    Thanks for the advise on the date_format also that will be very handy thanks.

    All the best :-)

  6. #6
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Southwest Florida
    Posts
    393
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow

    If you have more than one event in the future, and you want to display the next event, I think you'd want to include an ORDER BY in your sql, so that the row you get with your LIMIT 1 will be the next upcoming event, rather than some random upcoming event.
    Code:
    SELECT 
         date_format('%a %e %b', daydate ) as day
       , venue
       , date_format('%l %i %p',  daydate ) as time 
    FROM events 
    WHERE CURRENTDATE < datetime 
    ORDER BY datetime ASC
    LIMIT 1;

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Hi Bernie, yup, I hope I opened your eyes a little to what can be done inside your sql queries.

    It would not be a lost weekend if you spent it in the "functions" part of the mysql manual or website. You'd learn a lot.

    Good luck with your site.

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sonjay - thank you for the additional code there, that does make sense :-)

    Cups - yes I think I should do really, I am working through a Sitepoint book on Database driven websites at the moment and it has a huge appendix in the bak which when I have finished, i think I will take a good look through there too :-)

    Thanks for all your help

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello All,

    Sorry I'm not quite getting this :-(

    I have setup my table like below;

    Code MySQL:

    Then I use the following code to INSERT some test data into the table;

    Code MySQL:
    INSERT INTO gigs SET
    venue = "The Tavern",
    daydate = "2009-05-27 20:00:00",
    type = "Acoustic"
    ;

    Then when I do a SELECT query like you said above;
    Code MySQL:
    SELECT venue,
    date_format('%a %e %b', daydate),
    date_format('%l %i %p', daydate)
    FROM gigs WHERE CURRENTDATE < daydate
    ;

    I then get this error;

    Code MySQL:
    ERROR 1054 (42S22): Unknown column 'CURRENTDATE' in 'where clause'

    Can anyone please help me out?

    Regards,
    Bernie

  10. #10
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perhaps instead of CURRENTDATE you should use CURDATE()

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    CURDATE() is proprietary to mysql

    CURRENT_DATE (not CURRENTDATE) is the standard SQL function

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now I get the following error;

    Code MySQL:
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use near 'gigs
    WHERE CURRENT_DATE < daydate' at line 4

    This is when i use either CURDATE() or CURRENT_DATE. Is there something im not "finishing" or not "closing" correctly?

    Thanks for all the help so far though peeps really appreciate it.

    Regards,
    Bernie

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the problem isn't CURRENT_DATE

    it's whatever is near "gigs"

    could you show the exact query please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the query I have asked;

    Code MySQL:
    mysql> SELECT venue,
        -> DATE_FORMAT('%a %e %b', daydate),
        -> DATE_FORMAT('%l %i %p', daydate)
        -> FORM gigs WHERE CURRENT_DATE < daydate
        -> ;

    Thanks again for all you input :-)

    Regards,
    Bernie

  15. #15
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see my mistake there...silly :-( but now I recieve a good reply but no data in the daydate field...have I entered the wrong data? This is the reply i get;

    Code MySQL:
    +------------+----------------------------------+-------------------------------
    ---+
    | venue      | DATE_FORMAT('%a %e %b', daydate) | DATE_FORMAT('%l %i %p', daydat
    e) |
    +------------+----------------------------------+-------------------------------
    ---+
    | The Tavern | NULL                             | NULL
       |
    +------------+----------------------------------+-------------------------------
    ---+
    1 row in set, 2 warnings (0.03 sec)

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you can find the problem with this yourself, too

    it's just as obvious as misspelling FROM as FORM

    look up the DATE_FORMAT syntax and see

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm really sorry but I can't see what I have done wrong still :-( I am very new to MySQL so I apologises if I'm being bit of an idiot :-P it's also 1am where I am xD

    Thanks

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you know how you accidentally reversed the R and O in FROM?

    you accidentally reversed the parameters in the DATE_FORMAT function

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Haha great! Thank you so much for that...would have spent hours trying to find that :-P I have a good date reply, but the time comes back as 12 00 AM even though I inputted 20:00:00.

    Sorry to keep coming back with more and more problems :-P

    Thanks again for all your help and patience with me :-)

  20. #20
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    All my time results keep coming back as "12 00 AM" in the time column of the results. Have I got the INSERT code correct?

    Code MySQL:
    mysql> INSERT INTO gigs SET
        -> venue = "The Tavern",
        -> daydate = "2009-12-12 13:00:00",
        -> type = "Acoustic";
    Query OK, 1 row affected, 1 warning (0.00 sec)

    Also could someone please explain to me what the %a %e %b and %l %i %p stand for? :-)

    Many thanks for all your help

  21. #21
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Bernie91288 View Post
    All my time results keep coming back as "12 00 AM" in the time column of the results.
    The datatype is a "DATE". If you want time included, you should use "DATETIME".

    Quote Originally Posted by Bernie91288 View Post
    Also could someone please explain to me what the %a %e %b and %l %i %p stand for? :-)
    It's in the manual, but this page is perhaps a bit more intuitive:

    http://www.dan.co.uk/mysql-date-format/

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that INSERT code is far from "correct" but it should at least work correctly to store the time properly

    however, if your table is the same as the gigs table in post #9, then the reason it's not storing the time is because the column is a DATE column and there is no place for the time to go

    as for the DATE_FORMAT codes, they are more than adequately explained in da manual (which you should have bookmarked and with which you should be very familiar)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that INSERT code is far from "correct" but it should at least work correctly to store the time properly
    Could you please explain why as I am still learning and do not want to learn bad habits early?

    Yes my CREATE TABLE code is the same as post #9, will I need to create a separate column for the time?

    Code MySQL:
    CREATE TABLE gigs (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    venue TEXT,
    daydate DATE,
    daytime TIME,
    type TEXT
    );

    Or can I combine them into one as Cups said in post #4;

    if you have a time field and a date field you may as well simplify things by using the combined datetime (e.g. "05-12-2009 18:45:00" ) you can still select the date and/or the time using the same select statement like this
    Cheers :-)

  24. #24
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yay I have worked it out :-) thank you all for all your help :-)

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there are two reasons why your INSERT statement was less than "correct"

    first, it uses SET syntax, which is non-standard -- if you ever have to change the database to something other than mysql, it will fail, whereas if you used the standard form of the INSERT statement, it would work in all database systems

    secondly, it uses doublequotes as string delimiters, which again is non-standard -- doublequotes are supposed to be used to delimit problematic identifiers, such as column names which include special characters, while single quotes should be used to delimit string values

    here's the standard way --
    Code:
    INSERT 
      INTO gigs 
         ( venue
         , daydate
         , type )
    VALUES
         ( 'The Tavern'
         , '2009-12-12 13:00:00'
         , 'Acoustic' )
    in conclusion, it's better to learn the standard way of doing things, rather than the mysql way

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •