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, Feb 24 '16 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, Feb 24 '16 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, Feb 24 '16 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, Feb 24 '16 at 13:39
Oh forgot to change $table. But $table = "notches"; Its declared in my script so thats not the problem — Ramon Bakker, Feb 24 '16 at 13:44
How could i place all outside the loop? Then tje json string is out of format — Ramon Bakker, Feb 24 '16 at 13:45
@YourCommonSense i changed all and use json_encode now. Won't say thats its faster then manual actually with the exact same result... — Ramon Bakker, Feb 24 '16 at 14:22

Ronin · Accepted Answer · 2016-02-24T18:00:00.400

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 '16 at 18:00

answered Feb 24 '16 at 14:37

Ronin

1,688
1
13
23

Thanks. Good explaination! – Ramon Bakker Feb 24 '16 at 14:42
@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. – Ronin Feb 24 '16 at 14:50
I am aware of SQL Injection and i know how to prevent it. – Ramon Bakker Feb 24 '16 at 15:01
I'm not using prepared statements. But bin2hex the variable and use UNHEX('$var') in the query. – Ramon Bakker Feb 24 '16 at 15:02
@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()](http://php.net/manual/en/function.json-encode.php) function is exists in PHP which does exactly you want. No need to build it manually. – Ronin Feb 24 '16 at 15:04
I think `UNHEX` will not help you when `$projectid = bin2hex('1; SELECT * FROM users --'`. :) – Ronin Feb 24 '16 at 15:14
http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php and see Zaffy's answer – Ramon Bakker Feb 24 '16 at 15:15
And it will actually help. Also Hex attacks are not possible ;) – Ramon Bakker Feb 24 '16 at 15:16
`SELECT * FROM notches WHERE projectid = UNHEX('$projectid')`. Try inject yourself ;) – Ramon Bakker Feb 24 '16 at 15:17
Ah, now I understand the idea. Thank you for a link. I like the prepared statements because of their universality, but UNHEX also can be useful in some situations. I see some of caveats in UNHEX approach which will make code a bit more complex than it could be, but it's OK. – Ronin Feb 24 '16 at 15:24
@RamonBakker, I've updated my answer again. Actually, you need no querying table structure because you already have field names in the result. – Ronin Feb 24 '16 at 15:34

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

1 Answers1