Merge a count query & fetch query into one & let MySQL do the work

Question

I'm currently encountering a problem with false validations on the return of my function. & I've narrowed it down to the following line:

if ($User_ID === null){
   return false;
}

Now, i'm performing some queries using MySQLi;

$Query = $this->_Database->prepare("SELECT ID,Username,Password,Salt,UserStatus FROM users WHERE Username=?");
$Query->bind_param('s',$Username);
$Query->execute();
$Query->bind_result($User_ID,$Username,$User_Password,$User_Salt,$User_Status);
$Query->fetch();
$Query->close();

After fetching, I noticed that if there is no data detected from the queries, the bound results will return a null instead of actual data, but the problem is sometimes this isn't working for some reason as it allows access to the members area because of some type of validation being seen wrong from the code, i'm guessing it's the return statements either false or an array. So to tweak the performance and want to attempt to remove the NULL check.

Standard procedure for what i've currently implemented is as follows:

$User_Chk = $this->_Database->prepare("SELECT COUNT(ID) FROM users WHERE `username`=?");
$User_Chk->bind_param('s',$Username);
$User_Chk->execute();
$User_Chk->bind_result($Count);
$User_Chk->fetch();
$User_Chk->close();

if ($User_Chk === 1){

    $Query = $this->_Database->prepare("SELECT ID,Username,Password,Salt,UserStatus FROM users WHERE Username=?");
    $Query->bind_param('s',$Username);
    $Query->execute();
    $Query->bind_result($User_ID,$Username,$User_Password,$User_Salt,$User_Status);
    $Query->fetch();
    $Query->close();
    return array(
                "UserID" => $User_ID,
                "Username" => $Username,
                "Password" => $User_Password,
                "Salt" => $User_Salt,
                "Status" => $User_Status
            );  

}
return false;

But.. As i've always followed the rule of let MySQL do the work before PHP has to manipulate.. To be frank, i'm not that savvy with MySQL Queries/Keywords.. So, is there any built in function to join the two queries & manipulate the returns before PHP fetches the results with the bind_result() function?

you have different capitalization of `username` / `Username` in the queries... — PlantTheIdea, Feb 03 '14 at 16:27
@PlantTheIdea Good spot, perhaps this could sometimes return a true and fetch required rows, and sometimes not? — Daryl Gill, Feb 03 '14 at 16:28

score 0 · Accepted Answer · answered Feb 03 '14 at 17:12

0

$Query->fetch() should, in principle, be called in a loop, because it is in principle capable of returning zero, one, or multiple rows. The usual way of expressing that is

  while($Query->fetch()) {
        /* process the $row */
  }
  $Query->close();

It seems that your business rules (that is, uniqueness of your username column) means that your query can return either zero or one rows. So, you could do this effectively.

  if($Query->fetch()) {
      /* process the user's information */
  } else {
      /* no such user */
  }
  $Query->close();

answered Feb 03 '14 at 17:12

O. Jones

103,626
17
118
172

Sorry for the delay in replying. Using a loop to iterate through the result set would be wasted memory (I know micro but still), since there's only one row being fetched – Daryl Gill Feb 04 '14 at 01:04
Why would it waste memory? It would only iterate once. You're in php: the only memory involved here is dozens of bytes of language parse tree. – O. Jones Feb 04 '14 at 02:48

Merge a count query & fetch query into one & let MySQL do the work

1 Answers1