Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New to the CF scene
    Join Date
    Mar 2010
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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.
    Last edited by x86phre3x; 03-09-2010 at 02:18 AM. Reason: Missed some important info...

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,502
    Thanks
    8
    Thanked 1,089 Times in 1,080 Posts
    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 Code:
    <?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?

  • #3
    New to the CF scene
    Join Date
    Mar 2010
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'm getting
    Code:
    Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING
    on this line
    PHP Code:
    <td>$row['ct']</td

  • #4
    New to the CF scene
    Join Date
    Mar 2010
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  • #5
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,502
    Thanks
    8
    Thanked 1,089 Times in 1,080 Posts
    Now that we have an array of dealer names and quantity (count),
    we can use that for part 2.

    PHP Code:
    <?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' "
    );

    }
    ?>

  • Users who have thanked mlseim for this post:

    x86phre3x (03-10-2010)

  • #6
    New to the CF scene
    Join Date
    Mar 2010
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  • #7
    New to the CF scene
    Join Date
    Mar 2010
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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 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

    PHP Code:
    mysql_query("UPDATE dealers SET numberofusers = '$nu'
    WHERE username = '$name' AND '$reg' BETWEEN registration_timestamp AND (registration_timestamp + 2629744)"
    ); 
    Last edited by x86phre3x; 03-10-2010 at 07:04 AM.

  • #8
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,502
    Thanks
    8
    Thanked 1,089 Times in 1,080 Posts
    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.

  • #9
    New to the CF scene
    Join Date
    Mar 2010
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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:

    Code:
    <?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' ");
    
    }
    ?>

  • #10
    New to the CF scene
    Join Date
    Mar 2010
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi all,

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

    PHP Code:
    //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.
    Last edited by x86phre3x; 04-05-2010 at 10:14 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •