gpt4 book ai didi

php - 实时统计多个表中的记录

转载 作者:太空宇宙 更新时间:2023-11-03 12:28:19 24 4
gpt4 key购买 nike

我想按当前日期从不同的表中计算一条记录,并作为新表中不同列的一行返回。该代码将每三个小时更新一次记录,并在当前日期更改时插入新记录。我在“created_at”列中有当前日期和时间数据 (2013-05-20 14:12:12)。这是我当前的代码:

require_once('./db_connect.php');
$dbcon = new db;

//test to see if a specific field value is already in the DB
public function in_table($table,$where) {
$query = 'SELECT * FROM ' . $table . ' WHERE ' . $where;
$result = mysqli_query($this->dbh,$query);
$this->error_test('in_table',$query);
return mysqli_num_rows($result) > 0;
}

//running in background
while (true) {
$select= "SELECT (SELECT CURDATE()) AS time," .
"(SELECT COUNT(tweet_id) FROM tweets WHERE created_at= 'CURDATE() %') AS total_count," .
"(SELECT COUNT(fid) FROM fun WHERE ftime= 'CURDATE() %') AS f_count," .
"(SELECT COUNT(sid) FROM sad WHERE stime= 'CURDATE() %') AS s_count";

$results = mysqli_query( $dbcon, $select );

while($row = mysqli_fetch_assoc($result)) {
$time = $row['time'];
$total = $row['total_count'];
$fcount = $row['f_count'];
$scount = $row['s_count'];

$field_values = 'time = "' . $time . '", ' . 'total_count = ' . $total . ', ' . 'fun_count = ' . $fcount . ', ' . 'sad_count = ' . $scount;

if ($dbcon->in_table('count','time= "' . $time . '"')) {
$update = "UPDATE count SET $field_values WHEN time= '$time'";
mysqli_query( $dbcon, $update );
}
else {
$insert = "INSERT INTO count SET $field_values";
mysqli_query( $dbcon, $insert );
}
}

//update record every 3 hour
sleep(10800);
}

使用此代码我无法获得计数记录。结果返回| 2013-05-18 | 0 | 0 | 0 |。我该如何纠正这个问题?

最佳答案

我不熟悉 PHP,但您可以使用以下方法检索日期为今天任意时间的所有记录的计数:

     SELECT COUNT(tweet_id) 
FROM tweets
WHERE created_at >= curDate()
AND created_at < date_add(curDate(), interval 1 day)

相当于说

     ..
WHERE created_at >= (today at midnight *incusive*)
AND created_at < (tomorrow at midnight *exclusive*)

更新:

这种方法的优点是索引友好。虽然使用 WHERE DATE(Column) = currDate() 有效,但它可以防止数据库在该列上使用索引,从而使查询变慢。

关于php - 实时统计多个表中的记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16652888/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com