3

I have database "db2" with table "menjava"

In table menjava have "id", "author" and "date_submitted" field

  • id - auto_increment
  • author - int(11)
  • date_submitted - datetime

I want to count all the rows for todays date and all the rows for yesterdays date (so there will be two codes with conditions) based on a DATETIME field called 'date_submitted' that holds the date and time of each record's creation.

In the file result.php, there is this count displayed, but it does not work. In the same file (result.php) I have some other code to display data from different database, so I think that povezava.php is working ok.

My code:

 <?
    require "povezava.php";
    $q=mysql_query(" SELECT COUNT(*) AS total_number FROM menjava 
WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)",$link2);
// now you can 
if ( $nt = mysql_fetch_array($q)){
echo $nt["total_number"];
$q=mysql_query($nt) or die(mysql_error());
}

    ?>

my file povezava.php looks like this:

<?
$servername='localhost';

$dbusername='user';
$dbpassword='pass';

$dbname1='db1';
$dbname2='db2';

$link1 = connecttodb($servername,$dbname1,$dbusername,$dbpassword);
$link2 = connecttodb($servername,$dbname2,$dbusername,$dbpassword);

function connecttodb($servername,$dbname,$dbusername,$dbpassword)
{
    $link=mysql_connect ("$servername","$dbusername","$dbpassword",TRUE);
    if(!$link){die("Could not connect to MySQL");}
    mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
    return $link;
}
    ?>

Error that I get: 

A PHP Error was encountered

Severity: NoticeMessage: Array to string conversionFilename: templates/master.phpLine Number: 231 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 'Array' at line 1

Fixed: 

<?
    require "povezava.php";
    $q=mysql_query("SELECT COUNT(*) AS total_number FROM menjava WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY)",$link2);
// working 
if ( $nt = mysql_fetch_array($q)){
echo $nt["total_number"];
}

    ?>

Thank you!

Sašo Krajnc
  • 133
  • 2
  • 3
  • 11

3 Answers3

1

Try :

$q = 'SELECT COUNT(*) FROM menjava
          WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)';
$result = mysql_query($q);
$total_rows = mysql_fetch_row($result);
print $total_rows[0] . ' authors have been submitted today and yesterday.';
Jean Logeart
  • 52,687
  • 11
  • 83
  • 118
  • Severity: NoticeMessage: Undefined variable: link2Filename: index.phpLine Number: 197 A PHP Error was encountered Severity: WarningMessage: mysql_query(): supplied argument is not a valid MySQL-Link resourceFilename: index.phpLine Number: 197 A PHP Error was encountered Severity: WarningMessage: mysql_fetch_array(): supplied argument is not a valid MySQL result resourceFilename: index.phpLine Number: 199 – Sašo Krajnc Dec 25 '11 at 14:03
  • Severity: WarningMessage: mysql_fetch_array(): supplied argument is not a valid MySQL result resource – Sašo Krajnc Dec 25 '11 at 14:09
  • By the looks of the code the query should return multiple columns. – Karlson Dec 26 '11 at 07:59
  • do I have to change like this? WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),$link2'; – Sašo Krajnc Dec 26 '11 at 13:48
  • I get this error: `Severity: WarningMessage: mysql_fetch_row() expects parameter 1 to be resource, boolean givenFilename: templates/master.phpLine Number: 229 authors have been submitted today and yesterday.` – Sašo Krajnc Dec 26 '11 at 13:51
  • @Sašo then do some debugging: use `echo mysql_error();` to see what is wrong – Pekka Dec 26 '11 at 16:13
  • It's probably the extra space between `COUNT` and `(*)`. – Pekka Dec 26 '11 at 16:13
0

Please try the following SQL commands:

$sqlToday = "Select COUNT(*) FROM menjava WHERE DATE(date_submitted) = CURRENT_DATE()";

$sqlYesterday = "Select COUNT(*) FROM menjava WHERE DATE(dc_created) = CURDATE() - INTERVAL 1 DAY";
tuomastik
  • 4,559
  • 5
  • 36
  • 48
Aman
  • 5
  • 7
  • Please don't just throw in some code! Give a few lines of explanation about what was the problem and how is your answer a solution. – totymedli Jul 06 '17 at 15:01
0

I use this function it's very simple

function statsofdays($database,$tableName,$coloneDate,$past_days_to_count=0)
{
    $datenow = date("Y-m-d H:i:s");

    $stringDays = "(SELECT COUNT(Id) FROM `".$tableName."` WHERE DAY(".$coloneDate.") = '".add_date_to_date($datenow,'0 days','d')."') as day1,";
    
    for ($i=2; $i <= $past_days_to_count; $i++)
        $stringDays .= "(SELECT COUNT(Id) FROM `".$tableName."` WHERE DAY(".$coloneDate.") = '".add_date_to_date($datenow,'-'.$i.' days','d')."') as day".$i.",";

    $row = $database->query("SELECT ".$stringDays." (SELECT COUNT(Id) FROM `".$tableName."`) as total");

    $stringReturn[0] = $row['total'];
    $stringReturn[1] = $row['day1'];

    for ($c=2; $c <= $past_days_to_count; $c++)
         $stringReturn[$c] = $row['day'.$c];
    
    return $stringReturn;
}

Params :

$database :

You can modify the function has your database structure, for me i use a class load to use ->query()

$tableName :

The name of the table you want to get data from

$coloneDate

Name of date format fields

$past_days_to_count

Number of days to go back to the past (if == 0 you get a count of total rows and today rows)

Example

$stats_of_year = statsofdays( $database , "table_name" , "creation_date" , 365 );

return

total rows for each day for 365 days like example (the values >= 0)

using

echo $stats_of_year['total'];
echo $stats_of_year['day1'];
...
echo $stats_of_year['day365'];

you need this also :

function add_date_to_date($stringDate, $days, $stringFormat)
{
    $date = date_create($stringDate);
    date_add($date,date_interval_create_from_date_string($days));
    return date_format($date,$stringFormat);
}
Smar ts
  • 49
  • 5