Select one row at a time from a query with multiple rows?

Question

I have a query that selects 3 rows; but I only want to display the one row at a time, use the data from that row, then move to the next row, then repeat. How would this be accomplished? Table has 4 items in it.

PHP:

<?php
$server = "secret";
$user = "secret";
$pass = "secret";
$db = "secret";

$con=mysqli_connect($server,$user,$pass,$db);

if (mysqli_connect_errno($con))
   {
       echo "Failed to connect to MySQL: " . mysqli_connect_error();
   }

$sql = 'SELECT * FROM news ORDER BY id DESC LIMIT 3';

mysqli_select_db($con, $db);
$query = mysqli_query($con, $sql);
$number = 1;
while ($result = mysqli_fetch_array($query)) { 
    $id = $result['id'];
    $title = $result['title'];
    $news_date = $result['news_date'];
    $post = $result['post'];
}

mysqli_close($con);
?>

Attempt to display:

        <div name='title'><?php echo $title; ?></div> || <div name='news_date'><?php echo $news_date; ?></div>
        <p>News <?php echo $number; ?></p>
        <p><?php echo $post; ?></p>
        <?php   
           $number++;
        ?>

Output of attempt:

Test
 || 
7/14/13

News 1

Testing to see if this will work lalalalalla

NOTE: Tried to repeat the attempt to see if it would work, but it displayed same as output but with a second one that was a duplicate except said News 2

Desired output look:

Newest Title | Newest Date
Newest news. Etc. Insert Big Paragraph of news here etc etc.

2nd Newest Title | 2nd Newest Date
2nd Newest news. Etc. Insert Big Paragraph of news here etc etc.

3rd Newest Title | 3rd Newest Date
3rd Newest news. Etc. Insert Big Paragraph of news here etc etc.

and what have you tried so far, it looks like you need to process those results now. — Anigel, Jul 15 '13 at 16:17
As a matter of fact, every example shows you how to " display the one row at a time". Did you try any? — Your Common Sense, Jul 15 '13 at 16:20
Looks like a [duplicate](http://stackoverflow.com/q/17646456/285587). — Your Common Sense, Jul 15 '13 at 16:27
Common Sense the other question I phrased very poorly, this was an attempt to phrase it better. And The query that I used is MySQLI and it works as MySQLI. Anigel, I have tried what Your Common Sense has put as a duplicate question, but the answers there only displayed the first row repeatedly, not the other two — user2565624, Jul 15 '13 at 16:40
Post your code that you are currently trying/using. The one that is displaying the first row repeatedly. — Sean, Jul 15 '13 at 16:42

Sean · Accepted Answer · 2013-07-15T20:13:14.840

The problem is that you are overwriting your variables with each while() loop -

while ($result = mysqli_fetch_array($query)) { 
    $id = $result['id'];
    $title = $result['title'];
    $news_date = $result['news_date'];
    $post = $result['post'];
}

so when you try to echo $id, $title, $news_date, and $post, you will only get the last row data

you either need to add your html in the while loop -

while ($result = mysqli_fetch_array($query)) { 
    $id = $result['id'];
    $title = $result['title'];
    $news_date = $result['news_date'];
    $post = $result['post'];
    ?>

   <div name='title'><?php echo $title; ?></div> || <div name='news_date'><?php echo $news_date; ?></div>
    <p>News <?php echo $number; ?></p>
    <p><?php echo $post; ?></p>
    <?php   
       $number++;
}

or save them to an array

$id = array();
$title = array();
$news_date = array();
$post = array();

while ($result = mysqli_fetch_array($query)) { 
    $id[] = $result['id'];
    $title[] = $result['title'];
    $news_date[] = $result['news_date'];
    $post[] = $result['post'];
}

and then access the array in your display

    <div name='title'><?php echo $title[$number-1]; ?></div> || <div name='news_date'><?php echo $news_date[$number-1]; ?></div>
    <p>News <?php echo $number; ?></p>
    <p><?php echo $post[$number-1]; ?></p>
    <?php   
       $number++;
    ?>

I used [$number-1] as I assume $number is starting at 1, and arrays are 0 based

edit here are ways to echo your html. It would be beneficial to visit the php documentation, as all of this is there. In these I set $number back to 0, but you can change it back to 1, and just change each of the $number->$number-1, and the $number+1->$number

using a for loop - http://php.net/manual/en/control-structures.for.php

<?php
for($number=0;$number<count($title);$number++){
?>
    <div name='title'><?php echo $title[$number]; ?></div> || <div name='news_date'><?php echo $news_date[$number]; ?></div>
    <p>News <?php echo $number+1; ?></p>
    <p><?php echo $post[$number]; ?></p>
<?php   
}
?>

using a foreach loop - http://php.net/manual/en/control-structures.foreach.php

<?php
foreach($title as $number => $value{
?>
    <div name='title'><?php echo $title[$number]; ?></div> || <div name='news_date'><?php echo $news_date[$number]; ?></div>
    <p>News <?php echo $number+1; ?></p>
    <p><?php echo $post[$number]; ?></p>
<?php   
}
?>

using a while loop - http://php.net/manual/en/control-structures.while.php

<?php
$number = 0;
while($number<count($title){
?>
    <div name='title'><?php echo $title[$number]; ?></div> || <div name='news_date'><?php echo $news_date[$number]; ?></div>
    <p>News <?php echo $number+1; ?></p>
    <p><?php echo $post[$number]; ?></p>
<?php   
    $number++;
}
?>

each of the above was done by closing out of php, and writing the html, and then opening php again. You could also just stay in php, and echo the html - http://php.net/manual/en/function.echo.php

<?php
for($number=0;$number<count($title);$number++){
    echo "<div name='title'>".$title[$number]."</div> || <div name='news_date'>".$news_date[$number]."</div>";
    echo "<p>News ".($number+1)."</p>";
    echo "<p>".$post[$number]."</p>";
     }
    ?>

Will the second one display all of them or one then I have to call upon another etc? — user2565624, Jul 15 '13 at 17:20
The second one will display them all, if the code is in a loop - `for($i=0;$i$value){...display code...}` or `while($number<=count($title){...display code...}` — Sean, Jul 15 '13 at 17:24
And how would you properly echo the display code? Sorry I'm not good with echoing HTML — user2565624, Jul 15 '13 at 17:36
Thank you, I didn't know you could close and reopen php with a statement still running. You've taught me a lot, and for that, thank you a lot. — user2565624, Jul 16 '13 at 03:51

Select one row at a time from a query with multiple rows?

1 Answers1