Welcome Guest, Not a member yet? Register   Sign In
DB queries and security
#1

[eluser]lxdev[/eluser]
Hi, this is my first post.

I'm not too bad with PHP and have decided to give CI a go to develop a new app. My problem is that I don't have a lot of experience with databases and I'm not sure how the relationships between tables work, and how to query them securely.

Let's say I have the following tables in MySql:

accounts
orders
products

Assuming I were to take a GET param from this url: /orders/view/1
How would I build the query to get the details of that order, including the details of the products within it? Does the query need to contain the account id too (security: so only the orders in this user's account are accessible)? Does the products table need an account_id field?

I just want to make sure I start off doing this kind of thing the right way. Any help would be great!
#2

[eluser]LifeSteala[/eluser]
Hello,

Assuming your tables have the following table attributes:

tblAccounts - account_id, account_name, account_phone
tblProducts - product_id, product_name, product_desc, product_price
tblOrders - order_id, product_id, account_id

Controller

Code:
class Orders extends Controller
{
  function Orders()
  {
    parent::Controller();
    
    $this->load->model('ordersmodel', 'ordersdb', true); // Load orders database model
  }

  function view()
  {
    $data['orderId'] = $this->uri->segment(3); // Gets id from url (orders/view/1) after view/x = 1
    $orderId = $data['orderId'];
    $data['listOrders'] = $this->ordersdb->getOrders($orderId);
    
    $this->load->view('vieworder', $data);
  }
}

Model

Code:
class Ordersmodel extends Model
{
  function Ordersmodel()
  {
    parent::Model();
  }

  function getOrders($orderId)
  {
    // Note: I am giving the table names single letter aliases, makes it easier to code
    $this->db->select('o.order_id, p.product_name, p.product_price, p.product_desc, a.account_name');
    $this->db->where('o.order_id', $orderId);
    $this->db->join('tblProducts p', 'o.product_id=p.product_id');
    $this->db->join('tblAccounts a', 'o.account_id=a.account_id');
    $result = $this->db->get('orders o');

    if ( !$result )
      return false;
    else
      return $result;
  }
}

View

Code:
<h1>Order &lt;?=$orderId?&gt;</h1>

&lt;?
  if ( $listOrders ) {
    foreach ( $listOrders->result() as $rows ) {
      // Print your data here. Example product_name for Order ID of 1
      $rows->product_name;
    }
  }
?&gt;

Hope this helps

Resource: http://ellislab.com/codeigniter/user-gui...ecord.html
#3

[eluser]Johan André[/eluser]
CI does not have a built-in ORM (object-relation-model).
The product-table won't need an account_id field. The orders-table will though.
You do it with active record:

Code:
// Method in the controller "orders"
function view($id) {
  $this->db->select('*');
  $this->db->from('accounts');
  $this->db->join('orders', 'orders.account_id = accounts.id');
  $this->db->join('products', 'products.id = orders.product_id');
  $this->db->where('accounts.id', $id);
  $result = $this->db->get()->result_array();
}

Untested, but the basic idea is there. You might wanna fiddle around with left and right joins to get the result you want. You might wanna place the db-calls in a model too...

!!! Darn it, previous poster beat me to it, with a better explaination too... !!!

Good luck!
#4

[eluser]lxdev[/eluser]
Thank you very much for the replies - it's all starting to make sense now!

I have also found an existing CI app that I can look at for more examples, so hopefully I'll get the hang of it soon.

Thanks again Smile
#5

[eluser]LifeSteala[/eluser]
Yes I did! Though I did fail to clearly mention that account_id is not needed in products table. Wink

When your designing database tables, in your situation, products and accounts tables will hold raw data only. Orders table will be a dynamic table meaning its storing many orders for different accounts. So you can say orders is a linking table. You want to be able to get product and account information on a order, so therefore you link this up.

Hope that makes sense.
#6

[eluser]lxdev[/eluser]
Yes, that makes sense. Thanks for taking the time to write your code - I do understand MVC, but it's nice to see an example for my problem/question Smile




Theme © iAndrew 2016 - Forum software by © MyBB