connect2MAGE | WordPress plugin for easy Magento database connection

Featured Image

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 class="relatedProductInfo">No related products available...</p>';
}
?>
 
< ?php if (!empty($result)): ?>
<table id="relatedProductsList">
< ?php foreach ($result as $item): ?>
<tr class="productInfo">
<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.

Download connect2MAGE WordPress plugin.


12 comments

  1. I also have followed all the steps above, cleaned up the code and am stuck getting the array(0) { } error — what can I do to get this plugin to work and show my products? Thanks!

  2. I have some of the answers for some of the errors above. Regarding the error in the SQL syntax around the price line, I found an extra space at: >= 0 and _table_price.value <= between the last < and =. Taking out that space removed the error.

    I was getting the error "“Fatal error: Call to a member function get_results() on a non-object in " until I checked the connect2mage fields in the admin and found the password field blank.

    Then it was telling me that the "Table 'xxx.catalog_product_entity' doesn't exist". When I checked my database I found that the tables were all prefixed with mgn_ so adding that to the mysql code cleared up that error.

    Now I'm not getting any errors, just array(0) { } like a poster above mentioned. If anyone knows how to clear that up I'd love to hear it!

  3. Great plugin! This is exactly what I was looking for, but I am getting the following sql error:

    [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘.*, ‘_table_price’.’value’ AS ‘price’, IFNULL(_table_visibility.value, _table_’ at line 1]

    Any help fixing this would be greatly appreciated!

  4. i got this error after put those code in single.php

    “Fatal error: Call to a member function get_results() on a non-object in “

  5. Could you explain what we’re supposed to do with the Custom field name in the settings? I’m sure it should be obvious to me but I’m just not getting it. And are we supposed to fill in some custom field in the post?

    Thanks so much by the way!!! This is a very much needed plugin!

  6. Hi,
    It’s not working, there’s only the output ‘array(0) { }’
    I’ve deleted all the whitespaces in the code (hope so)
    and given that plugin all the data wich is needed for that plugin.
    Only with “Store url extension” – don’t know what this mean.
    Can anybody help me please?
    greetings

  7. Hi there, how can I use this “connector” to make Magento access WordPress content (and not the inverse situation)? Or I can’t? If not, is there any solution, a good one? The LazzyMonks module seems to have a lot of limitations…

  8. @ahsan

    In the book titled Guide to E-commerce Programming with Magento (by php Arhitects) in Chapter 7: CMS Integration, you have a example on how to integrate the login system from other CMS to work with Magento.

  9. hi all
    i want to use wordpress login id and password for magento.means user don,t need to create account on magento if he has already created account in wordpress.
    thanks in advance

  10. WordPress and Magento use separate databases. That’s why plugin asks for user name and passwordof database to be provided. If they were in the same database, one can easily use $wpdb approach.

  11. Are you running WordPress in a second database, or are you running it in the same DB as Magento?

    As well, does the plugin allow one to use the Magento Header / Footer on the wordpress pages?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.