Jump to content

kilnakorr

Members
  • Content Count

    22
  • Joined

  • Last visited

Community Reputation

0 Neutral

About kilnakorr

  • Rank
    Member

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  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?
  12. Thanks a bunch. I did get it to work doing an if statement in the loop, but this is much better!
  13. Thanks for the reply, and very true the "Error" makes no sense at all. I can't get your code to work, maybe because it needs to be split up as the while loop, sets an array. The main problem is, that I have the following code: var myData=[<?php $data=mysqli_query($mysqli,"SELECT Date, COUNT(*) as total FROM db WHERE Step='CPR' AND Error='1' AND Date BETWEEN '$Fromdate' AND '$Todate' GROUP BY Date"); while($info=mysqli_fetch_array($data)) echo '"'.$info['Date'].'",'; /* We use the concatenation operator '.' to add comma delimiters after each data value. */ ?>]; The result of the above code is: var myData=["2019-01-02","2019-01-03","2019-01-15","2019-01-18","2019-01-21","2019-01-22","2019-01-24","2019-01-28","2019-01-31","2019-02-01","2019-02-02","2019-02-06","2019-02-07","2019-02-08","2019-02-15","2019-02-17","2019-02-20","2019-02-25","2019-02-28","2019-03-05","2019-03-06","2019-03-15","2019-03-19","2019-03-30",]; Basically, getting every date where the 'Step' is CPR and 'Error' is 1. Now I also need to get the same values but only for a specific 'Department': $data=mysqli_query($mysqli,"SELECT COUNT(*) as total FROM jss_test WHERE Step='CPR' AND Error='1' AND Department='Kundeservice' AND Date BETWEEN '$Fromdate' AND '$Todate' GROUP BY Date"); ?> var totalKS=[<?php while($info=mysqli_fetch_array($data)){ echo $info['total'].',';} /* The concatenation operator '.' is used here to create string values from our database names. */ ?>]; This returns: var totalKS=[1,2,2,1,1,1,1,2,]; All good, but since I don't have same number of results, they don't really match up as intended. What I need really is be able to have the same amount of results, but if 'Department' isn't 'Kundeservice' then return a "0" Hope it makes sense, and someone got an idea.
  14. Thanks for the reply. Yes, I was thinking the same thing. Testing if something is returned and if not set my variable to '0'. I just can't seem to figure out how?!
  15. Hi I'm using the below code <?php //Getting failed orders for Online $data=mysqli_query($mysqli,"SELECT Error, COUNT(*) as total FROM db WHERE Department='Online' AND Date BETWEEN '$Fromdate' AND '$Todate' GROUP BY Date"); ?> var totalOnline=[<?php while($info=mysqli_fetch_array($data)){ echo $info['total'].',';} /* ?>]; Now some of these doesn't return anything as it should, but is there a way to return 0 instead?
×
×
  • 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.