DB queries and security |
[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!
[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 Model Code: class Ordersmodel extends Model View Code: <h1>Order <?=$orderId?></h1> Hope this helps Resource: http://ellislab.com/codeigniter/user-gui...ecord.html
[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" 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!
[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
[eluser]LifeSteala[/eluser]
Yes I did! Though I did fail to clearly mention that account_id is not needed in products table. 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.
[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 |
Welcome Guest, Not a member yet? Register Sign In |