Jump to content

  • You cannot start a new topic
  • You cannot reply to this topic

Can Someone Help With An Sql / Database Query? Rate Topic   - - - - -

 
  • sublok
  • Advanced Member
  • Trial users
  • Join Date: 07-Oct 16
  • 53 posts

Posted 10 May 2017 - 06:09 AM #1

Looking to see if anyone has a more elegant way of doing this. 

$u= db_get_fields('
SELECT 
DISTINCT user_id 
FROM ?:users.* 
WHERE timestamp >= ?i 
AND timestamp <= ?i 
and status = ?i', 
$set_time_from, $set_time_to, 'A') 

if (!empty($u)) {
foreach ($u as $user) {
$u = db_get_row('
SELECT *
FROM ?:users
LEFT JOIN ?:user_profiles ON ?:users.user_id=?:user_profiles.user_id
LEFT JOIN ?:orders ON ?:users.user_id =
?:orders.user_id
WHERE ?:users.user_id=?i',
$user);
 
if ($u['status'] == 'A' && $u['order_id'] == null) {
$non_purchased_user_group[$u['user_id']] = $u;
}
                    }
                }       

 

 

We are hoping to gather the list of all of our customers from x_time till y_time who are active who have not ordered.

 

just looking for assistance with making this more efficient, as when it runs it does what we want but it bogs down.

 

Any help is greatly appreciated.

 

 

 

 



 
  • dswood
  • Member
  • Members
  • Join Date: 19-Mar 16
  • 39 posts

Posted 10 May 2017 - 02:35 PM #2

Maybe something like:

select distinct 
  user_id
from
  cscart_users csu
where
  csu.status = 'A'
--  and csu.timestamp between ?i and ?i 
  and not csu.user_id in (select user_id from cscart_orders where status = 'C')

csc 4.3.6


 
  • tbirnseth
  • CS Cart Expert
  • Authorized Reseller
  • Join Date: 08-Nov 08
  • 12001 posts

Posted 10 May 2017 - 08:08 PM #3


We are hoping to gather the list of all of our customers from x_time till y_time who are active who have not ordered

You could try this:

$from = $start_timestamp;
$to = $end_timestamp;
$user_status = 'A';
$non_purchased_user_group = db_get_hash_array(
"SELECT u.*
FROM ?:users AS u
LEFT JOIN ?:orders AS o ON o.user_id=u.user_id
AND o.timestamp > ?i AND o.timestamp < ?i
WHERE u.status=?s AND o.order_id IS NULL",
'user_id', $from, $to, $user_status
);

EZ Merchant Solutions: Custom (USA based) B2B Development, Consulting, Development and Special Projects (get a quote here).
Commercial addons, payment methods and modifications to meet your business and operations needs.


 
  • sublok
  • Advanced Member
  • Trial users
  • Join Date: 07-Oct 16
  • 53 posts

Posted 04 November 2017 - 04:35 AM #4

ty works well..