CODEWITHSUNDEEP
phppdofetch
user2054896
user2054896

Reputation:

fetch() not returning first row

I am trying to update code that has been written using prepared statements. This is my first time using them and I am having difficulty retrieving all the results. When I use a direct SQL statement, it works so I don't think that's the problem.

The code will not return any results until there are at least two that match the query, then it will return all but the first row. I tried using fetchAll, but that gives different error about a call to an undefined method.

Thanks in advance for any help that you can provide. If it's not to much to ask, please provide example or reference I can refer to and complete my understanding.

function html_competitive_make_gallery ($init) {
global $USER;
    $user_id     = $USER->id;
    $page_name = 'competitive';
    $base_name = $init['base_name'];

    global $link;
     $sql_pre = "SELECT form_id, community_id FROM frm_root WHERE user_id = ? 
AND page_name = ? ORDER BY last_modified_date DESC LIMIT 1";
     $stmt = $link->prepare($sql_pre);
     $stmt->bind_param('is', $user_id, $page_name);
     $stmt->execute();
     $stmt->bind_result($form_id,$community_id);
     $stmt->fetch();
     $stmt->close();
$sql = "SELECT data FROM tester WHERE type= '".$base_name."' 
AND form_id= '".$form_id ."' AND community_id= '". $community_id ."' LIMIT 5";
$stmt = $link->prepare($sql);
$stmt->execute();
$stmt->bind_result($data);
$stmt->fetch();

$html[]='<div class="gallery" style ="width:100%;height:30%;overflow:hidden;">';


while ($stmt->fetch()){

                   echo $data;

                }
$stmt->close();

$html[]='</div>';

return implode ( $html);
}

Upvotes: 0

Views: 1582

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157839

Your main problem is called "mysqli".
You will face such problems as long as you're using mysqli with prepared statements.

Just quit it and use PDO:

function html_competitive_make_gallery ($init) {
    global $USER;
    global $link;

    $sql_pre = "SELECT form_id, community_id FROM frm_root WHERE user_id = ? 
                AND page_name = ? ORDER BY last_modified_date DESC LIMIT 1";
    $stmt = $link->prepare($sql_pre);
    $stmt->execute(array($USER->id, 'competitive'));
    return $stmt->fetch();
}

Upvotes: 0

Nils Werner
Nils Werner

Reputation: 36739

You're running fetch() before entering your loop, hence dropping the first row of your results:

$stmt->execute();
$stmt->bind_result($data);
$stmt->fetch();                    // <<< THIS LINE SHOULD NOT BE HERE

$html[]='<div class="gallery" style ="width:100%;height:30%;overflow:hidden;">';

while ($stmt->fetch()){
       echo $data;
}
$stmt->close();

Upvotes: 1

Tschallacka
Tschallacka

Reputation: 28722

Try this:

while ($data = $stmt->fetch()){

               echo $data;

            }
$stmt->close();

Upvotes: 0

Related Questions