PHP MySQL Array While ==

Question

I am trying to echo only certain rows of a PHP MySQL array.

I've gone through many iterations, but can't seem to figure it out.

<?php
$conn = new mysqli("SERVER", "user", "password", "dbname");
$sql = "
    SELECT
    POSTDATE,
    MEMBERS_ALL,
    MEMBERS_ACTIVE,
    FROM
    daily_numbers
";
$result = $conn->query($sql);
$conn->close();

while($row = $result->fetch_assoc()) {
    if ($row["MEMBERS_ALL"] == 600) {
        echo "Date: " . $row["POSTDATE"] . ", Members All: " . $row["MEMBERS_ALL"] . ", Members Active: " .$row["MEMBERS_ACTIVE"];
    }
};

while($row = $result->fetch_assoc()) {
    if ($row["MEMBERS_ALL"] == 705) {
        echo "Date: " . $row["POSTDATE"] . ", Members All: " . $row["MEMBERS_ALL"] . ", Members Active: " .$row["MEMBERS_ACTIVE"];
    }
};
?>

The example above will only show the first while/if statement (== 600), however I can not get it to echo the second while/if statement (== 705).

I would like to be able to echo the array multiple times using different constraints (I don't want to run the SQL statement more than once).

Schiem explains why your "error" is occurring, and a solution and Amit gives you a better way to solve the problem altogether — user5051310
– user5051310, Commented Jan 4, 2016 at 5:18

Schiem · Accepted Answer · 2016-01-04 05:17:16Z

1

PHP keeps track of where it's at in the array of results--each call to fetch_assoc() increments the pointer by 1 until it gets to the end, but it doesn't reset it for you. To check this, echo a test value during the second loop--you won't see any output.

As Amit says, you can either put both calls inside of the first while loop, or you can call:

$result->data_seek(0);

To reset the pointer back to the beginning of the $result loop. Place this in between the two while loops and it should work.

Iterating over the entire array isn't particularly efficient, so it's probably best to avoid that. If you want to maintain the ordering that you have now, but avoid iterating over the array multiple times, you could store the values as a string, like so:

<?php
$conn = new mysqli("SERVER", "user", "password", "dbname");
$sql = "
    SELECT
    POSTDATE,
    MEMBERS_ALL,
    MEMBERS_ACTIVE,
    FROM
    daily_numbers
";
$result = $conn->query($sql);
$conn->close();
$first = '';
$second = '';

while($row = $result->fetch_assoc()) {
    if ($row["MEMBERS_ALL"] == 600) {
        $first .= "Date: " . $row["POSTDATE"] . ", Members All: " . $row["MEMBERS_ALL"] . ", Members Active: " .$row["MEMBERS_ACTIVE"];
    } else if ($row["MEMBERS_ALL"] == 705) {
        $second .= "Date: " . $row["POSTDATE"] . ", Members All: " . $row["MEMBERS_ALL"] . ", Members Active: " .$row["MEMBERS_ACTIVE"];
    }
};   
echo $first;
echo $second;     
?>

edited Jan 4, 2016 at 5:17

answered Jan 4, 2016 at 5:10

Schiem

5893 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cascades Over a year ago

Worked great! Thank you! I especially liked adding the result to a variable that I can call later in the script.

Amit Rajput · Accepted Answer · 2016-01-04 05:06:54Z

You have no need of second loop. Just do like this..

    <?php
    $conn = new mysqli("SERVER", "user", "password", "dbname");
    $sql = "
        SELECT
        POSTDATE,
        MEMBERS_ALL,
        MEMBERS_ACTIVE,
        FROM
        daily_numbers
    ";
    $result = $conn->query($sql);
    $conn->close();

    while($row = $result->fetch_assoc()) {
        if ($row["MEMBERS_ALL"] == 600) {
            //do something
            echo "Date: " . $row["POSTDATE"] . ", Members All: " . $row["MEMBERS_ALL"] . ", Members Active: " .$row["MEMBERS_ACTIVE"];
        }
        if ($row["MEMBERS_ALL"] == 705) {
            //do something
            echo "Date: " . $row["POSTDATE"] . ", Members All: " . $row["MEMBERS_ALL"] . ", Members Active: " .$row["MEMBERS_ACTIVE"];
        }
    };

    ?>

kamal pal · Accepted Answer · 2016-01-04 05:35:56Z

0

You can manage this in SQL Query instead using order by clause, no php if condition needed.

<?php
$conn = new mysqli("SERVER", "user", "password", "dbname");
$sql = "
    SELECT
    POSTDATE,
    MEMBERS_ALL,
    MEMBERS_ACTIVE,
    FROM
    daily_numbers
        ORDER BY MEMBERS_ALL ASC
";
$result = $conn->query($sql);
$conn->close();

while($row = $result->fetch_assoc()) {
  echo "Date: " . $row["POSTDATE"] . ", Members All: " . $row["MEMBERS_ALL"] . ", Members Active: " .$row["MEMBERS_ACTIVE"];

};
?>

Note: this solution will only work, if you need results MEMBER_ALL by ascending or descending order.

answered Jan 4, 2016 at 5:35

kamal pal

4,1975 gold badges27 silver badges40 bronze badges

Comments

AddWeb Solution Pvt Ltd · Accepted Answer · 2016-01-04 05:38:03Z

Try this:

Example Object oriented style using seek to row:

You need to reset your mysqli resultset pointer to 0 using data_seek(0) default method. data_seek(0) function adjusts the result pointer to an arbitrary row in the result-set.

<?php
$conn = new mysqli("SERVER", "user", "password", "dbname");
$sql = "
    SELECT
    POSTDATE,
    MEMBERS_ALL,
    MEMBERS_ACTIVE,
    FROM
    daily_numbers
";
$result = $conn->query($sql);
$conn->close();

while($row = $result->fetch_assoc()) {
    if ($row["MEMBERS_ALL"] == 600) {
        echo "Date: " . $row["POSTDATE"] . ", Members All: " . $row["MEMBERS_ALL"] . ", Members Active: " .$row["MEMBERS_ACTIVE"];
    }
};

 /* seek to row no. 0 */
$result->data_seek(0);

while($row = $result->fetch_assoc()) {
    if ($row["MEMBERS_ALL"] == 705) {
        echo "Date: " . $row["POSTDATE"] . ", Members All: " . $row["MEMBERS_ALL"] . ", Members Active: " .$row["MEMBERS_ACTIVE"];
    }
};
?>

OR combine two while loop with single:

<?php
$conn = new mysqli("SERVER", "user", "password", "dbname");
$sql = "
    SELECT
    POSTDATE,
    MEMBERS_ALL,
    MEMBERS_ACTIVE,
    FROM
    daily_numbers
";

$result = $conn->query($sql);
$conn->close();

//Combine while loop as single...
while($row = $result->fetch_assoc()) {
    //if MEMBERS_ALL == 600... 
    if ($row["MEMBERS_ALL"] == 600) {
        //do something
        echo "Date: " . $row["POSTDATE"] . ", Members All: " . $row["MEMBERS_ALL"] . ", Members Active: " .$row["MEMBERS_ACTIVE"];
    }

    //if MEMBERS_ALL == 705...
    if ($row["MEMBERS_ALL"] == 705) {
        //do something
        echo "Date: " . $row["POSTDATE"] . ", Members All: " . $row["MEMBERS_ALL"] . ", Members Active: " .$row["MEMBERS_ACTIVE"];
    }
};

?>

Hope this help you!

Collectives™ on Stack Overflow

PHP MySQL Array While ==

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related