Insert array into database

[eluser]bohara[/eluser]
Hi,
I am trying to insert a array into my database. I had this working and I am not sure why it broke.

In my model I have this:

Code:

$reminder_id = $row['id'];
        
        for ($i = 0; $i <= count($_POST['bulb_type_id'])-1; $i++) {
            
                $sql = array(
                $this->reminder_id = $reminder_id,
                $this->quantity = $_POST['quantity'][$i][1],
                $this->bulb_type_id = $_POST['bulb_type_id'][$i][2],
                $this->shatterproof = $_POST['shatterproof'][$i][3],
                );
        
        }

$this->db->insert('line_items', $sql);

The array part of my form look like this: on the interface you can add more rows through AJAX that increment the the form values like [1][1], [1][2], [1][3], and so on.

Code:

<tbody id="container">
            <tr id="row0">
                <td>    
                    &lt;input type="text" name="quantity[0][1]" value="" maxlength="2" size="2"  /&gt;
                </td>
                <td>    
                    &lt;? echo form_dropdown('bulb_type_id[0][2]', $bulb_types); ?&gt;
                </td>
                <td>    
                    &lt;? echo form_checkbox('shatterproof[0][3]', 1, false); ?&gt;
                </td>
            </tr>
        </tbody>

I recieve the following errors:

----------------------------------------
A PHP Error was encountered

Severity: Notice

Message: Undefined index: shatterproof

Filename: models/reminder_model.php

Line Number: 67

----------------------------------------

An Error Was Encountered
Error Number: 1054

Unknown column '0' in 'field list'

INSERT INTO `line_items` (`0`, `1`, `2`, `3`) VALUES ('50', '1', '2', NULL)

-----------------------------------------

I hope this is enough information. Thanks in advance

[eluser]bohara[/eluser]
I am still stuck on this. Anyone have an idea what might be off in my code?

[eluser]Randy Casburn[/eluser]
You haven't given us enough information. I'm still confused by all sorts of things I see here and have avoided this post because of that. But I'll give this a shot for fear of looking like the fool that I am.

Let's start with the fact that you've not shown us what the HTML actually looks like. So we cannot resolve the first error for you. It would appear to me that the first error is thrown because the name of the dropdown is defined as 'shatterproof[0][3]' since that is what you passed to the form helper function and not 'shatterproof'.

The second error? Um...there isn't a column in your table called '0'. It doesn't get any simpler than that. Your query is structured improperly. Since you've not provided any of that, we can't help with any of that.

It's also very, very weird to create a variable named $sql that is simply an array of form data.

But maybe that's just me.

Randy

[eluser]bohara[/eluser]
Thanks for replying. Let me see if I can clarify.

The html looks like this

Code:

<fieldset>
    <legend><span>Customer Information</span></legend>
        <ol>
            <li><label>Company Name</label>
            &lt;? echo form_input('company_name'); ?&gt;</li>
            <li><label>Notes</label>
            &lt;textarea name="notes" value="" rows="3" cols="50"&gt;&lt;/textarea></li>
        </ol>
</fieldset>

<fieldset id="line_tems">
    <legend><span>Traps</span></legend>
    <ol>
        <li>
        <table cellspacing="0">
        <thead>
            <tr>
                <th>Qty</th>
                <th>Trap</th>
                <th>Shatterproof ?</th>
            </tr>
        </thead>
        <tbody id="container">
            <tr id="row0">
                <td>    
                    &lt;input type="text" name="quantity[0][0]" value="" maxlength="2" size="2"  /&gt;
                </td>
                <td>    
                    &lt;? echo form_dropdown('bulb_type_id[0][1]', $bulb_types); ?&gt;
                </td>
                <td>    
                    &lt;? echo form_checkbox('shatterproof[0][2]', 1, false); ?&gt;
                </td>
            </tr>
        </tbody>
    </table>
    </li>
    </ol>
    
    <p><a class="addRow" href="#">Add another trap</a></p>
</fieldset>

<fieldset>
    <legend><span>Reminder Information</span></legend>
        <ol>
            <li><label>Email to</label>
            &lt;? echo form_input('email_to'); ?&gt;</field><br /></li>
        
            <li><label>Remind me</label>
            &lt;? echo form_dropdown('reminder_date', $reminder_dates); ?&gt;</field> From &lt;?=$created_at?&gt;</li>
        </ol>
</fieldset>

When you click the 'addRow' link shown above another row is inserted to the form via AJAX that looks like this. with the form values incremented by 1 (below)

Code:

<tr id="row0">
                <td>    
                    &lt;input type="text" name="quantity[1][0]" value="" maxlength="2" size="2"  /&gt;
                </td>
                <td>    
                    &lt;? echo form_dropdown('bulb_type_id[1][1]', $bulb_types); ?&gt;
                </td>
                <td>    
                    &lt;? echo form_checkbox('shatterproof[1][2]', 1, false); ?&gt;
                </td>
            </tr>

The function that is inserting this data in my model is as follows:

Code:

function add_reminder()
    {
        $account_id = $this->session->userdata('account_id');
        
        $this->account_id = $account_id;
              $this->company_name = $_POST['company_name'];
        $this->notes = $_POST['notes'];
        $this->email_to = $_POST['email_to'];
        $this->reminder_date = $_POST['reminder_date'];
        $this->created_at = $_POST['created_at'];
        
        $this->db->insert('reminders', $this);
        
        // Insert line_items
        $query = $this->db->query('SELECT id FROM reminders ORDER BY id DESC LIMIT 1');
        $row = $query->row_array();
        
        $reminder_id = $row['id'];
        
        for ($i = 0; $i <= count($_POST['quantity'])-1; $i++) {
            
                $sql = array(
                   $this->reminder_id = $reminder_id,
                   $this->quantity = $_POST['quantity'][$i][1],
                   $this->bulb_type_id = $_POST['bulb_type_id'][$i][2],
                   //$this->shatterproof = $_POST['shatterproof'][$i][3]
                    );
                    
                $this->db->insert('line_items', $sql);
        
        }

    }

In regards to the variable name, I agree it is lame I am just trying to get it working at this point.

Everything up to the for loop executes as expected. I hope that clarifies things a bit. I don't think I am to far off as this was working at one point. Although I may have just been lucky.

Thanks again.

[eluser]Randy Casburn[/eluser]
So did you resolve the shatterproof vs. shatterproof[0][3] problem?

[eluser]bohara[/eluser]
Randy,
I am not getting any errors at the moment. It runs through and redirects, but it doesn't insert any line_item data.

[eluser]Randy Casburn[/eluser]
ok... once you resolve you form field name issue, on to the next one...


I think you've meant to construct your $sql array as key=>value pairs but you've made assignment statements instead.

Change the assignment operators (= signs into =>)

Randy

[eluser]Randy Casburn[/eluser]
Also....I really...really do hope you plan on sanitizing your input variables (POST) right? Going to run through the validation class and XSS clean them and all that ;-)

Randy

[eluser]bohara[/eluser]
Absolutely. I just want this working before I add more complexity. I will try changing my operators.

Beau

[eluser]bohara[/eluser]
Well that changed things. I have different errors now which is usually progress.


I get this repeated for every form field

Code:

Filename: models/reminder_model.php

Line Number: 64

A PHP Error was encountered

Severity: Notice

Message: Undefined property: Reminder_Model::$quantity

Filename: models/reminder_model.php

Followed by

Code:

An Error Was Encountered
Error Number: 1136

Column count doesn't match value count at row 1

INSERT INTO `line_items` () VALUES ('1')

I'm not sure why they are undefined? Or why the column count doesn't match.

Beau