2

I want select value from my custom table where there are tweets saved. I have the column for the counter of retweets and I want the sum of the count hourly for 24 hour.

I write this code:

$time = time();
$prev_date = date('d-m-Y H:i:s', $time);

$dataSet = new XYSeriesDataSet();

foreach($req as $val){
 $serie = new XYDataSet();
 $val_id = (int)substr((string)$val, 0, strpos((string)$val, "-")); 

    for($i=23; $i>=0; $i--){
        $next_date= date('d-m-Y H:i:s', $time - 3600*$i);
        $prev_date= date('d-m-Y H:i:s', $time - 3600*($i+1));
        $next_date = mysql_real_escape_string(trim($next_date));
        $prev_date = mysql_real_escape_string(trim($prev_date));
        /*$weight_q = db_query("SELECT SUM(`retweetCount` + `favoriteCount`)/2 as peso
                        FROM  `ax_twitter`
                        WHERE (`date` BETWEEN '{$prev_date}' AND '{$next_date}') AND (`ID_request` = %d)", $val_id);
        */
        $weight_q = db_query( "SELECT SUM(`retweetCount`) as peso FROM `ax_twitter` WHERE (`date` BETWEEN '$prev_date' AND '$next_date') AND (`ID_request` = %d)", $prev_date, $next_date, $val_id);

        while($row= db_fetch_object($weight_q)){
            echo($row->peso);
            $serie ->addPoint(new Point($next_date, $row->peso));
        }
        $prev_date = $next_date;
    }
$dataSet-> addSerie($val, $serie);

}
$chart->setDataSet($dataSet);

$chart->setTitle("Andamento dei Retweet e Favorite");
$chart->getPlot()->setGraphCaptionRatio(0.8);
$chart->render($path_image);    

}

I tried different ways with query sql but I have the following errors:

warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '18:39:52 AND 01-10-2014 19:39:52) AND (ID_request = 1)' at line 1 query: SELECT SUM(retweetCount) as peso FROM ax_twitter WHERE (date BETWEEN 01-10-2014 18:39:52 AND 01-10-2014 19:39:52) AND (ID_request = 1) in trigger_error() (line 134 of C:\xampp\htdocs\drupal\includes\database.mysqli.inc). =>

When I added the quotes to the parameters of the sql query , however do not get any results for my graph. My question: is right way?...is right query? With PHPAdmin I get right results if set two dates manually sorry for the language.

1 Answer 1

1

Looking at the error msg, I think the code needs to be adapted, so that the current string (as it is now in the error message) like BETWEEN 01-10-2014 18:39:52 AND 01-10-2014 19:39:52 is replaced by BETWEEN "01-10-2014 18:39:52" AND "01-10-2014 19:39:52". So basically prepend and append a " to the variables $prev_date and $next_date ...

What isn't clear to me in this question though:

  • What is that 'libchart' in the title of this question?
  • What's the purpose of the $chart in your code? If it is because you're using some charting module, it'd be interesting to know which module you're using, and you might want to add the charts tag to this question also ...

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.