Unable to retreive unique dates from mysql column having date and time in mysql using php

Question

Hi friend am trying to retreive the dates from mysql column named 'date' which has date and time. I want to retreive only dates not times. I want retreive only dates which are not duplicates..

Here is sample data of mysql with column date..

   03/07/2017 03:37:12pm
   03/07/2017 10:38:12pm
   04/07/2017 10:38:12pm
   04/07/2017 2:38:12pm

wanted out put as

     03/07/2017
     04/07/2017

Here is my php code

<?php

$sql = "SELECT  DISTINCT date('d/m/Y,date) FROM video_data";
echo $sql;
$result = mysqli_query($connection, $sql);
echo $result;
while ($row = mysqli_fetch_assoc($result)) {
    $id   = $row['id'];
    $date = $row['date'];
    $date = date('Y-m-d', strtotime($date));
    echo "<tr>";
    echo "<td>$date</td>";
    echo "</tr>";

}

?>

Am unable to print dates using php

Possible duplicate of [mysqli\_fetch\_array()/mysqli\_fetch\_assoc()/mysqli\_fetch\_row() expects parameter 1 to be resource or mysqli\_result, boolean given](https://stackoverflow.com/questions/2973202/mysqli-fetch-array-mysqli-fetch-assoc-mysqli-fetch-row-expects-parameter-1) — Jens, Jul 05 '17 at 09:53
You can use the DATE function to retrieve just the date part, and then use GROUP BY. — Sloan Thrasher, Jul 05 '17 at 09:55
Hint: check for errors after executing SQL Statements `mysqli_error($connection)` — Jens, Jul 05 '17 at 09:55
See [How to get mysqli error in different environments?](https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments) — Masivuye Cokile, Jul 05 '17 at 09:56
@MasivuyeCokile Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in — sravani, Jul 05 '17 at 10:04
Try this SELECT DISTINCT date_format('d/m/Y',date) as new_date FROM video_data group by new_date — JYoThI, Jul 05 '17 at 10:07
@JYoThI Catchable fatal error: Object of class mysqli_result could not be converted to string in C:\xampp\htdocs\video_analysis\pages\videos\summary.php on line 331 is shown — sravani, Jul 05 '17 at 10:10
yes you can't echo the object . so just comment this line // echo $result; instead of echo you need to use var_dump($result); or print_r($result); — JYoThI, Jul 05 '17 at 10:11
@JYoThI its shown like this how can I print date from that mysqli_result Object ( [current_field] => 0 [field_count] => 1 [lengths] => [num_rows] => 1 [type] => 0 ) — sravani, Jul 05 '17 at 10:13
@JYoThI yeah its working am getting only number of rows but unable to print dates — sravani, Jul 05 '17 at 10:16

Gyan · Answer 1 · 2017-07-05T10:08:01.080

0

Use something like

<?php 
$sql = "SELECT DISTINCT id,date FROM video_data";
//echo $sql;
$result = mysqli_query($connection, $sql);
//echo $result;
while ($row = mysqli_fetch_assoc($result)) {
 $id   = $row['id'];
 $date = $row['date'];
 $date = date('d/m/Y', strtotime($date));
 echo "<tr>";
 echo "<td>".$date."</td>";
 echo "</tr>";
}
?>

edited Jul 05 '17 at 10:08

answered Jul 05 '17 at 10:00

Gyan

498
6
10

then all dates will come because only time is different – sravani Jul 05 '17 at 10:03

JYoThI · Answer 2 · 2017-07-05T10:25:24.747

0

use Date_format get the date part from datetime column then apply distinct for that column like this

SELECT id,DISTINCT date_format(date,'%d/%m/%Y') as new_date FROM video_data group by new_date

PHP :

<?php

$sql = "SELECT id,DISTINCT date_format(date,'%d/%m/%Y') as new_date FROM video_data group by new_date";

$result = mysqli_query($connection, $sql);

while ($row = mysqli_fetch_assoc($result)) {
    $id   = $row['id'];
    $date = $row['new_date'];
    echo "<tr>";
    echo "<td>$date</td>";
    echo "</tr>";

}

?>

edited Jul 05 '17 at 10:25

answered Jul 05 '17 at 10:19

JYoThI

11,977
1
11
26

its printing date as 1970-01-01 if I remove $date = date('Y-m-d', strtotime($date)); its printing nothing – sravani Jul 05 '17 at 10:24
arning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\video_analysis\pages\videos\summary.php on line 331 is shown – sravani Jul 05 '17 at 10:29
when i give var_dump($result) its showing as bool(false) – sravani Jul 05 '17 at 10:34
@sravani meaning your query fails... can u paste your query and run it on phpmyadmin – Masivuye Cokile Jul 05 '17 at 10:37
unable to run the query its showing complete errors0 errors were found during analysis. An expression was expected. (near "DISTINCT" at position 10) Unrecognized keyword. (near "DISTINCT" at position 10) Unrecognized keyword. (near "date_format" at position 19) Unexpected token. (near "(" at position 30) Unrecognized keyword. (near "date" at position 31) Unexpected token. (near "," at position 35) Unexpected token. (near "'%d/%m/%Y'" at position 36) Unexpected token. (near ")" at position 46) Unrecognized keyword. (near "as" at position 48) Unexpected token. – sravani Jul 05 '17 at 10:45
date column type is ? @sravani datetime or varchar ? – JYoThI Jul 05 '17 at 10:53

score 0 · Answer 3 · answered Jul 05 '17 at 10:21

0

select distinct value from db and convert them according to your need.

$sql = "SELECT DISTINCT DATE_FORMAT(date, '%d/%m/%Y') FROM  video_data;"
$result = mysqli_query($connection, $sql);
while ($row = mysqli_fetch_assoc($result)) {
 $id   = $row['id'];
 $date = $row['date'];
 $date = date('d/m/Y', strtotime($date));
 echo "<tr>";
 echo "<td>".$date."</td>";
 echo "</tr>";
}

answered Jul 05 '17 at 10:21

partho222

71
5

its showing only 01/01/1970 this date but its not there in my database and Notice: Undefined index: id in C:\xampp\htdocs\video_analysis\pages\videos\summary.php on line 332 Notice: Undefined index: date in C:\xampp\htdocs\video_analysis\pages\videos\summary.php on line 333 – sravani Jul 05 '17 at 10:32
can you please share your **db** or partial data from 'video_data' – partho222 Jul 05 '17 at 10:49
i think your date is stored as '03-07-2017 03:37:12pm' format, is it ? – partho222 Jul 05 '17 at 10:52
id date video_id are column names 1 2 3 are ids 04/07/2017 03:37:12pm 04/07/2017 03:37:12pm 04/07/2017 03:37:12pm are dates with time JK8CzkQh1FY JK8CzkQh1FY JK8CzkQh1FY are videoids i need to get the date without duplicates – sravani Jul 05 '17 at 10:54
date is stored as 04/07/2017 03:37:12pm – sravani Jul 05 '17 at 10:56
can you please give me the output of - $sql = "SELECT DISTINCT DATE_FORMAT(date, '%d/%m/%Y') FROM video_data;" $result = mysqli_query($connection, $sql); while ($row = mysqli_fetch_assoc($result)) { var_dump($row); } – partho222 Jul 06 '17 at 03:00

Unable to retreive unique dates from mysql column having date and time in mysql using php

3 Answers3