Including zero values in a search

[davidwhiteuk]

Hello,

I'm not sure if this is more an issue with PHP or MySQL. I have a piece of code that counts how many records on each date have a specific word. It works fine.

    foreach($mysqli->query('SELECT DATE(`s_created_at`) , COUNT(*) FROM `tickets` where subject LIKE "%Word%" GROUP BY `s_created_at` order by s_created_at
    DESC') as $row1)

    {

    echo $row1['s_created_at'];
    echo "  ";
    echo $row1['COUNT(*)'];
    }

So the output format is:

2022-05-16    3

2022-05-13    2

2022-05-12    3

2022-05-11    1

The one issue is that obviously it ignores all dates that don't have the word. I'd like the format to include this so it looks like:

2022-05-16    3

2022-05-15    0

2022-05-14    0

2022-05-13    2

2022-05-12    3

2022-05-11    1

Any ideas?

Thanks.

Edited May 17, 2022 by davidwhiteuk

[gw1500se]

First please use the code icon (<>) at the top of the menu for your code and select PHP. It is not clear what you are asking. If you want the records whether of not they have the word then why is that phrase in the query? If I understand, you can check the resulting records for the word in the subject and if missing, output a zero.

Edited May 17, 2022 by gw1500se

[ginerjm]

Drop the where clause then.  No?

You might also write your code to handle more issues such as a bad query or a query that doesn't run for some reason.  And post the code here using the <> tag to put it into a more readable format, separate from your text.

$q = 'SELECT DATE(`s_created_at`) , COUNT(*) as rec_cnt FROM `tickets` 
		where subject LIKE "%Word%" 
		GROUP BY `s_created_at` 
		order by s_created_at DESC';
if (!$qrslts = $mysqli->query($q))	// check if query runs
{
	echo "Query did not run";
	(show mysqli error message also?)
	exit();
}
//  show the results now
while ($row = $qrslts->fetch())   // not familiar with mysqli format here
{
	echo "{$row1['s_created_at']} {$row['rec_cnt']}<br>";
}

 

[mac_gyver]

40 minutes ago, davidwhiteuk said:

Any ideas?

what range of zero count dates do you want to include? the current year? current month? current week? between the lowest and highest dates with count values, and if so, what overall range, because your query could match data from as long ago as you have data stored, 10 years, 20 years?

you would typically retrieve the data into an array using the date as the array index, then produce and loop over the date range you want to display, testing on each date being looped over if there is an entry in the array of fetched data, getting it if there is, otherwise displaying a zero. 

[davidwhiteuk]

6 minutes ago, mac_gyver said:

what range of zero count dates do you want to include? the current year? current month? current week? between the lowest and highest dates with count values, and if so, what overall range, because your query could match data from as long ago as you have data stored, 10 years, 20 years?

you would typically retrieve the data into an array using the date as the array index, then produce and loop over the date range you want to display, testing on each date being looped over if there is an entry in the array of fetched data, getting it if there is, otherwise displaying a zero. 

Goes back about 3 years at the moment. Obviously this would increase over time.

[ginerjm]

You originally showed us your results showing counts for those records that have a certain value in them.  Then you said you wanted to see the recs that did not have that value as well.  The suggestion I made was to skip the where clause which would show you all records in your table.  

Your latest posts seem to be heading somewhere else.  Do you not want all the records?  Or do you want only the records that are in a certain date range?  Obviously that could easily be answered with a where clause examining the appropriate date field in your records.  What else do you need from us?

[davidwhiteuk]

27 minutes ago, mac_gyver said:

you would typically retrieve the data into an array using the date as the array index, then produce and loop over the date range you want to display, testing on each date being looped over if there is an entry in the array of fetched data, getting it if there is, otherwise displaying a zero. 

I think this sounds like the way I'll need to go. I'll have to learn about arrays though. Thanks.

[ginerjm]

Why do you need this array?  You will have a set of query results that will be rows (in an array format) that have all the data you asked for.  You are not asking for anything complex here unless you and mac_gyver are in over my head.

Change your query to use this:

WHERE date_fld_name between '2021-01-01' and '2021-03-31'

and you will get a range of records regardless of whether they have the word value you mentioned.  Order it like you were doing.  Then go thru them with a while loop and present the output.  Why the array?

I'm not trying to be difficult.  I just don't see why this is becoming so convoluted.

Edited May 17, 2022 by ginerjm

[mac_gyver]

23 minutes ago, davidwhiteuk said:

Goes back about 3 years at the moment

so, you always want to display data for the entire history of the stored values? how are you going to display this much information, since no human is going to want to look at thousands and tens of thousands of values on a web page?

[mac_gyver]

2 minutes ago, ginerjm said:

You will have a set of query results that will be rows have all the data you ask for

which may not have any rows at all for some of the dates, ergo you need to fill in those dates.

[Barand]

A useful accessory to any database is a "digit" table containing the numbers 0 - 9.

SELECT date
     , COUNT(s_created_at) as rec_cnt
FROM (
        SELECT '2022-05-31' - INTERVAL a.num * 1000 + b.num * 100 + c.num * 10 + d.num DAY AS date
        FROM digit a, digit b, digit c, digit d 
        WHERE  '2022-05-31' - INTERVAL a.num * 1000 + b.num * 100 + c.num * 10 + d.num DAY > '2019-04-30'
	 ) last3years 
     LEFT JOIN
     `tickets` ON last3years.date = tickets.DATE(s_created_at)
               AND subject LIKE  "%Word%"  
GROUP BY date
ORDER BY date DESC

 

Edited May 17, 2022 by Barand

[ginerjm]

@mac_gyver - that was not the question he asked.  Or at least I didn't read it into this topic.  The original post showed data items which I took to mean they were existent records that just didn't have that 'word' value from the where clause.

[Barand]

I was assuming the problem was that data wasn't available for all dates.

If all dates are available then count the records that contain "word", not select the records that contain "word"

SELECT s_created_at
     , SUM(subject LIKE '%word%') as rec_cnt
FROM tickets
GROUP BY s_created_at
ORDER BY s_created_at DESC

 

[ginerjm]

So now that the 3 of us readers have shown 3 interpretations of the 'problem', we need to see what the OP can do to clear up our thoughts.