Jump to content

kilnakorr

Members
  • Posts

    22
  • Joined

  • Last visited

Everything posted by kilnakorr

  1. Point taken. One thing though: How will I be able to make a 'startpoint' from a specific user? Let's say I only want results for users under username 'fred'. How will I do that?
  2. I was thinking using an ids instead. Can you explain why ids would be a better choice? Thanks for your answer, I'll give it a try later today
  3. Hi I'm trying to figure out how to do a query for users on multiple levels in an organisation. Example from picture, I need to be able to fetch the 'users' that a 'leader' has in their path. I'm thinking in the database, that each user, will have his/hers leader id or name in a field. +----+----------+------------+ | id | username | leadername | +----+----------+------------+ | 1 | fred | mo | | 2 | mo | | | 3 | brian | mo | | 4 | john | mo | | 5 | peter | fred | | 6 | curly | fred | | 7 | joan | fred | | 8 | Dennis | curly | +----+----------+------------+ How can I get all the usernames for a given user? ('mo' has ALL users in his path. 'fred' has 'peter', 'curly', 'joan' AND 'Dennis' in his path.)
  4. It seems I was overthinking it trying to add this to my existing code. I did get it to work as intended! Thank you so much for your help!
  5. Thanks a lot for your info. I almost got it working (as mentioned, I only have on table, so no joining needed). I'm still not getting the right output, as I have more conditions going that I can't really get my head around (seems I get 'NO DATA' output, if a row has other 'user' or 'enne' value, even though another row with same date exists with correct 'user and 'enne' value. I'll fiddle around a bit, but might end up at the 'hire-someone-to-make-this-work' section
  6. Thanks a lot for your time (and your previous help ;)) The problem in itself, is pretty much what you describe, maybe I'm looking at it wrong, but results doesn't match up in your example Although I only have one table, I need to output a string 'NO DATA' if no rows matches my select. So, I need to check all the dates and if no dates matches `enne` and `user` then output 'NO DATA'. if a date matches `enne` and `user` AND score>2 then count it. if a date matches `enne` and `user` BUT score<=2 then don't count it. A modification of your example with desired output of both 'enne' and 'ennehead'. +----+----------+------------+-------+-------+------------+ | id | enne | date | score | user | department | +----+----------+------------+-------+-------+------------+ | 13 | ennehead | 2019-10-01 | 1 | fred | sales | | 16 | head | 2019-10-01 | 1 | fred | sales | | 17 | ennehead | 2019-10-01 | 3 | curly | accts | | 18 | ennehead | 2019-10-01 | 5 | mo | accts | | 20 | ennehead | 2019-10-02 | 3 | pete | sales | | 21 | ennehead | 2019-10-02 | 5 | mary | sales | | 22 | head | 2019-10-02 | 1 | fred | sales | | 23 | ennehead | 2019-10-02 | 3 | curly | accts | | 24 | ennehead | 2019-10-02 | 5 | mo | accts | | 25 | ennehead | 2019-10-03 | 5 | fred | accts | | 26 | ennehead | 2019-10-03 | 5 | fred | accts | | 27 | ennehead | 2019-10-03 | 5 | mo | accts | +----+----------+------------+-------+-------+------------+ +------------+---------+ | date | head | +------------+---------+ | 2019-10-01 | 0 | | 2019-10-02 | 0 | | 2019-10-03 | NO DATA | +------------+---------+ +------------+----------+ | date | ennehead | +------------+----------+ | 2019-10-01 | 0 | | 2019-10-02 | NO DATA | | 2019-10-03 | 2 | +------------+----------+ It is vital that I get, a result for each distinct date. 0 if no row has a score > 2, and 'NO DATA' if no rows are found matching `enne` and `user` for a date. the distinct dates: [2019-10-01, 2019-10-02, 2019-10-03] the count for each 'enne' - head: [0, 0, NO DATA] the count for each 'enne' - ennehead: [0, NO DATA, 2] This would be the result for a user name 'hercules' +------------+----------+ | date | head | +------------+----------+ | 2019-10-01 | 0 | | 2019-10-02 | 0 | | 2019-10-03 | 0 | +------------+----------+ Which would be wrong as user 'hercules' doesn't exists. it should output: +------------+----------+ | date | head | +------------+----------+ | 2019-10-01 | NO DATA | | 2019-10-02 | NO DATA | | 2019-10-03 | NO DATA | +------------+----------+ Hope it clears up everything
  7. Let me show you my code to better explain. SUM(CASE WHEN `enne`='ennehead' AND score>2 AND user='$username' THEN 1 ELSE 0 END) AS head FROM filestats WHERE score>2 AND department='$userdepartement' GROUP BY Date This will output eg: 1,2,5,0,0,2,0,4 ...... My problem is if no results are given for that day (NULL), I get output of '0', so I don't know if I hate multiple rows where score was less or equal to '2' or I had no rows at all at a specific Date. Does this make more sense?
  8. Hi I have previously been using the below code for getting a zero value when no results where found: SUM(CASE WHEN `foo`='bar' then 1 else 0 end) as something..... Currently, I need an output other than 0, as the result could be 0, and I have no way of telling an actually 0 value from a null. How can I output my chosen string, so a 0 value is outputted as 0 and a null value is outputted as 'no value' (as string)? Hope it makes sense
  9. thanks. It works as intended, but how can I distinct between the output? I need to create a variable for each of the outputs, so I have to be able to output the count for just 'cat' ,'dog' and so on. using a while loop will just output '10 12 15'
  10. I can't seem to figure out how to count the rows for more than one thing in a single query (I know it must be possible :)) if I have a column called 'animal' how can I count the rows for value 'cat', 'dog', 'horse'? eg. number of rows for cat =10 number of rows for dog =12 number of rows for horse =25
  11. Hi Already looked around, and although I've found some solution I simply don't understand them (not skilled at all). I'm getting 10 random results using this, which works fine and list 10 random results. $sql = "SELECT name FROM db ORDER BY RAND() LIMIT 10;"; $result = mysqli_query($con,$sql); while ($row=mysqli_fetch_array($result,MYSQLI_ASSOC)) { echo $row['name']."<br>"; } How can I make 10 new variables for each result?
×
×
  • 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.