By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,069 Members | 1,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,069 IT Pros & Developers. It's quick & easy.

Why WHILE LOOP Fails After One Execution ?

P: 2
Hiya,

Php gurus, can you tell me is this query ok to get row count ? It works here:

Expand|Select|Wrap|Line Numbers
  1. $query_2 = "SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page";
  2.  
The rows_count() function uses the above query.
Then it forwards you to the fetch_rows() function that fetches the rows and displays them in a pagination way to you.

When you click any page numbers on the pagination section, like page 2, then the fetch_rows() is supposed to fetch the relevant rows again for page 2.
Rows fetching is done with query:

Expand|Select|Wrap|Line Numbers
  1. $query_2 = "SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page";
  2.  
It displays matching rows using LOOP:
Expand|Select|Wrap|Line Numbers
  1. while($row = mysqli_fetch_array($result_2,MYSQLI_ASSOC))
  2.  
Is not loop code ok ? I got it from php manual.

MY DIFFICULTY
The fetch_rows() or $query_2 fails to fetch any matching rows after page 1 no matter what page you click. Be it page 3, page 4, etc on paginating section.
However, it manages to fetch matching rows for page 1. Only page 1. That is the problem. This while loop does not trigger on any other pages apart from page 1.

Code is set to display 1 row per page in DEV MODE.
Since there are 5 matching rows, rows are supposed to be spread across many pages via pagination. Basic logic.

My problem, only page 1 manages to fetch relevant rows and display them. But all other pages (2,3,4) etc. fail to fetch any rows or display them. Why is that ? I must learn from you NOW. 3-4 nights going in circles!

You can easily see which lines I am struggling with if you put your eyes on the 'CAPITALISED' comments.

Expand|Select|Wrap|Line Numbers
  1. //Do following if "Search" button clicked.
  2.     if($_SERVER['REQUEST_METHOD'] === 'POST')
  3.     {echo __LINE__; echo "<br>";//DELETE
  4.         //Do following if "Search" button clicked.
  5.         if(isset($_POST['search']))
  6.         {echo __LINE__; echo "<br>";//DELETE
  7.             rows_count(); //This function will forward script flow to fetch_rows() before halting the script.
  8.             die();
  9.         }
  10.     }
  11.     echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS FAILS TO ECHO. IT IS LINE: 24.
  12.     //Do following if "Search" button not clicked but pagination numbered links are clicked. Eg Page 1, 2, 3, etc..
  13.     fetch_rows(); //On PAGINATION PAGE 2, THIS FUNCTION IS NOT GETTING TRIGGERED! WHY ?
  14.     echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 205.
  15.  
Here is the full code. Context:


