PDO fetchAll only outputting one record

Question

With the code below, I am attempting to loop through data in my database and then output all of the records. The code is currently outputting only the first record. I thought that PDO's fetchAll function acted as a while loop?

What should I do to get all of my records to output?

try {   
    $con = new PDO('mysql:host='.$servername.';dbname=mb', $username, $password);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    //SQL Call
    $sql_project = "
      SELECT *, COUNT(solution) AS solution_count
      FROM projectslider
      WHERE solution = '". $page ."'
    ";

    if ($project_stmt = $con->prepare($sql_project)) {
        $project_stmt->execute();
        $project_rows = $project_stmt->fetchAll(PDO::FETCH_ASSOC);
        echo '<section id="solProj">';
        foreach ($project_rows as $project_row) {
            $solution_count = $project_row['solution_count'];
            $project_solution = $project_row['solution'];
            $project_img = $project_row['image'];
            $project_alt = $project_row['alt'];
            $project_img = '<img class="home-comment-profile-pic" src=" '. $project_img .'" alt="' . $project_alt .'">';
            if ($solution_count === 0) {
                echo 'No projects found.';
            } else {
                echo '<div class="projSlide">';
                echo $project_img;
                echo '</div>';

            }
        }
        echo '</section>';
    }
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Updated Code:

$sql_project_count = "
      SELECT *, COUNT(solution) AS solution_count
      FROM projectslider
      WHERE solution = '". $page ."'
    ";
    $sql_project = "
      SELECT *
      FROM projectslider
      WHERE solution = '". $page ."'
    ";
    if ($project_stmt_count = $con->prepare($sql_project_count)) {
        $project_stmt_count->execute();
        $project_rows_count = $project_stmt_count->fetchAll(PDO::FETCH_ASSOC);
        foreach ($project_rows_count as $project_row_count) {
            $solution_count = $project_row_count['solution_count'];
        }
    }
    //var_dump($solution_count);
    if ($project_stmt = $con->prepare($sql_project)) {
        $project_stmt->execute();
        $project_rows = $project_stmt->fetchAll(PDO::FETCH_ASSOC);
        echo '<section id="solProj">';
        foreach ($project_rows as $project_row) {
            $project_solution = $project_row['solution'];
            $project_img = $project_row['image'];
            $project_alt = $project_row['alt'];
            $project_img = '<img class="home-comment-profile-pic" src=" '. $project_img .'" alt="' . $project_alt .'">';
            if ($solution_count === 0) {
                echo 'No projects found.';
            } else {
                echo '<div class="projSlide">';
                echo $project_img;
                echo '</div>';

            }
        }
        echo '</section>';
    }

When you use an aggregate function, such as `COUNT()`, it lumps everything together unless you use a `GROUP BY` clause. — aynber, Apr 12 '18 at 13:00
@aynber How would I structure the `GROUP BY` clause for it to work? — Paul, Apr 12 '18 at 13:04
It depends on what you're looking for. If you want all of the records plus a count of how many there are, then either do another query or just count how many records it returns. If you want to get a count of how many rows have a specific value in a column or columns, then group by those specific column(s). — aynber, Apr 12 '18 at 13:07
@aynber This part of my query `WHERE solution = '". $page ."'`, I am checking what page the user is on. Then in my database I have records that indicate data that should be on specific pages. So, with the `COUNT()` function I am just trying to see if data exists on that particular page to populate. — Paul, Apr 12 '18 at 13:21
Then I would suggest dropping the count, and count how many results exist in `$project_rows` — aynber, Apr 12 '18 at 14:02
@aynber I changed this to two queries and believe I am getting it to work, but my if condition is not rendering the `echo 'No projects found.';`. I updated my question with the new code. Do you see why the first part of the `if-statement` would not be showing? — Paul, Apr 12 '18 at 14:29
It would never get that far. You have it inside of the `$project_rows` loop, so if it's inside the loop, the count is greater than 0. If it's 0, it would never enter that loop. — aynber, Apr 12 '18 at 16:06
@aynber How would I move the if statement out without breaking the code? — Paul, Apr 12 '18 at 18:46
Remove the count checks, and after `$project_rows = $project_stmt->fetchAll(PDO::FETCH_ASSOC);`, check for the count `if(count($project_rows) == 0) { show no rows found message } else { output your data}` — aynber, Apr 13 '18 at 12:21

PDO fetchAll only outputting one record

0 Answers0