[SOLVED]How to make a conditional insert in multiple tables?

[eluser]earlyriser[/eluser]
I want to make a conditional insert in multiple tables.
My tables are:
WINES: id, name, year.
WINE_COMMENTS: id, user_id, wine_id, rating.
and some user tables from Freak Auth

Then, my form asks for a wine name, a wine year, a rating and it has a hidden field with the logged user.

If the wine name is not in the database, the controller needs to
-add a wine: id (incremental), name (from the form), year (from form)
-add a comment: id (incremental), user_id (form), wine_id (recent added wine.id), rating(form).

If the wine name is in the database, the controller needs just to
-add a comment: id (incremental), user_id (form), wine_id (recent result wine.id), rating(form).

This is my form

Code:

<?=form_open('wine/comment_insert');    ?>
<?=form_hidden('user_id', getUserProperty('id')); ?>
<p>name&lt;input type='text' name='name'/&gt;&lt;/p>
<p>rating&lt;input type='text' name='year'/&gt;&lt;/p>
<p>year&lt;input type='text' name='rating'/&gt;&lt;/p>
<p>&lt;input type="submit" value="Submit Comment"&gt;&lt;/p>
&lt;/form&gt;

Code:

function comment_insert()
    {
        //check if the wine is in database
        $this->db->select('*');
        $this->db->from('wines');
        $this->db->where('name', $name);
        $data['query']= $this->db->get();
        if ($query->num_rows() > 0) //if it was found in wines.names
        {
            //add new comment
            insert (wine_comments.user_id)    
            insert (wine_comments.rating)
            insert (wine_comments.wine_id)
        }
        else    //if it wasnt found in wines.names
        {
            //add new wine
            insert (wines.name)        
            insert (wines.year)
            
            //add new comment
            insert (wine_comments.user_id)    
            insert (wine_comments.rating)
            insert (wine_comments.wine_id)
        }
    }    
}

Please help me turning this pseudocode in the correct code.
I have made the Blog tutorial, but it doesn't help me with this multiple insert.

[eluser]Dready[/eluser]
Hello,

I guess :

Code:

if ($query->num_rows() > 0) //if it was found in wines.names
        {
            $dbrow = $query->row_array();
            $wine_id = $dbrow['id'];
        }
        else    //if it wasnt found in wines.names
        {
            $this->db->insert('wines',array('name'=>$_POST['name'],'year'=>$__POST['year']));
            $wine_id = $this->db->insert_id();            
        }
        //insert comment
        $this->db->insert('wine_comments',array('user_id'=>$_POST['user_id'],'wine_id'=>$wine_id,'rating'=>$_POST['rating']));

oh, and welcome to CI !

[eluser]earlyriser[/eluser]
Dready: thanks for your reply.
I tried your code as you can see below, but I only got a blank page. Any advice?

Code:

function comment_insert()
    {
        //check if the wine is in database
        $this->db->select('*');
        $this->db->from('wines');
        $this->db->where('name', $_POST['name']);
        $data['query']= $this->db->get();
        if ($query->num_rows() > 0) //if it was found in wines.name
        {
            $dbrow = $query->row_array();
            $wine_id = $dbrow['id'];
        }
        else    //if it wasnt found in wines.name
        {
            $this->db->insert('wines',array('name'=>$_POST['name'],'year'=>$_POST['year']));
            $wine_id = $this->db->insert_id();            
        }
        //insert comment
        $this->db->insert('wine_comments',array('user_id'=>$_POST['user_id'],'wine_id'=>$wine_id,'rating'=>$_POST['rating']));
    }

[eluser]Sumon[/eluser]
you have no view page defined. so for successful case it shall show a blank page. so first check database for correct data inserted successfully and then use

Code:

redirect('yourcontroller/success_function');

end of comment_insert() function.

[eluser]earlyriser[/eluser]
Sumon: I have check the DB, but it doesn't insert data.
I have made tests, with a dummy boolean variable in the IF and then it works. Then I think there is something wrong between the begin of the function and the IF.
And yes, I don't have a success page, that will come later.

[eluser]Sumon[/eluser]
would you please try with

Code:

$this->db->where('name', '$_POST['name']');

EDIT: add this line before if condition and check the value is correct or not

Code:

echo $query->num_rows();
if ($query->num_rows() > 0) //if it was found in wines.name
.......................other codes................

[eluser]earlyriser[/eluser]
I have added the success page. It still doesn't work with the IF clause. Something must be wrong there because nothing in the if o else is executed

Code:

//check if the wine is in database
        $this->db->select('*');
        $this->db->from('wines');
        $this->db->where('name', $_POST['name']);
        $data['query']= $this->db->get();
        if ($query->num_rows() > 0) //if it was found in wines.name

[eluser]earlyriser[/eluser]
Sumon:
[quote author="Sumon" date="1223002306"]would you please try with

Code:

$this->db->where('name', '$_POST['name']');

[/quote]

I put the above line and now the page where the form should be is blank.

Then I tried with the next line with same result

Code:

$this->db->where('name', "$_POST['name']");

[eluser]Sumon[/eluser]
what about

Code:

echo $query->num_rows();

[eluser]earlyriser[/eluser]
[quote author="Sumon" date="1223003201"]what about

Code:

echo $query->num_rows();

[/quote]

I did it. I got a blank page

My current code is

Code:

function comment_insert()
    {
        //check if the wine is in database
        $thename=$_POST['name'];
        $this->db->select('*');
        $this->db->from('wines');
        $this->db->where('name', $thename);
        $data['query']= $this->db->get();
        echo $query->num_rows();
    }

and still is a blank page, I'm not sure if the query is correct.
Thanks for your comments.