3

So, I am fetching some data from MySQL with that code:

<?
$query=mysql_query("SELECT date,COUNT(*) as num FROM downloads WHERE prjID='".$_GET['id']."' GROUP BY date ORDER BY date ASC");
$num=mysql_num_rows($query);
$res='';
$i=0;
while($row=mysql_fetch_array($query)){

$i++;
$date=date("d.m.Y", strtotime($row['date']));

$dan=date("d", strtotime($row['date']));
$mesec=date("m", strtotime($row['date']));
$leto=date("Y", strtotime($row['date']));

if($i=1){
$danPrvi=$leto.", ".($mesec-1).", ".$dan;
$dan1=date("d", strtotime(time()));
$mesec1=date("m", strtotime(time()));
$leto1=date("Y", strtotime(time()));
$danZadnji=$leto1.", ".($mesec1-1).", ".$dan1;
}

$numb=1;

if($row['num']!=1){
$res.="[Date.UTC(".$leto.",".($mesec-1).",".$dan."),".$row['num']."], ";
}
else{
if($i!=$num){
$res.="[Date.UTC(".$leto.",".($mesec-1).",".$dan."),".$numb."], ";
}
else{
$res.="[Date.UTC(".$leto.",".($mesec-1).",".$dan."),".$numb."]";
}
}
}
?>

And I get results like that:

1.3.2013 - 1
6.3.2013 - 5

But I would like to get results like that:

1.3.2013 - 1
2.3.2013 - 0
3.3.2013 - 0
4.3.2013 - 0
5.3.2013 - 0
6.3.2013 - 1

I am also using Highcharts, so date values must be formatted like Date.UTC(year, month-1, day)

EDIT:

I don't have all dates in my databases. In my examle, there are only 1.3.2013 and 6.3.2013, so how would I detect and set 0 value for all dates between, that don't have a value >=1?

3
  • 3
    2.3.2013 doesn't actually exist in the table does it? I mean, if it did, you would have a count of 1, right? My point is, if it doesn't exist in the db, you need to come up with the date some other place .. i.e. - in your code. The database isn't going to know about what it is ignorant of - you will never get results that aren't there. Commented Mar 29, 2013 at 13:49
  • No, it's not in the database, but if it would be, the results would be wrong, or I should do -1 for every null download. But how would I do it in/with PHP? Commented Mar 29, 2013 at 13:52
  • I provided an answer with an example. Commented Mar 29, 2013 at 14:02

3 Answers 3

4

I imagine what you need to do is set up a construct to hold the dates you are interested in and either use that in your query or post processing the query data. For example:

<?php
$query=mysql_query("SELECT date,COUNT(*) as num FROM downloads WHERE prjID='".$_GET['id']."' GROUP BY date ORDER BY date ASC");
$num=mysql_num_rows($query);

// Get the first and last dates in the result set
$firstRow = mysql_result($query, 0);
$lastRow = mysql_result($query, $num-1);

// Now make thos the begin and end dates
$beginDate = new DateTime(strtotime($firstRow['date']));
$endDate = new DateTime(strtotime($lastRow['date']));
$currentDate = $beginDate;
$interestingDates = array();

// Populate our interestingDates array with all counts set to 0
while ($currentDate <= $endDate){
    $interestingDates[$currentDate->format('d.m.Y')] = 0;
    $currentDate->add(new DateInterval('P1D'));
}

// Reset the data result for looping over
mysql_data_seek($query,0);
while($row=mysql_fetch_array($query)){
    // Go ahead and format the string
    $formatedString = date("d.m.Y", strtotime($row['date']));

    // If the string is in our interestingDates array, update the count
    if (array_key_exists($formatedString, $interestingDates)){
        $interestingDates[$formatedString] = $row['num'];
    }
}

// Print it out
foreach ($interestingDates as $key=>$value){
    print "$key - $value\n";
}

NOTE 1: mysql_query is deprecated as of PHP 5.5.0 and will be removed in the future. Pleasue use another API - I recommend pdo_mysql.

NOTE 2: The current query is not parameterized. Using PDO, this would perhaps look like:

$sth = $dbh->prepare('SELECT date,COUNT(*) as num FROM downloads WHERE prjID= :prjID GROUP BY date ORDER BY date ASC');
$sth->bindParam(':prjID', $_GET['id'], PDO::PARAM_INT);
$sth->execute();

Disclaimer - I haven't actually run this code, I just wrote it off the top of my head. You might need to test/debug it.

Sign up to request clarification or add additional context in comments.

8 Comments

Well, there is a problem, I only have few dates, and I should detect which dates are missing, and assign them a value 0. Ex. I have in MySQL this: 1.3.2013 - 1, 3.3.2013 - 2, 6.3.2013 - 5, and I should detect dates 2.3., 4.3. and 5.3.
I think the above code is the easiest way to do it. Pick your start date and end date, then populate an array with all interesting dates, then get your query and update the array with the counts returned. Finally, do what you want with the finished array. I don't grasp why you feel this is a problem? Trying to guess the dates after the query is gonna get jacked up. Suppose you were interested in everything from 1.3.2013 - 20.3.2013 ... but the first date in the database is 5.3.2013 ... how will you know to go back in time to 1.3.2013 and fill in the dates?
I updated the code sample to automate generating the white list of interesting dates.
And here he also doesnot know start/finish date -- just project id.
@gaRex he does know the start and finish date. Does he want to simply start with the first date that IS in the db and finish with the last date that IS in the db? In which case .. he does know the start and finish date! I don't understand your downvote and comment. How do you think there is a scenario where he doesn't know or can't discover the start and end dates?
|
0

Assuming you have data for all dates, just not for a particular id, you can do a condition sum:

SELECT date, sum(case when prjID='".$_GET['id']."' then 1 else 0 end) as num
FROM downloads
GROUP BY date
ORDER BY date ASC

5 Comments

He doesnot have all dates in db -- only two monthes for example.
@gaRex . . . He has only two months for a given pid. The table may have all dates for other ids. The SQL in the question does the filtering in the where clause. Moving the condition to the case to more likely to work than not and is the simplest solution.
See his "But I would like to get results like that:"
@gaRex . . . You really don't seem to understand the difference between a condition in the where clause and a case statement. Assuming that there is some id on any day (although not necessarily the one of interest), then my statement works.
he has only two dates for that prjid: "I don't have all dates in my databases. In my examle, there are only 1.3.2013 and 6.3.2013". So even if he has other dates in other prjId, there is no guarantee, that they all will be. For example, assume noone download anything at 2.3.2013. Then case-solution will have a gap. I understand your solution on technical level completely. It has a problem on algoryhtm level. Pls reread OP requirements.
0

WARNING! It's a very bad style to do it in SQL, but if you want -- you can :)

http://sqlfiddle.com/#!2/b491a/1/0

SELECT dynamic_date AS date, COUNT(downloads.date) AS num FROM (
    SELECT DATE_ADD(start, INTERVAL i - 1 DAY) AS dynamic_date, prjID FROM
      (
        SELECT @i := @i + 1 AS i FROM 
        (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
        (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
        (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
        (SELECT @i:=0) init
      ) i_sequence,
      (SELECT MIN(date) start, MAX(date) finish, prjID FROM downloads WHERE prjID=5 GROUP BY prjID) minmax
    WHERE DATE_ADD(start, INTERVAL i - 1 DAY) <= finish
) date_sequence
LEFT JOIN downloads ON dynamic_date = downloads.date and downloads.prjID=date_sequence.prjID
GROUP BY dynamic_date
;

1 Comment

so all he needs now is only to pass project id var inside.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.