shades Posted September 1, 2017 Share Posted September 1, 2017 Hi, Can someone point out the mistake ? I tested the query in MySQL workbench and it works fine but in the prepare statement it is not executing and also I get no error. if(isset( $_POST['name'], $_POST['samplecount'], $_POST['decks'])) { $name = $_POST['name']; $sample = $_POST['samplecount']; $decks = $_POST['decks']; $rows = ($sample/$decks); for ($i=0; $i<$decks; $i++){ for ($j=1; $j<=$rows; $j++) { $stmt = $dbconnect -> prepare(" INSERT INTO decks (id, dnumber, vtext) ( SELECT :id, :dnumber, (SELECT r.vtext FROM rsample r WHERE r.groupname = :groupname AND r.vtext NOT IN (SELECT vtext FROM decks) ) ORDER BY rand() LIMIT 1 ) "); $stmt -> bindValue(':id', $i); $stmt -> bindValue(':dnumber', $j); $stmt -> bindValue(':groupname',$name) $stmt -> execute (); } } } In my code I use name = "test" value of samplecount = 8 and decks = 2. So $rows will be 4. My Requirement: Out of the 8 texts. I want to get the data like below and I want the texts to be in random order. DeckId DeckNumber Text D1 1 CC D1 2 AA D1 3 ZZ D1 4 JJ D2 1 FF D2 2 BB D2 3 CC D2 4 WW Any help will be greatly appreciated. Thanks Link to comment Share on other sites More sharing options...
Barand Posted September 1, 2017 Share Posted September 1, 2017 Prepare the statement once, before the loop. Inside the loop you bind the new values and execute. Link to comment Share on other sites More sharing options...
Psycho Posted September 1, 2017 Share Posted September 1, 2017 Running loops and doing a select statement each time [especially with rand()] is a bad idea. here is what I think is a better approach. 1. Run ONE SELECT query to get all of the vtext values from the rsample table for the groupname 2. Populate those results into an array 3. User array_shuffle() to randomize the results of the array 4. Run the loops for $i & $j like you have now, but create a simple INSERT statement like this INSERT INTO decks (id, dnumber, vtext) VALUES (:id, :dnumber, :vtext) 5. Define the vtext value on each loop using array_shift() which will remove and return the first value in the array $stmt -> bindValue(':vtext', array_shit($vtextValuesAry)); Link to comment Share on other sites More sharing options...
shades Posted September 2, 2017 Author Share Posted September 2, 2017 Running loops and doing a select statement each time [especially with rand()] is a bad idea. here is what I think is a better approach. 1. Run ONE SELECT query to get all of the vtext values from the rsample table for the groupname 2. Populate those results into an array 3. User array_shuffle() to randomize the results of the array 4. Run the loops for $i & $j like you have now, but create a simple INSERT statement like this INSERT INTO decks (id, dnumber, vtext) VALUES (:id, :dnumber, :vtext) 5. Define the vtext value on each loop using array_shift() which will remove and return the first value in the array $stmt -> bindValue(':vtext', array_shit($vtextValuesAry)); Thank you. The solution is perfect. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.