0

Instead of showing a list to the user of all the available properties that there are, I'm trying to show them all the properties that they have not already saved to their account. I record in a table on the database which properties the user has saved, I record this by the id of the property and the user's id.

So i would like to write a query like

$query = "SELECT * FROM propertys WHERE id != 1 or 3";

This would then select the property with an id of 2, and display it to the user, instead of a list of 1 and 2 and 3 where they have already seen and saved 1 and 3.

The Problem

This query written above does not seem to work. It still displays all the properties, which im not sure as why.

EDIT:

The Actual Problem

Now it wont display the properties that are left over. The query above doesnt display property 1 or 3, which is intented, but now also doesnt display property 2.

Axel
  • 3,331
  • 11
  • 35
  • 58
Nick
  • 71
  • 9
  • 1
    You need to include the column with each comparison. `$query = "SELECT * FROM propertys WHERE id != 1 and id != 3` or use `not in`. `$query = "SELECT * FROM propertys WHERE id no in (1,3)"` – chris85 Apr 20 '17 at 21:59
  • I'm not sure you have written the right syntax sir, please take a look at this: http://stackoverflow.com/questions/6156979/sql-where-condition-not-equal-to – Oussama Ben Ghorbel Apr 20 '17 at 21:59
  • My second query has a typo, `$query = "SELECT * FROM propertys WHERE id not in (1,3)"`. – chris85 Apr 20 '17 at 22:06

3 Answers3

0

That's not how SQL works. You're trying to use PHP "not equal" inside a SQL statement. You'll have to use the correct SQL for the database you are using. It should look a lot more like this: 

$query = "SELECT * FROM propertys WHERE id <> 1 OR id <> 3"

or this:

$query = "SELECT * FROM propertys WHERE id NOT LIKE 1 OR id NOT LIKE 3"
chris85
  • 23,846
  • 7
  • 34
  • 51
  • 3
    `id <> 1 OR id <> 3` is the wrong logic, the `not 1` will bring back 3 and the `not 3` will bring back `1`. You need `and`. The `!=` also would work, http://sqlfiddle.com/#!9/104245/1 (although I'm not sure how compatible that is, I tend to use the `<>`). – chris85 Apr 20 '17 at 22:02
  • 1
    @chris85 Was just about the write the same thing. – webnoob Apr 20 '17 at 22:03
  • Thank you for the help, I actually found I could use, NOT IN (1, 2) and it worked! Thank you for the help though. – Nick Apr 20 '17 at 22:15
  • `!=` is a valid MySQL 'not equal' sign. https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_not-equal – Ezenhis Oct 18 '17 at 09:31
0

If I had a row source for properties that are available values 1, 2 and 3 as stated in the question), e.g. 

 property
 id  name
 --  -------
  1  foo
  2  fi
  3  fo
  4  fum

And I had a second table that contained rows for the properties that are "assigned" to each user, ... each row represents a property assigned to a user, and a "missing" row represents a property that isn't assigned, e.g.

 user_assigned_property
 user_name  property_id 
 -------    -----------
 jack       1
 jack       3
 giant      3
 giant      4

I would probably use an anti-join pattern: return all rows from property, with an outer join to the assigned properties, and a condition in the where clause to exclude all rows where there was a match

We expect this:

SELECT p.id
     , p.name
  FROM property p 
  LEFT
  JOIN user_assigned_property a
    ON a.property_id = p.id
   AND a.user_name = 'jack'
 WHERE a.property_id IS NULL
 ORDER
    BY p.id

to return:

id  name
--  ----
 2  fi
 4  fum
spencer7593
  • 106,611
  • 15
  • 112
  • 140
0

I found my answer at least. From Here There problem was trying to have the write syntax for multiple exclusions. Thanks everyone for the help.

Community
  • 1
  • 1
Nick
  • 71
  • 9