MySQL: return values even if WHERE is null

Question

I have a table like so:

| article_id | parent_article_id | title         |
|------------|-------------------|---------------|
|         90 |              NULL | First article |
|         91 |                90 | part 2        |
|         92 |                90 | part 3        |
|         93 |                90 | part 4        |
|         94 |              NULL | 2f3           |
|         95 |              NULL | reer          |

In a PHP script, I have a select drop down box and I want to populate the select control with articles where the parent_article_id is null (I want all the parents) excluding the article_id im processing.

So, if I'm processing article_id 90, I want to return all the available parents, that is article_id: 94, 95 (im excluding the article_id im processing: 90)...

If I'm processing article_id 91, I want to return: 90, 94, 95...

If I'm processing article_id 95, I want to return: 90, 94...

I was able to create the query that works ok:

SELECT
    a.article_id,
    a.parent_article_id,
    a.title
FROM
    article a
WHERE
    a.article_id != ? AND a.parent_article_id IS NULL
ORDER BY
    a.article_id

....

$stmt = $conn->prepare($q);
$stmt->bind_param('i', $articleid);
$stmt->execute();

The problem is when I'm creating a new article and therefore the article_id I'm processing is null (doesn't not exist yet)... The query returns nothing, which is logical. I want to return all parent_article_id that are null even if the article_id doesn't not exist yet?

Is this feasible with one query?

you should check on mikehillyer.com/articles/managing-hierarchical-data-in-mysql for more information, as you get more query it is vital to understand the concept — nbk
– nbk, Commented Apr 22, 2020 at 20:27
Since null is not going to be the ultimate value of article_id when the new article is ultimately created, why not use a value of -1 rather than null as the temporizing value? I assume -1 will never be a valid existing article_id. — Booboo
– Booboo, Commented Apr 22, 2020 at 20:44

Barmar · Accepted Answer · 2020-04-22 20:26:20Z

1

Use the null-safe equality operator <=>:

SELECT
    a.article_id,
    a.parent_article_id,
    a.title
FROM
    article a
WHERE
    NOT a.article_id <=> ? AND a.parent_article_id IS NULL
ORDER BY
    a.article_id

and set $articleid to null.

answered Apr 22, 2020 at 20:26

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL: return values even if WHERE is null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related