[eluser]btray77[/eluser]
I need an ActiveRecord Expert.. I'm completely lost...
Here's the code I have so far, and I've included the database structure (not my choice on the structure)
I need to be able to perform CRUD functions products based off there SKU, which is a meta value in the productmeta table.
Currently get_many_products gives me a list of products based off of the productmeta id and I've not been able to figure out how to stop this. When it does this it repeats the same product data from the product_list table multiple times. I need to use the product_list id and show all the productmeta information associated with that ID.
At the end there is an example of the current output.
If someone could help me with this I would be eternally grateful...
Code: function delete_prodcut($id)
{
//$tables = array('productmeta', 'product_list');
$this->db->delete('productmeta', array('product_id' => $id));
$this->db->delete('product_list', array('id' => $id));
}
function get_many_products($num = 10)
{
//$tables = array('productmeta','product_list');
$this->db->select('*');
$this->db->from('product_list');
//$this->db->from($tables);
$this->db->join('productmeta', 'product_list.id = productmeta.product_id','right');
//$this->db->where('meta_key = "sku"');
$this->db->limit($num);
//$this->db->where('product_list.id = productmeta.product_id ');
$query = $this->db->get();
if ( $query->num_rows() > 0 )
{
return $query->result();
}
return array();
}
Code: --
-- Table structure for table `productmeta`
--
CREATE TABLE IF NOT EXISTS `productmeta` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`product_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) DEFAULT '',
`meta_value` longtext,
`custom` varchar(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `product_id` (`product_id`),
KEY `meta_key` (`meta_key`),
KEY `custom` (`custom`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=199 ;
--
-- Dumping data for table `productmeta`
--
INSERT INTO `productmeta` (`id`, `product_id`, `meta_key`, `meta_value`, `custom`) VALUES
(1, 1, 'url_name', 'test-prodcut', '0'),
(2, 1, 'sku', 'Test 1', '0'),
(3, 1, 'table_rate_price', '', '0'),
(4, 1, 'custom_tax', '8.25', '0'),
(5, 1, 'engraved', '0', '0'),
(6, 1, 'can_have_uploaded_image', '0', '0'),
(7, 1, 'external_link', 'http://www.website.com', '0'),
(8, 1, 'thumbnail_width', '96', '0'),
(9, 1, 'thumbnail_height', '96', '0');
--
-- Table structure for table `product_list`
--
CREATE TABLE IF NOT EXISTS `product_list` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`description` longtext NOT NULL,
`additional_description` longtext NOT NULL,
`price` decimal(11,2) NOT NULL DEFAULT '0.00',
`weight` float NOT NULL DEFAULT '0',
`weight_unit` varchar(10) NOT NULL DEFAULT '',
`pnp` decimal(11,2) NOT NULL DEFAULT '0.00',
`international_pnp` decimal(11,2) NOT NULL DEFAULT '0.00',
`file` bigint(20) unsigned NOT NULL DEFAULT '0',
`image` bigint(20) unsigned DEFAULT NULL,
`quantity_limited` varchar(1) NOT NULL DEFAULT '',
`quantity` int(10) unsigned NOT NULL DEFAULT '0',
`special` varchar(1) NOT NULL DEFAULT '0',
`special_price` decimal(11,2) NOT NULL DEFAULT '0.00',
`display_frontpage` varchar(1) NOT NULL DEFAULT '0',
`notax` varchar(1) NOT NULL DEFAULT '0',
`publish` varchar(1) NOT NULL DEFAULT '1',
`active` varchar(1) NOT NULL DEFAULT '1',
`donation` varchar(1) NOT NULL DEFAULT '0',
`no_shipping` varchar(1) NOT NULL DEFAULT '0',
`thumbnail_image` text,
`thumbnail_state` int(11) NOT NULL DEFAULT '0',
`date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=23 ;
--
-- Dumping data for table `product_list`
--
INSERT INTO `product_list` (`id`, `name`, `description`, `additional_description`, `price`, `weight`, `weight_unit`, `pnp`, `international_pnp`, `file`, `image`, `quantity_limited`, `quantity`, `special`, `special_price`, `display_frontpage`, `notax`, `publish`, `active`, `donation`, `no_shipping`, `thumbnail_image`, `thumbnail_state`, `date_added`) VALUES
(22, 'Test Prodcut 22', 'this is a test prodcut', '', '1233.00', 5, 'pound', '19.95', '85.95', 0, 4, '0', 0, '0', '1110.00', '0', '0', '1', '1', '0', '0', NULL, 0, '2009-08-19 22:51:05');
Current Output
Code: [0] => stdClass Object
(
[id] => 1
[name] => Test Prodcut 1
[description] => this is a test prodcut
[additional_description] =>
[price] => 1233.00
[weight] => 5
[weight_unit] => pound
[pnp] => 19.95
[international_pnp] => 85.95
[file] => 0
[image] => 4
[quantity_limited] => 0
[quantity] => 0
[special] => 0
[special_price] => 1110.00
[display_frontpage] => 0
[notax] => 0
[publish] => 1
[active] => 1
[donation] => 0
[no_shipping] => 0
[thumbnail_image] =>
[thumbnail_state] => 0
[date_added] => 2009-08-19 22:49:13
[product_id] => 1
[meta_key] => url_name
[meta_value] => test-prodcut
[custom] => 0
)
I would like it to list out all the meta_key's and values associated with that id.
Thanks
-Brad
[eluser]alboyd[/eluser]
SELECT * FROM productmeta meta
JOIN product_list prods ON meta.product_id = prods.id
LIMIT 0,10
I think this would work - hard to know without the database or testing it.
So in otherwords if you like activerecord - then you wanna do your initial select from the productmeta table and a left join to the product_list table...
[eluser]btray77[/eluser]
I used your example code, and I still get output like:
Code: [0] => stdClass Object
(
[id] => 1
[product_id] => 1
[meta_key] => url_name
[meta_value] => test-prodcut
[custom] => 0
[name] => Test Prodcut 1
[description] => this is a test prodcut
[additional_description] =>
[price] => 1233.00
[weight] => 5
[weight_unit] => pound
[pnp] => 19.95
[international_pnp] => 85.95
[file] => 0
[image] => 4
[quantity_limited] => 0
[quantity] => 0
[special] => 0
[special_price] => 1110.00
[display_frontpage] => 0
[notax] => 0
[publish] => 1
[active] => 1
[donation] => 0
[no_shipping] => 0
[thumbnail_image] =>
[thumbnail_state] => 0
[date_added] => 2009-08-19 22:49:13
)
[1] => stdClass Object
(
[id] => 1
[product_id] => 1
[meta_key] => sku
[meta_value] => Test 1
[custom] => 0
[name] => Test Prodcut 1
[description] => this is a test prodcut
[additional_description] =>
[price] => 1233.00
[weight] => 5
[weight_unit] => pound
[pnp] => 19.95
[international_pnp] => 85.95
[file] => 0
[image] => 4
[quantity_limited] => 0
[quantity] => 0
[special] => 0
[special_price] => 1110.00
[display_frontpage] => 0
[notax] => 0
[publish] => 1
[active] => 1
[donation] => 0
[no_shipping] => 0
[thumbnail_image] =>
[thumbnail_state] => 0
[date_added] => 2009-08-19 22:49:13
)
[2] => stdClass Object
(
[id] => 1
[product_id] => 1
[meta_key] => table_rate_price
[meta_value] =>
[custom] => 0
[name] => Test Prodcut 1
[description] => this is a test prodcut
[additional_description] =>
[price] => 1233.00
[weight] => 5
[weight_unit] => pound
[pnp] => 19.95
[international_pnp] => 85.95
[file] => 0
[image] => 4
[quantity_limited] => 0
[quantity] => 0
[special] => 0
[special_price] => 1110.00
[display_frontpage] => 0
[notax] => 0
[publish] => 1
[active] => 1
[donation] => 0
[no_shipping] => 0
[thumbnail_image] =>
[thumbnail_state] => 0
[date_added] => 2009-08-19 22:49:13
)
[3] => stdClass Object
(
[id] => 1
[product_id] => 1
[meta_key] => custom_tax
[meta_value] => 8.25
[custom] => 0
[name] => Test Prodcut 1
[description] => this is a test prodcut
[additional_description] =>
[price] => 1233.00
[weight] => 5
[weight_unit] => pound
[pnp] => 19.95
[international_pnp] => 85.95
[file] => 0
[image] => 4
[quantity_limited] => 0
[quantity] => 0
[special] => 0
[special_price] => 1110.00
[display_frontpage] => 0
[notax] => 0
[publish] => 1
[active] => 1
[donation] => 0
[no_shipping] => 0
[thumbnail_image] =>
[thumbnail_state] => 0
[date_added] => 2009-08-19 22:49:13
)
I'm looking for something like: but this is probably not possible...
Code: Array
(
[0] => stdClass Object
(
[id] => 1
[product_id] => 1
[meta_key] => url_name
[meta_value] => test-prodcut
[meta_key] => sku
[meta_value] => Test 1
[meta_key] => table_rate_price
[meta_value] =>
[meta_key] => custom_tax
[meta_value] => 8.25
[meta_key] => engraved
[meta_value] => 0
[meta_key] => can_have_uploaded_image
[meta_value] => 0
[meta_key] => external_link
[meta_value] => http://www.website.com
[meta_key] => thumbnail_width
[meta_value] => 96
[meta_key] => thumbnail_height
[meta_value] => 96
[custom] => 0
[name] => Test Prodcut 1
[description] => this is a test prodcut
[additional_description] =>
[price] => 1233.00
[weight] => 5
[weight_unit] => pound
[pnp] => 19.95
[international_pnp] => 85.95
[file] => 0
[image] => 4
[quantity_limited] => 0
[quantity] => 0
[special] => 0
[special_price] => 1110.00
[display_frontpage] => 0
[notax] => 0
[publish] => 1
[active] => 1
[donation] => 0
[no_shipping] => 0
[thumbnail_image] =>
[thumbnail_state] => 0
[date_added] => 2009-08-19 22:49:13
)
Any more suggestions?
Thanks for the help
-Brad
[eluser]davidbehler[/eluser]
Untested!
Code: function get_many_products($num = 10)
{
$this->db->select('*');
$this->db->from('product_list');
$this->db->limit($num);
$query = $this->db->get();
if ( $query->num_rows() > 0 )
{
$product_list = $query->result_array();
foreach($product_list as $key => $product)
{
$this->db->from('productmeta');
$this->db->where('product_id', $product['id']);
$query = $this->db->get();
if($query->num_rows() > 0)
{
$product_list[$key]['meta_data'] = $query->result_array();
}
else
{
$product_list[$key]['meta_data'] = array();
}
}
return $product_list;
}
return array();
}
[eluser]alboyd[/eluser]
Oh OK I misunderstood what you wanted because you only showed the one record returned...
You could do that (probably) but not through a query. I think you would need to do a couple of queries and some transposing using a separate meta array.
[eluser]LifeSteala[/eluser]
Hi Brad,
You actually require two queries to perform what is that you need. I would write some code but I need to popout in a few minutes so I'll try explain the idea.
Query 1: Select all products
Query 2: For each product (Query 1), get me it's meta data
So, you need to loop through each product (derived from query 1) and then with each product id, do another query getting the meta data for that particular product id.
As it loops, you will get all meta data, for each product.
The join that you are doing is not right for this requirement.
Hope that helps..
Edit: waldmeister's above post is what I was talking about.. just didn't reply quick enough. I'm off cya! Good Luck!
[eluser]btray77[/eluser]
Thanks everyone for the help on this... Now I see what I need to do.. I didn't think about having to loop though it.
@waldmeister your untested code worked like a champ!
-Brad
|
