CODEWITHSUNDEEP
phpmysql
John Emerson
John Emerson

Reputation: 55

Database query always returns TRUE

Trying to find if row exists in database. This code always returns TRUE. I want to add the row if it doesn't exist. See test code below.

 // CHECK IF RECORD EXISTS
  if (array_key_exists('test', $_GET)) {
    if(coupleRowExists($_GET['male'], $_GET['female'])) {
      echo 'EXISTS';
    } else {
      echo 'Does NOT exist';
    }
  }

  // CHECK DB FOR THIS COUPLE
  function coupleRowExists($male, $female) {
    global $db;
    $sp_couple_exists = "SELECT EXISTS(SELECT 1 FROM `couples` 
                         WHERE male = ? AND female = ? )";
    return ($db->prepare($sp_couple_exists)->execute([$male, $female]));
  }

Upvotes: 0

Views: 259

Answers (3)

John Emerson
John Emerson

Reputation: 55

I agree with user3783243 in that rowCount does not always work with SELECT (see PHP Manual->PDOStatement::rowCount(). They recommend use of COUNT as follows:

function coupleRowExists($m, $f) {
    global $db;
    $sp = "SELECT COUNT(*) FROM `couples` WHERE male = ? AND female = ?";
    $stmt = $db->prepare($sp);
    $stmt->execute([$m, $f]);
    $count = $stmt->fetchColumn();
    return $count;
  }

This has proven reliable.

Upvotes: 1

Bhushan
Bhushan

Reputation: 595

execute() will return only true or false. Here's a link for a reference. https://www.php.net/manual/en/pdostatement.execute.php

Here's a modified function for the same.

function coupleRowExists($male, $female) {
  global $db;
  $sql = "SELECT EXISTS(SELECT 1 FROM couples WHERE male = ? AND female = ?)";
  $db->prepare ($sql);
  $result = $db->execute ([$male, $female]);

  if ($result && $db->rowCount() > 0) {
    return true;
  } else {
    return false;
  }
}

Upvotes: 1

Luke Pittman
Luke Pittman

Reputation: 843

This code:

return ($db->prepare($sp_couple_exists)->execute([$male, $female]));

Will return true when the database query is executed successfully, not necessarily if there is a result or not. Which, if you wrote the query properly, will be all the time.

If you want to find out if it actually returned a row, then you'll want to check for an actual returned row.

I would modify this a bit so it returns that and is a bit cleaner:

function coupleRowExists($male, $female) {
  global $db;
  $sql = "SELECT EXISTS(SELECT 1 FROM couples WHERE male = ? AND female = ?)";
  $db->prepare ($sql);
  $db->execute ([$male, $female]);

  if ($db->rowCount()) {
    return true;
  } else {
    return false;
  }
}

Upvotes: 1

Related Questions