php – Why I can't display same result twice using mysqli_fetch_assoc?-ThrowExceptions

Exception or error:

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

How to solve:

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> 

Answer:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *