create a 'non returning clients' report from SQL data

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

create a 'non returning clients' report from SQL data

Post 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);
		}
	}
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post 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?
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post 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
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post 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.
yum-jelly
Forum Commoner
Posts: 98
Joined: Sat Oct 29, 2005 9:16 pm

Post 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
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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
yum-jelly
Forum Commoner
Posts: 98
Joined: Sat Oct 29, 2005 9:16 pm

Post 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
Post Reply