Hello Everyone! in this post i will show you how to fetch data from database using prepared statements. In the previous tutorials i gave some information about Prepared Statements and how to enter data into database. To fetch data we execute the query and store the result, then we bind the results to variables and finally fetch the data.

So here is also the same process of connecting to the database and providing the parameters.

<code class="php variable">$conn</code> <code class="php plain">= mysqli_connect(</code><code class="php string">'localhost'</code><code class="php plain">,</code><code class="php string">'root'</code><code class="php plain">,</code><code class="php string">'password'</code><code class="php plain">,</code><code class="php string">'database'</code><code class="php plain">); </code><code class="php comments">// localhost is server address,root username</code>

So now we prepare the database for the query and bind the parameters.

$query = "select username,email from users where username = ?";
$statement = $conn->prepare($query);
$username = 'abcdef';
$statement->bind_param('s',$username);

Now lets move to execute the query. if/else statements that shows if the query was executed successfully

if($statement->execute()){
  // success
}
else {
 // failed  
};

Now what i have done here is, if the query is executed successfully and the num rows is greater than 0. then we can finally fetch the data from the database. And we bind the result to the variables.

if($statement->execute()){
 $statement->store_result();
 $numrows = $statement->num_rows();
 if($numrows>0){
 echo "<table>
 <tr>
 <th>Username</th>
 <th>Email</th>
 </tr>";
 $statement->bind_result($user,$email_fetch);
 while($statement->fetch()){
 echo "<tr>
 <td>$user</td>
 <td>$email_fetch</td>
 </tr>";
 };
 echo "</table>";
 $statement->close();
 $conn->close();
 }
 else {
 echo "No Results Found!";
 };
}
else {
echo 'Failed ';
 $statement->close();
$conn->close();
};

fetch() is a function that fetches the data and is necessary to bind the result before fetching it. So finally our whole script would look like this.

<?php
$conn = mysqli_connect('localhost','root','','test');
$query = "select username,email from users where username = ?";
$statement = $conn->prepare($query);
$username = 'abcdef';
$statement->bind_param('s',$username);
if($statement->execute()){
 $statement->store_result();
 $numrows = $statement->num_rows();
 if($numrows>0){
 echo "<table>
 <tr>
 <th>Username</th>
 <th>Email</th>
 </tr>";
 $statement->bind_result($user,$email_fetch);
 while($statement->fetch()){
 echo "<tr>
 <td>$user</td>
 <td>$email_fetch</td>
 </tr>";
 };
 echo "</table>";
 $statement->close();
 $conn->close();
 }
 else {
 echo "No Results Found!";
 };
}
else {
echo 'Failed ';
 $statement->close();
$conn->close();
};

?>
So now you are ready with the Prepared statements and executing the queries securely.

 

Advertisements