Why WHILE LOOP Fails After One Execution ?

2020

2 New Member

Hiya,

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

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

 
$query_2 = "SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page";

It displays matching rows using LOOP:

Expand|Select|Wrap|Line Numbers

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.

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

After clicking the SEARCH button I get these echoed as expected:

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
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.

Jun 27 '20 #1

Subscribe Reply

2891

SioSio

272

Contributor

The conditional expression of the "while" statement uses the comparison operator like the if statement.
Example "while(x == 10)", "while(y<15)"

Jun 29 '20 #2

2020

New Member

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

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

Expand|Select|Wrap|Line Numbers

  
$row_count = $_SESSION['row_count'];

 //$total_pages = ceil($result_1/$result_per_page);

$total_pages = ceil($row_count/$result_per_page);

Context:

Expand|Select|Wrap|Line Numbers

  
$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

Jul 1 '20 #3

SioSio

272

Contributor

Are other session variables retained when the page changes?

Jul 2 '20 #4

Similar topics

Application.Exit() fails

by: Guinness Mann | last post by:

Pardon me if this is not the optimum newsgroup for this post, but it's the only .NET newsgroup I read and I'm certain someone here can help me. I have a C# program that checks for an error...

.NET Framework

Pausing execution of an ASP Script

by: Dr. Know | last post by:

I am working on an ASP page that writes to several databases, ranging from MDBs to x-base. One of the tasks involves using an existing highest value from the DB and incrementing it before...

ASP / Active Server Pages

Stored procedure fails execution sometime

by: tilak.negi | last post by:

Hi, There is a stored procedure which runs through job. It is calling to other stored procedure and other stored procedures are calling to another .. so on (approx 12-15 sp in batch) Problem:...

Microsoft SQL Server

Auto writing Excel spreadsheets from Access (97 & 2K) fails to releaseExcel

by: Mr. Smith | last post by:

Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it...

Microsoft Access / VBA

Strange do loop behavior

by: Alex | last post by:

C / C++

stepping through a collection object in a foreach loop

by: David C | last post by:

This is very strange. Say I have code like this. I am simply looping through a collection object in a foreach loop. Course course = new Course(); foreach(Student s in course.Students) {...

C# / C Sharp

VC++ 2005 beta1 fails with pthreads benchmark tests

by: um | last post by:

When the POSIX pthreads library for w32 release 2-2-0 (http://sources.redhat.com/pthreads-win32/) is compiled with VC++6 then it compiles and passes all the benchmark tests in the subdirectory...

.NET Framework

.NET Breakpoint execution failure

by: John Kutsor | last post by:

I have been working on a ASP.NET project with VB Code behind for a while now, and it ran perfectly up until now. When I try to set a breakpoint or a watch at certain parts of the project, even...

Visual Basic .NET

DTS Works, job fails!(data source Foxpro and destination SQL Server 2000

by: Biodigit | last post by:

The DTS works perfectly when I run it manually. However, when I run it as a job it fails. Before you ask if i'm running it under different security context. I have already made sure of that. I was...

Microsoft SQL Server

for loop is broken?

by: Andy B | last post by:

I have the following code inside of a WebBrowser.DocumentCompleted event: For index As Integer = 0 To Me.Browser.Document.GetElementsByTagName("ul").Item(0).GetElementsByTagName("li").Count ...

Visual Basic .NET

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...

C# / C Sharp

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...

Networking - Hardware / Configuration

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET