0

I have table "att" in mysql like this:

datetime                   userid     startorend
2022-10-25 10:00:00     1          0
2022-10-25 14:30:00     44         0
2022-10-25 11:00:00     1          1 
2022-10-25 14:40:00     44         1

So have 2 users which start and end their job.

Select works fine but it shows me wrong:

Name     starttime               endtime
user1    2022-10-25 10:00:00     
user1    2022-10-25 11:00:00
user2    2022-10-25 14:30:00    
2022-10-25 14:40:00

Need to show data in html table. I want to show it like:

Name     starttime               endtime
user1    2022-10-25 10:00:00     2022-10-25 11:00:00
user2    2022-10-25 14:30:00     2022-10-25 14:40:00

How can I achieve this?

$query = "SELECT * FROM att WHERE date(att.starttime) = CURDATE() ORDER BY userid ASC";
$result = mysqli_query($conn, $query);

if (!$result) {
  die("Problém" . mysqli_error($conn));
}

while ($row = mysqli_fetch_assoc($result)) {
  $userid = $row['userid'];
  $time= $row['datetime'];

  echo "<tr>";
  echo "<td>" . $userid. " " . $name . "</td>";
  echo "<td>" . $time . "</td>";

  echo "</tr>";
Dharman
  • 30,962
  • 25
  • 85
  • 135
sover
  • 11
  • 4
  • The columns in the table you are trying to echo do not seem to line up with the expected query output. You should fix your question. – Tim Biegeleisen Oct 25 '22 at 14:37
  • 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 Oct 25 '22 at 14:39
  • That's a job for SQL, not for PHP. You need to use a JOIN and find the end time. – Dharman Oct 25 '22 at 14:41
  • You'd join 2 copies of your table. – Akina Oct 25 '22 at 15:18

1 Answers1

0
SELECT userid, t1.`datetime`, t2.`datetime`
FROM att t1
JOIN att t2 USING (userid)
WHERE !t1.startorend
  AND t2.startorend
-- AND DATE(t1.starttime) = CURRENT_DATE
-- AND DATE(t2.starttime) = CURRENT_DATE

I do not see the source for username... another table must be used.

The query assumes that there exists only one income and one outcome per day in the table.

it doesn't work fine for me. no results there. – sover

You do something wrongly.

CREATE TABLE att (`datetime` DATETIME, userid INT, startorend BOOLEAN);
INSERT INTO att VALUES
('2022-10-25 10:00:00',     1,          0),
('2022-10-25 14:30:00',     44,         0),
('2022-10-25 11:00:00',     1,          1),
('2022-10-25 14:40:00',     44,         1);
SELECT * FROM att;
datetime userid startorend
2022-10-25 10:00:00 1 0
2022-10-25 14:30:00 44 0
2022-10-25 11:00:00 1 1
2022-10-25 14:40:00 44 1 
SELECT userid, t1.`datetime`, t2.`datetime`
FROM att t1
JOIN att t2 USING (userid)
WHERE !t1.startorend
  AND t2.startorend
-- AND DATE(t1.starttime) = CURRENT_DATE
-- AND DATE(t2.starttime) = CURRENT_DATE
userid datetime datetime
1 2022-10-25 10:00:00 2022-10-25 11:00:00
44 2022-10-25 14:30:00 2022-10-25 14:40:00

fiddle

Akina
  • 39,301
  • 5
  • 14
  • 25