Expand|Select|Wrap|Line Numbers
  1. <?php
  2. error_reporting(E_ALL);
  3. ?>
  4.  
  5. <!DOCTYPE HTML">
  6. <html>
  7.  
  8. <head>
  9. <meta name="viewport" content="width-device=width, initial-scale=1">
  10. </head>
  11. <body>
  12.  
  13. <?php
  14. session_start();
  15.  
  16. if(!isset($_GET['query_type']) && empty($_GET['query_type']))
  17. {
  18.     die("Invalid Query!");
  19. }
  20. else
  21. {
  22.     $_SESSION['query_type'] = $_GET['query_type']; echo __LINE__; echo "<br>";//DELETE
  23. }
  24. echo __LINE__; echo "<br>";//DELETE
  25.  
  26. if(!isset($_GET['form_type']) && empty($_GET['form_type']))
  27. {
  28.     die("Invalid Form!");
  29. }
  30. else
  31. {
  32.     $_SESSION['form_type'] = $_GET['form_type']; echo __LINE__; echo "<br>";//DELETE
  33.  
  34.     if(!function_exists($_SESSION['form_type']))
  35.     {
  36.         die("Invalid Form!");
  37.     }
  38.     else
  39.     {echo __LINE__; echo "<br>";//DELETE
  40.         if(!session_id() || !isset($_SESSION['form_step']) || $_SESSION['form_step'] != 'end')
  41.         {
  42.             $_SESSION['form_step'] = 'start'; echo __LINE__; echo "<br>";//DELETE
  43.             $_SESSION['form_type']();
  44.         }
  45.     }
  46. }
  47.  
  48. //FUNCTIONS START FROM HERE
  49. function search()
  50. {echo __LINE__; echo "<br>";//DELETE
  51.     function rows_count()
  52.     {
  53.         //Connect to Database. (DB_SERVER, BD_USERNAME, DB_PASSWORD, DB_NAME).
  54.         $conn = mysqli_connect("localhost","root","","powerpage");
  55.         $conn->set_charset('utf8mb4'); //Always set Charset.
  56.  
  57.         if($conn === false)
  58.         {
  59.             die("ERROR: Connection Error!. " . mysqli_connect_error());
  60.         }
  61.  
  62.         $query_1 = "SELECT COUNT(id) FROM users WHERE first_name = ? AND marital_status = ?";
  63.         $stmt_1 = mysqli_stmt_init($conn);
  64.         if(mysqli_stmt_prepare($stmt_1,$query_1))
  65.         {
  66.             mysqli_stmt_bind_param($stmt_1,"ss",$_POST["first_name"],$_POST["marital_status"]);
  67.             mysqli_stmt_execute($stmt_1);
  68.             $result_1 = mysqli_stmt_bind_result($stmt_1,$row_count);
  69.             mysqli_stmt_fetch($stmt_1);
  70.             $_SESSION['row_count'] = $row_count;
  71.             echo __LINE__; echo "<br>";//DELETE
  72.             $_SESSION['form_step'] = 'end';
  73.             fetch_rows();
  74.         }
  75.     }
  76.  
  77.     function fetch_rows()
  78.     {    echo __LINE__; echo "<br>";//DELETE
  79.         $form_step = $_GET['form_step'];
  80.  
  81.         $page_number = $_GET['page'];
  82.         $result_per_page = $_GET['page_limit'];
  83.         $offset = (($page_number * $result_per_page) - $result_per_page); //Offset (Row Number that 'Starts' on page).
  84.         $last_row_on_page = ($page_number * $result_per_page); //Max Result (Row Number that 'Ends' on page).
  85.         $previous_page = $page_number-1;
  86.         $next_page = $page_number+1;
  87.  
  88.         echo "Row Start: $offset";echo "<br>";
  89.         echo "Row End: $last_row_on_page";echo "<br>";
  90.  
  91.         //Connect to Database. (DB_SERVER, BD_USERNAME, DB_PASSWORD, DB_NAME).
  92.         $conn = mysqli_connect("localhost","root","","powerpage");
  93.         $conn->set_charset('utf8mb4'); //Always set Charset.
  94.  
  95.         if($conn === false)
  96.         {
  97.             die("ERROR: Connection Error!. " . mysqli_connect_error());
  98.         }
  99.  
  100.         $query_2 = "SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page";
  101.         $stmt_2 = mysqli_stmt_init($conn);
  102.         if(mysqli_stmt_prepare($stmt_2,$query_2))
  103.         {echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 103.
  104.             mysqli_stmt_bind_param($stmt_2,"ss",$_POST["first_name"],$_POST["marital_status"]);
  105.             mysqli_stmt_execute($stmt_2);
  106.             $result_2 = mysqli_stmt_get_result($stmt_2);
  107.             if(!$result_2)
  108.             {
  109.                 //Close Connection.
  110.                 mysqli_close($conn);
  111.                 die("<pre>2c. Statement Fetching failed!</pre>");
  112.             }
  113.             else
  114.             {echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 114.
  115.                 //Grab total number of pages to paginate.
  116.                 $row_count = $_SESSION['row_count'];
  117.                 //$total_pages = ceil($result_1/$result_per_page);
  118.                 $total_pages = ceil($row_count/$result_per_page);
  119.  
  120.                 echo "TOTAL PAGES: $total_pages<br><br>";
  121.  
  122.                 while($row = mysqli_fetch_array($result_2,MYSQLI_ASSOC))//On PAGE 2, PHP IGNORING THIS AND BYPASSING THIS WHOLE WHILE LOOP ON PAGE 2. IT IS LINE: 122. 
  123.                 {echo __LINE__; echo "<br>";//On PAGE 2, THIS FAILS TO ECHO. IT IS LINE: 123. PHP IGNORING IT BYPASSING IT ON PAGE 2.
  124.                     //Retrieve Values.
  125.                     $id = $row["id"];
  126.                     $first_name = $row["first_name"];
  127.                     $middle_name = $row["middle_name"];
  128.                     $surname = $row["surname"];
  129.                     $gender = $row["gender"];
  130.                     $marital_status = $row["marital_status"];
  131.                     $working_status = $row["working_status"];
  132.  
  133.                     echo "Id: $id<br>";
  134.                     echo "First Name: $first_name<br>";
  135.                     echo "Middle Name: $middle_name<br>";
  136.                     echo "Surname: $surname<br>";
  137.                     echo "Gender: $gender<br>";
  138.                     echo "Marital Status: $marital_status<br>";
  139.                     echo "Working Status: $working_status<br>";
  140.                     echo "<br>";
  141.                     echo "<br>";
  142.  
  143.                     $i = 1;
  144.                     while($i<=$total_pages)
  145.                     {
  146.                         if($i<$total_pages)
  147.                         {
  148.                             echo "<a href='http://localhost/power.page/pagination_test_simple_WORKING_ON_NOW.php?form_type=";?><?php echo $_SESSION['form_type'];?>&query_type=<?php echo $_SESSION['query_type'];?>&form_step=end&page_limit=1&page=<?php echo $i;?>'><?php echo " $i ";?></a><?php 
  149.                         }
  150.                         elseif($i==$page_number)
  151.                         {
  152.                             echo "<a href='http://localhost/power.page/pagination_test_simple_WORKING_ON_NOW.php?form_type=";?><?php echo $_SESSION['form_type'];?>&query_type=<?php echo $_SESSION['query_type'];?>&form_step=end&page_limit=1&page=<?php echo $i;?>'><?php echo "<b> $i </b>";?></a><?php 
  153.                         }
  154.  
  155.                         $i++;
  156.                     }
  157.                     if($page_number>$total_pages)
  158.                     {
  159.                         echo "<a href='http://localhost/power.page/pagination_test_simple_WORKING_ON_NOW.php?form_type=";?><?php echo $_SESSION['form_type'];?>&query_type=<?php echo $_SESSION['query_type'];?>&form_step=end&page_limit=1&page=<?php echo $previous_page;?>'><?php echo "<b> Previous </b>";?></a><?php 
  160.                     }
  161.                 }
  162.             }
  163.         } 
  164.         $_SESSION['form_step'] = 'end';
  165.     }
  166.     ?>
  167.  
  168.     <form action="<?php echo $_SERVER['PHP_SELF'];?>?form_type=<?php echo $_SESSION['form_type'];?>&query_type=<?php echo $_SESSION['query_type'];?>&form_step=end&page_limit=1&page=1" method='post' enctype='plain/text'>
  169.     <?php
  170.  
  171.     //Added '*' (asterisk) to indicate the 'Text Field' is a 'required' one.
  172.     echo "<label for=\"first_name\">First Name *:</label>
  173.     <input type=\"text\" name=\"first_name\" placeholder=\"First Name\" value = \"\">";?>
  174.     <br>
  175.     <?php
  176.     echo "<label for=\"marital_status\">Marital Status *:</label>";
  177.     echo "<select name=\"marital_status\">";
  178.     echo "<option value=\"single\">Single</option>";
  179.     echo "<option value=\"married\">Married</option>";
  180.     echo "</select>";
  181.     echo "<br>";
  182.     ?>
  183.     <input type="submit" name="search" value="Search">
  184.     <?php
  185.     //$current_function = __FUNCTION__;
  186.     //echo $current_function;
  187.  
  188.     //Do following if "Search" button clicked.
  189.     if($_SERVER['REQUEST_METHOD'] === 'POST')
  190.     {echo __LINE__; echo "<br>";//DELETE
  191.         //Do following if "Search" button clicked.
  192.         if(isset($_POST['search']))
  193.         {echo __LINE__; echo "<br>";//DELETE
  194.             rows_count(); //This function will forward script flow to fetch_rows() before halting the script.
  195.             die();
  196.         }
  197.     }
  198.     echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS FAILS TO ECHO. IT IS LINE: 24.
  199.     //Do following if "Search" button not clicked but pagination numbered links are clicked. Eg Page 1, 2, 3, etc..
  200.     fetch_rows(); //On PAGINATION PAGE 2, THIS FUNCTION IS NOT GETTING TRIGGERED! WHY ?
  201.     echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 205.
  202. }
  203.  
  204. ?>
  205.  
What is wrong ? Why is fetch_rows() or $query_2 failing to fetch the matching rows for pages beyond page 1 ?

ECHOES
Before clicking the SEARCH button, I get echoed these line numbers as expected:
Expand|Select|Wrap|Line Numbers
  1. 22
  2. 24
  3. 32
  4. 39
  5. 42
  6. 50
  7.  
After clicking the SEARCH button I get these echoed as expected:
Expand|Select|Wrap|Line Numbers
  1. 193
  2. 71
  3. 78
  4. Row Start: 0
  5. Row End: 1
  6. 103
  7. 114
  8. TOTAL PAGES: 5
  9.  
  10. 123
  11.  
After clicking the link for 'page 2' on pagination section, I get echoed the same line numbers I get echoed before clicking the SEARCH button as if everything is starting all over with a new query (when not). That is not supposed to happen.

I reckon line 200 is not taking action:

Expand|Select|Wrap|Line Numbers
  1. fetch_rows(); //On PAGINATION PAGE 2, THIS FUNCTION IS NOT GETTING TRIGGERED! WHY ? IT IS LINE: 200. MAIN ISSUE HERE, I SUSPECT.
  2.  
PS:
I still on OOP and mysqli. Not on pdo or oop yet. So, kindly show samples to that level, if you must.
2 Weeks Ago #1
Share this Question
Share on Google+
3 Replies


