[RESOLVED] Help with query

[kristen_m]

I could use some help with this query. I sense I'm missing something very obvious. I've been working on it for about 10 hours now (I wish I was exagerating :queasy🙂, and I'm ready to tap.

I have a table that accumulates website visits and page views, with new data dumps once a week. This means that the same user will have 1 row for each week he/she visited. So just doing a query by users with more then 2 visits won't capture people who visit 1x per week (and may have, total 3+ visits)

So here I pull the DISTINCT users:

$i=0;

$qry2=mysql_query("SELECT DISTINCT customer_number FROM activity_report")
or die(mysql_error());
$num2 = mysql_num_rows($qry2);


/* Stuffing distinct IDs into an array */

WHILE ($result2 = mysql_fetch_array($qry2)) {
	$customer_number[] = $result2['customer_number'];

}

print_r ($customer_number) spits out the expected 159 records.

I can query the DB by

$customer_number [$i] 

, if I set $i to a static variable (5, 23, 159, whatever)

All that works, except when I want to drop it into a loop to calculate each user in turn:

WHILE ($i <= 3) {
	$qry1=mysql_query("SELECT visits, last_name, customer_number FROM activity_report WHERE customer_number = '$customer_number[$i]'") or die (mysql_error());

{
WHILE ($result1 = mysql_fetch_array($qry1)){
	$result_views[]=$result1['visits'];
	$result_name[]=$result1['last_name'];
	}

print_r ($result_name);
echo "<BR><BR>";
}
	$i++;
}

I've tried for, foreach, swapping the brackets around, mysql_fetch_row/assoc/array, nesting more while/for statements, each time $i does not increment as I would expect, and I end up with something like this:

Array ( [0] => Snyder [1] => Snyder [2] => Snyder [3] => Snyder [4] => Snyder [5] => Snyder [6] => Snyder )

Array ( [0] => Snyder [1] => Snyder [2] => Snyder [3] => Snyder [4] => Snyder [5] => Snyder [6] => Snyder [7] => Granger )

Array ( [0] => Snyder [1] => Snyder [2] => Snyder [3] => Snyder [4] => Snyder [5] => Snyder [6] => Snyder [7] => Granger [8] => Schaffer )

Array ( [0] => Snyder [1] => Snyder [2] => Snyder [3] => Snyder [4] => Snyder [5] => Snyder [6] => Snyder [7] => Granger [8] => Schaffer [9] => Fathy [10] => Fathy )

[dagon]

why are you running a query for each customer (and even then guessing what there id may be -what happens when one is deleted?) when you can retrieve all the rows at once and loop through the results?

[kristen_m]

The customers are identified by a unique customer_number. It's the records that are not unique.

--------------------+-----------+
|customer_number | visits | page_views
+------------------+----------+
| customer_1 | 5 | 20|
| customer_2 | 2 | 15|
| customer_3 | 1 | 2 |
| customer_2 | 2 | 15|
| customer_4 | 1 | 5 |
| customer_3 | 1 | 15|

From that I need to find out how many total visits each customer (1, 2, 3, 4) had. For example, customer 1 had 5 visits and 20 views, while customer 2 had 4 visits and 30 views.

Searching by customer# by hand gives me the results I want. My thought was just to search for each customer by number (the DISTINCT query), put it an array and just loop through the array.

It's trying to loop through the array of customers that isn't giving the expected results.

[dagon]

group by customer_number, sum visits and page views.

..

select customer_number, sum(visits) as V, sum(page_views) as PV from TABLE group by customer_number

[kristen_m]

Thank you, Dragon. I figured I was going about it totally the wrong way!

I appreciate the help. 🙂