Web Analytics Made Easy -
StatCounter mysql next_day function, can't get it to work - CodingForum

Announcement

Collapse
No announcement yet.

mysql next_day function, can't get it to work

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql next_day function, can't get it to work

    Hi All,

    I am trying to get my sql query to return the datestamp of the next instance of a given day

    so my original query is
    PHP Code:
    SELECT c.clubIDcl.clubIDcl.classIDcc.classIDcl.classNight FROM tbl_clubs c LEFT JOIN tbh_club_classes cc ON c.clubID cc.clubID LEFT JOIN tbl_classes cl ON cl.classID cc.classID ORDER BY cl.classNight ASC 
    this returns
    Code:
    Array
    (
        [0] => Array
            (
                [clubID] => 
                [clubName] => Chipping Norton
                [clubLocation] => Clyme Hall
                [clubPostcode] => 
                [classID] => 
                [classType] => 
                [classStart] => 
                [classEnd] => 
                [classNight] => 
                [classAudience] => 
                [classCost] => 
            )
    
        [1] => Array
            (
                [clubID] => 1
                [clubName] => Club 1
                [clubLocation] => Sports Hall
                [clubPostcode] => 
                [classID] => 3
                [classType] => Karate / Judo
                [classStart] => 19:30:00
                [classEnd] => 21:00:00
                [classNight] => Friday
                [classAudience] => Mixed
                [classCost] => 5.00
            )
    
        [2] => Array
            (
                [clubID] => 3
                [clubName] => Club 2
                [clubLocation] => Community Centre
                [clubPostcode] => 
                [classID] => 4
                [classType] => Karate / Judo
                [classStart] => 18:00:00
                [classEnd] => 19:00:00
                [classNight] => Friday
                [classAudience] => Children
                [classCost] => 3.00
            )
    )
    what i want my query to do is instead of the classNight returning the day have it return the datestamp/timestamp of the next day that matches the value of classNight.

    i have found this code
    PHP Code:
    SELECT NEXT_DAY('02-FEB-2001','TUESDAY'"NEXT DAY" FROM DUAL
    which is supposed to return the date of the next Tuesday. Don't think it returns the datestamp tho? so i tried to modify this to suit my needs, but this is as far as i got
    PHP Code:
    SELECT NEXT_DAY(SYSDATE,`classNight`) "NEXT DAY" FROM DUAL
    would someone be so kind to help me modify my original sql code to incorporate this next day function please

    Many Thanks
    Last edited by LJackson; Sep 29, 2016, 05:30 PM.

  • #2
    Since NEXT_DAY() isn't a native mysql function, you would need to refer to the documentation and examples where you found the code for it to know what it returns as a value. I'm also assuming that you have successfully added the definition for the custom function to your mysql server. If you want us to help with its use, you would need to provide us with a link to the documentation.

    The only things I can tell from what you posted are, 1) the example query is unnecessarily using FROM DUAL, which probably just confuses anyone that doesn't know what the DUAL keyword means, you can just select that example term to see what it returns, and 2) to use this in a query for your database table, so that the classNight column reference will actually work, you would add just the select term to an sql query statement that references your table that contains the classNight column.
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

    Comment


    • #3
      Hi @CFMaBiSmAd,

      thanks for your reply, the NEXT_DAY function is something i found whilst searching for a solution to my problem. the link i got if from https://docs.oracle.com/cd/B19306_01...nctions093.htm again I'm not sure this will be useful for my problem.

      I have updated my initial post
      Is there another way/function that i can use to get my desired result?
      Many Thanks

      Comment


      • #4
        Are you using Oracle for the database - that is probably a proprietary command just for that database.
        Stephen
        Learn Modern JavaScript - http://javascriptexample.net/
        Helping others to solve their computer problem at http://www.felgall.com/

        Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

        Comment


        • #5
          What you found is an Oracle DB function. To do this using a mysql database will require a user written function. If you do a web search for the 'mysql next day function' keywords in your thread's title, you will find code to do this.
          Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

          Comment


          • #6
            can't believe it, just typing in mysql next day function the first result looks like it is what i need, why hasn't that come up before thank you!!!

            Comment


            • #7
              ok i must be really thick

              i have this function code
              Code:
               DELIMITER //
               
                CREATE FUNCTION next_day(start_date DATETIME, weekday CHAR(20))
                  RETURNS DATETIME
                BEGIN
                  DECLARE start DATETIME;
                  DECLARE i INT;
               
                  // Select the next date
                  SET start = ADDDATE(start_date, 1);
                  SET i = 1;
               
                  days: LOOP
                        -- Compare the day names
                      IF SUBSTR(DAYNAME(start), 1, 3) = SUBSTR(weekday, 1, 3) THEN	
              	   LEAVE days;
                      END IF;
               
                      // Select the next date
                      SET start = ADDDATE(start, 1);
                      SET i = i + 1;
               
                      -- Not valid weekday specified
                      IF i > 7 THEN
              	   SET start = NULL;
              	   LEAVE days;
               	END IF;
               
                   END LOOP days;
               
                   RETURN start;
                END;
                //
              but I'm not sure where to put it with in my page? everywhere i place it whether its within php tags or not it throws up an exception error: Parse error: syntax error, unexpected 'FUNCTION' (T_FUNCTION) in.

              what am i doing wrong please?

              Thanks

              Comment


              • #8
                It is SQL not PHP so you need to use one of the SQL calls from the PHP in order to run it.
                Stephen
                Learn Modern JavaScript - http://javascriptexample.net/
                Helping others to solve their computer problem at http://www.felgall.com/

                Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

                Comment


                • #9
                  thanks, i have tried doing a mysql_query($conn, above query); but it results in a Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given.

                  I have typed in the function in my sql query window within my phpmyadmin and it flags up an issue with the following line
                  Code:
                  days: LOOP
                  says unexpected character. (near

                  does the script look correct to you?

                  Comment


                  • #10
                    Try changing all the comments to start with // instead of having half of them start with --
                    Stephen
                    Learn Modern JavaScript - http://javascriptexample.net/
                    Helping others to solve their computer problem at http://www.felgall.com/

                    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

                    Comment


                    • #11
                      still can't get it to work

                      have this now
                      Code:
                      CREATE FUNCTION next_day(start_date DATETIME, weekday CHAR(20))
                          RETURNS DATETIME
                        BEGIN
                          DECLARE start DATETIME;
                          DECLARE i INT;
                       
                          // Select the next date
                          SET start = ADDDATE(start_date, 1);
                          SET i = 1;
                         days: LOOP //[B][U]ITS THE COLON IN THIS LINE THAT IT DOESNT LIKE[/U][/B]
                                //Compare the day names
                              IF SUBSTR(DAYNAME(start), 1, 3) = SUBSTR(weekday, 1, 3) THEN	
                      	   LEAVE days;
                              END IF;
                       
                              //Select the next date
                              SET start = ADDDATE(start, 1);
                              SET i = i + 1;
                       
                              //Not valid weekday specified
                              IF i > 7 THEN
                      	   SET start = NULL;
                      	   LEAVE days;
                       	END IF;
                       
                           END LOOP days;
                       
                           RETURN start;
                        END;
                      might just do a php work around instead as this seems too complicated for me.
                      Thanks for your help

                      Comment

                      Working...
                      X