Hi everyone. I wrote this little plugin while working on one of our projects. If you know your way around WordPress then you know what $wpdb variable stands for. Imagine the following scenario. You have WordPress installation on one database, Magento on another. You know your way around SQL. You can always make new object based on WPDB class inside your template files giving it database access parameters, or you can use this plugin and use $MAGEDB the same way you use $wpdb.

Below is a little example of using $MAGEDB to connect to Magento database and retrieve some products by reading id’s from custom field of some post inside your WordPress.

Place this code inside one of your templates, like single.php.

< ?php

global $MAGEDB;
$MAGEDB->show_errors(true);

/** BASIC SETUP */

//$storeUrl = 'http://server/shop/index.php/';
$storeUrl = get_option('connect2MAGE_StoreUrl');

//$urlExt = '.html';
$urlExt = get_option('connect2MAGE_UrlExt');

/** END OF BASIC SETUP */

$entityIds = get_post_custom_values(get_option('connect2MAGE_CustomFieldName'));

$result = array();

if(!empty($entityIds))
{
$entityIds = $entityIds[0];
$sql = "SELECT `e`.*, `_table_price`.`value` AS `price`, IFNULL(_table_visibility.value, _table_visibility_default.value) AS `visibility`, IFNULL(_table_status.value, _table_status_default.value) AS `status`, `_table_url_key`.`value` AS `url_key`, IFNULL(_table_name.value, _table_name_default.value) AS `name` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_entity_decimal` AS `_table_price` ON (_table_price.entity_id = e.entity_id) AND (_table_price.attribute_id='99') AND (_table_price.store_id=0) INNER JOIN `catalog_product_entity_int` AS `_table_visibility_default` ON (_table_visibility_default.entity_id = e.entity_id) AND (_table_visibility_default.attribute_id='526') AND _table_visibility_default.store_id=0 LEFT JOIN `catalog_product_entity_int` AS `_table_visibility` ON (_table_visibility.entity_id = e.entity_id) AND (_table_visibility.attribute_id='526') AND (_table_visibility.store_id='1') INNER JOIN `catalog_product_entity_int` AS `_table_status_default` ON (_table_status_default.entity_id = e.entity_id) AND (_table_status_default.attribute_id='273') AND _table_status_default.store_id=0 LEFT JOIN `catalog_product_entity_int` AS `_table_status` ON (_table_status.entity_id = e.entity_id) AND (_table_status.attribute_id='273') AND (_table_status.store_id='1') INNER JOIN `catalog_product_entity_varchar` AS `_table_url_key` ON (_table_url_key.entity_id = e.entity_id) AND (_table_url_key.attribute_id='481') AND (_table_url_key.store_id=0) INNER JOIN `catalog_product_entity_varchar` AS `_table_name_default` ON (_table_name_default.entity_id = e.entity_id) AND (_table_name_default.attribute_id='96') AND _table_name_default.store_id=0 LEFT JOIN `catalog_product_entity_varchar` AS `_table_name` ON (_table_name.entity_id = e.entity_id) AND (_table_name.attribute_id='96') AND (_table_name.store_id='1') WHERE (e.entity_id in (".$entityIds.")) AND (_table_price.value >= 0 and _table_price.value < = 999999999999) AND (IFNULL(_table_visibility.value, _table_visibility_default.value) in (2, 4)) AND (IFNULL(_table_status.value, _table_status_default.value) in (1)) AND (_table_url_key.value not in ('P1FHN3G0LRWGMYZ3')) AND (IFNULL(_table_name.value, _table_name_default.value) not in ('P1FHN3G0LRWGMYZ3'))";
$result = $MAGEDB->get_results($sql);
var_dump($result);
}

else
{
echo '<p>No related products available...</p>';
}
?>

< ?php if (!empty($result)): ?>
<table id="relatedProductsList">
< ?php foreach ($result as $item): ?>
<tr>
<td><a href="<?php echo $storeUrl ?>/< ?php echo $item->url_key ?>< ?php echo $urlExt ?>">< ?php echo $item->name ?></a></td>
<td>< ?php _e('Starting at') ?> $ < ?php echo floatval($item->price) ?></td>
</tr>
< ?php endforeach; ?>
</table>
< ?php endif; ?>

Hope some of you find this useful. Especially those who refuse to use Web Services for connecting different systems.

By Branko Ajzele from inchoo.net

Related posts:

1. Magento MySQL database diagram
2. How to use WordPress home page in Magento home page (CMS page)
3. Magento Navigation, how to customize very helpful information?
4. How to transfer large Magento database from live to development server and other way round
5. Out of the box Form Validation in Magento