Why WHILE LOOP Fails After One Execution ?

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • 2020
    New Member
    • Jun 2020
    • 2
    #1

    Why WHILE LOOP Fails After One Execution ?

    Hiya,

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

    Code:
    $query_2 = "SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page";
    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:

    Code:
    $query_2 = "SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page";
    It displays matching rows using LOOP:
    Code:
    while($row = mysqli_fetch_array($result_2,MYSQLI_ASSOC))
    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.

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


    Code:
    <?php
error_reporting(E_ALL);
?>

<!DOCTYPE HTML">
<html>

<head>
<meta name="viewport" content="width-device=width, initial-scale=1">
</head>
<body>

<?php
session_start();

if(!isset($_GET['query_type']) && empty($_GET['query_type']))
{
	die("Invalid Query!");
}
else
{
	$_SESSION['query_type'] = $_GET['query_type']; echo __LINE__; echo "<br>";//DELETE
}
echo __LINE__; echo "<br>";//DELETE

if(!isset($_GET['form_type']) && empty($_GET['form_type']))
{
	die("Invalid Form!");
}
else
{
	$_SESSION['form_type'] = $_GET['form_type']; echo __LINE__; echo "<br>";//DELETE
	
	if(!function_exists($_SESSION['form_type']))
	{
		die("Invalid Form!");
	}
	else
	{echo __LINE__; echo "<br>";//DELETE
		if(!session_id() || !isset($_SESSION['form_step']) || $_SESSION['form_step'] != 'end')
		{
			$_SESSION['form_step'] = 'start'; echo __LINE__; echo "<br>";//DELETE
			$_SESSION['form_type']();
		}
	}
}
		
