PHP Variables in MYSQL Select using PDO [duplicate]

Question

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.

stackoverflow.com/questions/854128 will help you on to find duplicates in your database. — Dorvalla
– Dorvalla, Commented Oct 10, 2012 at 11:57
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
– Sven Fischer, Commented Oct 10, 2012 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 and follow the error log. — hakre
– hakre, Commented Oct 10, 2012 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
– Sven Fischer, Commented Oct 10, 2012 at 12:15

Elias Van Ootegem · Accepted Answer · 2012-10-10 12:12:38Z

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

answered Oct 10, 2012 at 12:12

Elias Van Ootegem

76.7k10 gold badges123 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Elias Van Ootegem Over a year ago

@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

JvdBerg · Accepted Answer · 2012-10-10 12:09:21Z

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.

edited Oct 10, 2012 at 12:09

answered Oct 10, 2012 at 12:03

JvdBerg

21.8k8 gold badges40 silver badges55 bronze badges

3 Comments

Sven Fischer Over a year ago

I have include ("dbconnect.php");

Sven Fischer Over a year ago

should i use it inside the function?

JvdBerg Over a year ago

See my answer, pass it to the function like function uni($pdo, $field, $value) or make it a global $pdo

Collectives™ on Stack Overflow

PHP Variables in MYSQL Select using PDO [duplicate]

2 Answers 2

1 Comment

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Linked

Related