OOP: Function to check if value is in database

Question

I'm trying to create a user management system class and one of my functions is to check if a value is in a column in the database. It returns nothing at all.

Here is the relevant information:

require("opperators/connect.php");
class UserManagemen
{
protected $db;

public function __construct(PDO $db)
  {
    $this->db = $db;
  }
public function isInDatabase($checkIfThis, $isHere)
  {
    $stmt = $db->prepare("SELECT 1 from users WHERE $isHere = :$isHere");
    $stmt->execute(array(':$isHere' => $checkIfThis));
        if ($stmt->rowCount() > 0) {
        echo "It's in the database";
        } else {
        echo "Not in the database and you are good to go!";
        }
   }
}
$usermanagement = new UserManagemen($db, null, null, null);
$usermanagement->isInDatabase(Batman, username);

in connect.php: this worked in my procedural coding test.

$configurator['database'] = array(
'username' =>   'root',
'password' =>   'root',
'host' =>    'localhost',
'db' =>    'girlscouts',
);
$options  = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
);
try {
$db = new PDO("mysql:host={$configurator['database']['host']};dbname={$configurator['database']['db']};charset=utf8", $configurator['database']['username'], $configurator['database']['password'], $options);
}
catch (PDOException $ex) { 
die("Failed to connect to the database: " . $ex->getMessage());
}

I apologise in advance if this has been asked repeatedly, I've tried a google but returned nothing of value, possibly because I don't know what to google. I'm not big on asking for help but I'll give it a shot.

Adding to what anrewsi said, it looks like php error reporting is turned off, otherwise it would have told you there was an error in your query. You could then have used php to find out what the problem was and came to the same conclusion as andrewsi. — Jasper, Oct 23 '12 at 17:27
Sure do! Fixed it and tested it. Didn't change the end result yet but it sure was a necessary change and proves that I need some more coffee. — rococo polkadot bandit, Oct 23 '12 at 17:28
@rococopolkadotbandit - actually... I think you have your `if` statement the wrong way round. If your query returns rows, then you've found the data, but you're currently echoing the 'Not in the database' line. — andrewsi, Oct 23 '12 at 17:30
True, fixed the if statement. And I'll work on figuring what error reporting is and how to turn it on. — rococo polkadot bandit, Oct 23 '12 at 17:38

The Alpha · Accepted Answer · 2012-10-23T18:07:00.217

0

I think it should be

if ($stmt->rowCount() > 0) { // data available
    echo "It's in the database";
} else {
    echo "Not in the database and you are good to go!";
}

instead of

if ($stmt->rowCount() > 0) {
    echo "Not in the database and you are good to go!";
} else {
    echo "It's in the database";
}

Also you didn't initialize the PDO like new PDO(...);, in your constructor it should be

$this->db = new PDO(...);

and you should use it like

$stmt=$this->db->prepare(...);
$stmt->execute(...);

Take a look at this and this for details.

edited Oct 23 '12 at 18:07

answered Oct 23 '12 at 17:30

The Alpha

143,660
29
287
307

Thanks! Fixed it, but it still doesn't return anything. – rococo polkadot bandit Oct 23 '12 at 17:39
@rococopolkadotbandit, [check this answer](http://stackoverflow.com/questions/8179852/cant-initialize-a-pdo-object-in-a-class-as-a-property). – The Alpha Oct 23 '12 at 17:50
I've got it just like in my intro post. I didn't know if the fields were required so I just put null there. – rococo polkadot bandit Oct 23 '12 at 17:51
Thanks, my connection is totally wrong and you probably pinned the error. I'll have to get back on this after a short break but you helped out and I'm sure I'll get it working with this. Really appreciate the quick response. I've only been banging out PHP for a few weeks and it's way out of my graphics designer comfort zone. – rococo polkadot bandit Oct 23 '12 at 18:43

score 0 · Answer 2 · answered Oct 24 '12 at 00:01

0

You are doing it wrong.

Instead of making additional request just to see, if such value already is stored in table, you should set UNIQUE constraint on column. Then, if you have correctly set up the PDO instance, the attempt to insert a duplicate will rise an PDOException, which then you can handle in whichever way you want.

Also the UNIQUE constraint would let you in MySQL perform .. ON DUPLICATE UPDATE .. construction.

To learn more about PDO use, read this tutorial. Seems like you are emulating the prepared statements.

answered Oct 24 '12 at 00:01

tereško

58,060
25
98
150

I am trying to use prepared statements, the username is user input, trying to protect the database without needing any whitelisting as I want my users to be able to use unicode characters like š and apostrophes. I'm incredibly new to all of this so if I'm not making any sense, I admit my ignorance early on. Unicode and special character usage is critical for the design of the page. – rococo polkadot bandit Oct 24 '12 at 09:37

OOP: Function to check if value is in database

2 Answers2