Mysql query IFEXIST does not return any result

Question

I am trying to create a login form in PHP. i am passing the username and password that user had entered and check weather that exist in the data base.

i have done the coding, but IF EXIST query does not return any result.

can any one help me to fix this. or give me a alternate idea.. Thank you...

        <?php

        $name= $_POST["usname"];
        $pass = $_POST ["password"];

        $connection = mysqli_connect("localhost","sathya","sathya","learning1"); 
        //mysqli_query($connection,"INSERT INTO user (name, password) VALUES ('".$name."', '".$pass."')");
        $result = mysqli_query($connection, "IF EXISTS(SELECT *  FROM  user  WHERE name='".$name."'AND password='".$pass."')");
        mysqli_close($connection);

        echo "result ".$result;

        if($result == True){

          header("Location: logedin.php");
            //redirect_to('logedin.php');
        }else{

            echo "not logged in installed";
        }


        ?>

i tried without closing the connection also, it does not work. — Sathya Baman, Apr 24 '14 at 09:58
There's no such thing as `IF EXISTS`, there's only `SELECT IF ...` — Alma Do, Apr 24 '14 at 10:00
The first entry in Google by searching "IF EXISTS mysql". http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists — Hammurabi, Apr 24 '14 at 10:02

score 4 · Answer 1 · edited May 23 '17 at 12:00

This is a late answer, but there are a few things you need to be made aware of. (Not taking away from the accepted answer).

You will need to use if(mysqli_num_rows($result) > 0) because your query will always be TRUE if the username matches and the password does NOT, and vice-versa.

You are better off using mysqli_num_rows() rather than using if($result == True)

Sidenote: Consult my footnotes regarding password storage and SQL injection.

<?php
$DB_HOST = "xxx";
$DB_NAME = "xxx";
$DB_PASS = "xxx";
$DB_USER = "xxx";

$db = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
    if($db->connect_errno > 0) {
      die('Connection failed [' . $db->connect_error . ']');
    }

$name = $_POST["usname"]; // See footnotes about this
$pass = $_POST ["password"]; // See footnotes about this

$result = mysqli_query($db, "SELECT EXISTS(SELECT *  FROM  users  WHERE username='".$name."' AND password='".$pass."')");

// Works just as well
// $result = mysqli_query($db, "SELECT * FROM  users  WHERE username='".$name."' AND password='".$pass."'");

    if(mysqli_num_rows($result) > 0){
    echo "Both match.";
    }

    else{
    echo "Sorry, there was not a perfect match.";
    }

Footnotes:

You can also use:

$result = mysqli_query($db, "SELECT * FROM  users  WHERE username='".$name."' AND password='".$pass."'");

Which does the same for SELECT EXISTS(SELECT * while using less characters.

or choose actual columns:

$result = mysqli_query($db, "SELECT username, password FROM  users  WHERE username='".$name."' AND password='".$pass."'");

I suggest that you use prepared statements and sanitize your inputs. Not doing so will leave you open to SQL injection.

Here are a few tutorials on (mysqli) prepared statements that you can study and try:

Here are a few tutorials on PDO:

Passwords

I also noticed that you are storing passwords in plain text. This is not recommended.

Use one of the following:

CRYPT_BLOWFISH
crypt()
bcrypt()
scrypt()
On OPENWALL
PBKDF2
PBKDF2 on PHP.net
PHP 5.5's password_hash() function.
Compatibility pack (if PHP < 5.5) https://github.com/ircmaxell/password_compat/

Other links:

PBKDF2 For PHP

score 1 · Accepted Answer · edited Apr 24 '14 at 13:44

I can't say anything about the PHP part, but the query will surely result in a syntax error.

IF whatever ... is only allowed in stored procedures or functions, not in single queries. You can however replace the IF with SELECT like

$result = mysqli_query($connection, "SELECT EXISTS(SELECT *  FROM  user  WHERE name='".$name."'AND password='".$pass."')");

This query would return either 0 (if no entry exists) or 1 (if an entry exists). It's also a good idea to use EXISTS as it stops the query as soon as an entry was found and does not return the whole dataset.

score 1 · Answer 3 · answered Apr 24 '14 at 10:07

1

You can try this beside using 'IF EXISTS' function--

$result = mysqli_query($connection, "SELECT *  FROM  user  WHERE name='".$name."'AND password='".$pass."'");



$count=mysql_num_rows($result);
if($count==1)  // $count=1 if any row is present with mathing username and pwd in db
{
    echo "user already logged in";
}
else  
{
  echo "user not exist";
}

answered Apr 24 '14 at 10:07

shashank

566
3
10
31

You select everything just to do a count on the result afterwards to see if there's at least one entry? LOL – fancyPants Apr 24 '14 at 10:15
This `mysql_num_rows` should be `mysqli_num_rows` --- You can't mix two different MySQL APIs. – Funk Forty Niner Apr 24 '14 at 16:27

Mysql query IFEXIST does not return any result

3 Answers3