[Getting results in multiple levels]

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?

[Getting results in multiple levels]

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

[Getting results in multiple levels]

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.)

 

[Outputting chosen string instead of zero?]

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!

[Outputting chosen string instead of zero?]

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

 

[Outputting chosen string instead of zero?]

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

[Outputting chosen string instead of zero?]

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?

[Outputting chosen string instead of zero?]

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

[Counting rows in single query?]

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'

[Counting rows in single query?]

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

[generate variables in while loop?]

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?