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 12 of 12
  1. #1
    New Coder
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    91
    Thanks
    21
    Thanked 0 Times in 0 Posts

    Update MySQL Database -> Multiple Items

    Hey guys,

    Simple question, when I retrieve data from my MySQL database, for example the 'id' is 4, I wish to have 12 select menus displayed, ofcourse all with a different name, like so:



    I just need four, but with two small ones behind them. Basically it means, the first select menu is for a name, the second for "apples" and the third for "bananas".

    In other words, when the database says "4", I need to enter four names, and how many apples and bananas they collected.

    Anyone can help me with this?
    Last edited by Sylvester21; 05-18-2009 at 12:35 PM.
    Thank you for your time to read my post

  • #2
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    PHP Code:
    <?php

    $num 
    4// you get this from the database

    for($i 0$count = (int) $num$i $count$i++)
    {
        
    ?>
        <div style="float: left;">
            <label for="name">Name</label>
            <select id="name" name="name[<?php echo $i?>]">
                <option></option>
                <option>1</option>
                <option>2</option>
                <option>3</option>
            </select>

            <label for="apples">Apples</label>
            <select id="apples" name="apples[<?php echo $i?>]">
                <option></option>
                <option>1</option>
                <option>2</option>
                <option>3</option>
            </select>

            <label for="bananas">Bananas</label>
            <select id="bananas" name="bananas[<?php echo $i?>]">
                <option></option>
                <option>1</option>
                <option>2</option>
                <option>3</option>
            </select>
        </div>
        <br clear="both" />
        <?php
    }

    ?>
    Last edited by Inigoesdr; 05-17-2009 at 04:08 PM.

  • Users who have thanked Inigoesdr for this post:

    Sylvester21 (05-22-2009)

  • #3
    New Coder
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    91
    Thanks
    21
    Thanked 0 Times in 0 Posts
    Thanks a lot, this works quite nicely, but I have ran into the next problem, which might be simple.

    Let's say for example I have four people collecting apples and bananas, and each day I enter the new results. I want to display on my website how many apples and bananas each person collected. So for example:

    Name1: 34 Apples - 56 Bananas
    Name2: 15 Apples - 86 Bananas
    Name3: 72 Apples - 54 Bananas
    Name4: 12 Apples - 89 Bananas

    What is the best way to put this into the database each time, and the best way to query this? If you know what I mean?

    Thanks a lot in advance!!!!
    Thank you for your time to read my post

  • #4
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Quote Originally Posted by Sylvester21 View Post
    What is the best way to put this into the database each time
    Loop throught the arrays that PHP will create from the form fields(name, apples, bananas) and insert them into a table. Here is a basic example:
    PHP Code:
    foreach($_POST['name'] as $k => $v)
    {
        
    $name mysql_real_escape_string($v);
        
    $apples = (int) $_POST['apples'][$k];
        
    $bananas = (int) $_POST['bananas'][$k];
        
    $result mysql_query("INSERT INTO `myTable` (`name`, `apples`, `bananas`) VALUES('$name', $apples, $bananas)");
        if(
    $result && mysql_affected_rows() == 1)
            echo 
    'Row inserted successfully.';
        else
            die(
    'Failed to insert row.');

    You'll have to adjust it to match your database.
    Quote Originally Posted by Sylvester21 View Post
    and the best way to query this?
    Are you planning to keep a running total of everyone, or a daily count that you will sum up to display the totals?

  • Users who have thanked Inigoesdr for this post:

    Sylvester21 (05-22-2009)

  • #5
    New Coder
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    91
    Thanks
    21
    Thanked 0 Times in 0 Posts
    A total of everyone. For example, I've got two tables, one is called "people" and the other one is called "fruit". In the fruit table there are some fields, "apples", "bananas" and so on, but each time I insert it, it has to be added, not updated, else I could just fetch the data from the database and add the new results, OR is this just the best way to handle things? I'm struggling with how to best go around with this.

    Do I insert the person's id along with the count of apples and bananas, and then later on do something like:

    SELECT FROM `fruit`
    WHERE `person_id` = `value`

    ... I'm not getting how to do it and then display it properly.

    Example from day 1:

    Field name: p_id1: person_id: 1 - Apples: 10
    Field name: p_id2: person_id: 2 - Apples: 20
    Field name: p_id3: person_id: 3 - Apples: 30
    Field name: p_id4: person_id: 4 - Apples: 40

    Example from day 2:

    Field name: p_id1: person_id: 2 - Apples: 20
    Field name: p_id2: person_id: 3 - Apples: 30
    Field name: p_id3: person_id: 4 - Apples: 40
    Field name: p_id4: person_id: 5 - Apples: 50

    Ofcourse it starts with inserting it. Do I insert it in a seperate table, 'fruit', or simply have two extra fields in the "users" table, "apples" and "bananas", and simply update those two fields each time another result has been entered? I hope you're following me with this ^^

    The problem is, each day different people and different results will be inserted. How is the best way to handle this?
    Thank you for your time to read my post

  • #6
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    If you just need a total then you only need the one "users" table. Add the columns you want to it, and you can run something like:
    Code:
    UPDATE `users` SET `apples` = `apples` + 10, `bananas` = `bananas` + 20 WHERE `user_id` = 123
    So yes, this is basically the last thing you mentioned in your last post. You would, obviously(hopefully), have to add them the first time.

  • Users who have thanked Inigoesdr for this post:

    Sylvester21 (05-22-2009)

  • #7
    New Coder
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    91
    Thanks
    21
    Thanked 0 Times in 0 Posts
    BUT when I need to update this for three or four users at the same time, is that even possible? If so, THAT would be the solution.
    Thank you for your time to read my post

  • #8
    New Coder
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    91
    Thanks
    21
    Thanked 0 Times in 0 Posts
    I have found these two classes on php.net:

    PHP Code:
    class MySQLDB
    {
       private 
    $connection;          // The MySQL database connection

       /* Class constructor */
       
    function MySQLDB(){
          
    /* Make connection to database */
          
    $this->connection mysql_connect(DB_SERVERDB_USERDB_PASS) or die(mysql_error());
          
    mysql_select_db(DB_NAME$this->connection) or die(mysql_error());
       }

       
    /* Transactions functions */

       
    function begin(){
          
    $null mysql_query("START TRANSACTION"$this->connection);
          return 
    mysql_query("BEGIN"$this->connection);
       }

       function 
    commit(){
          return 
    mysql_query("COMMIT"$this->connection);
       }
       
       function 
    rollback(){
          return 
    mysql_query("ROLLBACK"$this->connection);
       }

       function 
    transaction($q_array){
             
    $retval 1;

          
    $this->begin();

             foreach(
    $q_array as $qa){
                
    $result mysql_query($qa['query'], $this->connection);
                if(
    mysql_affected_rows() == 0){ $retval 0; }
             }

          if(
    $retval == 0){
             
    $this->rollback();
             return 
    false;
          }else{
             
    $this->commit();
             return 
    true;
          }
       }

    };

    /* Create database connection object */
    $database = new MySQLDB;

    // then from anywhere else simply put the transaction queries in an array or arrays like this:

       
    function function(){
          global 
    $database;

          
    $q = array ( 
             array(
    "query" => "UPDATE table WHERE something = 'something'"),
             array(
    "query" => "UPDATE table WHERE something_else = 'something_else'"),
             array(
    "query" => "DELETE FROM table WHERE something_else2 = 'something_else2'"),
          );

          
    $database->transaction($q);

       } 
    PHP Code:
    <?php

    // $table - name of the mysql table you are querying
    // $exceptions - fields that will not be inserted into table
    //               i.e. 'submit, action, '; (note trailing comma and space!)
    // $sql_type - has to be 'insert' or 'update'
    // $sql_condition - have to define this if $sql_type = 'update'
    //                  i.e. "userID = '".$_POST['userID']."'"
    function formToDB($table$exceptions ''$sql_type 'insert'$sql_condition NULL) {

      
    // define some vars
      
    $fields '';
      
    $values '';
      
      
    // format input fields into sql
      
    foreach ($_POST as $field => $value) {
        if (!
    preg_match("/$field, /"$exceptions)) {
          
    $value mysql_real_escape_string($value);
          if (
    $sql_type == 'insert') {
            
    $fields .= "$field, ";
            
    $values .= "'$value', ";
          }
          else {
            
    $fields .= "$field = '$value', ";
          }
        }
      }
      
      
    // remove trailing ", " from $fields and $values
      
    $fields preg_replace('/, $/'''$fields);
      
    $values preg_replace('/, $/'''$values);
      
      
    // create sql statement
      
    if ($sql_type == 'insert') {
        
    $sql "INSERT INTO $table ($fields) VALUES ($values)";
      }
      elseif (
    $sql_type == 'update') {
        if (!isset(
    $sql_condition)) {
          echo 
    'ERROR: You must enter a sql condition!';
          exit;
        }
        
    $sql "UPDATE $table SET $fields WHERE $sql_condition";
      }
      else {
        echo 
    'ERROR: Invalid input for argument $sql_type: must be "insert" or "update"';
        exit;
      }
      
      
    // execute sql
      
    if (mysql_query($sql)) {
        return 
    true;
      }
      else {
        
    //echo mysql_error();
        
    return false;
      }

    // end of function formToDB()

    // Example for inserting new row
    formToDB('users''submit, ');

    // Example for updating existing row
    formToDB('users''submit, userID, ''update'"userID = '".$_POST['userID']."'");

    ?>
    Would any of these work? The question is, how to use this with my form, with `person`, `apples` and `bananas`?
    Thank you for your time to read my post

  • #9
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Quote Originally Posted by Sylvester21
    BUT when I need to update this for three or four users at the same time, is that even possible? If so, THAT would be the solution.
    Use a loop to update each row. It seems like you're trying to make this something more complicated than it needs to be. You don't need a monolithic class, or some ridiculously long query... just a simple loop will do. I provided you with an example before that should work, or be close to what you're looking for, if you change the INSERT statement to an UPDATE statement.
    Quote Originally Posted by Sylvester21 View Post
    Would any of these work?
    No.

  • Users who have thanked Inigoesdr for this post:

    Sylvester21 (05-22-2009)

  • #10
    New Coder
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    91
    Thanks
    21
    Thanked 0 Times in 0 Posts
    I am using your example from post #2 and also to create the update loop:

    PHP Code:
    <?php
    if($_POST['Submit']){

    $num 3;

    for(
    $i 1$count = (int) $num$i $count$i++)
    {

    $sql="UPDATE stats SET apples = apples + '".$_POST['apples']."', bananas = bananas + '".$_POST['bananas']."' WHERE id = '".$_POST['id']."'";

    $result=mysql_query($sql);
    }

    ?>
    My input stuff is exactly like your second post. But whatever I try, it does not work. What am I doing wrong?

    I have also tried something like this:

    PHP Code:
    $sql="UPDATE stats SET apples = apples + '".$_POST['apples[$i]']."', bananas = bananas + '".$_POST['bananas[$i]']."' WHERE id = '".$_POST['id[$i]']."'"
    Thank you for your time to read my post

  • #11
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Here, this is a working example. Hopefully this will be a little more enlightening for you:
    PHP Code:
    <form action="test.php" method="post">
    <?php
    mysql_connect
    ('localhost''root''');
    mysql_select_db('test');

    if(!empty(
    $_POST['name']))
    {
        foreach(
    $_POST['name'] as $k => $v)
        {
            
    $name mysql_real_escape_string($v);
            
    $apples = (int) $_POST['apples'][$k];
            
    $bananas = (int) $_POST['bananas'][$k];
            
    $result mysql_query("UPDATE `fruits` SET `apples` = `apples` + $apples, `bananas` = `bananas` + $bananas WHERE `name` = '$name'");
            if(
    $result && mysql_affected_rows() == 1)
                echo 
    'Updated counts for ' $v '<br />';
             else
                 echo 
    'Update failed for ' $v '<br />';
        }
    }

    $usersQuery mysql_query('SELECT `name` FROM `fruits` WHERE 1');
    $i 0;
    while(
    $row mysql_fetch_assoc($usersQuery))
    {
        
    ?>
        <div style="float: left;">
            <label for="name">Name</label>
            <input type="text" value="<?php echo $row['name']; ?>" disabled="disable" />
            <input type="hidden" name="name[<?php echo $i?>]" value="<?php echo $row['name']; ?>" />

            <label for="apples">Apples</label>
            <select id="apples" name="apples[<?php echo $i?>]">
                <option></option>
                <option>1</option>
                <option>2</option>
                <option>3</option>
            </select>

            <label for="bananas">Bananas</label>
            <select id="bananas" name="bananas[<?php echo $i?>]">
                <option></option>
                <option>1</option>
                <option>2</option>
                <option>3</option>
            </select>
        </div>
        <br clear="both" />
        <?php
        $i
    ++;
    }
    ?>
    <input type="submit" value="Save Counts" />
    </form>
    And here is the DB table I used to test it:
    Code:
    CREATE TABLE `fruits` (
      `name` varchar(50) NOT NULL,
      `apples` int(10) NOT NULL,
      `bananas` int(10) NOT NULL,
      PRIMARY KEY (`name`),
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
    INSERT INTO `fruits` (`name`, `apples`, `bananas`) VALUES
    	('Tom',0,0),
    	('Cliff',0,0),
    	('Adam',0,0),
    	('Greg',0,0);

  • Users who have thanked Inigoesdr for this post:

    Sylvester21 (05-22-2009)

  • #12
    New Coder
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    91
    Thanks
    21
    Thanked 0 Times in 0 Posts
    Thanks a lot man, this works quite nicely. Have been searching on google and what not, but haven't found anything usefull. And you have helped me really good, thanks for the "hard" work ;-)
    Thank you for your time to read my post


  •  

    Tags for this Thread

    Posting Permissions

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