PHP - Reference array more than once for different results

Question

I'm not sure how to search this issue I'm having nor if I'm using the right terminology.

I have a table with various records and each record have a category set. I want to query the table once and output the results in the relevant sections on a web page.

What I have done thus far:

$result_f1 = $db->query("SELECT * FROM files WHERE user_id = $user_id ") or die('cannot show tables');

... some html ...

if($result_f1) {
    while($row3 = $result_f1->fetch_object()) {
        if ($row3->category == "image") {
            echo '<div class="col-sm-6"><img class="img-responsive" src="/site/data/' .$user_id. '/',$row3->filename,'" alt="Photo"></div>';
        }
    }
}

...more html onto next section...

if($result_f1) {
    while($row4 = $result_f1->fetch_object()) {
        if ($row4->category == "vehicle") {
            echo '<div class="col-sm-6"><img class="img-responsive" src="/site/data/' .$user_id. '/',$row4->filename,'" alt="Photo"></div>';
        }
    }
}

So the one section I want to match records with the "image" category and the next section on the "vehicle" section.
Using the above code, it only displays records of the while loop, not the second loop. What should I be doing different?

Well I think you can't do it like that, you should use 2 query. One with category like "image", the second one with category like "vehicule" — kaldoran
– kaldoran, Commented May 4, 2017 at 8:07

hassan · Accepted Answer · 2017-05-04 09:53:57Z

2

that's because you are fetching your data using fetch_object method.

rather that fetch_object use fetch_all as follows :

$result_f1 = $db->query("SELECT * FROM files WHERE user_id = $user_id ") or die('cannot show tables');

$dataSet = $result_f1->fetch_all(MYSQLI_ASSOC);

if($dataSet) {
    foreach($dataSet as $row3) {
        if ($row3['category'] == "image") {
            echo '<div class="col-sm-6"><img class="img-responsive" src="/site/data/' .$user_id. '/',$row3['filename'],'" alt="Photo"></div>';
        }
    }
}

...more html onto next section...

if($dataSet) {
    foreach($dataSet as $row4) {
        if ($row4['category'] == "vehicle") {
            echo '<div class="col-sm-6"><img class="img-responsive" src="/site/data/' .$user_id. '/',$row4['filename'],'" alt="Photo"></div>';
        }
    }
}

that's because fetch_[object/array/assoc] returns only a result row and does not returns all data set.

for example :

mysqli_result::fetch_assoc -- mysqli_fetch_assoc — Fetch a result row as an associative array

and while you are iterating over this data set, php underneath the hood uses some functions like next & current to loop over the that object m and while the next function simply advanced the object/array pointer by one .

next — Advance the internal pointer of an array

at the end of your first iteration, you will reach the last value of the [object/array]'s pointer, so in the next iteration time you will be not able to iterate over that object unless you reset that pointer to 0

as follows:

$result_f1 = $db->query("SELECT * FROM files WHERE user_id = $user_id ") or die('cannot show tables');

... some html ...

if($result_f1) {
    while($row3 = $result_f1->fetch_object()) {
        if ($row3->category == "image") {
            echo '<div class="col-sm-6"><img class="img-responsive" src="/site/data/' .$user_id. '/',$row3->filename,'" alt="Photo"></div>';
        }
    }
}

...more html onto next section...

$result_f1->data_seek(0);

if($result_f1) {
    while($row4 = $result_f1->fetch_object()) {
        if ($row4->category == "vehicle") {
            echo '<div class="col-sm-6"><img class="img-responsive" src="/site/data/' .$user_id. '/',$row4->filename,'" alt="Photo"></div>';
        }
    }
}

edited May 4, 2017 at 9:53

answered May 4, 2017 at 8:10

hassan

8,3282 gold badges28 silver badges40 bronze badges

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

2 Comments

Stephen Over a year ago

I tried using fetch_assoc as you suggested, but get several warnings: PHP Warning: Illegal string offset 'category'. I have been troubleshooting it a while, but feel a little lost

hassan Over a year ago

sorry, forgot about updating my question, you should use fetch_all instead, checkout my update

Filchev · Accepted Answer · 2017-05-04 08:10:12Z

1

The reason this happens is that because once the first while loop finish the result set is empty. You need to get the full result set and put into a variable.

something like

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Fetch all of the values of the first column */
$results = $sth->fetchAll(PDO::FETCH_COLUMN, 0);

and then

foreach (results  as key => value){
// use hte results
}

answered May 4, 2017 at 8:10

Filchev

2242 silver badges12 bronze badges

2 Comments

hassan Over a year ago

from the syntax, it's seems that OP is using mysqli api instead of PDO

Filchev Over a year ago

Yeah I can see that, but the point of our answers is to point him out in the right direction :). Also I`d take the OOP approach anytime.

Alien426 · Accepted Answer · 2017-05-04 08:29:28Z

1

Assuming you are using MySQLi, you can use data_seek(0) to reset the pointer.

http://php.net/manual/en/mysqli-result.data-seek.php

You should consider doing different queries for each section (WHERE user_id = $user_id AND category="image"). And fetching all fields (SELECT *) may not be necessary either.

edited May 4, 2017 at 8:29

answered May 4, 2017 at 8:13

Alien426

1,33612 silver badges16 bronze badges

Collectives™ on Stack Overflow

PHP - Reference array more than once for different results

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related