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”
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
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 –
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)
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() ).
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
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?