Can Someone Help With An Sql / Database Query?

General General Questions
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.

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')
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_timest
$to = $end_timest
$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
);
4

ty works well..