[Skip to main content](#vessel-content) # Personalization question ## - **[neo191987](/forum/user/35364-neo191987 "View neo191987's Profile")** - [![neo191987's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar35364.jpg)](/forum/user/35364-neo191987 "View neo191987's Profile") Topic Author - [ Offline](/forum/user/35364-neo191987) Less More - Posts: 431 - Thank you received: 19 - - 8 years 1 month ago - 8 years 4 weeks ago [\#298524](#298524) by [neo191987](/forum/user/35364-neo191987 "View neo191987's Profile") *[Personalization question](/forum/2-general-talk-about-hikashop/895834-personalization-question#298524 "Hi, is it possible to retrieve information from the database as to which users have purchased a product. I want to retrieve their user_cms_id and other data.")* was created by *[neo191987](/forum/user/35364-neo191987 "View neo191987's Profile")* Hi, is it possible to retrieve information from the database as to which users have purchased a product. I want to retrieve their user_cms_id and other data. Last edit: 8 years 4 weeks ago by [neo191987](/forum/user/35364-neo191987 "View neo191987's Profile"). Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg5NTgzNC1wZXJzb25hbGl6YXRpb24tcXVlc3Rpb24/dG1wbD1tYXJrZG93bg==) or [Create an account](/register) to join the conversation. - **[Mohamed Thelji](/forum/user/6861-mohamed-thelji "View Mohamed Thelji's Profile")** - [![Mohamed Thelji's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar6861.png)](/forum/user/6861-mohamed-thelji "View Mohamed Thelji's Profile") - [ Offline](/forum/user/6861-mohamed-thelji) Less More - Posts: 12953 - Thank you received: 1778 - - - 8 years 1 month ago [\#298527](#298527) by [Mohamed Thelji](/forum/user/6861-mohamed-thelji "View Mohamed Thelji's Profile") Replied by *[Mohamed Thelji](/forum/user/6861-mohamed-thelji "View Mohamed Thelji's Profile")* on topic *[Personalization question](/forum/2-general-talk-about-hikashop/895834-personalization-question#298527 "Hi, is it possible to retrieve information from the database as to which users have purchased a product. I want to retrieve their user_cms_id and other data.")* Hello, Sure, the solution will be to directly use an SQL request on your code to directly retrieve the information you need. Kind regards, Mohamed Thelji. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg5NTgzNC1wZXJzb25hbGl6YXRpb24tcXVlc3Rpb24/dG1wbD1tYXJrZG93bg==) or [Create an account](/register) to join the conversation. - **[neo191987](/forum/user/35364-neo191987 "View neo191987's Profile")** - [![neo191987's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar35364.jpg)](/forum/user/35364-neo191987 "View neo191987's Profile") Topic Author - [ Offline](/forum/user/35364-neo191987) Less More - Posts: 431 - Thank you received: 19 - - 8 years 1 month ago [\#298552](#298552) by [neo191987](/forum/user/35364-neo191987 "View neo191987's Profile") Replied by *[neo191987](/forum/user/35364-neo191987 "View neo191987's Profile")* on topic *[Personalization question](/forum/2-general-talk-about-hikashop/895834-personalization-question#298552 "Hi, is it possible to retrieve information from the database as to which users have purchased a product. I want to retrieve their user_cms_id and other data.")* Thank you. Can there any guidelines to orient what to do? Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg5NTgzNC1wZXJzb25hbGl6YXRpb24tcXVlc3Rpb24/dG1wbD1tYXJrZG93bg==) or [Create an account](/register) to join the conversation. - **[Mohamed Thelji](/forum/user/6861-mohamed-thelji "View Mohamed Thelji's Profile")** - [![Mohamed Thelji's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar6861.png)](/forum/user/6861-mohamed-thelji "View Mohamed Thelji's Profile") - [ Offline](/forum/user/6861-mohamed-thelji) Less More - Posts: 12953 - Thank you received: 1778 - - - 8 years 1 month ago [\#298569](#298569) by [Mohamed Thelji](/forum/user/6861-mohamed-thelji "View Mohamed Thelji's Profile") Replied by *[Mohamed Thelji](/forum/user/6861-mohamed-thelji "View Mohamed Thelji's Profile")* on topic *[Personalization question](/forum/2-general-talk-about-hikashop/895834-personalization-question#298569 "Hi, is it possible to retrieve information from the database as to which users have purchased a product. I want to retrieve their user_cms_id and other data.")* Hello, Using that kind of SQL code will probably do the job : Code: SELECT DISTINCT o.order_user_id, u.user_cms_id FROM #__hikashop_order AS o LEFT JOIN #__hikashop_order_product AS op ON o.order_id = op.order_id LEFT JOIN #__hikashop_user AS u ON o.order_user_id = u.user_id WHERE op.product_id = YOUR_PRODUCT_ID AND (o.order_status = 'confirmed' OR o.order_status = 'shipped') You'll just have to replace YOUR_PRODUCT_ID by the product ID of the product you're interested in. Kind regards, Mohamed Thelji. The following user(s) said Thank You: [neo191987](/forum/user/35364-neo191987 "View neo191987's Profile") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg5NTgzNC1wZXJzb25hbGl6YXRpb24tcXVlc3Rpb24/dG1wbD1tYXJrZG93bg==) or [Create an account](/register) to join the conversation. - **[neo191987](/forum/user/35364-neo191987 "View neo191987's Profile")** - [![neo191987's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar35364.jpg)](/forum/user/35364-neo191987 "View neo191987's Profile") Topic Author - [ Offline](/forum/user/35364-neo191987) Less More - Posts: 431 - Thank you received: 19 - - 8 years 1 month ago [\#298610](#298610) by [neo191987](/forum/user/35364-neo191987 "View neo191987's Profile") Replied by *[neo191987](/forum/user/35364-neo191987 "View neo191987's Profile")* on topic *[Personalization question](/forum/2-general-talk-about-hikashop/895834-personalization-question#298610 "Hi, is it possible to retrieve information from the database as to which users have purchased a product. I want to retrieve their user_cms_id and other data.")* I was able to show ID, but it only shows to one user who has logged in. I want to retrieve all IDs from users who have purchased this product. Code: <?php $user = JFactory::getUser(); $userId = $user->get('id'); $db = JFactory::getDbo(); $query = "SELECT op.product_id FROM #__hikashop_user AS u LEFT JOIN #__hikashop_order AS o ON u.user_id=o.order_user_id LEFT JOIN #__hikashop_order_product AS op ON o.order_id=op.order_id WHERE u.user_cms_id=".(int)$userId." AND op.product_id = product_id AND (o.order_status = 'confirmed' OR o.order_status = 'shipped')"; $db->setQuery($query); $product_ids = $db->loadResult(); $user_id = hikashop_loadUser(); $db = JFactory::getDbo(); $query = 'SELECT `order_id` FROM ' . hikashop_table('order') . ' WHERE `order_user_id` = '. $user_id .' AND `order_type` = \'sale\' ORDER BY `order_created` DESC'; $db->setQuery($query); $order_ids = $db->loadColumn(); $orders = array(); $product_ids = array(); foreach($order_ids as $order_id) { $orderClass = hikashop_get('class.order'); $orders[$order_id] = $orderClass->loadFullOrder($order_id, true); foreach($orders[$order_id]->products as $product) { $product_ids[] = (int)$product->product_id; } } foreach ($orders as $key => $order) { $userClass = hikashop_get('class.user'); $hika_user = $userClass->get($order->order_user_id); $id = $hika_user->user_cms_id; $user = ES::user($id); var_dump($id); } ?> Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg5NTgzNC1wZXJzb25hbGl6YXRpb24tcXVlc3Rpb24/dG1wbD1tYXJrZG93bg==) or [Create an account](/register) to join the conversation. - **[Philip](/forum/user/34318-philip "View Philip's Profile")** - [![Philip's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar34318_2015-11-18-2.png)](/forum/user/34318-philip "View Philip's Profile") - [ Offline](/forum/user/34318-philip) Less More - Posts: 4820 - Thank you received: 654 - - 8 years 1 month ago - 8 years 1 month ago [\#298619](#298619) by [Philip](/forum/user/34318-philip "View Philip's Profile") Replied by *[Philip](/forum/user/34318-philip "View Philip's Profile")* on topic *[Personalization question](/forum/2-general-talk-about-hikashop/895834-personalization-question#298619 "Hi, is it possible to retrieve information from the database as to which users have purchased a product. I want to retrieve their user_cms_id and other data.")* Hello, First, if I look your **sql request** you only get the **product id**, so I don't really understand why you don't use Mohmed request in his previous post ? Second step, important to note for what you want achieved, if I understand you well you want have **all user cms id**, or let's say it differently **joomla user id**, but customers in guest mode **haven't** any joomla user id... The Mohamed solution will give what you requested but just not for guest customer. If that fit your needs you have here the solution, if you want more, I suggest that you precise your need and the use context. regards Last edit: 8 years 1 month ago by [Philip](/forum/user/34318-philip "View Philip's Profile"). The following user(s) said Thank You: [neo191987](/forum/user/35364-neo191987 "View neo191987's Profile") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg5NTgzNC1wZXJzb25hbGl6YXRpb24tcXVlc3Rpb24/dG1wbD1tYXJrZG93bg==) or [Create an account](/register) to join the conversation. - **[neo191987](/forum/user/35364-neo191987 "View neo191987's Profile")** - [![neo191987's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar35364.jpg)](/forum/user/35364-neo191987 "View neo191987's Profile") Topic Author - [ Offline](/forum/user/35364-neo191987) Less More - Posts: 431 - Thank you received: 19 - - 8 years 4 weeks ago [\#298649](#298649) by [neo191987](/forum/user/35364-neo191987 "View neo191987's Profile") Replied by *[neo191987](/forum/user/35364-neo191987 "View neo191987's Profile")* on topic *[Personalization question](/forum/2-general-talk-about-hikashop/895834-personalization-question#298649 "Hi, is it possible to retrieve information from the database as to which users have purchased a product. I want to retrieve their user_cms_id and other data.")* Thank you very much. Everything is working properly. The only thing I'm wrong with is loadResult() which it should be loadColumn() This is the code I made Code: <?php $product_id = (int)$this->element->product_id; $db = JFactory::getDbo(); $query = "SELECT DISTINCT o.order_user_id, u.user_cms_id FROM #__hikashop_order AS o LEFT JOIN #__hikashop_order_product AS op ON o.order_id = op.order_id LEFT JOIN #__hikashop_user AS u ON o.order_user_id = u.user_id WHERE op.product_id = '.$product_id.' AND (o.order_status = 'confirmed' OR o.order_status = 'shipped')"; $db->setQuery($query); $users_ids = $db->loadColumn(); foreach ($users_ids as $users) { $userClass = hikashop_get('class.user'); $users_id = $userClass->get($users); $id = $users_id->user_cms_id; $user = ES::user($id); var_dump($user->name); } ?> Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg5NTgzNC1wZXJzb25hbGl6YXRpb24tcXVlc3Rpb24/dG1wbD1tYXJrZG93bg==) or [Create an account](/register) to join the conversation. - **[neo191987](/forum/user/35364-neo191987 "View neo191987's Profile")** - [![neo191987's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar35364.jpg)](/forum/user/35364-neo191987 "View neo191987's Profile") Topic Author - [ Offline](/forum/user/35364-neo191987) Less More - Posts: 431 - Thank you received: 19 - - 8 years 4 weeks ago [\#298721](#298721) by [neo191987](/forum/user/35364-neo191987 "View neo191987's Profile") Replied by *[neo191987](/forum/user/35364-neo191987 "View neo191987's Profile")* on topic *[Personalization question](/forum/2-general-talk-about-hikashop/895834-personalization-question#298721 "Hi, is it possible to retrieve information from the database as to which users have purchased a product. I want to retrieve their user_cms_id and other data.")* Hi, this code works only for products, but I will mainly use variants. Is there any solution because I only allow one ID. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg5NTgzNC1wZXJzb25hbGl6YXRpb24tcXVlc3Rpb24/dG1wbD1tYXJrZG93bg==) or [Create an account](/register) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Offline](/forum/user/63-nicolas) Less More - Posts: 86322 - Thank you received: 14281 - - 8 years 3 weeks ago [\#298732](#298732) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Personalization question](/forum/2-general-talk-about-hikashop/895834-personalization-question#298732 "Hi, is it possible to retrieve information from the database as to which users have purchased a product. I want to retrieve their user_cms_id and other data.")* Hi, This code will work for variants too. You just need to give the product_id of the variant in $this->element->product_id. If however, you want to give a product_id of a product so that it takes into account all its variants, then you need to modify the query like that: Code: $query = "SELECT DISTINCT o.order_user_id, u.user_cms_id FROM #__hikashop_order AS o LEFT JOIN #__hikashop_order_product AS op ON o.order_id = op.order_id LEFT JOIN #__hikashop_product AS p ON op.product_id = p.product_id LEFT JOIN #__hikashop_user AS u ON o.order_user_id = u.user_id WHERE (p.product_parent_id = '.$product_id.' OR p.product_id = '.$product_id.' ) AND (o.order_status = 'confirmed' OR o.order_status = 'shipped')"; The following user(s) said Thank You: [neo191987](/forum/user/35364-neo191987 "View neo191987's Profile") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg5NTgzNC1wZXJzb25hbGl6YXRpb24tcXVlc3Rpb24/dG1wbD1tYXJrZG93bg==) or [Create an account](/register) to join the conversation. - **[neo191987](/forum/user/35364-neo191987 "View neo191987's Profile")** - [![neo191987's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar35364.jpg)](/forum/user/35364-neo191987 "View neo191987's Profile") Topic Author - [ Offline](/forum/user/35364-neo191987) Less More - Posts: 431 - Thank you received: 19 - - 8 years 3 weeks ago [\#298742](#298742) by [neo191987](/forum/user/35364-neo191987 "View neo191987's Profile") Replied by *[neo191987](/forum/user/35364-neo191987 "View neo191987's Profile")* on topic *[Personalization question](/forum/2-general-talk-about-hikashop/895834-personalization-question#298742 "Hi, is it possible to retrieve information from the database as to which users have purchased a product. I want to retrieve their user_cms_id and other data.")* Thank you. I found it yesterday after a thorough examination of the code. It's really not complicated if you find the right code. ![:)](/media/kunena/emoticons/3.png ":)") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg5NTgzNC1wZXJzb25hbGl6YXRpb24tcXVlc3Rpb24/dG1wbD1tYXJrZG93bg==) or [Create an account](/register) to join the conversation. Board Categories HikaShop Install & Update Product & Category Display Content Creation Sales and Orders Orders management Reports & Statistics Customers Checkout Payment Methods Shipping Methods Import & Export (CSV, XLS…) Specific Features Affilation Taxes Wishlist Vote Carousel & Slider Mass Actions Filters & Search Discount & Coupon Feature Request Integrations Showcase General Talk about HikaShop Development HikaShop 3.0 Beta Translation Commercial Jobs HikaShop Components HikaMarket Market: Feature requests Market: How to? Market: Bug Report HikaSerial General talk about HikaSerial Serial: Bug Report Serial: How to? Support en Français de HikaSerial (French Support) HikaSubscription HikaAuction Auction: Feature requests Auction: How to? Auction: Bug report Hikashop around the world Support en Français (French Support) Russian FAQ. Курс выживания с Hikashop. Частые вопросы и о Расширения Hikashop Наши сайты на Hikashop 日本語版サポート(Japanese Support) Thai German Archives Bug Report How to? [](/forum/topics/feed/mode-topics?format=feed&type=rss) Time to create page: 0.077 seconds [Powered by](/forum/credits) [Kunena Forum](https://www.kunena.org)