0

SELECT * FROM table_name WHERE column_name LIKE '%$search%' AND WHERE NOT column_name='$id';

I want to select all the results from my database that are like my search term, but exclude the row that is equal to my php variable $id.

Is it possible to do this in a MySQL query?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Hurly
  • 13
  • 3
  • A literal string `'string'` does not translate variables to their value, so this would not work unless you use `"double quoted strings"`. But don't do that either, use prepared statements by either using `PDO` or `MySQLi`. – Xorifelse Nov 24 '18 at 11:50
  • my sql variable is using mysqli $sql = "SELECT * FROM table_name WHERE column_name LIKE '%$search%' AND WHERE NOT column_name='$id'"; – Hurly Nov 24 '18 at 11:54
  • Exactly, you're not using prepared statements. Your database is wide open to an attack if you don't handle your data correctly. – Xorifelse Nov 24 '18 at 20:36

1 Answers1

2

Put the all the conditions inside a single WHERE block and use conditional operators like AND/OR/NOT to combine them

SELECT * FROM table 
WHERE `row` LIKE '%$search%' AND 
      `row` <> '$id';

Also, please learn to use Prepared Statements

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • ```$sql = "SELECT * FROM userprofile WHERE userUid LIKE '%$search%' OR profileFirstName LIKE '%$search%' OR profileLastName LIKE '%$search%' OR profileStatus LIKE '%$search%' OR profileGender LIKE '%$search%' NOT userUid <> '$id'";``` This is still not working – Hurly Nov 24 '18 at 12:02
  • remove `not` before `userUid`. use `and useruid <> '$id'` – Madhur Bhaiya Nov 24 '18 at 12:03
  • The code has no errors then, but it is still not excluding the rows in the `userUid` column that are equal to php variable $id. – Hurly Nov 24 '18 at 12:06
  • 1
    @Hurly please use proper parentheses around conditions. put all the `or` conditions inside one pair of brackets and `and useruid <>` outside that – Madhur Bhaiya Nov 24 '18 at 12:08
  • Also @Madhur Bhaiya i am using Prepared Statements. ;) I only show the code for my mysql query. Because that is what is not working. – Hurly Nov 24 '18 at 12:11
  • @Hurly does putting parentheses properly work now ? You can read about operator precedence to understand the importance of parentheses (brackets). – Madhur Bhaiya Nov 24 '18 at 12:11
  • this code gives no error, but is still not filtering out the data equal to php variable $id. ```$sql = "SELECT * FROM userprofile WHERE userUid LIKE '%$search%' OR (profileFirstName LIKE '%$search%') OR (profileLastName LIKE '%$search%') OR (profileStatus LIKE '%$search%') OR (profileGender LIKE '%$search%') AND userUid <> '$id'";``` – Hurly Nov 24 '18 at 12:18
  • 1
    @Hurly do this: `$sql = "SELECT * FROM userprofile WHERE (userUid LIKE '%$search%' OR profileFirstName LIKE '%$search%' OR profileLastName LIKE '%$search%' OR profileStatus LIKE '%$search%' OR profileGender LIKE '%$search%') AND userUid <> '$id'"` – Madhur Bhaiya Nov 24 '18 at 12:20
  • thankyou. That code is working without error and is the solution to my question. – Hurly Nov 24 '18 at 12:24