Jump to content


Photo

selecting users with null entries

mysql

  • Please log in to reply
2 replies to this topic

#1 glendango

glendango
  • Members
  • PipPipPip
  • Advanced Member
  • 57 posts
  • LocationEngland

Posted 01 November 2017 - 11:17 AM

i have 2 queries . the first query gives me all the users with the same company_id and displays a '0' against their name if they have no enties. but the second query doesn't display the users with no entries against their name. in this case the query counts dates of entries they have made. 
 
I am guessing its something to do with using 'between' and 'where'. 
 
 
any ideas ? thx
 
$result = mysqli_query($conn,"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']."'    ") ;
 

//  company session from this month,,above code gives all users even if 0,,below desnt??
$result = mysqli_query($conn,"SELECT u.id , u.name , u.surname ,  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) WHERE year(curdate()) = year(date_made) and month(curdate()) = month(date_made)   group by  u.id having u.company_id='".$_SESSION['company_id']."'   ") ;

Edited by glendango, 01 November 2017 - 11:18 AM.


#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 17,974 posts

Posted 01 November 2017 - 12:32 PM

I suggest you refer back your previous topic. I have already told that selection criteria on the LEFT JOINed table (firsts) need to go in the JOIN ON conditions and NOT in the WHERE clause. I also told you to use WHERE, and not HAVING, for the users selection in this situation.

Read, learn and remember.

SELECT u.id 
, u.name 
, u.surname 
, u.company_id
, count(f.date_made) as date_made 
FROM users u 
  LEFT JOIN 
  firsts f 
    ON u.id  = f.usr_id 
    AND year(curdate()) = year(date_made) and month(curdate()) = month(date_made)   
WHERE u.company_id = ?
GROUP BY  u.id "
Use prepared queries.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 glendango

glendango
  • Members
  • PipPipPip
  • Advanced Member
  • 57 posts
  • LocationEngland

Posted 01 November 2017 - 02:16 PM

i changed the where to 'and'  and it worked...   thanks for your advice... i keep getting errors when i try your code with my session company_id....     i know your code is correct but this is what i ended up with to make my code work...

 

your advice still helped me plaster the query together though as the logic is certain things like where, and etc only work together in certain ways . thx again 


Edited by glendango, 01 November 2017 - 02:17 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users