mysqli_fetch_assoc($result) not show all of the results

Question

I am trying to access my query table using the php code below. Currently I have two entries in my mysql database. I am trying to use mysqli_fetch_assoc to print all the entries in my table into my webpage, but all the method I tried is either printing the second entry and not the first one. When I use a while loop while($row = mysqli_fetch_assoc($result), it prints the second entry instead of both entries. If i use print_r($posts); it will print the first entry and the second entry is not printed. I am new to php and trying to learn how to print all the entries in my database onto php page and I am not understanding what I am doing wrong. Any help would be great! Thanks!

$sql = 'SELECT * FROM myTable ORDER BY created_at';
$result = mysqli_query($conn, $sql);
$posts = mysqli_fetch_assoc($result);
while($row = mysqli_fetch_assoc($result)){
    print_r($row); 
}
//if I do this I see the first entry title only
echo 'testing title: '. $posts['title'];

mysqli_free_result($result);
mysqli_close($conn);

output:

Array ( [id] => 2 [title] => SecondTitle [content] => Lorem ipsum dolor sit amet, consectetur 
adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. [created_at] => 
2020-07-17 12:32:04 ) testing title: First Title

output from print_r($posts):

Array ( [id] => 1 [title] => First Title [content] => First Content. [created_at] => 2020-07-17 
12:31:42 )

in tag:

<div class="row">
    <?php foreach ($posts as $post): ?>
        <div>
            <h1><?php echo $post['title'] ?></h1>
            <h3><?php echo $post['created_at'] ?></h3>
            <p> <?php echo $post['content'] ?></p>
        </div>
    <?php endforeach; ?>
</div>

output of the divs is for some reason:

1
1
1

F
F
F

F
F
F

2
2
2

Not sure if these info helps but if I used mysqli_fetch_all instead of mysqli_fetch_assoc the file crashes, not sure if it something to do with the database or something else. PHP version 7.3.6

$posts = mysqli_fetch_all($result, MYSQLI_ASSOC);

Answer 1

each call of mysqli_fetch_assoc() of a mysqli result, return an assoc array of the next result line. for example: you have table named user have 3 records:

id          name

1           Mark
2           Sebastian
3           Smith

after select all of these records:

$result = mysqli_query("SELECT * FROM user");

the first fetching will return assoc array of the first result line

$posts = mysqli_fetch_assoc($result);
// $posts["id] = 1 -- $posts["id] = "Mark"

the seconde call of mysqli_fetch_assoc of the $result will return assoc array of the seconde result line:

$posts = mysqli_fetch_assoc($result);
    // $posts["id] = 2 -- $posts["id] = "Sebastian"

so each call, will return the next result line until there is no record, the function will return null. document

so if you want your code work as you want i suggest you to delete the first mysqli_fetch_assoc call that out of the while loop:

// delete this line
$posts = mysqli_fetch_assoc($result);