向数据库提交NULL - 单引号问题？

I wish to UPDATE NULL into my database if the post value is blank. If it's not blank, i want it to insert the value posted.

如果post值为空,我希望将NULL更新为我的数据库。如果它不是空白,我希望它插入已发布的值。

if(empty($post['gallery']))$post['gallery'] = NULL;

It's a rather large query so here is the bit i'm interested in:

这是一个相当大的查询,所以这里是我感兴趣的位:

"article.article_gallery_id = '".$post['gallery']."', ".

The above never inserts a NULL but a 0.

上面从不插入NULL但是0。

This does insert a NULL:

这确实插入了一个NULL:

"article.article_gallery_id = NULL, ".

I believe the problem lays with the single quotes around the $post['gallery']. If I remove them, the query does not work at all.

我相信这个问题与$ post ['gallery']周围的单引号有关。如果我删除它们,查询根本不起作用。

How can I fix this?

我怎样才能解决这个问题?

I don;t want to use PDO and don't worry about security - I've taken bits out for the example.

我不想使用PDO而不担心安全问题 - 我已经拿出了一些例子。

5 个解决方案

#1

PHP null will not convert to SQL's NULL when putting it into a string.
If the value if empty, the query needs to look like article.article_gallery_id = NULL.
If it's not empty, the query needs to look like article.article_gallery_id = 'value'.
Compare the use of quotes and no quotes and the NULL keyword. You'll need to explicitly format the query this way, you can't rely on a PHP null value.

将null放入字符串时,PHP null不会转换为SQL的NULL。如果值为空,则查询需要看起来像article.article_gallery_id = NULL。如果它不为空,则查询需要看起来像article.article_gallery_id ='value'。比较引号和无引号的使用以及NULL关键字。您需要以这种方式显式格式化查询,您不能依赖PHP null值。

#2

You don't need to put quotes in the SQL around null:

您不需要在SQL周围使用引号:

if(empty($post['gallery'])){ $post['gallery'] = "NULL"; } 
$sql = "UPDATE article SET article.article_gallery_id = " . $post['gallery'] . " WHERE somefield = 'someValue'";

You should avoid using MySQL functionality as its depreciated. Try MySQLi or PDO

您应该避免使用MySQL功能作为其折旧。试试MySQLi或PDO

#3

Yes, remove quotes, because you are dealing with integers, not strings.

是的,删除引号,因为你处理的是整数,而不是字符串。

Why does it fail then? Try this:

那为什么失败呢?试试这个:

echo "column = " . null;

It echoes "column = ", because null is what it says: nothing. You need to account for the special case when $post['gallery'] is null.

它回应“column =”,因为null就是它所说的:什么都没有。当$ post ['gallery']为空时,您需要考虑特殊情况。

 $sqlQuery .=
     "article.article_gallery_id = "
     . (is_null($post['gallery']) ? 'NULL' : $post['gallery'])
     . " [rest of your query here] ";

#4

To insert null into a database you have to remove the quotes around it. I noticed you said you tried it and it didn't work. I imagine what happened then is that you had a different error occur. You said the query is long so maybe the error happened somewhere else? Here's a simple way to solve it:

要将null插入数据库,您必须删除它周围的引号。我注意到你说你试过了它并没有用。我想当时发生的事情就是你发生了不同的错误。你说查询很长,所以错误可能发生在其他地方?这是解决它的简单方法:

$post['gallery'] = (!empty($post['gallery'])) "'$post[gallery]'" : 'NULL';

"article.article_gallery_id = $post[gallery], ".

If you notice I removed the single quotes from the query and I add them to the post var if it's not empty.

如果您注意到我从查询中删除了单引号,并且如果它不为空,我将它们添加到post var。

I hope that helps!

我希望有所帮助!

#5

-1

You should set your table default to null, but to actually fix this you'll have to switch your quotes around, pHP evaluates any var between " and not so now it set an empty value and not literally null.

你应该把你的表默认设置为null,但要实际修复它你必须切换你的引号,pHP评估任何var之间的“而不是现在它设置一个空值而不是字面上的空值。

THe best solution is this though

但最好的解决方案就是这样

if(empty($post['gallery']))$post['gallery'] = "NULL";

then it actually puts in the NULL value mysql needs

然后它实际上放入了mysql需要的NULL值

#1

#2