Unexpected Mysqli WHERE is not null Value Returned

Question

Mysqli query to a table containing rows of data.

When i use this SELECT it returns no rows when a row does exist with the desired request.

I have written this several ways of which i have seen on various references but all return no rows.

I wish to retrieve(SELECT) the latest(DESC)(LIMIT 1) row by(datetime_upload) in full(*) WHERE (licensePath) has something in the cell(IS NOT NULL).

Q. I would like to know what i need to adjust in my select statement to return the desired result?

MySQL Table

The Select:

$query = "SELECT * FROM driver_docs WHERE driver_profileId LIKE '$uid' AND WHERE licensePath IS NOT NULL ORDER BY datetime_upload DESC LIMIT 1";
    $result = mysqli_query($conn, $query);
    $row = mysqli_fetch_array($result, MYSQLI_ASSOC);

    if ($row) { 
        if ($row["licensePath"]) {
            echo "license";
        } 
    }
    else {
        echo "NO ROWS";
    }

Add `or die(mysqli_error($conn))` to `mysqli_query()` and you'll see the syntax error. — Funk Forty Niner, Apr 13 '15 at 13:50
What is being passed for `LIKE '$uid'`? You may want to do `WHERE driver_profileId = '$uid'` instead. LIKE may not be returning what you're looking for. — Funk Forty Niner, Apr 13 '15 at 13:53
the error returns as expected: ` 'WHERE licensePath IS NOT NULL ORDER BY datetime_upload DESC LIMIT 1' ` — Derple, Apr 13 '15 at 13:56
I assume licensepath is text or var char, however null and "" are both different values. Try licenspath != "" — Mike M., Apr 13 '15 at 13:58
When i remove the later where it works correctly with the LIKE. I have tried replacing like with = and with the second WHERE continues to error. — Derple, Apr 13 '15 at 13:58
Try `$query = "SELECT * FROM driver_docs WHERE licensePath IS NOT NULL AND driver_profileId LIKE '$uid' ORDER BY datetime_upload DESC LIMIT 1";` @StuartWickenden — Narendrasingh Sisodia, Apr 13 '15 at 13:58
@StuartWickenden - that's because you only need one WHERE: ` WHERE driver_profileId LIKE '$uid' AND licensePath IS NOT NULL`. Two WHEREs will give you a syntax error — andrewsi, Apr 13 '15 at 14:00
Allright still try what I said the `WHERE licensePath != "" AND ....` as said 2x where probably gives you the issue, what does the dbms like phpmyadmin say? — Mike M., Apr 13 '15 at 14:01
@MikeM. Spot on. That's clarified soemthing for me which i never knew! Thanks. Answer the question for tick and upvote. Thanks Mike! `$query = "SELECT * FROM driver_docs WHERE licensePath !='' AND driver_profileId LIKE '$uid' ORDER BY datetime_upload DESC LIMIT 1";` — Derple, Apr 13 '15 at 14:05

score 1 · Answer 1 · answered Apr 13 '15 at 13:49

1

$query = "SELECT * FROM driver_docs WHERE driver_profileId LIKE '$uid' AND WHERE licensePath IS NOT NULL ORDER BY datetime_upload DESC LIMIT 1";

you have used 2 where clause. Just remove second "Where" clause and add both condition with "And" Operator. It should work.

answered Apr 13 '15 at 13:49

Manish Shukla

1,355
2
8
21

`WHERE driver_profileId LIKE '$uid' AND licensePath IS NOT NULL ORDER BY datetime_upload DESC LIMIT 1";` – Derple Apr 13 '15 at 13:53

score 1 · Accepted Answer · answered Apr 13 '15 at 14:10

A null value and empty value is not the same. As your cell is var_char your text would automatically be shown as an empty value. Which would mean you do != '' instead of IS NOT NULL.

Most likely a NULL value is typed out as NULL.

-written on a phone in the bus on my way home. Excuse me for grammatical errors and/or unfinished answering.

Unexpected Mysqli WHERE is not null Value Returned

2 Answers2