Jump to content
glendango

display user even if select query returns nothing

Recommended Posts

$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 by glendango

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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  ");

Share this post


Link to post
Share on other sites

... 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 by Barand

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 ;
  • Like 2

Share this post


Link to post
Share on other sites

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 by glendango

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by glendango

Share this post


Link to post
Share on other sites

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 by glendango

Share this post


Link to post
Share on other sites

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 by glendango

Share this post


Link to post
Share on other sites

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
  • Like 1

Share this post


Link to post
Share on other sites

"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 by glendango

Share this post


Link to post
Share on other sites

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();

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • 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.