Jump to content

Prepare statement query not executing


Go to solution Solved by Psycho,

Recommended Posts

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 post
Share on other sites
  • Solution

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));
Edited by Psycho
Link to post
Share on other sites

 

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 post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.