Updating a table with result of another table

[x86phre3x]

Hi,

I am in the middle of developing a web application (customer DB) and currently attempting to create a module to update the number of member registered under 1 agent.

I have 2 table, 1 is 'users' and another 1 is 'dealers'. The 'users' table contains some normal user credential "agent" column which contains the "username" of 'dealers'. This table also includes "user_registration_timestamp" which indicates the registration time of this particular user.

On 'dealers' table, I have "registration_timestamp" too but with"expiredtime" to indicate the agent active period.

My script is to calculate how many "users" contains the same dealers in the period of that dealers active status (by using users' registration_timestamp and compare it with dealers registration_timestamp and expiredtime) and update into "thisperiodusercount" column inside 'dealers'.

Since the dealer has 2 types of active period, I have to use the "usercountbalance" column to add balance after 1 active period to another so when updating, it needs to add total users + usercount balance.

Below is my attempt:

//Query to get the max dealer ID
$getMaxID = mysql_query("SELECT MAX(`id`) AS maxid FROM dealers");
$row = mysql_fetch_array($getMaxID);
$maxID = $row[maxid];

for ($i = 0; $i <= $maxID; $i++){
	//get registration_timestamp, expiredtime and usercountbalance from dealer
	$getDealerAttributes = mysql_query("SELECT registration_timestamp, expiredtime, usercountbalance, username FROM dealers WHERE `id` = '$i'") or die;
	
	while (($row2 = mysql_fetch_array($getDealerAttributes)) != false){
		$registration_timestamp = $row2['registration_timestamp'];
		$expiredtime = $row2['expiredtime'];
		$usercountbalance = $row2['usercountbalance'];
		$username = $row2['username'];
		
		$getAgentCountResult = mysql_query("SELECT `agent`, COUNT(`agent`) AS agentcount FROM users WHERE `registration_timestamp` BETWEEN '$registration_timestamp' AND '$expiredtime' AND `agent` = '$username' GROUP BY `agent` ORDER BY `agent` ASC");
	
		while ($row3 = mysql_fetch_assoc($getAgentCountResult)) {
			$nu=$row3['agentcount'];
			$name=$row3['agent'];
			
				mysql_query("UPDATE `dealers` SET thisperiodusercount = ('$nu' + '$usercountbalance') WHERE username = '$name' AND dealershipstatus = 'subsequent3months'");
		
			}
	}
}

Currently it is still not working and it does not update the table correctly. Can somebody help to correct my syntax and concept? Thanks.

[johny_d]

Here's a very simple mysql solution:

<?php
mysql_query('UPDATE dealers AS d, (SELECT agent, COUNT(*) AS agentcount, registration_timestamp FROM users GROUP BY agent) AS u
SET d.thisperiodusercount = u.agentcount + d.usercountbalance
WHERE d.username = u.agent AND u.registration_timestamp > d.registration_timestamp AND u.registration_timestamp < d.expiredtime') or die(mysql_error());

I'd suggest you don't use the dealer username as a key in users table; use dealer ID instead! It's basic mysql:
first of all, numeric keys have amuch smaller fingerprint in the db and work much faster than textual keys
second: if you used username as a key and a dealer should change his uername at some point, you would have to update all the rows in the users table that correspond to that dealer ;)

[x86phre3x]

Hi there, thanks for the tips. I'll put that in mind. I will give this code a try. I really appreciate it though.

[x86phre3x]

UPDATE:

The query returns "0 row(s) affected". I'm still looking at the code, but if you find something, do let me know.

[johny_d]

UPDATE:

The query returns "0 row(s) affected". I'm still looking at the code, but if you find something, do let me know.

The code is fine, but it's probably something that don't match the real structure of your tables. Or maybe you don't have users/dealers in the db that match those conditions in the query.
Do a mysql export for the dealers and users tables structure and data, put it in a zip and post it here as an attachment, so I can test with the same structure and data you have.

[x86phre3x]

Hi there, ya, the expired time was wrong in my DB (on my test data). When I convert from date to Unix Timestamp, there was some mistake on the year, it should be 2010 instead of 2009, that is why the criteria doesn't match. Your code works well. I really appreciate it though.

[johny_d]

You're welcome!