Jump to content

Barand

Moderators
  • Content Count

    21,154
  • Joined

  • Last visited

  • Days Won

    520

Everything posted by Barand

  1. Does the height of your player depend on which roster they are in? If a player is the same height no matter what roster s/he is placed in then the height columns should be in the player table, not the roster. In answer to your question, it is often useful to load csv data into an intermediate table then move it to the correct table using PHP, where you can use functions like explode(). Why have 2 columns (feet/inches) and not just convert to inches?
  2. If you had error reporting tuned on, you would see a message like this If the PHP processor comes across an unquoted string it assumes it is a defined constant. It then tries to find the value that was defined for that constant. If it cannot find one it then assumes it must be a string value 'green'. All this is a waste of time making your code less efficient, so always quote string literals.
  3. 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).
  4. 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.
  5. You will if you try it. Experiment. Planes won't fall out of the sky if get it wrong.
  6. 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 t
  7. 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
  8. 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?
  9. That, according to Excel, would give a total absence of 503 days. Is that your expected result?
  10. Use a form. https://www.php.net/manual/en/language.variables.external.php
  11. Then I suggest you post on the "job Offerings" board in these forums.
  12. 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.
  13. 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?
  14. 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-0
  15. 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 va
  16. The most common causes of that error are an opening { or " without a corresponding closing } or "
  17. 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 | +------------+
  18. 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.
  19. 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', '20
  20. Have you considered RTFM?
  21. 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.
  22. NOTE: both instances of $db->query(..) in the above post should be $db->prepare(..)
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.