Jim R Posted June 6, 2019 Share Posted June 6, 2019 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? Quote Link to comment Share on other sites More sharing options...
requinix Posted June 6, 2019 Share Posted June 6, 2019 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted June 6, 2019 Share Posted June 6, 2019 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. Quote Link to comment Share on other sites More sharing options...
Jim R Posted June 6, 2019 Author Share Posted June 6, 2019 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. Quote Link to comment Share on other sites More sharing options...
Jim R Posted June 6, 2019 Author Share Posted June 6, 2019 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.) Quote Link to comment Share on other sites More sharing options...
Barand Posted June 6, 2019 Share Posted June 6, 2019 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. Quote Link to comment Share on other sites More sharing options...
Jim R Posted June 6, 2019 Author Share Posted June 6, 2019 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 7, 2019 Share Posted June 7, 2019 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. Quote Link to comment Share on other sites More sharing options...
Jim R Posted June 8, 2019 Author Share Posted June 8, 2019 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 8, 2019 Share Posted June 8, 2019 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. Quote Link to comment Share on other sites More sharing options...
Jim R Posted June 8, 2019 Author Share Posted June 8, 2019 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 9, 2019 Share Posted June 9, 2019 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> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.