mysqli calling same data twice

Question

Can anyone explain a detailed answer as, am not able to found it on stackoverflow

<?php
  error_reporting(E_ALL);
  ini_set('display_errors', 1);
  ini_set('log_errors',1);
  mysqli_report(MYSQLI_REPORT_ALL);
   $link = mysqli_connect('localhost', 'root', '', 'test') or 
  die(mysqli_connect_error());
    $query2 = "Select * from qualifications";
    $result=mysqli_query($link,$query2)or die (mysqli_error($link));   
    ?>
 <form>
  <table>
     <tr>
        <td>
           <select name="short_term_degree" id="short_term_degree" >
              <option value="">Select</option>
              <?php
                 while ( $d=mysqli_fetch_assoc($result)) {
                  echo "<option 
  value='".$d['qual_id']."'>".$d['qualification']."</option>";
                  }
                  ?>
           </select>
        </td>
     </tr>
     <tr>
        <td>
           <select name="short_term_course" id="short_term_course" >
              <option value="">Select</option>
              <?php
                 while ( $d=mysqli_fetch_assoc($result)) {
                 echo "<option 
  value='".$d['qual_id']."'>".$d['qualification']."</option>";
                 }
                 ?>
           </select>
        </td>
       </tr>
    </table>
  </form>
</body>

Result I Got

<select name="short_term_degree" id="short_term_course">
 <option value="">Select</option>
 <option value="268">Graduate</option>
 <option value="269">Mtech</option>
 <option value="353">Bachelor of Econimics</option>
</select>
<select name="short_term_course" id="short_term_course">
 <option value="">Select</option>
</select>

The Second Select Box Doesn't show any data What's the reason.

why do i require to use again mysqli_query() before while OR mysqli_data_seek($result,0); for getting result in select box

you can't use result set twice in while loop . you can store it in array and use it wherever you want — JYoThI, May 03 '17 at 11:22
You could probably try and change the second `while ( $d` to something like `while ( $d2`. — Funk Forty Niner, May 03 '17 at 11:24
i already mention above that why do i require to use again mysqli_query() before while OR mysqli_data_seek($result,0); for getting result in select box **** why above fails can i get the explanation — newbie, May 03 '17 at 11:44
@Fred-ii- I have already used the function replied to Dilip Patel answer below , expecting an explanation why above one fails why this is happening — newbie, May 03 '17 at 12:02
when first while loop run the internal data pointer move ahead and reach the last data pointer . so while your trying to get data from that using second while loop . so there is no more data because .it's already reach last data pointer . this is what the reason for failure @newbie and this is what is explained my below answer — JYoThI, May 03 '17 at 12:08
Thanks @JyoThi For Explanation Is comment can be accepted as answer. — newbie, May 03 '17 at 12:18

score 2 · Answer 1 · answered May 03 '17 at 11:29

Don't write same code twice use like this

<?php
  error_reporting(E_ALL);
  ini_set('display_errors', 1);
  ini_set('log_errors',1);
  mysqli_report(MYSQLI_REPORT_ALL);
   $link = mysqli_connect('localhost', 'root', '', 'test') or 
  die(mysqli_connect_error());
    $query2 = "Select * from qualifications";
    $result=mysqli_query($link,$query2)or die (mysqli_error($link));  
    $options = "";
    while ( $d=mysqli_fetch_assoc($result)) {
                  $options .= "<option 
  value='".$d['qual_id']."'>".$d['qualification']."</option>";
                  } 
    ?>
 <form>
  <table>
     <tr>
        <td>
           <select name="short_term_degree" id="short_term_degree" >
              <option value="">Select</option>
              <?php
                 echo $options;
                  ?>
           </select>
        </td>
     </tr>
     <tr>
        <td>
           <select name="short_term_course" id="short_term_course" >
              <option value="">Select</option>
              <?php
                 echo $options;
                 ?>
           </select>
        </td>
       </tr>
    </table>
  </form>
</body>

You have made a valid point but am using function as this is going to be on multiple pages and was am just curious about the reason of failure — newbie, May 03 '17 at 12:50

JYoThI · Accepted Answer · 2017-05-03T12:11:27.113

1

Returns an array that corresponds to the fetched row and moves the internal data pointer ahead.

The internal data point is still at the end when you try to use your second while loop. You have done nothing to reset it.

You can move it back to the start with mysqli_data_seek($result, 0);

Update:

when first while loop run the internal data pointer move ahead and reach the last data pointer . so while your trying to get data from that using second while loop . so there is no more data because .it's already reach last data pointer .

edited May 03 '17 at 12:11

answered May 03 '17 at 11:29

JYoThI

11,977
1
11
26

they're using the mysqli_ api, not mysql_. Edit: You edited ;-) – Funk Forty Niner May 03 '17 at 11:29
I wonder why your answer got a downvote? and a few others. – Funk Forty Niner May 03 '17 at 11:34
I wonder why in 2017 PHP users are still bluntly writing mysqli_data_seek($result, 0); inbetween HTML tags as though it's still 1997 – Your Common Sense May 03 '17 at 11:45
Already mention in question why do i require mysqli_data_seek($result, 0)and $result= mysqli_query($link,$query2) above the while loop to get my result. I am accepting for an explanation for cause – newbie May 03 '17 at 11:55
@newbie *"Already mention in question don't want to use mysqli_data_seek"* - Yet you wrote this in your question *"why do i require to use again mysqli_query() before while OR mysqli_data_seek($result,0);"* --- You're contradicting yourself. – Funk Forty Niner May 03 '17 at 11:56
Thanks @JyoThi For Update accepting the updated answer – newbie May 03 '17 at 12:37

score -1 · Answer 3 · edited May 23 '17 at 12:02

-1

You can use mysqli_fetch_assoc twice in the following manner:-

How to go through mysql result twice?

edited May 23 '17 at 12:02

Community

1
1

answered May 03 '17 at 11:27

Mudit Gupta

21
2

mysqli calling same data twice

3 Answers3

Linked