100+
P: 134
The conditional expression of the "while" statement uses the comparison operator like the if statement.
Example "while(x == 10)", "while(y<15)"
1 Week Ago #2

P: 2
SioSio,

I just realized that, $_SESSION['row_count'] = 5, when I click the SEARCH button. 5 matching rows found.

Then when I click PAGE 2 on pagination, the $_SESSION['row_count'] = 0, why is that ? It should stay 5. i am not overwriting the variable value either.

This is the reason why, when I click PAGE 2 or PAGE 3 on the PAGINATION section, I see zero results or rows shown. No rows get shown beyond page 1.

If I can findout why the '$_SESSION['row_count'] = 5' auto becomes $_SESSION['row_count'] = 0, then mystery solved.

Look, after clicking the SEARCH button, this part of code yields $_SESSION['row_count'] = 5. So far so good.
Expand|Select|Wrap|Line Numbers
  1.  
  2. $query_1 = "SELECT COUNT(id) FROM users WHERE first_name = ? AND marital_status = ?";
  3.         $stmt_1 = mysqli_stmt_init($conn);
  4.         if(mysqli_stmt_prepare($stmt_1,$query_1))
  5.         {
  6.             mysqli_stmt_bind_param($stmt_1,"ss",$_POST["first_name"],$_POST["marital_status"]);
  7.             mysqli_stmt_execute($stmt_1);
  8.             $result_1 = mysqli_stmt_bind_result($stmt_1,$row_count);
  9.             mysqli_stmt_fetch($stmt_1);
  10.             $_SESSION['row_count'] = $row_count;
  11.  
  12.  
I get shown matching rows on PAGE 1. Since I set it to display 1 row per page, I am shown 1 matching row. So far, so good.

Now, when I click PAGE 2 on the PAGINATION section, I expect to see the 2nd matching row, but "$_SESSION['row_count'] = 5" switches to "$_SESSION['row_count'] = 0" and so no matching rows get shown. Why the switching of values from '5' to '0' when I click PAGE 2 ?

This illegal switching ruins this following query that runs when I click PAGE 2 or any PAGE (eg PAGE 3) beyond PAGE 1:

Expand|Select|Wrap|Line Numbers
  1.  
  2. $row_count = $_SESSION['row_count'];
  3.  //$total_pages = ceil($result_1/$result_per_page);
  4. $total_pages = ceil($row_count/$result_per_page);
  5.  
  6.  

Context:

Expand|Select|Wrap|Line Numbers
  1.  
  2. $query_2 = "SELECT id,first_name,middle_name,surname,gender,marital_status,working_status FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page";
  3.         echo "$query_2<br>";
  4.         $stmt_2 = mysqli_stmt_init($conn);
  5.         if(mysqli_stmt_prepare($stmt_2,$query_2))
  6.         {echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 111.
  7.             mysqli_stmt_bind_param($stmt_2,"ss",$_POST["first_name"],$_POST["marital_status"]);
  8.             mysqli_stmt_execute($stmt_2);
  9.             $result_2 = mysqli_stmt_get_result($stmt_2);
  10.             echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 114.
  11.             //Grab total number of pages to paginate.
  12.             $row_count = $_SESSION['row_count'];
  13.             //$total_pages = ceil($result_1/$result_per_page);
  14.             $total_pages = ceil($row_count/$result_per_page);
  15.  
  16.  

Do check my original post's code to see if you can figure-out why the "$_SESSION['row_count']" switches from "5" to "0".


Thanks
1 Week Ago #3

100+
P: 134
Are other session variables retained when the page changes?
1 Week Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.