CODEWITHSUNDEEP
phpdatabasepdo
Tim Rideyourbike
Tim Rideyourbike

Reputation: 707

Database Class Error - php

I am having a problem with a database class that I have created. When I try and display the data that is in the table, no data shows, but it still passes the database->select().

<?php

class database {

private $DBhost  = "localhost";
private $DBuser  = "username";
private $DBpass  = "password";
private $DBname  = "database";

private $PDO    = null;
private $stmt   = null;

function __construct() {
  $this->PDO = new PDO("mysql:host=" . $this->DBhost . ";dbname=" . $this->DBname . ";charset=utf8", $this->DBuser, $this->DBpass);
}

function __destruct() {
  $this->PDO = null;
}

function select($query, $param = array()) {
  try {
    $this->stmt = $this->PDO->prepare($query);
    if (count($param) != 0) {
      $this->stmt->execute($param);
    } else {
      $this->stmt->execute();
    }
  } catch(Exception $ex) {
    $this->error($ex);
    return false;
  }
}



function resultset() {
  return $this->stmt->fetchAll();
}

function error($ex) {
  die('Something broke :(');
}
}
?>

I am trying to use this to display data in a html table, as shown on this next page.

<?php
include 'code/db.php';
$DB = new database();
?>
<html>
<head>
  <title>List</title>
</head>
<body>
<table>
  <tr>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Email</th>
    <th>Phone number</th>
    <th>Mobile number</th>
    <th>Address</th>
    <th>State</th>
    <th>Post Code</th>
    <th>Settings</th>
  </tr>
<?php
$DB->select('SELECT * FROM owners ORDER BY first_name, last_name');

while($line = $DB->resultset()) {
  echo "<tr>";
  echo "<td>" . $line['first_name'] . "</td>";
  echo "<td>" . $line['last_name'] . "</td>";
  echo "<td>" . $line['email'] . "</td>";
  echo "<td>" . $line['phone_number'] . "</td>";
  echo "<td>" . $line['mobile_number'] . "</td>";
  echo "<td>" . $line['address'] . "</td>";
  echo "<td>" . $line['state'] . "</td>";
  echo "<td>" . $line['post_code'] . "</td>";
  echo '<td><a href="item-list?id=' . $line['id'] . '">Details</a><a href="edit-owner?id=' . $line['id'] . '"</a></td>';
  echo "</tr>";
}

?>
</table>
</body>
</html>

If you could help me out with this that would be great.

Upvotes: 0

Views: 459

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157864

PDO is already a database class, you don't need any other. Especially such a stateful one. You just dug yourself a huge pitfall with this useless class and soon you will fall in it.

So, just keep raw PDO:

<?php

$DBhost  = "localhost";
$DBuser  = "username";
$DBpass  = "password";
$DBname  = "database";

$dsn = "mysql:host=$DBhostdbname=$DBname;charset=utf8";
$PDO = new PDO($dsn, $DBuser, $DBpass);
$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

What you actually need is to separate your SQL from HTML. So, get all your data first an then use whatever template to output it.

<?php
include 'code/db.php';
$stm  = $PDO->query('SELECT * FROM owners ORDER BY first_name, last_name');
$data = $stm->fetchAll();
?>
<html>
<head>
  <title>List</title>
</head>
<body>
<table>
  <tr>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Email</th>
    <th>Phone number</th>
    <th>Mobile number</th>
    <th>Address</th>
    <th>State</th>
    <th>Post Code</th>
    <th>Settings</th>
  </tr>
<?php foreach($data as $line):?>
  <tr>
    <td><?=$line['first_name']?></td>
    <td><?=$line['last_name']?></td>
    <td>
      <a href="item-list?id=<?=$line['id']?>">Details</a>
      <a href="edit-owner?id=<?=$line['id']?>Owner</a>
    </td>
  </tr>
<?php endforeach ?>
</table>
</body>
</html>

Upvotes: 1

Mihai
Mihai

Reputation: 26784

Change this line 

while($line = $DB->resultset()) {

to

foreach($DB->resultset() as $line ) {

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80639

You are using fetchAll in your resultset function. An entire array of selected values will be returned and stored in $line. You'd then have to iterate over it.

Change the function to:

function resultset() {
  return $this->stmt->fetch( PDO::FETCH_ASSOC );
}

Upvotes: 0

Related Questions