How to echo the first and last occurrence of time log of the specific date of a specific user id in the ?

Asked Apr 11 '23 at 00:55

Active Aug 30 '23 at 03:18

Viewed 52 times

0

I have an html table which is used for viewing the timelog of a user from the userlist. Once a view button is clicked, it will redirect to a page where the first and last timelog of a specific day will appear inside the `<table>`.

Here is an example of the data inside tbl_timelog of db_time

id | user_id | date | time | am_pm 1 | literal01 | Apr 5, 2023 | 09:49 | AM 2 | adlaon05 | Apr 5, 2023 | 09:50 | AM 3 | ong07 | Apr 5, 2023 | 10:01 | AM 4 | literal01 | Apr 5, 2023 | 17:20 | PM 5 | adlaon05 | Apr 5, 2023 | 17:51 | PM 6 | ong07 | Apr 5, 2023 | 18:13 | PM 7 | ong07 | Apr 7, 2023 | 10:11 | AM 8 | literal01 | Apr 7, 2023 | 10:11 | AM 9 | adlaon05 | Apr 7, 2023 | 10:11 | AM 10 | literal01 | Apr 7, 2023 | 10:16 | AM 11 | literal01 | Apr 7, 2023 | 17:12 | PM 12 | ong07 | Apr 7, 2023 | 17:12 | PM 13 | adlaon05 | Apr 7, 2023 | 17:15 | PM

Expected output:

user_id: literal01

`+-------+---------------+---------------+ |Date | Apr 5, 2023 | Apr 7, 2023 | +-------+-------+-------+-------+-------+ |Time | 09:49 | 17:20 | 10:11 | 17:12 | +-------+-------+-------+-------+-------+`

user_id: adlaon05

`+-------+---------------+---------------+ |Date | Apr 5, 2023 | Apr 7, 2023 | +-------+-------+-------+-------+-------+ |Time | 09:50 | 17:51 | 10:11 | 17:15 | +-------+-------+-------+-------+-------+`

user_id: ong07

`+-------+---------------+---------------+ |Date | Apr 5, 2023 | Apr 7, 2023 | +-------+-------+-------+-------+-------+ |Time | 10:01 | 18:13 | 10:11 | 17:12 | +-------+-------+-------+-------+-------+`

database connection:

`$server_name = "localhost"; $db_username = "root"; $db_password = ""; $db_name = "db_time"; $connection = mysqli_connect($server_name,$db_username,$db_password,$db_name);`

query:

$query = "SELECT * FROM `tbl_timelog` WHERE `user_id` = '$user_id'"; $query_run = mysqli_query($connection, $query); if($query_run){ $_SESSION['id'] = $user_id; }

table:

<table> <thead> <tr> <th scope="col">Date</th> <?php $dates = array(); foreach($query_run as $row) { if (in_array($row['date'], $dates)) { continue; } $dates[] = $row['date']; { ?> <th scope="col" colspan="2"><?php echo $row['date'];?></th> <?php } }?> </tr> </thead> <tbody> <tr> <th scope="row">Time</th> <?php $out = array(); foreach($query_run as $row) { if (in_array($row['am_pm'], $out)) { continue; } $out[] = $row['am_pm']; {?> <td><?php echo $row["time"];?></td> <?php } }?> </tr> </tbody> </table>

Here is the result for each user_id.

user_id: literal01

`+-------+---------------+-------------+ |Date | Apr 5, 2023 | Apr 7, 2023 | +-------+---------------+-------------+ |Time | 09:49 | 17:20 | +-------+---------------+`

user_id: adlaon05

`+-------+---------------+-------------+ |Date | Apr 5, 2023 | Apr 7, 2023 | +-------+---------------+-------------+ |Time | 09:50 | 17:51 | +-------+---------------+`

user_id: ong07

`+-------+---------------+-------------+ |Date | Apr 5, 2023 | Apr 7, 2023 | +-------+---------------+-------------+ |Time | 10:01 | 18:13 | +-------+---------------+`

Timelog is not reflected on Apr 7, 2023.

php mysqli

edited Aug 30 '23 at 03:18
Paul T.

4,703

11

25

29

asked Apr 11 '23 at 00:55
Jephoy

11

4

1

You are open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized prepared statements instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) and [MySQLi](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even data from the database, [you are still at risk of corrupting your data](https://bobby-tables.com/). If this is a school project. Best time to learn. How to do things right. – Jason K Apr 11 '23 at 03:33

Is the date field have a data type of date? Or is just a string? You should have date and time in an order by clause. Not a good format for sorting the date. In your second loop. You are using am_pm not date. So it's only going to allow two entries. – Jason K Apr 11 '23 at 04:01

Step one, merge the `date`, `time`, and `am_pm` data to form a single column with datetime values -- this will end the headache that you currently have and then prevent hundreds of headaches in the future. Then this looks inspiring: [Get Min and Max values with one MySQL query with Group BY](https://stackoverflow.com/a/50305992/2943403) or [Get the min and max value using sql group by query](https://stackoverflow.com/a/49298738/2943403) or [SQL : get Min and Max value in one column](https://stackoverflow.com/a/54794969/2943403) – mickmackusa Apr 11 '23 at 04:24

@JasonK - I am using prepared statements to insert those data in the database. What I need is how to display the values with the first and last occurrence of time log of the specific date of a specific user id in the table? – Jephoy Apr 11 '23 at 04:54

@mickmackusa - I did merge the date and time, unfortunately, while using the suggested links you have provided, none of them work. Thank you so much for extending your help. I appreciate you for that. But, still it doesn't suit to solve this problem. `$query = "SELECT user_id, MIN(time) AS 'out', MAX(time) AS 'in' FROM tbl_timelog WHERE user_id = '$user_id' GROUP BY user_id"; $query_run = mysqli_query($connection, $query) or die(mysqli_error($connection)); $log = mysqli_fetch_array($query_run); echo $log['logout']; echo $log['login']` – Jephoy Apr 11 '23 at 05:42

1

It is a very bad idea to use `die(mysqli_error($$conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman Apr 11 '23 at 10:57

0 Answers0

Question

I have an html table which is used for viewing the timelog of a user from the userlist. Once a view button is clicked, it will redirect to a page where the first and last timelog of a specific day will appear inside the <table>.

Here is an example of the data inside tbl_timelog of db_time

id | user_id   | date        | time  | am_pm
1  | literal01 | Apr 5, 2023 | 09:49 | AM
2  | adlaon05  | Apr 5, 2023 | 09:50 | AM
3  | ong07     | Apr 5, 2023 | 10:01 | AM
4  | literal01 | Apr 5, 2023 | 17:20 | PM
5  | adlaon05  | Apr 5, 2023 | 17:51 | PM
6  | ong07     | Apr 5, 2023 | 18:13 | PM
7  | ong07     | Apr 7, 2023 | 10:11 | AM
8  | literal01 | Apr 7, 2023 | 10:11 | AM
9  | adlaon05  | Apr 7, 2023 | 10:11 | AM
10 | literal01 | Apr 7, 2023 | 10:16 | AM
11 | literal01 | Apr 7, 2023 | 17:12 | PM
12 | ong07     | Apr 7, 2023 | 17:12 | PM
13 | adlaon05  | Apr 7, 2023 | 17:15 | PM

Expected output:

user_id: literal01

+-------+---------------+---------------+
|Date   | Apr 5, 2023   | Apr 7, 2023   |
+-------+-------+-------+-------+-------+
|Time   | 09:49 | 17:20 | 10:11 | 17:12 |
+-------+-------+-------+-------+-------+

user_id: adlaon05

+-------+---------------+---------------+
|Date   | Apr 5, 2023   | Apr 7, 2023   |
+-------+-------+-------+-------+-------+
|Time   | 09:50 | 17:51 | 10:11 | 17:15 |
+-------+-------+-------+-------+-------+

user_id: ong07

+-------+---------------+---------------+
|Date   | Apr 5, 2023   | Apr 7, 2023   |
+-------+-------+-------+-------+-------+
|Time   | 10:01 | 18:13 | 10:11 | 17:12 |
+-------+-------+-------+-------+-------+

database connection:

$server_name = "localhost";
$db_username = "root";
$db_password = "";
$db_name = "db_time";

$connection = mysqli_connect($server_name,$db_username,$db_password,$db_name);

query:

    $query = "SELECT * FROM `tbl_timelog` WHERE `user_id` = '$user_id'";
    $query_run = mysqli_query($connection, $query);
    if($query_run){

        $_SESSION['id'] = $user_id;
    }

table:

<table>
    <thead>
        <tr>
            <th scope="col">Date</th>
            <?php 
                $dates = array();
                    foreach($query_run as $row) {
                    if (in_array($row['date'], $dates)) {
                        continue;
                    }
                    $dates[] = $row['date'];
                    { ?>
            <th scope="col" colspan="2"><?php echo $row['date'];?></th>
            <?php } }?>
        </tr>
    </thead>
    <tbody>
        <tr>
            <th scope="row">Time</th>
            <?php 
                $out = array();
                foreach($query_run as $row)
                    {
                        if (in_array($row['am_pm'], $out))
                            {
                                continue;
                            }
                    $out[] = $row['am_pm'];
                        {?>
            <td><?php echo $row["time"];?></td>
            <?php } }?>
        </tr>
    </tbody>
</table>

Here is the result for each user_id.

user_id: literal01

+-------+---------------+-------------+
|Date   | Apr 5, 2023   | Apr 7, 2023 |
+-------+---------------+-------------+
|Time   | 09:49 | 17:20 |
+-------+---------------+

user_id: adlaon05

+-------+---------------+-------------+
|Date   | Apr 5, 2023   | Apr 7, 2023 |
+-------+---------------+-------------+
|Time   | 09:50 | 17:51 |
+-------+---------------+

user_id: ong07

+-------+---------------+-------------+
|Date   | Apr 5, 2023   | Apr 7, 2023 |
+-------+---------------+-------------+
|Time   | 10:01 | 18:13 |
+-------+---------------+

Timelog is not reflected on Apr 7, 2023.

You are open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized prepared statements instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) and [MySQLi](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even data from the database, [you are still at risk of corrupting your data](https://bobby-tables.com/). If this is a school project. Best time to learn. How to do things right. — Jason K, Apr 11 '23 at 03:33
Is the date field have a data type of date? Or is just a string? You should have date and time in an order by clause. Not a good format for sorting the date. In your second loop. You are using am_pm not date. So it's only going to allow two entries. — Jason K, Apr 11 '23 at 04:01
Step one, merge the `date`, `time`, and `am_pm` data to form a single column with datetime values -- this will end the headache that you currently have and then prevent hundreds of headaches in the future. Then this looks inspiring: [Get Min and Max values with one MySQL query with Group BY](https://stackoverflow.com/a/50305992/2943403) or [Get the min and max value using sql group by query](https://stackoverflow.com/a/49298738/2943403) or [SQL : get Min and Max value in one column](https://stackoverflow.com/a/54794969/2943403) — mickmackusa, Apr 11 '23 at 04:24
@JasonK - I am using prepared statements to insert those data in the database. What I need is how to display the values with the first and last occurrence of time log of the specific date of a specific user id in the table? — Jephoy, Apr 11 '23 at 04:54
@mickmackusa - I did merge the date and time, unfortunately, while using the suggested links you have provided, none of them work. Thank you so much for extending your help. I appreciate you for that. But, still it doesn't suit to solve this problem. `$query = "SELECT user_id, MIN(time) AS 'out', MAX(time) AS 'in' FROM tbl_timelog WHERE user_id = '$user_id' GROUP BY user_id"; $query_run = mysqli_query($connection, $query) or die(mysqli_error($connection)); $log = mysqli_fetch_array($query_run); echo $log['logout']; echo $log['login']` — Jephoy, Apr 11 '23 at 05:42
It is a very bad idea to use `die(mysqli_error($$conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Apr 11 '23 at 10:57