CODEWITHSUNDEEP
phpmysqlsqlarraysprepared-statement
user2704687
user2704687

Reputation: 185

Prepared statement result in query

Converted my normal SQL queries to prepared statements a while back, but now I want to implement a function and I'm not sure how. Below is my current code:

$stmt = $db->stmt_init();

$query = "pretty long query...";

$stmt->prepare($query);

  $stmt->bind_param('ssii', $dienstenpage, $huidigegemeente, $startpoint, $limit);

  $stmt->execute();


  $stmt->bind_result($prepid, $prephoofdrubriek, $prepplaats, $prepbedrijfsnaam, $prepgemeente, $prepbedrijfsomschrijving, $prepbedrijfsslogan, $prepstraatnaam, $prephuisnummer, $preppostcode, $preptelefoonnummer, $prepfax, $prepemail, $prepwebsite, $prepbedrijfslogo, $prepdubb);

  $stmt->store_result();

  $numrows = $stmt->num_rows;



   while($stmt->fetch()) {

Here appears a nice div with all the data for every result (row)

}

All works fine, no hick-ups there. However, for some of the page I want to shuffle the first 20 rows (only the first 20!) of the result. Thanks to the help of others I learned the best way to do that is to put the result in a query and do this:

$first20 = array_splice($allResults,0,20);
shuffle($first20);
array_splice($allResults,0,0,$first20);

However, I'm not sure how to put the results in array? I should be able to do that in the while loop ($stmt->fetch), but not sure how? Once the results are in an array I can slice it with the code above, put in back in the array and display the results in the new order. How do I insert the rows in an array?

THE ANSWER WAS (COMPLETED CODE):

 $array = array();

    while($stmt->fetch()) { 
    $array[] = array('prepid'=>$prepid, 'prephoofdrubriek'=>$prephoofdrubriek, 'prepplaats'=>$prepplaats, 'prepbedrijfsnaam'=>$prepbedrijfsnaam);
    }

    $first20 = array_splice($array,0,20);
    shuffle($first20);
    array_splice($array,0,0,$first20);

    $number = count($array);

    $cnt = $number; // if you wanted to get them all you could set it to $cnt = array_count($array);
        for ($i=0; $i<=$cnt; $i++) {
        echo $array[$i]['prepid'];
        echo "<Br />";
   }

Upvotes: 0

Views: 124

Answers (4)

netdigger
netdigger

Reputation: 3789

Why not simply use fetchAll()?

$arr = array_slice($stmt->fetchAll(), 0, 20);
shuffle($arr);
foreach ($arr as $a)
{
    echo $a['prepaid'];
}

If that takes up to much memory (as fetchAll() returns all rows) you can use fetch() but limit to 20 (no need to slice).

$i = 0;
$arr = array();

while (($row = $stmt->fetch()) && $i++ < 20)
{
    $arr[] = $row;
}

shuffle($arr);

foreach ($arr as $a)
{
    echo $a['prepaid'];
}

Upvotes: 1

LifeQuery
LifeQuery

Reputation: 3282

Save the $stmt->fetch() into a variable and add a parameter.

$result = $sth->fetch(PDO::FETCH_ASSOC);

For multiple rows:

 while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
       $result = array_push($result, $row);
    }

Now you've extracted your data and you can array_splice it

$first20 = array_splice($result ,0,20);
shuffle($first20);
array_splice($result,0,0,$first20);

Upvotes: 1

user3470953
user3470953

Reputation: 11315

You can do like the following

$arr_val=array();

while($stmt->fetch()) {
    $arr_val[] = $stmt->fetch() //or your result row;
}

$sliced_arr = array_slice($arr_val, 0, 20);
array_splice($arr_val, 0, 20);
shuffle($sliced_arr);
$arr_new_val = array_merge($sliced_arr,$arr_val);
print_r($arr_new_val);

Upvotes: 1

James Waring
James Waring

Reputation: 356

What i would do is put it all into an array like you said and then echo it out.

$array = array();

while($stmt->fetch()) { 
    $array[] = array('prepid'=>$prepid, 'prephoofdrubriek'=>$prephoofdrubriek, 'prepplaats'=>$prepplaats, 'prepbedrijfsnaam'=>$prepbedrijfsnaam);
}

I have only put a few in the array to demonstrate how to do it.

Then to get the data out I would use a for loop.

   $cnt = 20; // if you wanted to get them all you could set it to $cnt = array_count($array);
   foreach ($i = 0; $i <= $cnt; $i++) { 
        echo $array[$i]['prepid'];
   }

Upvotes: 2

Related Questions