Jump to content

Including zero values in a search


Recommended Posts

Posted (edited)

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 by davidwhiteuk
Link to comment
Share on other sites

Posted (edited)

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 by gw1500se
Link to comment
Share on other sites

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>";
}

 

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Posted (edited)

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 by ginerjm
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

Posted (edited)

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 by Barand
Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.