CODEWITHSUNDEEP
phpmysqlmysqliwhile-loop
Ramon Bakker
Ramon Bakker

Reputation: 1085

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

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

Upvotes: 1

Views: 172

Answers (1)

Ronin
Ronin

Reputation: 1703

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.

Upvotes: 1

Related Questions