Jump to content


Photo

Prepare statement query not executing

php pdo mysql

Best Answer Psycho, 01 September 2017 - 05:22 PM

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));
Go to the full post


  • Please log in to reply
3 replies to this topic

#1 shades

shades
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 01 September 2017 - 02:25 PM

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



#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,117 posts

Posted 01 September 2017 - 03:08 PM

Prepare the statement once, before the loop.

Inside the loop you bind the new values and execute.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Psycho

Psycho
  • Moderators
  • Move along, nothing to see here
  • 11,928 posts
  • LocationCanada

Posted 01 September 2017 - 05:22 PM   Best Answer

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, 01 September 2017 - 05:22 PM.

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#4 shades

shades
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 02 September 2017 - 02:58 PM

 

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.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users