Database query always returns TRUE

Question

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]));
  }

You are executing the SQL Query but not checking the result before return. After execute, fetchAll and then finally use if else to see if rowCount is >= 1. If yes, return true and if now return false. — Mohammed Akhtar Zuberi
– Mohammed Akhtar Zuberi, Commented Jun 21, 2019 at 1:15

Luke Pittman · Accepted Answer · 2019-06-21 01:17:51Z

1

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;
  }
}

answered Jun 21, 2019 at 1:17

Luke Pittman

8434 silver badges12 bronze badges

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

Comments

Bhushan · Accepted Answer · 2019-06-21 04:41:41Z

1

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;
  }
}

answered Jun 21, 2019 at 4:41

Bhushan

6403 silver badges9 bronze badges

Comments

John Emerson · Accepted Answer · 2019-06-22 07:47:31Z

1

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.

answered Jun 22, 2019 at 7:47

John Emerson

551 silver badge6 bronze badges

Collectives™ on Stack Overflow

Database query always returns TRUE

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related