SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query? Count? or ELSE?

    Hi there, I have tried lots of different ways to get what i want out of this query but i seem to be failng all the time and if anyone could PLEASE help me!!

    I am trying to basically do a query that says if there are no events in the current month then please return a value of "no records" or display a different record with a different confirmed number? Which ever way i need to show that if there are no records related to the current month then it needs to show another record saying there are no records.

    this is my current base query, that if in Date_Month there are no records, it just wont show anything. I need it to show a return somehow if there are no records available.

    SELECT Date_Day AS 'Day', Circuit, Country_Name AS Country
    FROM Calendar
    WHERE Date_Month = month(now()) AND Confirmed = 1
    ORDER BY Day
    LIMIT 0, 100

    I have tried, saying that if there are no records, then I want it to look for records with Confirmed= 2 as I have used that as one that lists "no records"

    if anyone can help please!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Welcome to the SP forums.

    Why don't you handle the 'no records found' scenario in PHP (or whatever language you're using)?

  3. #3
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Somthing I dont know anything about im afriad, very new to this and using Joomla. Using a basic text editor with a plugin that lets me display mysql

  4. #4
    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)
    Code:
    SELECT Date_Day AS 'Day'
         , Circuit
         , Country_Name AS Country 
         , 0 AS sort_key
      FROM Calendar
     WHERE Date_Month = MONTH(CURRENT_DATE) 
       AND Confirmed = 1
    UNION ALL
    SELECT 'no records' 
         , NULL
         , NULL   
         , 1
    ORDER 
        BY sort_key ASC
         , Day ASC 
     LIMIT 100
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok thats great thank you! must be missing somthing as that has returned both dates that are on the current month, and no records available?

  6. #6
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Make myself clearer, there is one date on this current month, and that sql has return that and shown it, but it also has shown "no records" available as well?

  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)
    Quote Originally Posted by staustell View Post
    Make myself clearer, there is one date on this current month, and that sql has return that and shown it, but it also has shown "no records" available as well?
    i don't suppose the fact that you wanted only 100 rows returned might have influenced me to simply stick the "no records" row at the end...

    okay, let's refine that a bit --
    Code:
    SELECT Date_Day AS 'Day'
         , Circuit
         , Country_Name AS Country 
         , 0 AS sort_key
      FROM Calendar
     WHERE Date_Month = MONTH(CURRENT_DATE) 
       AND Confirmed = 1
    UNION ALL
    SELECT CONCAT_WS(' ',COUNT(*),'records')
         , NULL
         , NULL   
         , 1
      FROM Calendar
     WHERE Date_Month = MONTH(CURRENT_DATE) 
       AND Confirmed = 1     
    ORDER 
        BY sort_key ASC
         , Day ASC 
     LIMIT 100
    ordinarily one would not return both the detail rows and a summary count row (since application languages like php have builtin functions to count rows returned)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok the the 100 rows wasnt a must it was just somthing i had in the code, sorry and i didnt mean to offend!

    The result still is the same. - its still coming up with the results of the month and saying no records found?!

  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 staustell View Post
    its still coming up with the results of the month and saying no records found?!
    really? you tried the revised query?

    it's supposed to give the count, and if no records were actually found, it would say "0 records" instead of "no records"
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am as confused as you are:

    http://79.170.40.171/myracingcalendars.com/

    have a look for yourself, on the left hand side under "upcoming events" you can see it clearly there?

    Sorry to cause hassle!

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Yes, it always returns the "summary count row". It will say "0 records" if no rows are found, and "n records" (where n is the number of rows found) if rows are found.

    Like I said in my reply, and Rudy in reply #7, you should handle the zero rows scenario in PHP (take a look at mysql_num_rows() ).

  12. #12
    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 guido2004 View Post
    ...you should handle the zero rows scenario in PHP (take a look at mysql_num_rows() ).
    see post #3
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    see post #3
    Ah yes, missed that. Hmm, that complicates things.

  14. #14
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry guys, im really silly on these kinda of things, and thank you for helping!

  15. #15
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Would this work? Didn't test it.
    Code:
    SELECT 
        c.Date_Day AS 'Day'
      , c.Circuit
      , c.Country_Name AS Country 
    FROM (
      SELECT MIN(Confirmed) AS Confirmed
      FROM (
        SELECT Confirmed
        FROM Calendar
        WHERE Date_Month = MONTH(CURRENT_DATE) 
        AND   Confirmed = 1     
        LIMIT 1
        UNION
        SELECT Confirmed
        FROM Calendar
        WHERE Confirmed = 2
      ) AS a
    ) AS b
    INNER JOIN Calendar AS c
    ON b.Confirmed = c.Confirmed
    WHERE c.Date_Month = MONTH(CURRENT_DATE) 
    OR    b.Confirmed = 2
    ORDER BY Day
    LIMIT 0, 100
    Last edited by guido2004; Feb 9, 2012 at 05:59. Reason: Corrected error

  16. #16
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That works as it shows just the events in the month, but when there are no events, there is nothing shown?

  17. #17
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    I made some corrections 5 minutes after I posted the query. Did you try the corrected version?
    And you still have 1 row with Confirmed = 2 in that table, right?

  18. #18
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No its was my mistake! sorry that works beatifully! thank you every so much, thats so very kind! i would have never worked that out!!!

  19. #19
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    You're welcome.


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
  •