How to select first row then other rows from a single mysql query? [duplicate]

问题: This question already has an answer here: pdo how to check if it is 1st record that retrieve from database? 2 answers How to separate first result of q...

问题:

I may be missing the obvious, how could I return the results I need? Take a look at this simple example:


$fetch_image = "SELECT image_url FROM table"; //returns about 10 rows
$stmt_img = $db->prepare($fetch_image);
$stmt_img->execute();
$stmt_img_result = $stmt_img->get_result();

while ($row_img = $stmt_img_result->fetch_assoc()) {
     $image = $row_image['image_url'];
     echo 'Image url: '.$image.'<br>'; //this will echo out 10 times.
}

The above code iterates through all 10 rows. How could I return the first row from the result set, and then the other 9 rows?

Edit: I'm trying to create an image previewer so that the first image shows up as the placeholder/main image then the other 9 rows show up as the thumbnails, something like this:

<div class="sp-wrap">
            <a href="images/1.jpg">
                <!-- This would be the placeholder/main/first image from the mysql result set -->
                <img src="images/1_tb.jpg" alt="">
            </a>
            <!-- In real life these would be dynamically generated -->
            <a href="images/3.jpg"><img src="images/3_tb.jpg" alt=""></a>
            <a href="images/4.jpg"><img src="images/4_tb.jpg" alt=""></a>
            <a href="images/5.jpg"><img src="images/5_tb.jpg" alt=""></a>
            <a href="images/6.jpg"><img src="images/6_tb.jpg" alt=""></a>
        </div>

回答1:

A simple boolean variable can distinguish the first row:

$first_row = true;

while ($row_img = $stmt_img_result->fetch_assoc()) {
     if ($first_row) {
         echo 'the first row';
         $image = $row_image['image_url'];
         echo 'Image url: '.$image.'<br>';
         $first_row = false;
     } else {
         $image = $row_image['image_url'];
         echo 'Image url: '.$image.'<br>'; //this will echo out 9 times.
     }
}

回答2:

You may try to get the first row, and then the rest. But you need an ORDER BY clause in your SQL statement that will order rows in the same way in every execution.

<?php
$fetch_image = "SELECT image_url FROM table"; //returns about 10 rows
$stmt_img = $db->prepare($fetch_image);
$stmt_img->execute();
$stmt_img_result = $stmt_img->get_result();

// Fetch first row
if ($row_first = $stmt_img_result->fetch_assoc()) {
     $image = $row_first['image_url'];
     echo 'Image url: '.$image.'<br>'; 
}

// Fetch next rows
while ($row_next = $stmt_img_result->fetch_assoc()) {
     $image = $row_next['image_url'];
     echo 'Image url: '.$image.'<br>'; 
}
?>

Another possible approach is to implement custom check for first row:

<?php
$fetch_image = "SELECT image_url FROM table"; //returns about 10 rows
$stmt_img = $db->prepare($fetch_image);
$stmt_img->execute();
$stmt_img_result = $stmt_img->get_result();

$first = true;
while ($row_img = $stmt_img_result->fetch_assoc()) {
   if ($first) {
      // Specific output for the first row
      $first = false;
      $image = $row_image['image_url'];
      echo 'Image url: '.$image.'<br>'; // this will echo out 1 time.
   } else {
      // Specific output for the next rows
      $image = $row_image['image_url'];
      echo 'Image url: '.$image.'<br>'; // this will echo out count - 1 times.
   }
}
?>

回答3:

make imageType Column in your table then use this query

SELECT image_url FROM table order by imageType`

give value 1 to the image you want to appear first in the loop.

if your first row is having the first image then you don't need imageType column. you can simply use

SELECT image_url FROM table order by id

where id is you PRIMARY KEY

but still, the above method is a more dynamic approach in case your records will change in the future. it is better to give them imageType column for future use which is more proper way instead of just getting the first record from the database.

    $stmt_img = $db->prepare($fetch_image);
    $stmt_img->execute();
    $stmt_img_result = $stmt_img->get_result();

  if ( $firstrow = mysql_fetch_assoc($stmt_img_result) ) {
        $image = $firstrow['image_url'];
         echo 'Image url: '.$image.'<br>'; //this will echo out 1 times.

    while($row = mysql_fetch_assoc($stmt_img_result)) {


      $image = $row['image_url'];
         echo 'Image url: '.$image.'<br>'; //this will echo out 9 times.

      }

    }
  • 发表于 2019-03-16 05:18
  • 阅读 ( 38 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除