PHP/MySQL SELECT statement with 'variable' <> ' ' or 'variable' IS NOT NULL not working

Question

I have a SQL routine written that compares a user's input with an existing value in a table. If the value exists, a green check mark appears next to the input. If it doesn't, then a red x appears. My SQL statement is as follows:

      $check = $con->prepare("SELECT count(*) FROM emaillist");
      $check->execute();
      $result = $check->fetchColumn(); //Get no. of columns
      $check = $con->prepare("SELECT Username FROM emaillist WHERE Username =                                                                                                                                                                                                                               
              '$Name' AND '$Name' <> '' ");
      $check->execute();
      $result = $check->fetchColumn(); //Get exact column
      if(!$result) {
               show red x } else { show green check }

This works fine as long as there is input. The red x appears when the input doesn't match and the green check appears when it does; however, I don't want anything to display if the field is left blank. Right now, the red x appears if the field is empty or is null. Using IS NOT NULL didn't work, either. What am I missing?

If $Name is coming from a user input then I think it would be better to check for its validity independent of the SQL query rather than putting it in the query itself. — Maximus2012
– Maximus2012, Commented Oct 2, 2013 at 20:57

Explosion Pills · Accepted Answer · 2013-10-02 20:59:25Z

2

You only have two branches: show red x and show green check. You need a third, e.g.

if (!$Name) {
    //show nothing
}
else if (!$result) {
    //show red x
}
else {
    //show green check
}

On an unrelated note, it's great that you're using PDO but you need to properly parameterize your queries to be safe from injection.

answered Oct 2, 2013 at 20:59

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

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

1 Comment

Chris Over a year ago

No worries. When this goes into production, we'll be using named placeholders. This is just a prototyping exercise. ;-)

Maximus2012 · Accepted Answer · 2013-10-02 21:00:38Z

1

Try rewriting your logic like this:

if ($Name != ''){
    $check = $con->prepare("SELECT Username FROM emaillist WHERE Username = '$Name'");
    $check->execute();
    $result = $check->fetchColumn(); //Get exact column
    if(!$result) {
          // show red x 
    } else { 
         // show green check 
    }
}
else {
    // show red here or some other input error message
}

answered Oct 2, 2013 at 21:00

Maximus2012

1,8392 gold badges12 silver badges15 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL SELECT statement with 'variable' <> ' ' or 'variable' IS NOT NULL not working

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related