HTML form select multiple -> MySQL / PHP query...

[Jim R]

Right now I have a form with a drop down box, but my Users would like it if they could select multiple options.  As the form is currently set up, after submission, it UPDATES the database.  

$nameFirst = $_POST['nameFirst'];
	$nameLast = $_POST['nameLast'];
	$user_email = $_POST['email'];
	$square = $_POST['square'];

  
  
  mysqli_query($con,"UPDATE selection
  		SET nameFirst = '" . $nameFirst . "',
  			nameLast = '". $nameLast ."',
  			email = '". $user_email ."'
  		WHERE '". $square . "' = id");
  

With the multiple selections, the form will need to UPDATE a row for each selection.  

I’m somewhat assuming I take the potential multiple selections and Exploding them.  Correct?

 

[requinix]

Depends what mechanism they use to select multiple options.

One way or another you end up with an array of IDs. Make sure every single value in there is a valid ID (eg, is a number) then yes, you could use an IN (...) clause instead of your id=.

inb4 prepared statements

[Barand]

Why the hell would you want to put the same user's names and email address into multiple records? Think "normalization"!. Database tables are not spreadsheets.

[Jim R]

4 hours ago, Barand said:

Why the hell would you want to put the same user's names and email address into multiple records? Think "normalization"!. Database tables are not spreadsheets.

I just do on this one.  It’s 100 predetermined rows of data.  The User picks an available number (ID), and whether or not I was using userID vs names/emails doesn’t change my issue of them grabbing multiple available numbers.  Right now they have to choose one a time, which is fine, I guess, but if I can allow them to select more at a time, it simplifies it on their end.  

(It’s just a bunch of friends choosing up squares for sporting events.  I’m trying to automate it vs. having one of us to keep track on manually.)

I have nothing set up to check in real time if User A picked a number since User B had loaded the page too.  (Simultaneous selections.). I just have it set to check on each page load, altered by submission.  

[Jim R]

8 hours ago, requinix said:

Depends what mechanism they use to select multiple options.

One way or another you end up with an array of IDs. Make sure every single value in there is a valid ID (eg, is a number) then yes, you could use an IN (...) clause instead of your id=.

inb4 prepared statements

Yes, the selection is predetermined numbers, and as the page loads, there is a query to determine which numbers/ID are still available.  (As noted to Barand above, that’s the only time it checks to see which numbers are available.)

[Barand]

20 minutes ago, Jim R said:

(It’s just a bunch of friends choosing up squares for sporting events.

In that case, doesn't your query need to to check if a square is already taken? In between you displaying available squares and a user picking his squares, someone else might have picked one or more of the same squares.

[Jim R]

12 minutes ago, Barand said:

In that case, doesn't your query need to to check if a square is already taken? In between you displaying available squares and a user picking his squares, someone else might have picked one or more of the same squares.

Yes, and I'm viewing this as a one thing at a time situation.  My group are all friends, and it won't be at a volume that it would likely happen.  If it did, it would be easily correctable.  Eventually I would like to make that check in real time and not just on page loads.  

[Barand]

I would recomend that you don't prepopulate the "square" and then update.

If you start with an empty table ...

CREATE TABLE `square` (
  `square_id` int(11) NOT NULL ,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`square_id`),
  KEY `idx_square_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

... and add squares as they are selected then it becomes impossible for a user to poach someone else's square - the primary key will make it a strictly "first come first served" situation. Should cut down the bar-room brawls.

[Jim R]

On 6/7/2019 at 10:50 AM, Barand said:

I would recomend that you don't prepopulate the "square" and then update.

If you start with an empty table ...

CREATE TABLE `square` (
  `square_id` int(11) NOT NULL ,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`square_id`),
  KEY `idx_square_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

... and add squares as they are selected then it becomes impossible for a user to poach someone else's square - the primary key will make it a strictly "first come first served" situation. Should cut down the bar-room brawls.

 

How will they know which squares are available, while keeping the restraints of 1-100?  The code only allows for available squares to be printed in the dropdown.  There is no poaching.  the only issue is simultaneous selection, which shouldn't be too much of a problem given the size of the group and timestamp.  Even if two people the same number at almost the same time, the second one would overwrite the first one, but the first one's confirmation would come across first.  

[Barand]

38 minutes ago, Jim R said:

How will they know which squares are available, while keeping the restraints of 1-100?  

You have 100 squares (1 - 100). Squares 5, 10, and 15 get taken. It isn't rocket science to work out that 1-4, 6-9, 11-14 and 16-100 are still available.

• Create an array with keys 1 - 100.
• As squares are taken, store the square number and the user.
• Query the stored data and allocate the users to the array using the square number as the key.
• Empty array elements are still available.

Store

square  user
------  ---------
  5     Curly
 10     Larry
 15     Mo

Load into array

squares = Array
(
    [1] => 
    [2] => 
    [3] => 
    [4] => 
    [5] => Curly
    [6] => 
    [7] => 
    [8] => 
    [9] => 
    [10] => Larry
    [11] => 
    [12] => 
    [13] => 
    [14] => 
    [15] => Mo
    [16] => 
    [17] => 
    [18] => 
    [19] => 
    [20] =>
      .
      .
    [100] =>
)

You and Fred visit the site at the same time. You both see square 25 is available. It's your birthday so you pick it for luck. DB table updated to "25 - Jim". On Fred's screen it is still available and he picks 25. DB now overwrites Jim with Fred for square 25. When the results come in and 25 has won, how do you prove you had it first? After all, the DB says it's Fred's square.

[Jim R]

1 hour ago, Barand said:

You and Fred visit the site at the same time. You both see square 25 is available. It's your birthday so you pick it for luck. DB table updated to "25 - Jim". On Fred's screen it is still available and he picks 25. DB now overwrites Jim with Fred for square 25. When the results come in and 25 has won, how do you prove you had it first? After all, the DB says it's Fred's square.

Timestamp of email confirmation, figured out in real time, or at least as I check them nightly.  The email count equals the number of squares taken, so if it's off, I know to look.  I could set it up to check on the UPDATE and return a message, but I was getting it out quickly.  I appreciate what you DM'd, and I'll look at it for the same type of event next month.  

 

I'm still in search of an answer for the original question.  

[Barand]

9 hours ago, Jim R said:

I'm still in search of an answer for the original question.  

Here's some sample code. Note the "[ ]" in the name of the dropdown in the form so multiple values are posted as an array

<?php
include("db_inc.php");     // defines DB credentials (HOST etc)
$db = pdoConnect('jimr');  // connects to database

//
//  PROCESS POSTED SELECTIONS
//
if ($_SERVER['REQUEST_METHOD']=='POST') {
    
    $placeholders = array_fill(0, count($_POST['square']), '?' );
    $placeStr = join(',', $placeholders);               // puts a "?" in placeStr for each selected square
    $params[] = $_POST['name'];                         // put name in params array
    foreach ($_POST['square'] as $s) $params[] = $s;    // add squares to params array
    
    $stmt = $db->prepare("UPDATE square 
            SET user_id = ?
            WHERE square_id IN ($placeStr)");
    $stmt->execute(  $params  );
}

/**
* converts 1-100 to ROW - COL format (0-0 to 9-9)
*/
function squareOptions()
{
    $opts = '';
    foreach (range(1,100) as $s)  {
        $sqr = sprintf("%d – %d", intdiv($s-1,10), ($s-1)%10 );
        $opts .= "<option value='$s'>$sqr</option>\n";
    }
    return $opts;
}
?>
<html>
<head>
<meta http-equiv="content-language" content="en">
<meta name="generator" content="PhpED 18.0 (Build 18044, 64bit)">
<title>Example</title>
</head>
<body>
    <form method="post">
        Name <select name='name' >
                <option value=''>-select your name -</option>
                <option value='1'>Curly</option>
                <option value='2'>Larry</option>
                <option value='3'>Mo</option>
             </select>
        <br>
        Choose squares <br>
        <select name='square[]' multiple="multiple" size='10'>
            <?=squareOptions()?>
            </select>
        <br><br>
        <input type="submit" name="btnSubmit" value="Submit">  
    </form>
</body>
</html>