CODEWITHSUNDEEP
phpmysqlcodeigniter
BigRob
BigRob

Reputation: 160

Insert an object into MySQL database field

i'm developing a web platform in codeigniter (first time with CI) to calculate quotes for a growing number of different products.

The problem i'm facing is that each of my products have different sets of 10+ options but I want to save this data to just one table in my database. I have previously used a different table for each product allowing the table structure to represent the different sets of options however this isn't very scalable with our growing product range.

After some research it appears one solution would be using the 'serialize' function to store all of my post data (from the quote form) for each product in one column and then unserialize when I want to use this data...

Is serializing the data the best approach and would anyone be able to provide a simple example to show how to handle the insert from a form submission / retrieving the data?

Thanks very much in advance

EDIT: Searching will only ever on a product type or unique id. My thoughts were to have a table like 'id, product_type, product_options' with the product_options containing the serialized data?

EDIT 2: Taking an EAV approach seems like a good shout. I'm used to querying and returning a single result object to be passed into the view ($query->quote_ref, $query->quote_date for example). Could anyone point me in the right direction on how to use the single quote's data when the query would return multiple rows, one for each attribute?

Upvotes: 1

Views: 5963

Answers (1)

Steve
Steve

Reputation: 20469

As you mention you would not need to search on the serialized data, then yes this approach will be fine. I would opt for json_encode as the data is more human readable in this form.

Then code will depend on your DAL but a basic example would be:

$productOptions=array($_POST['option1'], $_POST['option2']);//etc will need to validate data
$databaseMapper->product_type='product type';
//your product_options column is suitable sized varchar
$databaseMapper->product_options=json_encode($productOptions);
$databaseMapper->save();

To retrieve:

$databaseMapper->loadById(20);
//$productOptions is a standard php array
$productOptions = json_decode($databaseMapper->product_options, true);

EDIT re displaying in your view. This is codeignitor specific (whereas the above is not). Based on code from here: http://ellislab.com/codeigniter/user-guide/general/views.html

In your controller:

//code similar to above to retrieve product options data, ideally contained within a model
$data['productOptions']=$productOptions;
$this->load->view('content', $data);

in your view:

<ul>
<?php foreach ($productOptions as $option):?>

    <li><?php echo $option;?></li>

<?php endforeach;?>
</ul>

Upvotes: 2

Related Questions