How to show all time records from two tables day wise

Question

I have below given mysql query to output required records for single day. It is working perfectly. But i want to show all time records from two table day wise. my two tables have unix timestamps

Query for single day records is

    // DAY DATA STATS
$stmt_check = $conn->prepare("SELECT * FROM webstats WHERE pubhash =? AND time BETWEEN $day6 AND $day5");
$stmt_check->bind_param("s", $pubhash);
$stmt_check->execute();
$stmt_check->store_result();
$numberofrowsimpresssday = $stmt_check->num_rows;

$stmt_check->close();

$sql2 = "SELECT SUM(click) AS click_sum FROM webstatsclick WHERE pubhash='$pubhash' AND time BETWEEN $day6 AND $day5";
         $result2 = mysqli_query($conn, $sql2);

         if (mysqli_num_rows($result2) > 0) {
            while($row = mysqli_fetch_assoc($result2)) {

$clickz = $row['click_sum'];
$clicks = $clickz ?: '0';

            }
         } else {

         }

?>
<br>
<?php echo $day6; ?>||<?php echo $numberofrowsimpresssday; ?>||<?php echo $clicks; ?>
<?php
// DAY STATS ENDS

This should output AS 1542758400||113||2 where DAY MIDNIGHT TIMESTAMP||IMPRESSIONS||CLICKS

Kindly help me. Thanks in advance.

New updated code

$sql2 = "SELECT 
    count(*) as 'impressions',
    unix_timestamp(date(from_unixtime(A.time))) as 'timestamp',
    sum(click) as 'clicks'
FROM `webadstats` as A
LEFT JOIN `webstatsclick` as B
ON A.pubadhash = B.pubadhash
WHERE A.pubadhash='$pubadhash'
GROUP BY date(from_unixtime(A.time))";

$result2 = mysqli_query($conn, $sql2);
if (mysqli_num_rows($result2)>0) {
    while($row = mysqli_fetch_assoc($result2)) {
        $impressions[] = $row['impressions'];
        $clicksall[] = $row['clicks'];
        $timestamp[] = $row['timestamp'];
    }


   for($i=0; $i<count($clicksall); $i++){
    $str .= $timestamp[$i] . '||' . $impressions[$i] . '||' . $clicksall[$i] . ',';
    }
}
echo $str;

Answer 1

I think what you are asking can be solved with GROUP BY and the DATE() function. See this question: MySQL/SQL: Group by date only on a Datetime column

You also need to use from_unixtime() in order to convert the timestamp into a date. See this question: Convert Unix timestamp into human readable date using MySQL

SELECT count(*),
       unix_timestamp(date(from_unixtime(time))) as 'timestamp' 
FROM webstats 
WHERE pubhash =? 
GROUP BY date(from_unixtime(time))

SELECT SUM(click) AS 'click_sum',
       unix_timestamp(date(from_unixtime(time))) as 'timestamp' 
FROM webstatsclick 
WHERE pubhash='$pubhash' 
GROUP BY date(from_unixtime(time))

Edit: To help with your specific case, your PHP code can be cleaned up and you can do everything in one query instead of two. See below:

$sql2 = "SELECT 
    count(*) as 'impressions',
    unix_timestamp(date(from_unixtime(A.time))) as 'timestamp',
    sum(B.click) as 'clicks'
FROM `webstats` as A
LEFT JOIN `webstatsclick` as B
ON A.pubhash = B.pubhash
WHERE A.pubhash='$pubhash'
GROUP BY date(from_unixtime(A.time))";

$result2 = mysqli_query($conn, $sql2);
if (mysqli_num_rows($result2)>0) {
    while($row = mysqli_fetch_assoc($result2)) {
        $impressions[] = $row['impressions']
        $clicksall[] = $row['clicks'];
        $timestamp[] = $row['timestamp'];
    }
    while($i=0; $i<strlen($clicksall); $i++){
        $str .= '<br>' . $timestamp[$i] . '||' . $impressions[$i] . '||' . $clicksall[$i];
    }
}
echo $str;