0

actually i have 2 table the log table and the user table.

my current statement is working fine to get all logs in the inverse order

this is my current query

SELECT *
FROM ( SELECT * FROM logs WHERE roomid = '$room' AND post_id > '$last' ORDER BY post_id DESC LIMIT 100) AS log 
ORDER BY post_id ASC

here are the 2 table structure

logs table

date | user_logs_id | message | type

user table

user_id | avatar | color | ....

the goal for me is to keep the same order as i actually have with the query above but getting information avatar and color from user table matching to the user_logs_id

then now my problem is that i have field user_logs_id in logs table and i want to retreive some information matching that user_logs_id from the user table

anyone can help me to solve that thanks

Mireille28
  • 337
  • 5
  • 13

1 Answers1

0

Assuming that user_logs_id = user_id simply use a join 

SELECT logs.*, user.color, user.avatar FROM logs 
JOIN user on user.user_id = logs.user_logs_id
WHERE logs.roomid = '$room' AND post_id > '$last' ORDER BY post_id DESC LIMIT 100

**edit updated query to show specific fields

sharper
  • 38
  • 5
  • ok this seem to work but the only thing is it will require all the user information is it doable to only require avatar and color from user ? – Mireille28 May 18 '16 at 01:15
  • If you mean you only what the color and avatar fields? then in your select use `user.color as 'color', user.avatar as 'avatar'` – sharper May 18 '16 at 01:23
  • hi, that wont work since color is not = to something specific it can be different from every user what i need is to get only avatar and color values from users table matching to the user_logs_id – Mireille28 May 18 '16 at 01:24
  • or i might not understanding clearly where to add those user.color as 'color' for the query it work good like that but it will be more optimised selecting only desired field from user table – Mireille28 May 18 '16 at 01:27
  • Hi I've updated the query in the original answer does this help? – sharper May 18 '16 at 01:29
  • `SELECT logs.*, user.avatar, user.avatar, user.user_name, users.user_id FROM logs, user WHERE user.user_id = logs.post_user AND logs.post_roomid = '$room' AND logs.post_id > '$last' ORDER BY logs.post_id ASC LIMIT 100` – Mireille28 May 18 '16 at 01:36
  • both my version and your version works... i ask myself do the join is faster to execute than my version without a join ? – Mireille28 May 18 '16 at 01:42
  • If it solves your problem then its fine. My personal preference is to use the join syntax as I find it easier to read also I often need to use different types of join (LEFT, RIGHT, OUTER, etc) – sharper May 18 '16 at 01:50
  • You are still creating a join with your syntax, its just not explicitly stated – sharper May 18 '16 at 01:52