my function that checks if something is already in the database isn't working

Question

I'm working on this project and I need help with something. I am trying to check if someone is already in the database upon logging in and if they are not, they will be added. However, my code always adds them to the database...

Login code:

<?php
if(isset($_POST["emaillogin"]) and isset($_POST["passwordlogin"])){
    $sql = "SELECT `accnr` 
            FROM `Account` 
            WHERE '$emaillogin' = `emailadress` 
            AND '$passwordlogin' = `password` LIMIT 1";

    $result = mysql_query($sql);

    if ($result == false){
        echo "E-mail or password incorrect! <br>";
    }else{
        $accnr = mysql_fetch_array($result);                    
        setcookie("accnr", $accnr[0] , time() + (1800), "/"); 
        $accnmr = $accnr[0];

        if(check_firstest($accnmr) == false){
            $query = "INSERT INTO `VRIENDEN`
                               (`accnr`,`vriendnr`) 
                        VALUES ('$accnmr','$accnmr')";
            $result = mysql_query($query);
        }

        header("location:home.php");
        die();
    }
}

?>

The function in functions.php:

function check_firstest($accnr){    
$query = mysql_query("SELECT count(*) AS 'num' FROM `VRIENDEN` WHERE `accnr` = '$accnr' AND `vriendnr` = '$accnr'");
    if($result > 0){
        return true;
    }
    else{
        return false;
    }
}

The login on its own works just fine, so thats no problem. Thank you!

`mysql_query` returns a resource, not the output from the query. You need to use a fetch function on that result. — Jonnix, Apr 04 '17 at 08:18
Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[this happens](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Apr 04 '17 at 08:33
Plain Text Password **Very bad idea** PHP provides [`password_hash()`](http://php.net/manual/en/function.password-hash.php) and [`password_verify()`](http://php.net/manual/en/function.password-verify.php) please use them. And here are some [good ideas about passwords](https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet) If you are using a PHP version prior to 5.5 [there is a compatibility pack available here](https://github.com/ircmaxell/password_compat) — RiggsFolly, Apr 04 '17 at 08:38

score 0 · Answer 1 · edited May 23 '17 at 12:02

Your first query is somewhat odd and you do not capture the values from $_POST into the variables that you are using in the query either

<?php
error_reporting(E_ALL); 
ini_set('display_errors', 1);

if(isset($_POST["emaillogin"]) and isset($_POST["passwordlogin"])){
    $sql = "SELECT `accnr` 
            FROM `Account` 
            WHERE `emailadress` = '{$_POST['emaillogin']}'
            AND   `password` = '{$_POST['passwordlogin']}'
            LIMIT 1";

    $result = mysql_query($sql);

    if ($result == false){
        // something went REALLY WRONG, report it
        echo mysql_error();
        exit;
    }
    if ( mysql_num_rows($result) == 1 ) {
        // found user and password matches
        header("location:home.php");
        exit;
    }else{
        // new user, create the account
        $accnr = mysql_fetch_array($result);                    
        setcookie("accnr", $accnr[0] , time() + (1800), "/"); 
        $accnmr = $accnr[0];

        if(check_firstest($accnmr) == false){
            $query = "INSERT INTO `VRIENDEN`
                               (`accnr`,`vriendnr`) 
                        VALUES ('$accnmr','$accnmr')";
            $result = mysql_query($query);
        }
        // and go to home page
        header("location:home.php");
        die();
    }
}
?>

And of course the fix for the check_firstest() is also required

function check_firstest($accnr){    
    $result = mysql_query("SELECT count(*) AS 'num' 
                            FROM `VRIENDEN` 
                            WHERE `accnr` = '$accnr' 
                            AND `vriendnr` = '$accnr'");
    if(mysql_fetch_field($result, 0) > 0){
        return true;
    } else{
        return false;
    }
}

But I have to add

Your script is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared parameterized statements

And

You should not be using the mysql_ database extension, it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the PDO or mysqli database extensions and prepared statements. Start here

I have literally done all of this and still it won't add to the database when there is nothing in there... — literallyme, Apr 04 '17 at 08:51
error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '} AND `password` = 'hh' LIMIT 1' at line 3 — literallyme, Apr 04 '17 at 08:58
Silly me! I forgot the quotes around the text variables. See amended code for first query — RiggsFolly, Apr 04 '17 at 09:08
Welp... nothing happens. it just logs in and thats it. It doesn't add anything to the databse. It just logs in. It doesn't give me an error code or something — literallyme, Apr 04 '17 at 09:10
I am assuming you always want to go to `home.php` when user exists, and once a user has been created. Try above amendment — RiggsFolly, Apr 04 '17 at 09:15

score -1 · Answer 2 · edited Apr 04 '17 at 08:25

-1

You have to count the resulting rows:

function check_firstest($accnr){    
    $result = mysql_query("SELECT count(*) AS 'num' 
                            FROM `VRIENDEN` 
                            WHERE `accnr` = '$accnr' 
                            AND `vriendnr` = '$accnr'");
    if(mysql_fetch_field($result, 0) > 0){
        return true;
    } else{
        return false;
    }
}

Here the mysql_num_rows() function gives the number of rows in the result set. If it is greater than 0 then it means that there is some data.

edited Apr 04 '17 at 08:25

RiggsFolly

93,638
21
103
149

answered Apr 04 '17 at 08:18

Shakti Phartiyal

6,156
3
25
46

5

Won't that always be at least `1`? `COUNT` will always return a row. – Jonnix Apr 04 '17 at 08:19
This will always return TRUE! Suggest you fix it – RiggsFolly Apr 04 '17 at 08:20
@JonStirling How would you suggest to fix it then? – literallyme Apr 04 '17 at 08:25
Please go through the official manual @http://php.net/manual/en/function.mysql-num-rows.php It clearly says `The number of rows in a result set on success or FALSE on failure.` – Shakti Phartiyal Apr 04 '17 at 08:26
1

Apparently @RiggsFolly fixed the answer for you. – Jonnix Apr 04 '17 at 08:26
@ShaktiPhartiyal We're well aware of how it works. I'd suggest you re-read it, alongside reading up about the SQL `COUNT` function. – Jonnix Apr 04 '17 at 08:26
1

I have amended your answer, as it got UV from those who have not had enough coffee yet this morning – RiggsFolly Apr 04 '17 at 08:26
1

@ShaktiPhartiyal I suggest you also apply a little logic! When the answer to the COUNT query is Zero(0) you still get ONE ROW RETURNED with the result of ZERO in it – RiggsFolly Apr 04 '17 at 08:30
but if there's nothing in the database yet, it won't add it. – literallyme Apr 04 '17 at 08:34
@literallyme that is because you are holding the result of `mysql_query` in the variable $query and checking it against $result. – Shakti Phartiyal Apr 04 '17 at 08:39
@RiggsFolly I got it.. ;) – Shakti Phartiyal Apr 04 '17 at 08:40
1

@ShaktiPhartiyal Excellent :) – Jonnix Apr 04 '17 at 08:42

my function that checks if something is already in the database isn't working

2 Answers2