1

Possible Duplicate:
Find duplicate records in MySQL

I am a newbe trying to find duplicates in a database with this function:

function uni($field, $value) {
        $sql= "SELECT * FROM user WHERE ".$field." = '".$value."'";
        $result = $pdo->query($sql);
        $rows = $result->fetchAll(PDO::FETCH_COLUMN, 1);
        return count($rows);
    }

$username = $_POST['username']; 
$result = uni("username", $username);

...i am about to bang my head against something solid. For some reason the query won't return a result and I dont know why.

Community
  • 1
  • 1
Sven Fischer
  • 87
  • 2
  • 3
  • 9
  • 1
    http://stackoverflow.com/questions/854128/ will help you on to find duplicates in your database. – Dorvalla Oct 10 '12 at 11:57
  • Let's know exactly at which point you are stuck. – Leri Oct 10 '12 at 11:59
  • I am getting data from a form via post, when i echo $sql the statement looks fine _SELECT * FROM `user` WHERE `username` = 'svensenn'_ but i dont get a result – Sven Fischer Oct 10 '12 at 12:02
  • I see no error checking in your code so I wonder what the concrete problem is you run into. A database normally just works, if you're not confident with the outcome you should pinpoint where the problem to query the database first arises. This might need basic debugging first. Please do a `error_reporting(~0); ini_set('display_errors', 1);` at the very beginning of your script. Additionally you should [enable error logging](http://www.cyberciti.biz/tips/php-howto-turn-on-error-log-file.html) and follow the error log. – hakre Oct 10 '12 at 12:08
  • thank you hakre. i have tried _$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);_ with a try/catch, but would't get an error. thank you for your tips, i didn't know how to do that. – Sven Fischer Oct 10 '12 at 12:15

2 Answers2

2

Ok, so you're using PDO, good. Your snippet is still wide open to injection attacks, though: you're still passing raw user input to a query. Also, don't use SELECT * if all you want is the number of rows that were found, and don't FETCH the full result-set to count them!

function uni($field,$value)
{
    $db = new PDO();//make connection, which you don't seem to do
    //or (not so good approach):
    //global $db;
    //Best approach would be to pass the connection to the function, as an extra argument, though
    $stmt = $db->prepare('SELECT '.$field.' FROM user WHERE '.$field.' = :value');
    if ($stmt->execute(array(':value' => $value)))
    {
        return $stmt->rowCount();
    }
    //query failed, throw errors or something
}

Read the docs for more examples.
Anyway, your code, in full should look like this:

function uni($field,$value,$db)
{
    $stmt = $db->prepare('SELECT '.$field.' FROM user WHERE '.$field.' = :value');
    if ($stmt->execute(array(':value' => $value)))
    {
        return $stmt->rowCount();
    }
    return false;
}
$username = $_POST['username']; 
$result = uni('username', $username,$pdo);//<--pass connection
Elias Van Ootegem
  • 74,482
  • 9
  • 111
  • 149
  • @SvenFischer: Glad I could help. I must say, this is only addressing the most _"urgent"_ issue (getting the code to work). I left so many things out (like why `SELECT *` should be avoided, why you shouldn't fetch the results,... why Bobby Tables would find your code very interesting,...). Just google these three things, and find out more; it's well worth your while, I promise – Elias Van Ootegem Oct 10 '12 at 12:30
1

You are using a reference to the $pdo object inside your function, but the $pdo object is not defined.

function uni($field, $value) {
  $sql= "SELECT * FROM user WHERE ".$field." = '".$value."'";
  $result = $pdo->query($sql);
//          ^^ undefined object

  $rows = $result->fetchAll(PDO::FETCH_COLUMN, 1);
  return count($rows);
}

Either pass the $pdo to your function, or make it a global.

Turn on error reporting, so you can see where the errors in your code are.

JvdBerg
  • 21,777
  • 8
  • 38
  • 55