How to check existence of records in mysql with php?

Question

How to check existence of records in mysql with php?

Navigation

#1 by (4 votes)
#2 by (2 votes)
#3 by (0 votes)

4

I'm doing a class work in which they ask me to make a record system, to insert the user's data in a database, I have the following function:

function comprovaUsuari($user, $passwd){
    $servername = "localhost";
    $username = "root";
    $dbpasswd = "";

    $conn = new PDO("mysql:host=$servername;dbname=php",$username, $dbpasswd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT * FROM users WHERE username='$user'";
    $rows = $conn->query($sql);
    $rows = $rows -> fetch();

    return $CORRECTE = ($rows==0);
}

This was the last thing I tried, then I have a conditional, if the variable CORRECT is true, it will tell me an error, but otherwise, it will insert the records in the table:

$CORRECTE = comprovaUsuari($user, $pass);

if ($CORRECTE){
    setcookie("error", "Nom d'usuari ja ocupat, torna a intentar", time() + 2, "/");
    header("Location: register.php");

}else{
    afegirUsuari($user, $name, $pass);
}

The main problem I have is that I have been testing for a couple of days, but changing all kinds of conditions, I simply have two options, or let me introduce the same user infinitely, or do not let me enter any, when the idea is that I can only enter once "x" username, to see if someone can light me up a bit.

By the way, the fields in the users table are name, username, password and ID

mysql sql php phpmyadmin

asked by THR4SH3RP0L0 28.03.2018 в 12:54

source

3 answers

4

Since you do not return all the columns in the users table, you have a Count and you only get this value. Your function would be as follows:

Modified to improve security in the query thanks to A. Cedano.

function comprovaUsuari($user, $passwd){
    $servername = "localhost";
    $username = "root";
    $dbpasswd = "";

    $conn = new PDO("mysql:host=$servername;dbname=php",$username, $dbpasswd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT COUNT(*) FROM users WHERE username = :user";
    $stmt = $conn->prepare($sql);
    $stmt->execute(array(':user' => $user));
    $result = $stmt->fetchColumn();
    return ($result==0);
}

answered by 28.03.2018 в 13:22

0

There are a couple of things that, just as my ignorance, I do not see clearly in your code.

The first is how to get the number of records. If really the only thing you want is to obtain the number of records that meet a condition I suggest you do a COUNT() in your sql query and then access the value returned by the query.

Secondly, and assuming you want to keep the sql query, I do not have a clear idea that this is correct:

$rows = $rows -> fetch();
$rows == 0; //$rows no debe ser un número, en todo caso será un array
            //por lo que deberias hacer un count

In the third place I am not clear either that this sentence is correct:

return $CORRECTE = ($rows==0);

Since that syntax is confusing, it should be of the return (/*Expresión bool*/); style because even if it works correctly it does not follow a good syntax.

answered by 28.03.2018 в 13:19

How to align right css? It is possible to cast objects of abstract classes

score 2 · Accepted Answer

You have 2 options for checking data:

Check that the rows returned is empty, then insert the user

function comprovaUsuari($user, $passwd){
    $servername = "localhost";
    $username = "root";
    $dbpasswd = "";

    $conn = new PDO("mysql:host=$servername;dbname=php",$username, $dbpasswd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $CORRECTE = false;

    $sql = "SELECT * FROM users WHERE username='$user'";
    $rows = $conn->query($sql);
    $rows = $rows->fetch();

    if(!rows){
        $CORRECTE = true;
    }

    return $CORRECTE;
}

Check that the columns returned are equal to 0, then insert the user

function comprovaUsuari($user, $passwd){
    $servername = "localhost";
    $username = "root";
    $dbpasswd = "";

    $conn = new PDO("mysql:host=$servername;dbname=php",$username, $dbpasswd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $CORRECTE = false;

    $sql = "SELECT * FROM users WHERE username='$user'";
    $rows = $conn->query($sql);

    if($rows->columnCount() === 0){
        $CORRECTE = true;
    }

    return $CORRECTE;
}