CODEWITHSUNDEEP
phpmysql
Grasper
Grasper

Reputation: 1313

SQL Select count of repeated dates

I'm trying to select count of repeated dates and output its numbers

$user_curr_id = $_SESSION['user_id'];
$sql = "SELECT COUNT(datum) FROM table_name WHERE user_ids = $user_curr_id";

I have no idea how to do it.

2014-07-23,2014-07-23,2014-07-23 => 3
2014-07-24,2014-07-24 =>2
2014-07-25 => 1

and get $result = 3,2,1

Upvotes: 0

Views: 105

Answers (1)

VMai
VMai

Reputation: 10336

I assume you're looking after the GROUP BY clause:

$sql = "SELECT datum, COUNT(datum) as cnt
        FROM table_name 
        WHERE user_ids = $user_curr_id 
        GROUP BY datum
        ORDER BY COUNT(datum) DESC;";

if your column datum is of the data type DATE.

Note

As already mentioned you're vulnerable to sql injection. You should use a parameterized prepared statement and bind your input value to this parameter like that:

$sql = "SELECT datum, COUNT(datum) cnt
        FROM table_name 
        WHERE user_ids = ? 
        GROUP BY datum
        ORDER BY COUNT(datum) DESC;";

$result = array();
if ($stmt = $mysqli->prepare($sql)) {
    if ($stmt->bind_param('s', $user_curr_id)) {
        if($res = $stmt->execute()) {
            while ($row = $res->fetch_assoc()) {
                $result[] = $row['cnt'];             // add the content of field cnt
            }
        }
    }
}
echo implode(',', $result);

Upvotes: 2

Related Questions