Update MySQL Database -> Multiple Items

[Sylvester21]

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?

[Inigoesdr]

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
}

?>

[Sylvester21]

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!!!!

[Inigoesdr]

Originally Posted by Sylvester21

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.

Originally Posted by Sylvester21

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?

[Sylvester21]

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?

[Inigoesdr]

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:

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.

[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.

[Sylvester21]

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_SERVER, DB_USER, DB_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`?

[Inigoesdr]

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.

Originally Posted by Sylvester21

Would any of these work?

No.

[Sylvester21]

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]']."'"; 


[Inigoesdr]

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:

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);

[Sylvester21]

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 ;-)