CODEWITHSUNDEEP
phpmysqlmysqli
sridhar
sridhar

Reputation: 1389

PHP: Fetching records after mysql query

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?

Upvotes: 0

Views: 72

Answers (3)

Shailesh Katarmal
Shailesh Katarmal

Reputation: 2785

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

        }

Upvotes: 1

Saty
Saty

Reputation: 22532

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

Upvotes: 1

Rajdeep Paul
Rajdeep Paul

Reputation: 16963

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

    }
}

Upvotes: 1

Related Questions