Problem inserting array data into MySQL database.

[tobytwo]

Hello everyone,

I'm hoping someone can help me out here. Right, this is a long one, so stay with me. In brief, I'm trying to get form data from a checkbox group into a description field of a product database for an OSCommerce based shop.

I'm using a mod for OScommerce that allows customers to build computers by selecting components from different component categories, which then inserts the "built" computer, as selected by the customer, into the database as a new product.

A working version of which can be viewed [a href=\"http://www.wired2fire.co.uk/build_ignite939.php\" target=\"_blank\"]here[/a]. As you can see, the customer selects a single component from each category and then when you process the page it creates a description of the entire computer using the selected components. This is then inserted into the product database.

I am now trying to add checkboxes, so that certain categories e.g. something like "Games" would allow the customer to select more than one item from that category.

It's simple enough to generate this code dynamically, with the checkbox group set up so that it can pass the form data of the selected items as an array (just use square brackets for the name). The code writes out the html something like this:


[code]
<input name="cat4[]" type="checkbox" price="40" p_id="7">Farcry<br>
<input name="cat4[]" type="checkbox" price="50" p_id="8">Half Life 2<br>
<input name="cat4[]" type="checkbox" price="60" p_id="9">Doom 3<br>
[/code]

When the form data is posted the following array data is sent:
[code]

Array
(
    [osCsid] => 5c1c44bd64aff3b15248e72c52438d84
    [cat1] => Array
        (
            [0] => Wavemaster
        )

    [cat2] => Array
        (
            [0] => Enermax 600W
        )

    [cat3] => Array
        (
            [0] => FX-60
        )

    [cat4] => Array
        (
            [0] => Far Cry
            [1] => Half Life 2
        )

    [fsb] => 5
    [systype] => 5
    [Total] => 794.89
)
[/code]

All great, the next page that processes this data, as on the website I've linked to above, creates a computer description from the posted data. This is stored in a varaible called $message and when I echo $message it outputs to the screen fine with something like this:

Case: Wavemaster
Power Supply: Enermax 600W
Processor: FX-60
Games: Far Cry
Half Life 2

This is created with the following code, edited for clarity I hope:

[code]
<?php
$ccc_count_query     = tep_db_query('    select
                                        c.*,
                                        cd.*
                                        from
                                        '.TABLE_CCC_CAT.' c,
                                        '.TABLE_CCC_CAT_DESCRIPTION.' cd
                                        where
                                        c.fsb_id = "'.$_POST['fsb'].'"
                                        and
                                        c.cat_id = cd.cat_id
                                        and
                                        cd.language_id = "'.(int)$languages_id.'"
                                        and
                                        c.status = "1"
                                        order by sort_order asc'
                                    );

while ($count = tep_db_fetch_array($ccc_count_query))
{
    $i = $i + 1;
    $message .= '<tr><td class="smallText">' . $count['cat_name'] . ':</td><td class="smallText">';
    $items =  $_POST['cat' . $i];
    $no_items = count($items);
    $z = 0;
    while ($z < $no_items)
    {
        $message .= $items[$z] . "<br>";
        $z++;
    }
    
    $message .= '</td></tr>';

}
[/code]

The problem occurs when I try and insert $message into the description field of the product database. What I end up with is basically this:

Case:
Power Supply:
Processor:
CPU Cooling:

So, basically the selected parts, which are defined by this bit of code [code]
$items =  $_POST['cat' . $i];
$no_items = count($items);
$z = 0;
while ($z < $no_items)
{
    $message .= $items[$z] . "<br>";
    $z++;
}
[/code] are not processed correctly by the database insert function, which in OSCommerce is as follows:

[code]
// performs the database insert
$pd_array = array(    'products_description' => $message,
        'products_id' => $ccc_prod_id,
        'products_name' => $products_name,
                    'language_id' => (int)$languages_id);
tep_db_perform(TABLE_PRODUCTS_DESCRIPTION, $pd_array);
        
// the SQL database functions
function tep_db_perform($table, $data, $action = 'insert', $parameters = '', $link = 'db_link')
{
    reset($data);
    if ($action == 'insert')
    {
        $query = 'insert into ' . $table . ' (';
        while (list($columns, ) = each($data))
        {
            $query .= $columns . ', ';
        }
        $query = substr($query, 0, -2) . ') values (';
        reset($data);
        while (list(, $value) = each($data))
        {
            switch ((string)$value)
            {
                case 'now()':
                $query .= 'now(), ';
                break;
                case 'null':
                $query .= 'null, ';
                break;
                default:
                $query .= '\'' . tep_db_input($value) . '\', ';
                break;
            }
        }
        $query = substr($query, 0, -2) . ')';
    }
return tep_db_query($query, $link);
}

  function tep_db_query($query, $link = 'db_link') {
    global $$link, $logger;

    if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) {
      if (!is_object($logger)) $logger = new logger;
      $logger->write($query, 'QUERY');
    }

    $result = mysql_query($query, $$link) or tep_db_error($query, mysql_errno(), mysql_error());

    if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) {
      if (mysql_error()) $logger->write(mysql_error(), 'ERROR');
    }

    return $result;
  }
[/code]

This code works fine when $message is created with non-arrayed data, but as soon as I try and insert multidimensional array data, it doesn't work.
I hope the description of the problem is clear, and if anyone would like to have a look at the complete code then let me know.

Thanks in advance,

Toby

[Advancewebsoft]

Hello

The problem you have is somewhere in the area of this code:
$items =  $_POST['cat' . $i];
$no_items = count($items);
$z = 0;
while ($z < $no_items)
{
    $message .= $items[$z] . "<br>";
    $z++;
}

The way you build a $message variable is wrong for some reason (still dont have a full code, so cant help) - please recheck
the way you build it, and if it is really a STRING, so tep_db_preform functions should functions properly.