Query outside while loop, for using inside loop, does'nt work

Question

I have the following working code

$notchDetails = mysqli_query($conn, "SELECT * FROM notches WHERE projectid = ".$projectid." LIMIT ".$offset.", ".$limit."");
// i want $query here //
$outp3 = "[";
if (mysqli_num_rows($notchDetails) > 0) {
  while($notch = mysqli_fetch_assoc($notchDetails)) {

    $query = mysqli_query($conn, "DESCRIBE $table");

    $count = count($notch);
    $allnotches[] = $notch["notchid"]; // $allnotches is needed further in script //
    if ($outp3 != "[") {$outp3 .= ",";}

    $outp3 .= "{";

    $x = 1;
    while ($rs = mysqli_fetch_assoc($query)) {
        $field = $rs["Field"];
        $outp3 .= '"'.$field.'":"'.$notch[$field].'"';
        if ($x != $count) { $outp3 .= ","; } 
        $x++;
    }   

    $outp3 .= "}";
  }
}
$outp3 .="]";

(Don't look at the var name notch, could'nt find a better translation than notch. Its complicated ;-) )

Problem explained:

When i place $query = mysqli_query...

outside the while loop (just under $notchDetails = mysqli_query...),

it only gives 1 result and the rest is left empty in: while ($rs = mysqli_fetch_assoc($query)) { //result// }

Af far as i can see, it should work with the $query above the loop. But i don't understand why it is'nt.

Can someone explain me why this does'nt work?

P.s. The reason for placing it outside the loop is performance/speed

Am I taking you right and you are creating JSON structure manually? — Your Common Sense
– Your Common Sense, Commented Feb 24, 2016 at 13:28
That's right. I need to put multiple json inside 1 text document (using serialize and then a encryption over that). Do you have a better solution? — Ramon Bakker
– Ramon Bakker, Commented Feb 24, 2016 at 13:31
Hmm i am aware of json_encode, but choose for manually because of a reason i dont know anymore. But i can give it a try — Ramon Bakker
– Ramon Bakker, Commented Feb 24, 2016 at 13:34
To be frank, I am still wondering what does this code do, and what for. Where does $table come from, why can't you move not only query but whole $table business outside the loop, and many other questions. — Your Common Sense
– Your Common Sense, Commented Feb 24, 2016 at 13:39

Ronin · Accepted Answer · 2016-02-24 18:00:00Z

1

mysqli_fetch_assoc is iterating through mysqli_result. When you have ended the iterating, you are not able to iterate it again. You can create a new query and iterate it.

So, when you put $query = mysqli_query($conn, "DESCRIBE $table"); outside the while loop, you are not creating a new query to iterate, so, after first iterating is completed, mysqli_fetch_assoc is not returning anything because you have no new queries, and old query is already iterated.

I would do something like:

$fields = [];

$table_structure = mysqli_query($conn, "DESCRIBE `notches`");
while ($row = $table_structure->fetch_assoc()) {
    $fields[] = $row['Field'];
}

$notch_details = mysqli_prepare(
    $conn,
    'SELECT * FROM `notches` WHERE `projectid` = ? LIMIT ?, ?'
);
$notch_details->bind_param('iii', $projectid, $offset, $limit);
$notch_details->execute();
$notch_details = $notch_details->get_result();

$result = [];

while($notch = $notch_details->fetch_assoc()) {
    $values = [];

    foreach ($fields as $field) {
        $values[$field] = $notch[$field];
    }

    $result[] = $values;
}

$result = json_encode($result);

As you can see, I have prepared a list of $fields once, and I use it later just as a list of fields, no need to query table description again and again.

Edit: Also, when you querying a database and fetching data as an associative array, you need no knowledge about table fields because you already have fields names in your result:

$notch_details = mysqli_prepare(
    $conn,
    'SELECT * FROM `notches` WHERE `projectid` = ? LIMIT ?, ?'
);
$notch_details->bind_param('iii', $projectid, $offset, $limit);
$notch_details->execute();
$notch_details = $notch_details->get_result();

$result = json_encode($notch_details->fetch_all(MYSQLI_ASSOC));

You will have the same result here without querying a table structure.

edited Feb 24, 2016 at 18:00

answered Feb 24, 2016 at 14:37

Ronin

1,7231 gold badge14 silver badges24 bronze badges

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

11 Comments

Ramon Bakker Over a year ago

Thanks. Good explaination!

Ronin Over a year ago

@RamonBakker. You are welcome. I also hope this answer will be useful not only as an answer for your question, but also as an example of prepared statements which are safer than direct inserting variables into a query.

Ramon Bakker Over a year ago

I am aware of SQL Injection and i know how to prevent it.

Ramon Bakker Over a year ago

I'm not using prepared statements. But bin2hex the variable and use UNHEX('$var') in the query.

Ronin Over a year ago

@RamonBakker, also, I've just edited my answer. As I understand, you want to get JSON string as a result. There is a json_encode() function is exists in PHP which does exactly you want. No need to build it manually.

|

Collectives™ on Stack Overflow

Query outside while loop, for using inside loop, does'nt work

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related