Jump to content

display user even if select query returns nothing


glendango

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

Link to comment
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  ");
Link to comment
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)){
Link to comment
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).

Link to comment
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  "

Link to comment
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  ") ;

Link to comment
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
Link to comment
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. 

Link to comment
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();
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

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