how to print the fetched value in mysqli prepared statement?

Question

MY html form :

<form action="" method="post" > 
<tr> 
<td><select name="bs_edu">
<option value="" selected="selected">Select Basic Education</option>   
<option value="BA">B.A</option>
<option value="BARCH">B.ARCH</option>
<option value="BCA">BCA</option>
<option value="BBA">BBA</option> 
</select>
<td><input type="text" name="desg"/> </td>
<td><input type="email" name="email"/></td>
</tr>
</form>

My Query:

<?php
    $stmt = $con->prepare("SELECT * FROM user_tbl WHERE bs_edu = ? AND desg = ? AND exp = ? AND pr_sal = ? AND plc = ? AND email = ?  ");
    $stmt->bind_param('ssssss', $_POST['bs_edu'], $_POST['desg'], $_POST['exp'], $_POST['prsal'], $_POST['place'], $_POST['email']);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($result->num_rows > "0")
    {
        while($row = $result->fetch_assoc());
        {
            echo 'first name: ' .$row['fname'];  
        }
    }
    $stmt->close(); 
?>

I know this the wrong way to show the data. Going through this manual http://php.net/manual/en/mysqli-stmt.fetch.php n really having bad luck, show how can i show the fetched data .. any help here.. thanks

Answer 1

The semi-colon in the following is making your loop stop and is an end of statement character.

while($row = $result->fetch_assoc());
                                    ^ end of statement

Remove it.

while($row = $result->fetch_assoc())

You wouldn't have gotten an error about it, since it's considered as valid syntax.

Also the quoted zero is interpreted as a string, so you need to remove it.

if ($result->num_rows > 0)

Sidenote about get_result().

The manual http://php.net/manual/en/mysqli-stmt.get-result.php states:

MySQL Native Driver Only

Available only with mysqlnd.

Example pulled from this answer https://stackoverflow.com/a/24985955/

if ($stmt = $mysqli->prepare("SELECT id, community, map, image FROM `googleMaps`")) {
    $stmt->execute();
    $stmt->bind_result($id, $community, $map, $image);
    while ($stmt->fetch()) {
            printf("%s %s\n", $title);
    }
    $stmt->close();
}

---

Additional notes.

<form> cannot be child of <table> since I suspect you may have <table></table> tags hidden somewhere in code you did not show.

You also don't have form elements to match your POST arrays.

Add error reporting to the top of your file(s) right after your opening PHP tag for example
<?php error_reporting(E_ALL); ini_set('display_errors', 1); then the rest of your code, to see if it yields anything, as well as or die(mysqli_error($con)) to mysqli_query().

Or another method would be to change:

$stmt->execute();

to:

if(!$stmt->execute()){
   trigger_error("There was an error.... ".$con->error, E_USER_WARNING);
}

---

Edit:

Borrowed from this answer https://stackoverflow.com/a/22899155/

$stmt = $con->prepare("SELECT * FROM user_tbl ... ");

$result = $stmt->execute(); 
$stmt->store_result();

if ($stmt->num_rows > 1) {

  while($row = $result->fetch_assoc()){ 

  echo 'First name: ' .$row['fname'];

  }

}else{

   echo "0 records found";
}

Plus, you also seem to be using your HTML form and PHP/MySQL in the same file.

You need to use isset() or !empty() against your POST arrays.

You also didn't include a submit button and is unknown if you are using one.

Same for what I already stated about missing form elements with matching name attributes for:

• $_POST['exp']
• $_POST['prsal']
• $_POST['place']