Page 1 of 1

create a 'non returning clients' report from SQL data

Posted: Sun Nov 20, 2005 6:51 am
by robster
Hi all,

I have a site with customer transaction data and customer data. There's a fair bit of it (data), about 600 clients and about 2000 transactions recorded.

In this situation (my first report so far) I want to create a 'non returning clients' report.

So far, with the code below, things run slowly and it often times out my php execution limit (even when extending it to 90 seconds). The idea for this is, loop through the 'clients' table, then for every clientID, loop through the transactions table, looking for transactions from that client. If the last transaction for that client is older than $DATE then they are classed as a non return client.

Can anyone see why this would be SO Slow? and perhaps, is there a better way? (As you can see here, i'm just echoing some data, i'm not even doing logic on the results and it's SLOOOOW)

ta :)

Rob

Code: Select all

//Loop thru client database
	mysql_select_db($dbname);
	$sql = "SELECT * FROM clients ORDER BY id ASC";
	$content = mysql_query($sql);
	$Xcontent = mysql_fetch_array($content);	  
	$num_rows = mysql_num_rows($content);
	
	if ($num_rows > 0) 
	{		
		
		for ($y=1; $y<=$num_rows; $y++)
		{ 		
			$id = $Xcontent["id"]; //the clients id
			
					//we will search through the TRANSACTIONS table for each and every client now and extract required data into an array
					mysql_select_db($dbname);
					$sql2 = "SELECT * FROM transactions WHERE cust_id = '$id' ORDER BY date ASC";
					echo "cust_id = $id:<br>";
					$content2 = mysql_query($sql2);
					$Xcontent2 = mysql_fetch_array($content2);	  
					$num_rows2 = mysql_num_rows($content2);
					
					if ($num_rows2 > 0) 
					{		
						for ($z=1; $z<=$num_rows2; $y++)
						{ 	
							$trans_id = $Xcontent2["id"];
							$trans_cust_id = $Xcontent2["cust_id"];
							echo "trans_id = $trans_id:<br>";
		
		
							$Xcontent = mysql_fetch_array($content);
						}
					}//end if num_rows2 > 0


			
			
			$Xcontent = mysql_fetch_array($content);
		}
	}

Posted: Sun Nov 20, 2005 7:05 am
by redmonkey
Try selecting only the data you need. From your description, I see no reason why you can't just select all clients then left join the date of the last transaction. That way you only execute one SQL query and not the 601 you are currently executing.

Also, my own personal preference, I tend to only select colums I need, that way I don't fill the ram with unessecary data.

Database indexing may also be an issue?

Posted: Sun Nov 20, 2005 7:09 am
by robster
Thanks,

eeek, i thought this might come up. I am a complete retard at joins! :) I've not successfully completed one yet despite the tutorials I've read etc.!

I'd appreciate any advice on joins in that case.


Rob

Posted: Sun Nov 20, 2005 7:21 am
by m3mn0n
The MySQL manual has some good information about JOIN and example scripts you could test out.

http://dev.mysql.com/doc/refman/5.0/en/join.html

Posted: Sun Nov 20, 2005 7:35 am
by redmonkey
Without knowing SQL server and version it's difficult to say, something like.....

Code: Select all

SELECT  `c`.*, MAX(`t`.`date`) AS `trans_date`
FROM  `clients` `c` 
LEFT JOIN `transactions` `t` ON (`t`.`cust_id` = `c`.`cust_id`)  GROUP BY `c`.`cust_id` ORDER BY `c`.`cust_id`
...may work as a generic MySQL query.

Posted: Sun Nov 20, 2005 7:39 am
by yum-jelly
If your just wanting to know * non return clients * then only sselect them, the database is smart so use it!

Code: Select all

SELECT t_b.id AS customer_id, t_b.cust_id AS trans_id FROM clients AS t_a LEFT JOIN transactions AS t_b ON ( t_a.id = t_b.cust_id ) WHERE t_b.date > some_date GROUP BY t_a.id ORDER BY t_b.date ASC
where as...

some_date = the cut off date where you label a client as non returning!

yj

Posted: Sun Nov 20, 2005 7:55 am
by robster
woah, very heavy stuff for me, I really have some learning to do it seems. I'll try and decipher that code for the upcoming week and give you all some feedback. Thank you all so much, let me sink my teeth into this :)

Oh, and yum-jelly, it's not JUST non returning clients, but also all their info (like what they've spent, who was the last staff member to serve them, who was the most regular staff member to serve them... etc etc. But I think what you have shown still gives back the data I require (if I stick those fields into the query))

Just so you know, the tables look like this (out of interest):

clients

Code: Select all

id
	 name_first
	 name_initial
	 name_last
	 phone_work
	 phone_home
	 phone_mobile
	 email
	 recieve_emails
	 confirmation
	 cc_num
	 cc_month
	 cc_year
	 referral_type
	 referral_notes
	 add_street
	 add_city
	 add_state
	 add_pcode
	 gender
	 bday_day
	 bday_month
	 bday_year
	 occupation
	 pref_hair
	 pref_colour
	 pref_massage
	 pref_beauty
	 notes_medical
	 notes_formula
	 notes_notes
and for transactions:

Code: Select all

id
	 type
	 type_id
	 price
	 date
	 notes
	 cust_id
	 employee_id
	 pos_history_id

Posted: Sun Nov 20, 2005 8:31 am
by yum-jelly
if you you tell me exactly what you want and how you want formatted I can show you how to write the query so you get it in one query. Most times in do not need more than one query if you have the right indexes and table relationships! My example was just to show how to do a simple JOIN, you can go do much more like grouping a, b, c or do this IF(that) does not contain or is greater or less than this. Any database can do just about anything, if it can't do something, it is mostly because the design logic has limited it!

Creating a query is simple, the hard part is designing the database to work in the most resource friendly way. You do this by using simple logic, create indexes only when they are needed ( not using good indexing is bad, using to many is terrible), create relationships that can be used across your database. Use the the correct data types for the type of data the column will hold! These are just basic rules as there are many more but just using these rules you will design a better database.


yj