display user even if select query returns nothing

[glendango]

$result = mysqli_query($conn, "SELECT users.name,users.surname,  firsts.usr_id, count(usr_id) ,count(date_made) as date_made FROM `firsts`  left join users on users.id=firsts.usr_id         

    WHERE DATE(`date_made`) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() group by usr_id  ");

 

 

Hi, as you can see i join 2 tables which gives me a small table with:   name |   surname  |   firsts for last 7 days  |    

 

i search results by counting the entries from date_made.

 

If the user has made no 'firsts' in the last 7 days, they don't show up in the table..which is kind of cool but i need the staff who have no firsts to show up in the table with 0 firsts next to them..

 

sorry for the below text,,it let me paste the table in and on submission printed this :

 

i get this result

 

Name         id Last 7 days

billy bragg 1 10

James brown 5 1

bless you 6 1

Total 12

 

 

i want

 

Name                 id         last 7 days

billy bragg          1          10

lisa jones           3           0

James brown     5           1

bless you           6           1

redd eyes          7             0

dunelm bob     11            0

Total   12

 

my statements are prob rank but good to know if there is a solution. thx

Edited October 18, 2017 by glendango

[Barand]

You need

 

... FROM users LEFT JOIN firsts ...

That will give all users with matching firsts data where it exists. You will also need to put the date conditions in the JOIN ON condition.

[glendango]

thanks fo reply , this gave me the same result as my version:

 

"SELECT users.name,users.surname,  usr_id, count(date_made) as date_made FROM users LEFT JOIN firsts on users.id=firsts.usr_id

 

    WHERE DATE(`date_made`) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() group by usr_id  ");

[Barand]

... You will also need to put the date conditions in the JOIN ON condition.

If you leave it in the where clause it behave lie an inner join (as you found)

Edited October 19, 2017 by Barand

[glendango]

sorry you ve lost me,... what would that look like? ..  grateful for your time and will donate when iam making money  

[Barand]

SELECT 

     u.name

   , u.surname

   , u.id

   , count(f.date_made) as date_made 

FROM 

    users u

      LEFT JOIN 

    firsts f 

        ON u.id = f.usr_id

        AND DATE(`f.date_made`) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() 

GROUP BY u.id ;

[glendango]

really sorry but iam getting an error. it gives error direct into sql as well. 

 

i can see that your requesting the id from the users table so this is a clue to how this then gets displayed.. sorry to waste your time but is the firsts.f and users.u supposed to be there as i cant see what it references.

 

 $result = mysqli_query($conn,"SELECT
     users.name , users.surname , users.id , count(firsts.date_made) as date_made
FROM
    users.u
      LEFT JOIN
    firsts.f
        ON users.id = firsts.usr_id
        AND DATE(`firsts.date_made`) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()
GROUP BY users.id");

 

        while($firsts=mysqli_fetch_assoc($result)){

Edited October 19, 2017 by glendango

[Barand]

Why ask me what it looks like then rewrite it your own way introducing errors?

 

The "u" and the "f" are table aliases and there should not be a period between the name and the alias. You may use an optional "AS" keyword (eg FROM users AS u).

 

If you do define an alias (as I have) then you need to use the alias and not the tablename when prefixing column names (ie f.date_made and not firsts.date_made).

[glendango]

found the problem!

 

  AND DATE(`f.date_made`) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()

 

​should be 

 

  AND DATE(f.date_made) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()

 

​thank you again...

  

Edited October 19, 2017 by glendango

[glendango]

Sorry to ask again.   If i wanted to also use this select statement to only show the users with same branch_id , where would this go? i've tried a few things but keep getting errors. is it to do with sessions or just adding = to branch_id somewhere in the select statement?  

 

 

 "SELECT u.name , u.surname , u.id , u.company_id, count(f.date_made) as date_made FROM users u LEFT JOIN firsts f ON u.id  = f.usr_id AND DATE(f.date_made) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() GROUP BY u.id  "

Edited October 23, 2017 by glendango

[Barand]

Is he branch_id in the users table or the firsts table?

[glendango]

in the users table and the id is related to table `company`

[Barand]

Because it's in the users table you can add

 

... WHERE branch_id = ?

[glendango]

how would i select the branch id dynamically?   i.e. where u.company_id=4​    i need it to group the users with the same branch id. without me actually putting 4 in..  

 

SELECT u.name , u.surname , u.id , u.company_id, count(f.date_made) as date_made FROM users u LEFT JOIN firsts f ON u.id  = f.usr_id where u.company_id=4 AND DATE(f.date_made) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()  group by  u.id  ") ;

Edited October 25, 2017 by glendango

[Barand]

Why have you put the date condition back in the WHERE clause. I have already told you that was wrong. We're going to be here forever at this rate.

 

SELECT 
     u.name
   , u.surname
   , u.id
   , count(f.date_made) as date_made 
FROM 
    users u
      LEFT JOIN 
    firsts f 
        ON u.id = f.usr_id
        AND DATE(f.date_made) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() 
WHERE u.company_id = ?
GROUP BY u.id ;

Use a prepared statement and bind the required id as a parameter before executing the query

[glendango]

"SELECT u.name , u.surname , u.id , u.company_id, count(f.date_made) as date_made FROM users u LEFT JOIN firsts f ON u.id  = f.usr_id  AND DATE(f.date_made) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()   group by  u.id having u.company_id='".$_SESSION['company_id']."'    ") ;

 

this seems to have worked..will send a donation as think your bollockings are very useful :  what do you  think?  i set up a session as company_id  and put it in the select...    is this how business apps are able to link all users by their branches etc  or is there a better link you know of to teach to set up users by branch / company  /  all in a league..a bit like strava but for sales leads. 

Edited October 25, 2017 by glendango

[Barand]

A couple of points.

 

1) You should use prepared queries and not put data directly into a query string. Use a "?" placeholder for the parameter value.

 

2) Why have you changed to HAVING instead of the WHERE clause I suggested? A WHERE will filter the inputs, a HAVING will filter the outputs so I expect the WHERE should be more efficient, especially if you have index set up on the company id column (although I would have to benchmark test to be sure). The main use of a HAVING clause is when you want a condition on an aggregated column (eg ... HAVING COUNT(date_made) > 50 )

 

$query = "SELECT 
           ... 
          WHERE company_id = ? 
          GROUP BY u,id";
$stmt = $conn->prepare($query);
$stmt->bind_param('i', $_SESSION['company_id'];
$stmt-execute();