I am having an issue with a SQL call I have. Ultimately, I am running an AJAX call from a search box and returning the results to screen. I limit the results to 5 displayed but if the total number of results exceed 5, provide link to view all elsewhere. I thought by simply adding a COUNT() clause in this would take care of the issue and while COUNT() does return the full number of matches correctly, it kills my while statement when looping through and only returns the first result. When I remove count(), everything works.

CODE

<?php

if(isset($_POST['word']) && $_POST['word'] != "") 
    {
        $q=$_POST['word'];

            try {
                $db = new PDO('mysql:host=localhost;dbname=DB', 'USER', 'PW');
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $db->beginTransaction();

                $stmt = $db->prepare("SELECT COUNT(ID) as counter, ID, fname, lname FROM tablename WHERE fname LIKE ? or lname LIKE ? ORDER BY ID LIMIT 5");

                $stmt->execute(array('%'.$q.'%', '%'.$q.'%'));

                $foundrows = $db->query("SELECT FOUND_ROWS()")->fetchColumn();

                $db->commit();
            }

            catch (PDOException $e)
            {
                echo "There was a system DB error. <br>".$e->getMessage();          
            }               


        if(isset($foundrows) && $foundrows == 0) {
            echo "<div class='display_box' align='left'>
                No matching results found</div>";
        } else {        

                while($row = $stmt->fetch()) {

                    $counter = $row['counter'];

                    $id = $row['ID'];
                    $fname = $row['fname'];
                    $lname = $row['lname'];

            ?>
                    <a href="#" style="text-decoration:none; color:#000;">
                    <div class="display_box" align="left">

                    <?php echo $fname; ?>&nbsp;<?php echo $lname; ?><br/>
                    </div></a>

<?php     
                }

                if(isset($counter) && $counte开发者_C百科r > 5) {
?>                  
                    <a href="#" style="text-decoration:none; color:#000;">
                    <div class="display_box" align="left">
                    <?php echo (5-$counter)." additional matches found."; ?>
                    </div></a>
<?php                                   
                }
        }
    }   
?>

COUNT() is an aggregate operation on the table that returns just one row, which is why you're not getting 5 results as you expect from your query when including it.

One way you can get what you want is by using a SUBSELECT clause in your query:

SELECT (SELECT COUNT(ID) FROM tablename) as counter, ID, fname, lname FROM tablename WHERE fname LIKE ? or lname LIKE ? ORDER BY ID LIMIT 5

This will return the 5 results you expect, with a full count of the rows in the table as a column in each row.

if the total number of results exceed 5, provide link to view all elsewhere

To determine the number of rows that would have been returned had there been no LIMIT clause, use SQL_CALC_FOUND_ROWS

SELECT SQL_CALC_FOUND_ROWS ID, fname, lname FROM tablename WHERE fname LIKE ? or lname LIKE ? ORDER BY ID LIMIT 5

Then, your call to SELECT FOUND_ROWS() will return the number had there been no limit. Note: No other SQL calls should occur between the SELECT SQL_CALC_FOUND_ROWS and the SELECT FOUND_ROWS() statement or the value will not be correct.

That has nothing to do with PDO or PHP. If you specify an aggregate function (like COUNT(), MAX(), ...) it needs to group the rows, because you can not count a single row (of course). If you do not specify GROUP BY it will collapse the whole result set.