fetching user data with prepared statement

问题:

hey guy so am trying to create profile table by displaying user data. have used some code samples that you'll see in the code below, have commented some out so watch out.

here is my code

    // $stmt = $con->prepare("SELECT * FROM user WHERE id = ? ");
    // $stmt->bind_param('s', $id);
    // if($stmt->execute()){
    // //$result = $stmt->get_result();
    // if($result->num_rows > 0){
    // //     username exists
    // //$stmt->bind_result($firstname, $lastname, $user_type, $email, $profession);
    //   echo 'No Data Found for this use';
    // }else{
    //    $row = $stmt->fetch_array();
    // $stmt = "select firstname, lastname, user_type, profession, email, dob, gender, country, phone, bio, address, created_at from user where id = $userId";
    // if ($stmt = $mysqli->prepare($stmt)) {

    //     /* execute statement */
    //      $stmt->execute();
    // // $stmt->store_result();
    // // if($stmt->num_rows){
    //     /* bind result variables */
    //     $stmt->bind_result($firstname, $lastname, $user_type, $profession, $email, $dob, $gender, $country, $phone, $bio, $address, $created_at);

    //     /* fetch values */
    //     while ($stmt->fetch()) {
    // //    }

    $stmt = $con->prepare("SELECT firstname, lastname, user_type, profession, email, dob, gender, country, phone, bio, address, created_at FROM user WHERE id = ?");
    $stmt->bind_param('s', $userId);
    $stmt->execute();
    $stmt->store_result();   
    if(!$stmt->num_rows > 0) {  
      echo 'No Data Found for this user';
      }
    else {
    $stmt->bind_result($firstname, $lastname, $user_type, $profession, $email, $dob, $gender, $country, $phone, $bio, $address, $created_at);  // <- Add; #args = #cols in SELECT

       $row = $stmt->fetch();

As you can see there are 1 open bracket } in the code above, that because it was wrap with the HTML.

below is the html code

     <!-- self post back url  -->
  <?php
      $url = 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF'];
  ?>

  <table>

        <tr>
          <td>
                <center>
                      <img src="userfiles/avatars/<?php echo $row['avatar'];?>" width="150" height="150">
                  </center>

          </td>
        </tr>
         <tr>
           <td><label><strong>First Name</strong></label></td>
           <td></td>
            <td><label><?php echo $row['firstname'];?> </td>
        </tr>
          <tr>
           <td><label><strong>Last Name</strong></label></td>
           <td></td>
            <td><label><?php echo $row['lastname'];?> </td>
        </tr>
   <tr>
           <td><label><strong>User Type</strong></label></td>
           <td></td>
            <td><label><?php echo $row['user_type'];?> </td>
        </tr>
       <tr>
           <td><label><strong>Profession</strong></label></td>
           <td></td>
            <td><label><?php echo $row['profession'];?> </td>
        </tr>
     <tr>
         <tr>
           <td><label><strong>Phone</strong></label></td>
           <td></td>
            <td><label><?php echo $row['phone'];?> </td>
        </tr>
     <tr>
           <td><label><strong>Gender</strong></label></td>
           <td></td>
            <td><label><?php echo $row['gender'];?> </td>
        </tr>
        <tr>
           <td><label><strong>Date Of Birth</strong></label></td>
           <td></td>
            <td><label><?php echo $row['dob'];?> </td>
        </tr>
        <tr>
           <td><label><strong>Email</strong></label></td>
           <td></td>
            <td><label><?php echo $row['email'];?> </td>
        </tr>
        <tr>
           <td><label><strong>Country</strong></label></td>
           <td></td>
            <td><label><?php echo $row['country'];?> </td>
        </tr>
      <tr>
           <td><label><strong>Address</strong></label></td>
           <td></td>
            <td><label><?php echo $row['address'];?> </td>
        </tr>
        <tr>
           <td><label><strong>Biography</strong></label></td>
           <td></td>
            <td><label><?php echo $row['bio'];?> </td>
        </tr>
        <tr>
           <td><label><strong>Join Date</strong></label></td>
           <td></td>
            <td><label><?php echo $row['created_at'];?> </td>
        </tr>

  </table>

  <?php 

     }  

  $mysqli->close(); 
  ?>

The above code work, but no user data displayed. here is screenshot: http://prntscr.com/cmfa0n

回答1:

Look at this statement here,

$row = $stmt->fetch();

From the documentation,

->fetch() method returns,

TRUE Success. Data has been fetched
FALSE Error occurred
NULL No more rows/data exists or data truncation occurred

It doesn't return any row from the result set.

With the ->bind_result() method, you've already binded variables for result storage, and what ->fetch() does is, it fetches results from a prepared statement into the bound variables, so you can directly use $firstname, $lastname, $user_type etc. in your table.

Note: You haven't selected avatar column in the SELECT statement, and add $avatar in the $stmt->bind_result($firstname, $lastname,...) statement also.