0

This is an elementary question, but I am finding it very confusing. Earlier I used to bind the results and fetch them using while loop. I am using * in the sql statement, hence the doubt. Here is the code:

$mysql = new mysqli("localhost", "user", "password", "database");
$sql = "SELECT * FROM mytable WHERE id = ?";
$stmt = $mysql->prepare($sql);
$prm = $_POST['txt'];
$stmt->bind_param("i",$prm);
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows > 0) {

}

There are around 20 columns in the table, hence I want to avoid including all column names in the sql. How do I echo out all the columns of each record?

Saty
  • 21,683
  • 7
  • 29
  • 47
sridhar
  • 1,023
  • 4
  • 14
  • 25

3 Answers3

1

For bind_result you have to write your query as

 $sql = "SELECT column1,column2 FROM mytable WHERE id = ?";
    $stmt = $mysql->prepare($sql);
    $prm = $_POST['txt'];
    $stmt->bind_param("i", $prm);
    $stmt->execute();
/*bind your result*/
    $stmt->bind_result($col1, $col2);
    if ($stmt->num_rows > 0) {
/* fetch values */
        while ($stmt->fetch()) {
            printf("%s %s\n", $col1, $col2);
        }
    }

Read http://php.net/manual/en/mysqli-stmt.bind-result.php

Updated

Using fetch_array(MYSQLI_ASSOC)

$sql = "SELECT * FROM mytable WHERE id = ?";
    $stmt = $mysql->prepare($sql);
    $prm = $_POST['txt'];
    $stmt->bind_param("i", $prm);
    $stmt->execute();
/*bind your result*/

    if ($stmt->num_rows > 0) {
/* fetch values */
        while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
           printf ("%s (%s)\n", $row["row1"], $row["row2"]);
        }
    }
Saty
  • 21,683
  • 7
  • 29
  • 47
1
        $mysql = new mysqli("localhost", "user", "password", "database");
        $sql = "SELECT * FROM mytable WHERE id = ?";
        $stmt = $mysql->prepare($sql);
        $prm = $_POST['txt'];
        $stmt->bind_param("i",$prm);
        $stmt->execute();
        $result = $stmt->store_result(); //store_result() 

        if ($result->num_rows > 0) { //Uses the stored result and counts the rows.

        while($data = $result->fetch_assoc()){ 
                //And here, the answer-object is turned into an array-(object)
                // which can be worked with nicely.
                //It loops trough all entries in the array.
        }

        }
Shailesh Katarmal
  • 2,587
  • 1
  • 10
  • 15
1

Use get_result() instead of store_result(), and then use result object's ->num_rows property to check if it returns any row or not, like this:

$mysql = new mysqli("localhost", "user", "password", "database");
$sql = "SELECT * FROM mytable WHERE id = ?";
$stmt = $mysql->prepare($sql);
$prm = $_POST['txt'];
$stmt->bind_param("i",$prm);
$stmt->execute();

$result = $stmt->get_result();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()){

        // your code

    }
}
Rajdeep Paul
  • 16,801
  • 3
  • 16
  • 34
  • Rajdeep, I am getting this error-> PHP Fatal error: Call to undefined method mysqli_stmt::get_result() – sridhar Jan 16 '16 at 10:57
  • @sridhar Which version of PHP are you using? Please note that `mysqli_stmt :: get_result` is available only with mysqlnd driver. Refer [this stack overflow answer](http://stackoverflow.com/a/8343970/5517143) and see the first comment of OP. – Rajdeep Paul Jan 16 '16 at 11:08
  • php 5.5. It looks like I will have to type out all column names and bind them individually and use fetch() after all. – sridhar Jan 16 '16 at 11:10
  • @sridhar Is this line **extension=php_mysqli_mysqlnd.dll** commented or uncommented in **php.ini** file? – Rajdeep Paul Jan 16 '16 at 11:11
  • The mysqlnd.dll is part of core PHP, so it does not need an `extension...` statement to load it in PHP5.5. Run a `phpinfo()` and you should see `MYSQLND` listed there if that is the library being used – RiggsFolly Jan 16 '16 at 17:32