Why I can't display same result twice using mysqli_fetch_assoc?

Question

I am displaying some results under one of my pages. But while I am trying to get the same result two times under the same page, it is not showing in the desired way. I have included the function file and called the function at first. Then I tried to display the same result twice. But only the first result is displaying, second one is not. Here's the code:

require 'functions.php';
$query_result=select_all_published_category();

<div class="control-group">
        <label class="control-label">Parent Category Name</label>
        <div class="controls">
            <select name="category_id">

                <?php while($cat_info=mysqli_fetch_assoc($query_result)) {?>
                <option value="<?php echo $cat_info['category_id']; ?>"><?php echo $cat_info['category_name']; ?></option>
                <?php }?>

            </select>
        </div>
</div>

<div class="control-group">
        <label class="control-label">Parent Category Name 2</label>
        <div class="controls">
            <select name="category_id2">

                <?php while($cat_info_two=mysqli_fetch_assoc($query_result)) {?>
                <option value="<?php echo $cat_info_two['category_id']; ?>"><?php echo $cat_info_two['category_name']; ?></option>
                <?php }?>

            </select>
        </div>
</div>

Can anyone help me out? Thanks

`mysqli_fetch_assoc` fetches a row from the result, actually consuming it (it is removed from result, allowing you to do a `while` without infinite loop). At the end of the loop, result is empty. If you want to display the same thing twice, build your html in a unique loop before, in a variable, and you can display it twice by displaying the var — Kaddath, Nov 22 '17 at 13:20

Alive to die - Anant · Accepted Answer · 2023-05-10T05:05:42.857

According to mysqli_fetch_assoc manual

Returns an associative array of strings representing the fetched row in the result set, where each key in the array represents the name of one of the result set's columns or NULL if there are no more rows in resultset.

So after first iteration(using while) it's get empty.So you can't do second-iteration directly.

Solution: Create a variable first, and assign all data to that variable.Now use it as many time as you want

<?php
require 'functions.php';
$query_result=select_all_published_category();
$category = []; //create array
while($cat_info=mysqli_fetch_assoc($query_result)) {
    $category[] = $cat_info; //assign whole values to array
}
?>

<div class="control-group">
        <label class="control-label">Parent Category Name</label>
        <div class="controls">
            <select name="category_id">

                <?php foreach($category as $cat){?>
                    <option value="<?php echo $cat['category_id']; ?>"><?php echo $cat['category_name']; ?></option>
                <?php }?>

            </select>
        </div>
</div>

<div class="control-group">
        <label class="control-label">Parent Category Name 2</label>
        <div class="controls">
            <select name="category_id2">
                <?php foreach($category as $cat){?>
                    <option value="<?php echo $cat['category_id']; ?>"><?php echo $cat['category_name']; ?></option>
                <?php }?>
            </select>
        </div>
</div>

Note: You can use mysqli_fetch_all($query_result, MYSQLI_FETCH_ASSOC); to grab all records in one go and then the while() loop will not be required.

Thanks. It helps. But how I can take $cat_info['category_name'] also with category_id and display it within the loop to show the category name? — Gamer, Nov 22 '17 at 14:26
@Gamer Sorry for late response. I have update my code. Please check now(check while loop and foreach loop changes) — Alive to die - Anant, Nov 22 '17 at 16:28
Thanks!!! You just saved my time and solved my problem. That's why I always love STACKOVERFLOW — Gamer, Nov 22 '17 at 18:20
@Alivetodie-Anant Why do you do while and assign to an array every time? You should either push the array elements or just assign it once. — S. W. G., May 08 '23 at 17:11
@S.W.G. I have given an answer according to OP's code regarding what he did wrong and what corrections is required in his code to make it work. Yes, it's true that we can fetch all the results in one-go. I will ad that as a note. — Alive to die - Anant, May 10 '23 at 05:01

score 2 · Answer 2 · answered Nov 22 '17 at 13:21

Look at the description of mysqli_fetch_assoc in the manual;

Returns an associative array of strings representing the fetched row in the result set, where each key in the array represents the name of one of the result set's columns or NULL if there are no more rows in resultset.

When you try to go over it a second time, you are already at the end, so it just returns NULL.

If you keep reading down that page you will see:

mysqli_data_seek() - Adjusts the result pointer to an arbitrary row in the result

… which will let you set the pointer back to the beginning (0) so you and loop over it from the start.

Why I can't display same result twice using mysqli_fetch_assoc?

2 Answers2

Linked

Related