Query Models with WHERE

[notlimahrelyt]

Hello,

This tutorial has been awesome for me, so first thank you!

I've been using this git as a starting point and modifying it a bit to work with subscriptions. The one thing i'm struggling with at this point is how to use the WHERE clause within the Customer and Transaction models.

I'd like the transactions page to show only transactions with customer ID of _____

I've done the following, but it isn't working.

<?php
  class Transaction {
    private $db;

    public function __construct() {
      $this->db = new Database;
    }

    public function addTransaction($data) {
      // Prepare Query
      $this->db->query('INSERT INTO transactions (id, customer_id, product, amount, currency, status) VALUES(:id, :customer_id, :product, :amount, :currency, :status)');

      // Bind Values
      $this->db->bind(':id', $data['id']);
      $this->db->bind(':customer_id', $data['customer_id']);
      $this->db->bind(':product', $data['product']);
      $this->db->bind(':amount', $data['amount']);
      $this->db->bind(':currency', $data['currency']);
      $this->db->bind(':status', $data['status']);

      // Execute
      if($this->db->execute()) {
        return true;
      } else {
        return false;
      }
    }

    public function getTransactions($activeCustomer) {
      $this->db->query('SELECT * FROM transactions WHERE id = $activeCustomer');

      $results = $this->db->resultset();

      return $results;
    }
  }

Then on the php webpage i've instantiated:

<?php
// Instantiate Transaction
$transaction = new Transaction();

// Get Transaction
$transactions = $transaction->getTransactions(abc123);
?>

So I guess my question is how can I use the WHERE clause with a variable inside of the query?

[jackttcoms]

If you make subscriptions a success, could you share the code? It would be very helpful and beneficial to the coding community who have just started with MVC's

Thanks

[KevinMGA]

The first ID is the sub ID, so that will not work. Edit your query to the following.

 public function getTransactions($activeCustomer) {
      $this->db->query('SELECT * FROM transactions WHERE customer_id = :customerID');
      $this->db->bind(':customerID', $activeCustomer);
      $results = $this->db->resultset();

      return $results;
    }

This is of course assuming you followed the tutorial and your columns are set the same.