mysqli_fetch_assoc not showing all results

Question

I cannot figure out why mysqli_fetch_assoc shows only one result instead of five. I doubled checked the database and there are 5 entries for sure..

In the case if I remove second mysqli_fetch_assoc then shown are all results.

Please note: connection include is above..

Any advice and suggestions will be greatly appreciated... Thank you!

<table class="table table-bordered table-hover">
    thead>
         <tr>
            <th>Id</th>
            <th>Author</th>
            <th>Title</th>
            <th>Category</th>
            <th>Status</th>
            <th>Image</th>
            <th>Tags</th>
            <th>Comments</th>
            <th>Date</th>
        </tr>
 </thead>
       <tbody>
            <?php 
               if(isset($_GET['delete'])){
                     $post_id = $_GET['delete'];   
                     $query = "DELETE FROM posts  WHERE post_id='$post_id'";
                     $delete_post_query = mysqli_query($con, $query);

            }
               $query = mysqli_query($con, "SELECT * FROM posts");
           while($row = mysqli_fetch_assoc($query)) {
                     $post_id = $row['post_id'];
                     $post_category_id = $row['post_category_id']; 
                     $post_title = $row['post_title']; 
                     $post_author = $row['post_author'];
                     $post_status = $row['post_status'];
                     $post_image = $row['post_image'];
                     $post_content = $row['post_content'];
                     $post_tags = $row['post_tags'];
                     $post_comment_count = $row['post_comment_count'];
                     $post_date = $row['post_date'];


                                    echo "<tr>";
                                    echo "<td>$post_id</td>";
                                    echo "<td>$post_author</td>";
                                    echo "<td>$post_title</td>";

                                $query = mysqli_query($con, "SELECT * FROM categories WHERE cat_id='$post_category_id'");

                                while($row = mysqli_fetch_assoc($query)) {

                                    $cat_id = $row['cat_id'];    
                                    $cat_title = $row['cat_title'];

                                    echo "<td>$cat_title</td>";

                                }

                                    echo "<td>$post_status</td>";
                                    echo "<td><img src='../images/$post_image' alt='image' width='100'></td>";
                                    echo "<td>$post_tags</td>";
                                    echo "<td>$post_comment_count</td>";
                                    echo "<td>$post_date</td>";
                                    echo "<td><a href='posts.php?delete=$post_id'>Delete</a></td>";
                                    echo "<td><a href='posts.php?source=edit_post&p_id=$post_id'>Edit</a></td>";
                                    echo "</tr>";

                                }

                                ?>

                            </tr>
                        </tbody>
                    </table>

**Danger**: You are **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that you need to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Sep 28 '17 at 06:45
**Never** delete records via a `GET` request. See https://thedailywtf.com/articles/WellIntentioned-Destruction — Phil, Sep 28 '17 at 06:47
That's like building a house and then fixing the problems with the foundations at the end. You end up knocking the walls over to get to the problems you built at the start. Then you just have to rebuild the walls pointlessly. Just do it right in the first place. — Quentin, Sep 28 '17 at 06:48
@MattDomer that has to be one of the worst attitudes frequently seen on StackOverflow — Phil, Sep 28 '17 at 06:48
@MattDomer I recommend you to learn prepared statements and implement them directly. Its actually two lines of code per statement you'll need to write more, but it doesn't really take more time. So you don't have to go over your whole code at the end. — Twinfriends, Sep 28 '17 at 06:49
You edited the question to put **RESOLVED** at the top. That isn't how Stackoverflow works. Please read [How does accepting an answer work?](http://meta.stackoverflow.com/questions/5234/how-does-accepting-an-answer-work) — Quentin, Sep 28 '17 at 06:49
@MattDomer why what? Why is it a bad attitude? See [Quentin's comment](https://stackoverflow.com/questions/46462592/mysqli-fetch-assoc-not-showing-all-results#comment79880019_46462592). Why should you not delete records via `GET` requests? Read the article — Phil, Sep 28 '17 at 06:50

Quentin · Answer 1 · 2017-09-28T06:50:47.233

4

You are nesting database queries and are reusing the same variable names.

The first time around the loop you make the nested query and overwrite your $query variable.

You finish your inner loop and then try to read the next item from the inner loops query (which you just finished looping over).

Don't recycle variable names.

Also consider using JOINs instead of multiple queries.

edited Sep 28 '17 at 06:50

answered Sep 28 '17 at 06:47

Quentin

914,110
126
1,211
1,335

mysqli_fetch_assoc not showing all results

1 Answers1

Linked