Query? Count? or ELSE?

Databases
1

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

Welcome to the SP forums.

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

3

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 
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
5

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

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

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 –

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)

8

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

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”

10

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

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

see post #3

13

Ah yes, missed that. Hmm, that complicates things.

14

Sorry guys, im really silly on these kinda of things, and thank you for helping!

15

Would this work? Didn’t test it.


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
16

That works as it shows just the events in the month, but when there are no events, there is nothing shown?

17

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

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

You’re welcome.