CODEWITHSUNDEEP
phpmysqlsql
user1182607
user1182607

Reputation: 67

Check many times two columns SQL

i have a table that contains

a user have a list of the ids that he downloaded already and their update number. i want to give the user only the necessary ids to download each time (which are new ids - that he didn't yet download or ids that got update). I want to make a query that bring me this. lets say for the example that the user has downloaded 3 ids.

so now me in the server want to update an id so the user will download it again so i put the update number of the first id to 1 (and if it was 1 I put it to 2 or 3 and more and more).

I need the query that the user will send to the server in order to only get the necessary ids.

I tried this query:

SELECT * FROM idtable WHERE (id <> 1 AND update <> 0) AND (id <> 2 AND update <> 0) AND (id <> 3 AND update <> 0)

but this is not valid. (If you are looking on the example it should return only id number 1 because I updated it from the server).

Please help me!!

Upvotes: 0

Views: 101

Answers (3)

Puggan Se
Puggan Se

Reputation: 5846

If you want to filter out packages that alredy have is same version as the database, then you could use:

SELECT * 
FROM idtable 
WHERE
    CASE id
        WHEN 1 THEN IF(update > 0, TRUE, FALSE)
        WHEN 2 THEN IF(update > 0, TRUE, FALSE)
        WHEN 3 THEN IF(update > 0, TRUE, FALSE)
        ELSE TRUE
    END

Upvotes: 2

gasior
gasior

Reputation: 1

Assuming that you're incrementing update numbers, try something like this:

SELECT * FROM idtable
WHERE (
    (id = 1 AND update > 0) OR
    (id = 2 AND update > 0) OR
    (id = 3 AND update > 0)
)

If your users have more than a couple IDs downloaded, you can consider creating another table with:

  • userID
  • ID
  • Update number

This way you could get all updates without adding multiple OR statements:

SELECT it.* FROM idtable it, userdownload ud WHERE ud.userID = it.id and ud.id = it.id and ud.update < it.update

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

The query to get the IDs to download is simply

SELECT *
  FROM idtable
 WHERE update > 0

The query assumes that you simply toggle update column to 1 when there's something new to update.

Now, if your question is about how to update the table such that it will turn the next non-update row to 1, then it's a different kettle of fish.

If you're updating the "update" (I'm sure it's a proper name) column to a version number, then your table structure should be something like:

user_id int
version int
downloaded_version int

So that when the user downloads a version, you update downloaded_version to match the version they just updated. Next time around, you update version to version+1. And the query to look for updates becomes:

SELECT *
  FROM idtable
 WHERE user_id = <userid>
   AND version > downloaded_version

Upvotes: 0

Related Questions