-2

I have table user where id = primary key, lastchange = int with lastchange data (in seconds). And i have frontend page with js script. Also all users placed in div like table by pages. So, items has user id and used changed timevalue in attributes. I want to request all changed user ids in 1 request (1 http and 1 sql). They can be changed by other user on site. How can i do this? I dont want check every user in page by timer, there is too many requests.

In my mind it looks like:

  1. js do get request with list of users in page in json format [{"id":1, "lastchange":123123},{"id":2, "lastchange":123123}...
  2. Php does request in mysql like SELECT * FROM `users` WHERE `id` IN (1, 2, 3) AND `lastchange` NOT IN (123459, 123456, 123459); (this not works fine, there is no queue for lastchange, it checks all inside braces and result are wrong)
  3. Php return only ids of different rows [1, 15, 22] etc. to js.
  4. Js check every id separately in other request getting full info about user by id

I can check every user in php separately, but i want to know how can i do it with 1 SQL request

Sorry my bad English.

FelixSFD
  • 6,052
  • 10
  • 43
  • 117
neomasterr
  • 1
  • 1
  • Maybe i can store every user change in other table (id, user, changetime) and check like ``select `user`, `changetime` from (where `user` in (select `id` from `changes` where `id` = $var))`` – neomasterr Sep 09 '17 at 09:03
  • Once asked https://stackoverflow.com/questions/689912/sql-retrieve-only-the-records-whose-value-has-changed – eli Sep 09 '17 at 09:04
  • Possible duplicate of [SQL: retrieve only the records whose value has changed](https://stackoverflow.com/questions/689912/sql-retrieve-only-the-records-whose-value-has-changed) – xiawi Sep 09 '17 at 09:08
  • Or maybe, i can only send user ids and retrieve lastchanges from mysql, after check differents by JS and update user data – neomasterr Sep 09 '17 at 09:12

2 Answers2

0

I think you might want to implement the solution differently as per the first comment.... but, if you want keep your current model the SQL you need would look something like:

SELECT * FROM users WHERE
(id = 1 AND lastchange <> 123123) OR
(id = 2 AND lastchange <> 123123) OR
...

This will keep the id's and the lastchange values that relate to each other being compared properly. It is pretty ugly SQL but not hard to generate in a loop.

Paul Coldrey
  • 1,389
  • 11
  • 20
0

Sorry my stupidness. I think i solve this.

  1. Js request api with only user id in page
  2. php response json like id, lastchange
  3. js checking lastchange value and id in page
  4. js request full data about changed user replaces old in page

Thx all for helping

neomasterr
  • 1
  • 1