选择不等于Mysql Php的字段。

I'm just wondering what kind of mysql command i could execute in php that would select all items from a certain table where columna is not equal to x and columnb is not equal to x

我想知道，在php中，我可以用哪种mysql命令来选择从某个表中选择所有的项，在这个表格中，columna不等于x，而columnb不等于x。

Something like: select something from table where columna does not equal x and columnb does not equal x

类似于:从表中选择不等于x和columnb不等于x的东西。

Any ideas?

什么好主意吗?

6 个解决方案

#1

The key is the sql query, which you will set up as a string:

关键是sql查询，您可以将其设置为字符串:

$sqlquery = "SELECT field1, field2 FROM table WHERE NOT columnA = 'x' AND NOT columbB = 'y'";

Note that there are a lot of ways to specify NOT. Another one that works just as well is:

请注意，有很多方法可以指定NOT。另一个同样有效的方法是:

$sqlquery = "SELECT field1, field2 FROM table WHERE columnA != 'x' AND columbB != 'y'";

Here is a full example of how to use it:

下面是一个如何使用它的完整示例:

$link = mysql_connect($dbHost,$dbUser,$dbPass) or die("Unable to connect to database");
mysql_select_db("$dbName") or die("Unable to select database $dbName");
$sqlquery = "SELECT field1, field2 FROM table WHERE NOT columnA = 'x' AND NOT columbB = 'y'";
$result=mysql_query($sqlquery);

while ($row = mysql_fetch_assoc($result) {
//do stuff
}

You can do whatever you would like within the above while loop. Access each field of the table as an element of the $row array which means that $row['field1'] will give you the value for field1 on the current row, and $row['field2'] will give you the value for field2.

您可以在上面的while循环中做任何您想做的事情。将表的每个字段作为$row数组的元素访问，这意味着$row['field1']将为您提供当前行的field1的值，$row['field2']将为您提供field2的值。

Note that if the column(s) could have NULL values, those will not be found using either of the above syntaxes. You will need to add clauses to include NULL values:

注意，如果列(s)可以有NULL值，那么就不会使用上面的任何语法。您需要添加包含空值的子句:

$sqlquery = "SELECT field1, field2 FROM table WHERE (NOT columnA = 'x' OR columnA IS NULL) AND (NOT columbB = 'y' OR columnB IS NULL)";

#2

You can use like

您可以使用像

NOT columnA = 'x'

或

columnA != 'x'

或

columnA <> 'x'

And like Jeffly Bake's query, for including null values, you don't have to write like

就像Jeffly Bake的查询一样，包括空值，你不必像这样写。

(NOT columnA = 'x' OR columnA IS NULL)

You can make it simple by

你可以让它变得简单。

Not columnA <=> 'x'

<=> is the Null Safe equal to Operator, which includes results from even null values.

<=>是Null安全等于操作符，它包含了甚至是空值的结果。

#3

select * from table where fiels1 NOT LIKE 'x' AND field2 NOT LIKE 'y'

//this work in case insensitive manner

//这种工作不区分大小写。

#4

$sqlquery = "SELECT field1, field2 FROM table WHERE columnA <> 'x' AND columbB <> 'y'";

I'd suggest using the diamond operator (<>) in favor of != as the first one is valid SQL and the second one is a MySQL addition.

我建议使用diamond操作符(<>)来支持!=第一个是有效的SQL，第二个是MySQL添加。

#5

Or can also insert the statement inside bracket.

也可以将语句插入括号内。

SELECT * FROM tablename WHERE NOT (columnA = 'x')

#6

-1

You can use also

你也可以使用

select * from tablename where column1 ='a' and column2!='b';

#1