//FUNCTIONS START FROM HERE
function search()
{echo __LINE__; echo "<br>";//DELETE
	function rows_count()
	{
		//Connect to Database. (DB_SERVER, BD_USERNAME, DB_PASSWORD, DB_NAME).
		$conn = mysqli_connect("localhost","root","","powerpage");
		$conn->set_charset('utf8mb4'); //Always set Charset.
		
		if($conn === false)
		{
			die("ERROR: Connection Error!. " . mysqli_connect_error());
		}
		
		$query_1 = "SELECT COUNT(id) FROM users WHERE first_name = ? AND marital_status = ?";
		$stmt_1 = mysqli_stmt_init($conn);
		if(mysqli_stmt_prepare($stmt_1,$query_1))
		{
			mysqli_stmt_bind_param($stmt_1,"ss",$_POST["first_name"],$_POST["marital_status"]);
			mysqli_stmt_execute($stmt_1);
			$result_1 = mysqli_stmt_bind_result($stmt_1,$row_count);
			mysqli_stmt_fetch($stmt_1);
			$_SESSION['row_count'] = $row_count;
			echo __LINE__; echo "<br>";//DELETE
			$_SESSION['form_step'] = 'end';
			fetch_rows();
		}
	}

	function fetch_rows()
	{	echo __LINE__; echo "<br>";//DELETE
		$form_step = $_GET['form_step'];
		
		$page_number = $_GET['page'];
		$result_per_page = $_GET['page_limit'];
		$offset = (($page_number * $result_per_page) - $result_per_page); //Offset (Row Number that 'Starts' on page).
		$last_row_on_page = ($page_number * $result_per_page); //Max Result (Row Number that 'Ends' on page).
		$previous_page = $page_number-1;
		$next_page = $page_number+1;
		
		echo "Row Start: $offset";echo "<br>";
		echo "Row End: $last_row_on_page";echo "<br>";
		
		//Connect to Database. (DB_SERVER, BD_USERNAME, DB_PASSWORD, DB_NAME).
		$conn = mysqli_connect("localhost","root","","powerpage");
		$conn->set_charset('utf8mb4'); //Always set Charset.

		if($conn === false)
		{
			die("ERROR: Connection Error!. " . mysqli_connect_error());
		}

		$query_2 = "SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page";
		$stmt_2 = mysqli_stmt_init($conn);
		if(mysqli_stmt_prepare($stmt_2,$query_2))
		{echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 103.
			mysqli_stmt_bind_param($stmt_2,"ss",$_POST["first_name"],$_POST["marital_status"]);
			mysqli_stmt_execute($stmt_2);
			$result_2 = mysqli_stmt_get_result($stmt_2);
			if(!$result_2)
			{
				//Close Connection.
				mysqli_close($conn);
				die("<pre>2c. Statement Fetching failed!</pre>");
			}
			else
			{echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 114.
				//Grab total number of pages to paginate.
				$row_count = $_SESSION['row_count'];
				//$total_pages = ceil($result_1/$result_per_page);
				$total_pages = ceil($row_count/$result_per_page);
				
				echo "TOTAL PAGES: $total_pages<br><br>";
				
				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. 
				{echo __LINE__; echo "<br>";//On PAGE 2, THIS FAILS TO ECHO. IT IS LINE: 123. PHP IGNORING IT BYPASSING IT ON PAGE 2.
					//Retrieve Values.
					$id = $row["id"];
					$first_name = $row["first_name"];
					$middle_name = $row["middle_name"];
					$surname = $row["surname"];
					$gender = $row["gender"];
					$marital_status = $row["marital_status"];
					$working_status = $row["working_status"];
					
					echo "Id: $id<br>";
					echo "First Name: $first_name<br>";
					echo "Middle Name: $middle_name<br>";
					echo "Surname: $surname<br>";
					echo "Gender: $gender<br>";
					echo "Marital Status: $marital_status<br>";
					echo "Working Status: $working_status<br>";
					echo "<br>";
					echo "<br>";
					
					$i = 1;
					while($i<=$total_pages)
					{
						if($i<$total_pages)
						{
							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 
						}
						elseif($i==$page_number)
						{
							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 
						}
						
						$i++;
					}
					if($page_number>$total_pages)
					{
						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 
					}
				}
			}
		} 
		$_SESSION['form_step'] = 'end';
	}
	?>
	
	<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'>
	<?php

	//Added '*' (asterisk) to indicate the 'Text Field' is a 'required' one.
	echo "<label for=\"first_name\">First Name *:</label>
	<input type=\"text\" name=\"first_name\" placeholder=\"First Name\" value = \"\">";?>
	<br>
	<?php
	echo "<label for=\"marital_status\">Marital Status *:</label>";
	echo "<select name=\"marital_status\">";
	echo "<option value=\"single\">Single</option>";
	echo "<option value=\"married\">Married</option>";
	echo "</select>";
	echo "<br>";
	?>
	<input type="submit" name="search" value="Search">
	<?php
	//$current_function = __FUNCTION__;
	//echo $current_function;
	
	//Do following if "Search" button clicked.
	if($_SERVER['REQUEST_METHOD'] === 'POST')
	{echo __LINE__; echo "<br>";//DELETE
		//Do following if "Search" button clicked.
		if(isset($_POST['search']))
		{echo __LINE__; echo "<br>";//DELETE
			rows_count(); //This function will forward script flow to fetch_rows() before halting the script.
			die();
		}
	}
	echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS FAILS TO ECHO. IT IS LINE: 24.
	//Do following if "Search" button not clicked but pagination numbered links are clicked. Eg Page 1, 2, 3, etc..
	fetch_rows(); //On PAGINATION PAGE 2, THIS FUNCTION IS NOT GETTING TRIGGERED! WHY ?
	echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 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:
    Code:
    22
24
32
39
42
50
    After clicking the SEARCH button I get these echoed as expected:
    Code:
    193
71
78
Row Start: 0
Row End: 1
103
114
TOTAL PAGES: 5

123
    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:

    Code:
    fetch_rows(); //On PAGINATION PAGE 2, THIS FUNCTION IS NOT GETTING TRIGGERED! WHY ? IT IS LINE: 200. MAIN ISSUE HERE, I SUSPECT.
    PS:
    I still on OOP and mysqli. Not on pdo or oop yet. So, kindly show samples to that level, if you must.
    Tags: None
    • SioSio
      Contributor
      • Dec 2019
      • 272
      #2
      The conditional expression of the "while" statement uses the comparison operator like the if statement.
      Example "while(x == 10)", "while(y<15 )"

        Comment

        • 2020
          New Member
          • Jun 2020
          • 2
          #3
          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.
          Code:
          $query_1 = "SELECT COUNT(id) FROM users WHERE first_name = ? AND marital_status = ?";
        $stmt_1 = mysqli_stmt_init($conn);
        if(mysqli_stmt_prepare($stmt_1,$query_1))
        {
            mysqli_stmt_bind_param($stmt_1,"ss",$_POST["first_name"],$_POST["marital_status"]);
            mysqli_stmt_execute($stmt_1);
            $result_1 = mysqli_stmt_bind_result($stmt_1,$row_count);
            mysqli_stmt_fetch($stmt_1);
            $_SESSION['row_count'] = $row_count;
          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:

          Code:
          $row_count = $_SESSION['row_count'];
 //$total_pages = ceil($result_1/$result_per_page);
$total_pages = ceil($row_count/$result_per_page);

          Context:

          Code:
          $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";
        echo "$query_2<br>";
        $stmt_2 = mysqli_stmt_init($conn);
        if(mysqli_stmt_prepare($stmt_2,$query_2))
        {echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 111.
            mysqli_stmt_bind_param($stmt_2,"ss",$_POST["first_name"],$_POST["marital_status"]);
            mysqli_stmt_execute($stmt_2);
            $result_2 = mysqli_stmt_get_result($stmt_2);
            echo __LINE__; echo "<br>";//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 114.
            //Grab total number of pages to paginate.
            $row_count = $_SESSION['row_count'];
            //$total_pages = ceil($result_1/$result_per_page);
            $total_pages = ceil($row_count/$result_per_page);

          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

            Comment

            • SioSio
              Contributor
              • Dec 2019
              • 272
              #4
              Are other session variables retained when the page changes?

                Comment

                Previous template Next
                Working...