Barand

As an alterntive for $price = $res->fetchColumn(); return $price ? $price : 'Not set'; I could have used return $res->fetch()['price'] ?? 'Not set'; which might be better (if you have freebies and 0.00 is a valid price).

Correct - the ternary expression reads "If price is true, return price otherwise return 'Not set'. In the line above $price = $res->fetchColumn(); price will be "false" if no price was found.

You will if you try it. Experiment. Planes won't fall out of the sky if get it wrong.

I've given you a couple of alternative methods - the main change is to to use the previous log in instead of the log out time so it shouldn't be to difficult for you to alter the queries to your requirements. The tricky bit is your "edge condition", but give it a go. (my 503 was because I forgot to subtract the 1 to get the days inbetween when getting the last record's time difference, so I had 97) I still don't understand why, when you are calculating days between logins, you decide to add in the duration of the login for the last record (15 apples + 1 orange). If anything I would have the thought the last record's time would be from final login to the current date. Just my 0.02 worth

Try "Plan A" function determine_multi_price_kit( $order_id, $kit_id, $pdo ) { $res = $pdo->prepare("SELECT l.price , l.enterprise FROM production_data p JOIN leds_prices l ON l.enterprise IN (p.enterprise, 'Unbranded') AND kit_id = :kit_id WHERE p.id = :order_id ORDER BY l.enterprise = 'Unbranded' "); $res->execute( [ 'kit_id' => $kit_id, 'order_id' => $order_id ] ); $price = $res->fetchColumn(); return $price ? $price : 'Not set'; } $kit = 48; $orders = [ 5017, 7094, 7179, 7185, 1234 ] ; foreach ($orders as $oid) { echo "$oid : ". determine_multi_price_kit( $oid, $kit, $pdo ) . '<br>'; }

Question: Your function specifies (order_id, kit_id, link) as parameters but your sample function call in last post has ( 7185, 119, $pdo ) . According to your data, 119 is a product_id and not a kit_id. Which is it?

Excellent - I'll get back to you.

Any chance of some test data to work with?

That, according to Excel, would give a total absence of 503 days. Is that your expected result?

Use a form. https://www.php.net/manual/en/language.variables.external.php

Then I suggest you post on the "job Offerings" board in these forums.

The whole purpose of this exercise was to to find the number of days between the date the user logging out and the date of their next login If you think that then you are nor understanding your own data or your initial description of the problem was wrong. 8 days is the number of days logged in in row 11, not the the absent time between logout/next login. As far as I am concerned, the query and results are correct.

As I have no idea what the query was that produced those results, or what the input data to the query was, what is it you expect?

Introducing your "studentlogin" column to the data ... +----+---------------------+---------------------+--------------+ | id | datein | dateout | studentlogin | +----+---------------------+---------------------+--------------+ | 1 | 2019-03-30 17:05:24 | 2019-03-30 17:09:47 | mina1111 | | 2 | 2019-04-01 15:13:32 | 2019-04-01 15:19:46 | abcd1234 | | 3 | 2019-04-04 23:37:21 | 2019-04-04 23:50:51 | xyz12345 | | 4 | 2019-04-18 15:28:35 | 2019-04-18 15:33:10 | mina1111 | | 5 | 2019-04-23 16:35:20 | 2019-04-23 16:42:35 | xyz12345 | | 6 | 2019-04-24 12:03:07 | 2019-04-24 12:10:28 | abcd1234 | | 7 | 2019-05-01 08:05:48 | 2019-05-01 08:20:28 | abcd1234 | | 8 | 2019-05-08 18:04:04 | 2019-05-08 18:14:57 | xyz12345 | | 9 | 2019-05-09 08:18:15 | 2019-05-09 08:29:38 | mina1111 | | 10 | 2019-06-18 12:49:01 | 2019-06-18 13:10:15 | xyz12345 | | 11 | 2019-09-05 17:17:33 | 2019-09-13 15:24:28 | abcd1234 | | 12 | 2019-09-28 07:05:03 | 2019-09-28 08:12:26 | mina1111 | | 13 | 2019-09-28 12:55:56 | 2019-09-28 13:21:15 | xyz12345 | | 14 | 2019-09-28 16:47:52 | 2019-10-01 16:28:18 | abcd1234 | | 15 | 2019-10-03 13:11:44 | 2019-12-10 17:56:25 | mina1111 | | 16 | 2020-05-22 12:08:32 | 2020-08-27 17:21:02 | xyz12345 | +----+---------------------+---------------------+--------------+ then SELECT studentlogin , SUM(CASE WHEN DATE(datein) > DATE(dateout) THEN DATEDIFF(datein, dateout) - 1 ELSE 0 END ) as tot_absent FROM ( SELECT a.dateout , MIN(b.datein) as datein , a.studentlogin FROM ajoo_login a LEFT JOIN ajoo_login b ON a.dateout < b.datein AND a.studentlogin = b.studentlogin GROUP BY a.studentlogin, a.dateout ) logins GROUP BY studentlogin; +--------------+------------+ | studentlogin | tot_absent | +--------------+------------+ | abcd1234 | 168 | | mina1111 | 183 | | xyz12345 | 409 | +--------------+------------+

Because of the JOIN, it is initialized before it is used. As an alternative to the join you could have two queries ... SELECT @prevout := NULL; -- initialize @prevout SELECT SUM(diff) AS tot_absent FROM ( SELECT CASE WHEN DATE(datein) > DATE(@prevout) THEN DATEDIFF(datein, @prevout) - 1 ELSE 0 END AS diff , datein , @prevout := dateout AS dateout -- store dateout in @prevout FROM ajoo_login ) logins; @vars are just like variables in any other language - you assign values to them (and use those values) as the query processor loops through each of the records

The most common causes of that error are an opening { or " without a corresponding closing } or "

Meanwhile, here's an alternative solution to my previous one, this one without the SQL variables. SELECT SUM(CASE WHEN DATE(datein) > DATE(dateout) THEN DATEDIFF(datein, dateout) - 1 ELSE 0 END ) as tot_absent FROM ( SELECT a.dateout , MIN(b.datein) as datein FROM ajoo_login a LEFT JOIN ajoo_login b ON a.dateout < b.datein GROUP BY a.dateout ) logins; +------------+ | tot_absent | +------------+ | 327 | +------------+

Where did that suddenly spring from? There's no mention in your original post. Don't keep us in the dark and still expect help.

OK, I loaded your data into a test table INSERT INTO ajoo_login (datein, dateout) VALUES ('2019-03-30 17:05:24', '2019-03-30 17:09:47'), ('2019-04-01 15:13:32', '2019-04-01 15:19:46'), ('2019-04-04 23:37:21', '2019-04-04 23:50:51'), ('2019-04-18 15:28:35', '2019-04-18 15:33:10'), ('2019-04-23 16:35:20', '2019-04-23 16:42:35'), ('2019-04-24 12:03:07', '2019-04-24 12:10:28'), ('2019-05-01 08:05:48', '2019-05-01 08:20:28'), ('2019-05-08 18:04:04', '2019-05-08 18:14:57'), ('2019-05-09 08:18:15', '2019-05-09 08:29:38'), ('2019-06-18 12:49:01', '2019-06-18 13:10:15'), ('2019-09-05 17:17:33', '2019-09-13 15:24:28'), ('2019-09-28 07:05:03', '2019-09-28 08:12:26'), ('2019-09-28 12:55:56', '2019-09-28 13:21:15'), ('2019-09-28 16:47:52', '2019-10-01 16:28:18'), ('2019-10-03 13:11:44', '2019-12-10 17:56:25'), ('2020-05-22 12:08:32', '2020-08-27 17:21:02'); Running the query gives SELECT SUM(diff) AS tot_absent FROM ( SELECT CASE WHEN DATE(datein) > DATE(@prevout) THEN DATEDIFF(datein, @prevout) - 1 ELSE 0 END AS diff , datein , @prevout := dateout AS dateout -- store dateout in @prevout FROM ajoo_login JOIN (SELECT @prevout := NULL) init -- initialize @prevout ) logins; +------------+ | tot_absent | +------------+ | 327 | +------------+ Running just the subquery portion gives mysql> SELECT -> CASE WHEN DATE(datein) > DATE(@prevout) -> THEN DATEDIFF(datein, @prevout) - 1 -> ELSE 0 -> END AS diff -> , datein -> , @prevout := dateout AS dateout -> FROM ajoo_login -> JOIN (SELECT @prevout := NULL) init; +------+---------------------+---------------------+ | diff | datein | dateout | +------+---------------------+---------------------+ | 0 | 2019-03-30 17:05:24 | 2019-03-30 17:09:47 | | 1 | 2019-04-01 15:13:32 | 2019-04-01 15:19:46 | | 2 | 2019-04-04 23:37:21 | 2019-04-04 23:50:51 | | 13 | 2019-04-18 15:28:35 | 2019-04-18 15:33:10 | | 4 | 2019-04-23 16:35:20 | 2019-04-23 16:42:35 | | 0 | 2019-04-24 12:03:07 | 2019-04-24 12:10:28 | | 6 | 2019-05-01 08:05:48 | 2019-05-01 08:20:28 | | 6 | 2019-05-08 18:04:04 | 2019-05-08 18:14:57 | | 0 | 2019-05-09 08:18:15 | 2019-05-09 08:29:38 | | 39 | 2019-06-18 12:49:01 | 2019-06-18 13:10:15 | | 78 | 2019-09-05 17:17:33 | 2019-09-13 15:24:28 | | 14 | 2019-09-28 07:05:03 | 2019-09-28 08:12:26 | | 0 | 2019-09-28 12:55:56 | 2019-09-28 13:21:15 | | 0 | 2019-09-28 16:47:52 | 2019-10-01 16:28:18 | | 1 | 2019-10-03 13:11:44 | 2019-12-10 17:56:25 | | 163 | 2020-05-22 12:08:32 | 2020-08-27 17:21:02 | +------+---------------------+---------------------+

Have you considered RTFM?

Yes. Make sure your data is ordered by date Use an @variable to store the record's date compare the date in record n with the date stored from record n-1 store the cumalative difference in a second @variable [edit] PS as it isn't possible to load your data into a test table from an image, that's all the help I'll give.

There's Logo..

NOTE: both instances of $db->query(..) in the above post should be $db->prepare(..)

If the "ellipsis" operator is still a problem for you, that line can be rewritten as fwrite($fp, vsprintf('"%s","%s","%s","%s","%s","%s"'."\n", $v));

If you read my code you would see I store my database connection in the variable $db. You are storing yours in $con. Therefore you need to use $con where I have used $db, such as in $db->prepare() 🙄