Going from old SQl to pdo, num rows alternative

Alex Lord
Alex Lord used Ask the Experts™
on
So i am converting this old outdated sql to pdo.


$query2 = "
								SELECT
									DISTINCT ev.EVENT AS EVENT
								FROM TXT2GIVE_EMAIL_EVENT$emailTableNum ev
								WHERE ev.CHARITY_ID='$charityId' AND ev.CAMPAIGN_ID='$campaignId' AND ev.EMAIL_SAVE_ID='$saveId' AND ev.EMAIL_ADDRESS='$email' AND ev.EMAIL_SAVE_ID IN (SELECT EMAIL_SAVE_ID FROM TXT2GIVE_EMAIL_SENT WHERE ev.CHARITY_ID='$charityId')
								ORDER BY TIMESTAMP ASC";
							$result2=mysql_query($query2, $db) or die(mysql_error());
							if(mysql_num_rows($result2))
							{

Open in new window



to pdo -

$stmt = new Database();

					$query = "SELECT
					DISTINCT ev.EVENT AS EVENT
						FROM TXT2GIVE_EMAIL_EVENT$emailTableNum ev
						WHERE ev.CHARITY_ID = :clientID AND ev.CAMPAIGN_ID = :campaignID AND ev.EMAIL_SAVE_ID = :saveID AND ev.EMAIL_ADDRESS = :email AND ev.EMAIL_SAVE_ID IN (SELECT EMAIL_SAVE_ID FROM TXT2GIVE_EMAIL_SENT WHERE ev.CHARITY_ID = :clientID)
						ORDER BY TIMESTAMP ASC";

					$stmt->query( $query );
					$stmt->bind( ':clientID', $clientId );
					$stmt->bind( ':campaignID', $campaignId );
					$stmt->bind( ':saveID', $saveId );
					$stmt->bind( ':email', $email );
					$stmt->execute();
					$result = $stmt->all();

Open in new window



What is my alternative for  this line
if(mysql_num_rows($result2))

Open in new window


i need to be able to do the same with pdo get the number of rows to perform this statement
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
try:

if ($stmt->rowCount() > 0) {
   //... got record, do your stuffs
}

Open in new window


PDOStatement::rowCount
http://php.net/manual/en/pdostatement.rowcount.php
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
You have a few options here. As Ryan says, rowCount() is one option although as the docs say, you can't guarantee that it will actually return the number of reords from a SELECT statement, so use it with caution.

The advisable way to do a row count is actually to run another query and return a COUNT(), although this often feels like overkill, as you're doing 2 queries. The same is true if you run the FOUND_ROWS() mySql function.

Having said that, your code look like it actually retrieves all the records in one hit anyway - $stmt->all(). That's not a standard PDO function, so I'm guessing you've created your own wrapper class. If that function does indeed return all the records, then you don't even need to do a database call to get the number of rows - you just count the array:

count($result);

Finally, if you just planning on looping over your records, don't even bother to count them. I often see something like:

if ($records->rowCount() > 0):
    while ($record = $records->fetch() ):
        ...

In this case, the if statement is completely redundant.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial