...

View Full Version : [PHP & MySQL]: Run a table query while updating another table with the result



x86phre3x
03-09-2010, 02:02 AM
Hi,

This is my first post in this forum. I would like to seek some help in my php (involves MySQL) code. Not sure to put this in PHP or MySQL section though.

I have 2 table named "users" & "dealers" with below attributes (truncated to relevant ones):

users
- id
- username
- dealer (which contains the "username" of dealer in "dealers" table).
- registration_timestamp (unix timestamp format)

dealers
-id
-username
-numberofusers
-registration_timestamp (unix timestamp format)

What I would like to do is, count the number of users with the "dealer" consisting of EVERY username in "dealers" and the user registration_timestamp is BETWEEN dealer registration_timestamp AND dealer registration_timestamp + 1 month then UPDATE the "numberofusers" of that username with the result. Could somebody help me?

Example, let's say we have 10 users with dealer = mrjohn, and this user was registered BETWEEN the registration date of mrjohn AND 1 month after, update mrjohn "numberofusers" (in dealers database) with value "10".

Thanks.

mlseim
03-09-2010, 02:25 AM
This will be hard to answer without being able to test it, and there's probably several ways to do it.

Here's my shot at it ... the first of two parts ...

First, let's test to see if we count the dealers correctly (part 1):



<?php

// connect to your database here

// here's the query to test ...
$query = "SELECT `dealer`, COUNT(`dealer`) AS ct " .
"FROM users " .
"GROUP BY `dealer`" .
"ORDER BY `dealer` ASC";
$result = mysql_query($query);

echo"
<table cellspacing='5'>
";

// test the result of the query
while ($row = mysql_fetch_assoc($result)) {
echo"
<tr>
<td>$row['dealer']</td>
<td>$row['ct']</td>
</tr>
";
}

echo "</table>";

?>


You should have a list of dealers from "users" table and how many of each were found.

Did that part work OK?

x86phre3x
03-09-2010, 03:59 AM
I'm getting
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRINGon this line
<td>$row['ct']</td>

x86phre3x
03-09-2010, 06:09 AM
Anyway, the query works fine when I directly query the DB. I can work on the php later. So, let's continue with part 2.

mlseim
03-09-2010, 01:14 PM
Now that we have an array of dealer names and quantity (count),
we can use that for part 2.



<?php

// connect to your database here

// here's the query to test ...
$query = "SELECT `dealer`, COUNT(`dealer`) AS ct " .
"FROM users " .
"GROUP BY `dealer`" .
"ORDER BY `dealer` ASC";
$result = mysql_query($query);

// loop through the array
while ($row = mysql_fetch_assoc($result)) {
$nu=$row['ct'];
$name=$row['dealer'];

// update the "dealers" table.
mysql_query("UPDATE dealers SET numberofusers = '$nu'
WHERE username = '$name' ");

}
?>

x86phre3x
03-09-2010, 01:48 PM
Hi there, thanks. I will give it a try tommorow. Will let you know if it works. It looks promising though. Thank you so much.

x86phre3x
03-10-2010, 06:21 AM
Hi there again,

I think you missed my other requirement for the date of registration. Anyway, below is my code with that, can you confirm the code?


<?php

// connect to your database here

// here's the query to test ...
$query = "SELECT `dealer`, `registration_timestamp`, COUNT(`dealer`) AS ct " .
"FROM users " .
"GROUP BY `dealer`" .
"ORDER BY `dealer` ASC";
$result = mysql_query($query);

// loop through the array
while ($row = mysql_fetch_assoc($result)) {
$nu=$row['ct'];
$name=$row['dealer'];
$reg=$row['registration_timestamp'];

// update the "dealers" table.
mysql_query("UPDATE dealers SET numberofusers = '$nu'
WHERE username = '$name' AND (registration_timestamp + 2629744) > '$reg' AND '$reg' > registration_timestamp");

}
?>

Or should I use


mysql_query("UPDATE dealers SET numberofusers = '$nu'
WHERE username = '$name' AND '$reg' BETWEEN registration_timestamp AND (registration_timestamp + 2629744)");

mlseim
03-10-2010, 12:30 PM
I'm sure hoping someone else comes up with the best answer to that. I'm not
that experienced at MySQL to know which syntax is proper ... and I can't test
your script myself.

Let's see if anyone else knows this one.

x86phre3x
04-05-2010, 07:20 AM
Hi all,

I need some more help. The script contributed by mlseim does work for my program. Anyhow, I would like to do something more than that. While updating the data, I would like to add certain value to it.

Example, after getting all the amount of dealer, I would like to get a value from one of the column of that dealer and add it to the total number or member registered under that dealer. Something like this:


<?php

// connect to your database here

// here's the query to test ...
$query = "SELECT `dealer`, COUNT(`dealer`) AS ct " .
"FROM users " .
"GROUP BY `dealer`" .
"ORDER BY `dealer` ASC";
$result = mysql_query($query);

// loop through the array
while ($row = mysql_fetch_assoc($result)) {
$nu=$row['ct'];
$name=$row['dealer'];

// update the "dealers" table.
mysql_query("UPDATE dealers SET numberofusers = '$nu' + balancecolumnofthisdealer
WHERE username = '$name' ");

}
?>

x86phre3x
04-05-2010, 09:59 AM
Hi all,

If this would help, below is my real code for my real applications


//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'");

}
}
}

I hope somebody can help me to correct the syntax as it doesn't update the database accordingly.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum