MYSQL Check if username already exists in database

Question

My code was working but after I inserted a query to check if the first name in MYSQL database already exists, it does not work anymore. Here you can see my code, if you have any tip on how to make this work, I will appreciate it. Thank you very much!

I have tried to work with mysql_num_rows command, but it seems like I didn't use it correctly.

<?php 

require_once __DIR__.'/connect.php';

$sName = $_POST['txtName'];

$query = mysql_query("SELECT * FROM users WHERE firstName = '$sName' ");
if (mysql_num_rows ($query) > 0){
    echo 'User with this name already exists';
}else{
    try {
        $stmt = $db->prepare('INSERT INTO users
    VALUES (null, :sName, :sLastName, :sEmail, :sCountry )');
        $stmt->bindValue(':sName', $sName);

        $stmt->execute();
        echo 'New user was successfully inserted';
    } catch (PDOEXception $ex) {
        echo $ex;
    }
}

You are trying to mix `mysql` (which you shouldn't be going anywhere near) and `pdo` queries. — Nick, May 13 '19 at 10:40
mysql_* functions are deprecated and removed from PHP7 - use mysqli_ or pdo as your code suggests — treyBake, May 13 '19 at 10:40
try to print the query and run in phpmyadmin(or any other software you use). Then check the results what it give. — anuj arora, May 13 '19 at 10:40
"it does not work anymore" is not a precise description of a problem. And you want to read about [SQL Injection](https://www.php.net/manual/en/security.database.sql-injection.php). — sticky bit, May 13 '19 at 10:41
I think you need to call the functions using object `$db`. This may be giving you error. — anuj arora, May 13 '19 at 10:41
Your current logic would prevent anyone with the exact same (first)name to be registered. What of `John Smith` and `John Parker`? Only one of them are allowed to register with your current approach. — Qirel, May 13 '19 at 10:46
Do you guys have the: "Don't use `mysql_*`" on your clipboard or something? As if you're able to smell people using it! — Peter, May 13 '19 at 10:47
@Qirel you can mix `mysql/mysqli` and `PDO`, you just have to make separate connections. **NOT** that I would recommend doing so... — Nick, May 13 '19 at 10:48
I'm well aware, @Nick. But I find it very unlikely that its the case here (of defining two connections), otherwise it would have worked as-is. — Qirel, May 13 '19 at 10:50
@Qirel agreed, my point was that the duplicate you proposed was about mixing `mysql` and `mysqli`. Sorry, I should have made that more clear. — Nick, May 13 '19 at 10:51
(I'm assuming) it would have worked if there was a valid connection through `mysql_`, thus, in my eyes, the duplicate is relevant (as OP is trying to query the DB through `mysql_` when there's a PDO connection). The dupe is tagged PDO as well (see the second-top answer too). Seems like we're (again) nitpicking/arguing about being in agreement with eachother :-) — Qirel, May 13 '19 at 10:53

score 3 · Accepted Answer · answered May 13 '19 at 10:46

You are trying to use mysql_query when you have (based on the rest of your code that is working) a PDO connection. Change your query to use your existing connection:

try {
    $stmt = $db->prepare("SELECT COUNT(*) FROM users WHERE firstName = :sName");
    $stmt->bindValue(':sName', $sName);
    $stmt->execute();
    $num_rows = $stmt->fetchColumn();
}
catch (PDOEXception $ex) {
    echo $ex;
}
if ($num_rows > 0) {
    echo 'User with this name already exists';
}
else {
    // the rest of your code here

MYSQL Check if username already exists in database

1 Answers1