ActiveRecord DB Query

[eluser]btray77[/eluser]
Hello, I've got some questions and I hope someone can help me on this. I'm NOT strong with database work, as a matter fact this is my 1st project with a real relational database.

Here's the basics:
product table has id:int, productname:varchar(255), description:text, price:decimal(2), weight:float,image:varcahr(255)

keys table has id:int, prodcut_id:int,key:varchar(255), value:varchar(255)

I need to be able to do crud with this data, but it's all based off of a specific key in the data base that = sku.

1. I need to be able to check to see if a product exists by the sku
so I'm guessing I do this: (123434 is a random sku number)

$query = $this->db->get_where('keys', array('key' => '123434'), 1);
if ( $query->num_rows() == 0 )
{ Here's where I need to get the product_id, and pull up that product but I'm confused on how this works..}

2. I need to be able to delete the product based on the sku.

3. I need to be able to update the product information based on the sku.

4. I need to be able to insert a new product is the sku is not there.

I'm totally confused about database queries in mysql and activerecord. Any help would be nice. Also any suggested reading or tutorials would be "GREAT"!

Thanks

-Brad

[eluser]InsiteFX[/eluser]
Hi,

I need to be able to do crud with this data, but it’s all based off of a specific key in the data base that = sku.

1. I need to be able to check to see if a product exists by the sku
so I’m guessing I do this: (123434 is a random sku number)

$query = $this->db->get_where(‘keys’, array(‘key’ => ‘123434’), 1);
if ( $query->num_rows() == 0 )
{ Here’s where I need to get the product_id, and pull up that product but I’m confused on how this works..}

2. I need to be able to delete the product based on the sku.

3. I need to be able to update the product information based on the sku.

4. I need to be able to insert a new product is the sku is not there.

You can use a while loop or a foreach,

Code:

foreach ($query->result_array() as $row)
{
    echo $row['title'];
    echo $row['name'];
    echo $row['email'];
}

instead of echoing out the $row, use an if statement to check if the key matches what you are looking for.

as far as delete, update and insert, you will still need the key value except for the insert.

read the user_guide below.

codeigniter user_guide database

Enjoy
InsiteFX

[eluser]btray77[/eluser]
Thanks for the reply...

I've read the user guide cover to cover..

I don't know if I need to but I keep thinking I need to use the join command to join these tables together when I'm doing this.

I understand the CI syntax, but i'm not very strong with SQL commands, so I get confused..

-brad

[eluser]InsiteFX[/eluser]
Hi,

Check out MySQL web site or do a search on sql.


Yes you should use join for your tables.

Sitepoint's web site also has some good tutorials.

Enjoy
InsiteFX

w3schools SQL site

[eluser]btray77[/eluser]
@InsiteFX I never saw that tutorial at w2schools, that's what I needed... I'll checkout sitepoints tutorials also.

Thanks

-Brad

[eluser]InsiteFX[/eluser]
Your very welcome anytime.

I like it so much that I created a .pdf file so I have it right here at all times.

Enjoy
InsiteFX