In some of my pages i need to check the num_rows returned by a select query to make decision.
in some forums i found by googling that it can be done with PDO:: fetchAll(); and others says it can also be done by querying PDO: FOUND_ROWS() function after my select query.
I tried
[php]//this is only an artifical draft no real codes.
My question is how can i know the number of results without querying more than once?
If you just need to know if there are results:
[php]if (count($result)){
// Do something
}
[/php]
If you need the actual number of results:
[php]$num_rows = count($result);[/php]
There is also
[php]$count = $result->rowCount();[/php]
Per the documentation:
PDOStatement::rowCount() returns the number of rows affected by a DELETE, INSERT, or UPDATE statement.
Notice that it does not officially support SELECT (One of my peeves with PDO). If you are up to date on your versions of PHP and Mysql, it does work though.
Cool interesting i just neeed something like this
[php]
if ((count($result)) == 1){
// Do something
}
[/php]
hope this work, I let you know if works
Thats not the code I gave you
It is, I changed a lil 
nah that is not what im looking for, count will return 1 no matter how many results are found
the aproach im using now is as follow, but it might be stupid or unprofessional
[php] $wResult = $conn->query(“SELECT wID,wDate FROM debtor_weeksdate WHERE
money_id=’$money_id’ ORDER BY wID ASC LIMIT 1”);// or die(mysql_error());
//querying 1 more to time to get the the Num_rows
$wNum_rows = $conn->query("SELECT `wID` FROM `debtor_weeksdate` WHERE `money_id`='$money_id' LIMIT 1");
$num_rows = count($wNum_rows->fetchAll()); //using PDO::fetchAll()
if ($num_rows == 1)
{
$qFetch = $wResult->fetch();
$weekDue = $qFetch[1];
}else
{
//do something else
}
[/php]
I’m looking for an alternative without querying twice.
Looking at your code I see what you want. It does not take two querys. I gave you the answer previously and you wrongly changed my code. You also do not need the limit one. Your query is based on a unique ID so there is only going to be one result. All you need is one query without the Limit 1 and the code I gave you UNMODIFIED.
[php] if (count($result)){
// Do something
}[/php]
[php]$result = $conn->query(“SELECT wID,wDate FROM debtor_weeksdate WHERE
money_id=’$money_id’ ORDER BY wID ASC”);// or die(mysql_error());
if (count($result)){
// Do something
}[/php]
[php]$num_rows = count($result); // this output 1 no matter how many rows were found!![/php]
I just need the number of results found from my select query.
I need the limit because money_id is not an unique column on that table. Table debtor_weeksdate may contain many money_id
Sounds like you may have a database design problem. Post an sql dump of your tables and I will take a look at it.
Thats because of the Limit 1
CREATE TABLE IF NOT EXISTS `debtor_weeksdate` (
`wID` int(1) NOT NULL AUTO_INCREMENT,
`money_id` mediumint(1) NOT NULL,
`debtor_id` mediumint(1) NOT NULL,
`wDate` varchar(11) NOT NULL,
PRIMARY KEY (`wID`),
UNIQUE KEY `wID` (`wID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=124 ;my struture is good. debtor_weeksdate will contain payments that are past due from a money loan. so money_id must NOT be unique because a debtor may be 2 weeks or more past due.
I have another table where money_id is the primary and unique key because a loan is unique.
Ok, so what is the end game? What are you wanting to see or achieve at this point of the code?
even when 0 results were found. very strange!!
You need to provide better details so I can help you. Please provide a few sample records as well as an explanation of what you are trying to do. I have no idea what the value of money_id is or what it represents other than it is not a unique id.
CREATE TABLE IF NOT EXISTS `debtor_weeksdate` (
`wID` int(1) NOT NULL AUTO_INCREMENT,
`money_id` mediumint(1) NOT NULL,
`debtor_id` mediumint(1) NOT NULL,
`creditor_id` mediumint(11) NOT NULL,
`wDate` varchar(11) NOT NULL,
PRIMARY KEY (`wID`),
UNIQUE KEY `wID` (`wID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=122 ;
--
-- Dumping data for table `debtor_weeksdate`
--
INSERT INTO `debtor_weeksdate` (`wID`, `money_id`, `debtor_id`, `creditor_id`, `wDate`) VALUES
(29, 68, 19, 457456, 'Jan 13,2014'),
(30, 68, 19, 457456, 'Jan 20,2014'),
(85, 60, 5, 457456, 'Jan 27,2014'),
(87, 96, 35, 457456, 'Jan 28,2014'),
(92, 60, 5, 457456, 'Feb 03,2014'),
(93, 68, 19, 457456, 'Feb 03,2014'),
(98, 60, 5, 457456, 'Feb 10,2014'),
(99, 68, 19, 457456, 'Feb 10,2014'),
(105, 60, 5, 457456, 'Feb 17,2014'),
(121, 104, 10, 457456, 'Feb 25,2014');So this is the real deal.
[ol][li] I run a query looking for the first money_id that was inserted only that 1 record, that is why LIMIT and ASC are used.[/li]
[li]There might be a chance that money_id is not found so result will not be 1, it will be 0[/li]
[li]So depending on nums of rows I run different blocks of codes, found record will always be either 1 or 0[/li][/ol]
Okay, we are still back to the code I gave you. Why does this not work for you?
[php]$result = $conn->query(“SELECT wID,wDate FROM debtor_weeksdate WHERE
money_id=’$money_id’ ORDER BY wID ASC” LIMIT 1);// or die(mysql_error());
if (count($result)){
// Do something - There was a result
}else{
//doesomething else - There was no result
}[/php]
[php] if (count($result)){
echo “there was result”;
}else{
echo “there was NO result”;
}[/php]
because if money_id was not found it will still echo ‘there was result’.
like i mentioned earlier it will always output 1 as a result no matter how many rows were found.
This is tested and works with your table and sample data.
[php]<?php
// Connection data (server_address, database, name, poassword)
$hostdb = ‘localhost’;
$dbname = ‘phphelp_money’;
$username = ‘root’;
$password = ‘’;
$money_id = 68;
try
{
$conn = new PDO(“mysql:host=localhost;dbname=$dbname”, $username, $password);
$stmt = $conn->prepare(“SELECT wID,wDate FROM debtor_weeksdate WHERE
money_id= ? ORDER BY wID ASC LIMIT 1”);
$stmt->execute(array(
$money_id
));
$result = $stmt->fetchAll();
if (count($result))
{
foreach ($result as $row)
{
print_r($row);
}
}
else
{
echo "No rows returned.";
}
}
catch (PDOException $e)
{
echo 'ERROR: ’ . $e->getMessage();
}
?>[/php]
that worked for me thank you very much