CODEWITHSUNDEEP
phpmysql
user6484270
user6484270

Reputation:

fetch() won't work inside a while loop

I'm working on my collage project and a problem just came up. I want to make a while loop to fetch all the records from the database.

The code that I use for my SQL statement: 

$sql2 = "SELECT * FROM phones WHERE users_id = '$user_id'";
$stm2 = $db->prepare($sql2);
$stm2->execute();

The code that I use for the while loop:

<?php while ($record = $stm2->fetch()) :?>
    <div class="col-md-2">
       Some text
    </div>
<?php endwhile; ?>

This loop hasn't got output. Nothing shows up when I run the project.

If I try to fetch all the records first and then var_dump them out, it's okay. I mean that I can see that there are records in the database, but I can't do use these records inside the loop.

I've tried this code too and nothing came out again.

<?php while($record = $stm2->fetch()){
   echo '<div class="col-md-2">';
   echo 'Some text';
   echo '</div>';
}

Upvotes: 0

Views: 653

Answers (2)

clearshot66
clearshot66

Reputation: 2302

To get it to work the way you seem to be intending, you should do it this way:

First, select the exact columns you want.

Second, use ? as placeholder for security.

Third, prepare, then bind your parameters in to your query. i for integers, s for strings.

Execute, then run a while on the fetch. 

<?php
    $sql2 = "SELECT col1,col2,col3 FROM phones WHERE users_id = ?";
    $stm2 = $db->prepare($sql2);
    $stmt2->bind_param("i",$user_id);
    $stm2->execute();
    $stmt->bind_result($col1,$col2,$col3);


while($stmt2->fetch()){
echo "<div class='col-md-2'>";
echo $col1;   // echo each iteration out, works just like $result->fetch_assoc()
echo $col2;    // equivalent of $row['col2']; but in prepared statement style because you're binding the rows
echo $col1." plus ".$col2;

echo "</div>";
}
$stm2->close();  // close stmt
$db->close();   // close connection
?>

Upvotes: 1

Rushikumar
Rushikumar

Reputation: 1812

Try this:

$query = "SELECT * FROM phones WHERE users_id = '$user_id'";

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        printf ("%s (%s)\n", $row["FirstName"], $row["LastName"], ...);
    }

    /* free result set */
    $result->free();
}

EDIT

This is just a quick and dirty example... you should indeed use prepare statements as suggested in the comment

EDIT 2

Remove the , ... from this line: printf ("%s (%s)\n", $row["FirstName"], $row["LastName"], ...); if you don't have any other columns to output from the database

Upvotes: 0

Related Questions