Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2006
    Location
    Lancashire, UK
    Posts
    473

    is this mysql query possible?

    instead of using a php while loop to update each mysql record in my table individually (it takes time) i was wondering if i could do this

    instead of writing a query to select each row at a time and unsing a
    while($row=mysql_fetch_array($result)) {

    and then working out how much resources to give to players, and updating each row indivually

    PHP Code:

    $amount
    =70;

    $result "UPDATE game SET resourceone=mine_resourceon*$amount"
    etc

    i was wondering if i could do it all in one query

    i have heard about update...select....or maybe just update game and give resources to each user all at once...rather than using a while loop to do it one by one

    can anyone help me?

  2. #2
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    2,549
    Best way to find out = test it yourself!

    If mine_resourceon is a row it will work that is. Example

    Code:
    mysql> create table game ( resourceone int, mineresource_one int );
    Query OK, 0 rows affected (0.24 sec)
    
    mysql> insert into game set resourceone='0',mineresource_one='15';
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from game;
    +-------------+------------------+
    | resourceone | mineresource_one |
    +-------------+------------------+
    |           0 |               15 |
    +-------------+------------------+
    1 row in set (0.00 sec)
    
    mysql> update game set resourceone=mineresource_one*2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from game;
    +-------------+------------------+
    | resourceone | mineresource_one |
    +-------------+------------------+
    |          30 |               15 |
    +-------------+------------------+
    1 row in set (0.00 sec)
    Server Management - AdminGeekZ.com
    Infrastructure Management, Web Application Performance, mySQL DBA. System Automation.
    WordPress/Magento Performance, Apache to Nginx Conversion, Varnish Implimentation, DDoS Protection, Custom Nginx Modules
    Check our wordpress varnish plugin. Contact us for quote: [email protected]

  3. #3
    Join Date
    Feb 2006
    Location
    Lancashire, UK
    Posts
    473
    thanks, but what i wanted to know was.....when there is more than one row in the table, will it update them differently, or set them all the same?

  4. #4
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    2,549
    The answer is yes, you just have to try it yourself to find out.

    Anyway I have also done it for you.

    Code:
    mysql> insert into game set resourceone='0',mineresource_one='15';
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into game set resourceone='0',mineresource_one='3';
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into game set resourceone='0',mineresource_one='20';
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from game;
    +-------------+------------------+
    | resourceone | mineresource_one |
    +-------------+------------------+
    |           0 |               15 |
    |           0 |                3 |
    |           0 |               20 |
    +-------------+------------------+
    3 rows in set (0.00 sec)
    
    mysql> update game set resourceone=mineresource_one*2;
    Query OK, 3 rows affected (0.01 sec)
    Rows matched: 3  Changed: 3  Warnings: 0
    
    mysql> select * from game;
    +-------------+------------------+
    | resourceone | mineresource_one |
    +-------------+------------------+
    |          30 |               15 |
    |           6 |                3 |
    |          40 |               20 |
    +-------------+------------------+
    3 rows in set (0.00 sec)
    Server Management - AdminGeekZ.com
    Infrastructure Management, Web Application Performance, mySQL DBA. System Automation.
    WordPress/Magento Performance, Apache to Nginx Conversion, Varnish Implimentation, DDoS Protection, Custom Nginx Modules
    Check our wordpress varnish plugin. Contact us for quote: [email protected]

  5. #5
    Join Date
    Feb 2006
    Location
    Lancashire, UK
    Posts
    473
    thanks

    by the way, are you using a shell to execute those queries?

  6. #6
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    2,549
    Yes, phpmyadmin will work too.

    -Scott
    Server Management - AdminGeekZ.com
    Infrastructure Management, Web Application Performance, mySQL DBA. System Automation.
    WordPress/Magento Performance, Apache to Nginx Conversion, Varnish Implimentation, DDoS Protection, Custom Nginx Modules
    Check our wordpress varnish plugin. Contact us for quote: [email protected]

  7. #7
    Join Date
    Feb 2006
    Location
    Lancashire, UK
    Posts
    473
    thanks for your help

Posting Permissions

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