PHP: Fetching records after mysql query

Question

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?

If you want to fetch Colum of your Rows you can use distinct in the place of * and use while Query again to Fetch all records — Lakshya
– Lakshya, Commented Jan 16, 2016 at 10:31

Rajdeep Paul · Accepted Answer · 2016-01-16 10:51:29Z

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

    }
}

answered Jan 16, 2016 at 10:51

Rajdeep Paul

16.9k3 gold badges21 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sridhar Over a year ago

Rajdeep, I am getting this error-> PHP Fatal error: Call to undefined method mysqli_stmt::get_result()

Rajdeep Paul Over a year ago

@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 and see the first comment of OP.

sridhar Over a year ago

php 5.5. It looks like I will have to type out all column names and bind them individually and use fetch() after all.

Rajdeep Paul Over a year ago

@sridhar Is this line extension=php_mysqli_mysqlnd.dll commented or uncommented in php.ini file?

RiggsFolly Over a year ago

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

Saty · Accepted Answer · 2016-01-16 11:15:09Z

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"]);
        }
    }

edited Jan 16, 2016 at 11:15

answered Jan 16, 2016 at 10:34

Saty

22.5k7 gold badges35 silver badges52 bronze badges

2 Comments

sridhar Over a year ago

Saty, this will be my last resort. I want to avoid lengthy lines of column names and their bindings.

RiggsFolly Over a year ago

So where did you invent a $result variable

Shailesh Katarmal · Accepted Answer · 2016-01-18 04:40:21Z

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.
        }

        }

edited Jan 18, 2016 at 4:40

answered Jan 16, 2016 at 10:36

Shailesh Katarmal

2,7931 gold badge15 silver badges16 bronze badges

2 Comments

sridhar Over a year ago

Shailesh, I get this error -> PHP Warning: mysqli_stmt::fetch() expects exactly 0 parameters, 1 given

RiggsFolly Over a year ago

So where did you invent a $result variable

Collectives™ on Stack Overflow

PHP: Fetching records after mysql query

3 Answers 3

5 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related