CODEWITHSUNDEEP
phpmysqlfunctionpdo
jonboy
jonboy

Reputation: 2749

Php pdo calling functions and accessing variables

I'd like some help understanding how to call the results of a function on separate pages within my simple web app. I'm quite new to php/pdo.

Most of the time I just want to create a simple SELECT function and display the data on another page.

I have a page containing all of my functions named class.crud.php, the contents are as follows (minified for display purposes);

class.crud.php

class crud {

    private $db;

    function __construct($DB_con) {
        $this->db = $DB_con;
    }

    public function testing() {
        $stmt = $this->db->prepare("SELECT * FROM users WHERE user_id=:userId");
        $stmt->bindparam(":userId", $_SESSION['user_session']);
        $stmt->execute();
        $results = $stmt->fetch(PDO::FETCH_ASSOC);
        //var_dump($results); this dumps the results successfully on the desired page
        if ($stmt->rowCount() > 0) {
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                print($row['user_id']);
                print($row['user_name']);
                echo $row['user_id'];
                echo $row['user_name'];
            }
        } else {
            echo 'nothing here';
        }
        return true;
    }

}

How do I display the results of this testing() function on any other page? I have tried the following but it doesn't work (no data displayed at all, although if I uncomment the var_dump($results) I can see an array of data displayed);

user-details.php

<div>
  <p>User Details</p>
  <?php $crud->testing();?>
</div>

A few questions;

My database connection is fine and I have many functions that work. However they are very simple functions that I have been using for testing.

Any help is appreciated in understanding the logic. I have read so many tutorials :/

Upvotes: 0

Views: 2475

Answers (2)

Dan Hennion
Dan Hennion

Reputation: 1745

The best way to accomplish this is to save the data to an array and return it within your function: 

$returnResults = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $returnResults[] = $row;
}
return $returnResults;

Within your template file, you can then call your function, and iterate over the results:

<?php 
$testing = $crud->testing();
foreach($testing as $row) {
    // Echo your data here, in whatever way it best fits your template goals
    echo $row['user_id'];
    echo $row['user_name'];
}
?>

Functions should almost never print or echo anything directly, as you've found, because it makes the data inaccessible. Moreover, a template should always control how data is displayed on page. This is called separation of concern:

In MVC structure, the model (your crud class) should communicate with your database to create, read, update, or delete data. It should have no concerns about how or when that data will be displayed.

Your view (in this case your template file), on the other hand, should not concern itself with talking to the database. It should ask your model for data, and only worry about displaying it accordingly.

If you want to get into MVC structure even more, technically it is your controller layer that should pass the data back and forth between the model and view. This would mean removing the $crud->testing() call from your template, and calling it in an intermediary file that first gets the data from the model, then renders the view passing it the data it needs to render. This is probably more complex than your code has to be at this point, but it's worth understanding if you want to get further into best MVC practices and separation of concerns.

Upvotes: 2

Zimmi
Zimmi

Reputation: 1599

The reason behind this is that you can fetch results only once, unless you build your PDOStatement object with scrollable cursor.

When all rows have been returned (in your case I go with your result set is made of only one row, because you have user_id as key and unique field), the function fetch returns false. So in your while loop, fetch gives false.

Or you use the $result array, as proposed by carbide, or you need to call again the execute function before the second call to fetch, or you use scrollable cursors to go back and forth. You have an example of usage in the PHP doc for PDOStatement::fetch.

And if you want to get all results in an array, you can also use the function PDOStatement::fetchAll.

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

So it makes your whole function like this: 

public function testing() {
    $stmt = $this->db->prepare("SELECT * FROM users WHERE user_id=:userId");
    $stmt->bindparam(":userId", $_SESSION['user_session']);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Upvotes: 2

Related Questions