How to set up queries not starting from the first record

I am just trying to set up a page with news items split into three levels. The first is the most recent, and most prominent story, the second the next three stories by date, and lastly a third with the next three.

A bit like the crude layout here:

http://www.handprintwebdesign.co.uk/news.html

I'm assuming I would use three queries - the first to display record number 1, the second to display records 2 - 4, and the third to display records 5-7.

I'm assuming this is possible, just not sure of the SQL syntax?

I've been trying LIMIT at the end of the query, e.g.

SELECT NewsID, Item, Date FROM news LIMIT 1, 3

But am getting the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 20' at line 1

Where Line 1 is just the code for the connection file include.

Thanks.

dont worry about limiting the recordset.

you can use only one recordset, than manually move the pointer to the next result using mysql_data_seek, for example, this sets the recordset "Recordset1" to the first record:

mysql_data_seek($Recordset1, 0);

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

or you move to the 3rd record using:

mysql_data_seek($Recordset1, 2);

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

mover to the 4th record:

mysql_data_seek($Recordset1, 3);

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

and so on.

so using mysql_data_seek you can manually move to the pointer to write the first record

then write the next 2-4

and then write 5-7

Thanks Jason.

I assume the above code goes in the header.

But not so sure how I echo it out on the page?

it goes just before the binding to write the recordset information to the page.

  But not so sure how I echo it out on the page?  

use the bindings on the bindings panel

Hi Jason - just been having a look at this. I've added:

mysql_data_seek($WADAtbnews, 0);
$row_WADAtbnews = mysql_fetch_assoc($WADAtbnews);

mysql_data_seek($WADAtbnews, 1);
$row_WADAtbnews = mysql_fetch_assoc($WADAtbnews);

And I have the two bits of code that should show the first news story, with the wider image, and then the next three stories beneath that.

As I've laid out here on a static page:

http://www.mphrp.org/new/index201.php

In my test page I have the code:

<?php do { ?>

<img src="http://www.mphrp.org/gambar/<?php echo($row_WADAtbnews['image_banner']); ?>" />
<h3><?php echo($row_WADAtbnews['judul_news']); ?></h3>
<span class="newsdate"><?php echo date('j F Y',strtotime($row_WADAtbnews['tgl'])); ?></span>
<p><?php echo($row_WADAtbnews['news_summary']); ?></p>
<p><a href="index301.php">Read full story...</a></p>

<?php } while ($row_WADAtbnews = mysql_fetch_assoc($WADAtbnews)); ?>

For the first story.

And then this for the next three stories:

<table width="100%" border="0" cellspacing="0" cellpadding="0" class="morenewstable">

<?php do { ?>

<tr>
<td width"160px" class="newspic">
<img src="http://www.mphrp.org/gambar/<?php echo($row_WADAtbnews['image_tmb']); ?>" />
</td>
<td class="newsintro">
<h3><?php echo($row_WADAtbnews['judul_news']); ?></h3>
<span class="newsdate"><?php echo date('j F Y',strtotime($row_WADAtbnews['tgl'])); ?></span>
<p><?php echo($row_WADAtbnews['news_summary']); ?></p>
<p><a href="index301.php">Read full story...</a></p>
</td>
</tr>

<?php } while ($row_WADAtbnews = mysql_fetch_assoc($WADAtbnews)); ?>

</table>

I'm not sure if that's right though, or what I need to do in the Bindings panel for the first bit to reference the first record, and the second bit to reference records 2-4.

Have attached a copy of the page for reference.

to show the first story, use the following code:

<?php 

mysql_data_seek($WADAtbnews, 0); 

$row_WADAtbnews = mysql_fetch_assoc($WADAtbnews);

?>

<img  src="http://www.mphrp.org/gambar/<?php echo($row_WADAtbnews['image_banner']); ?>" />

<h3><?php echo($row_WADAtbnews['judul_news']); ?></h3>

<span class="newsdate"><?php echo date('j F Y',strtotime($row_WADAtbnews['tgl'])); ?></span>

<p><?php echo($row_WADAtbnews['news_summary']); ?></p>

<p><a href="index301.php">Read full story...</a></p>

to show stories 2 - 4, use this code:

<table width="100%" border="0" cellspacing="0" cellpadding="0" class="morenewstable">



<?php for($i=1;$i<=3;$i++) { ?>

<?php 

mysql_data_seek($WADAtbnews, $i);

$row_WADAtbnews = mysql_fetch_assoc($WADAtbnews);

?>



<tr>

<td width"160px" class="newspic">

<img  src="http://www.mphrp.org/gambar/<?php echo($row_WADAtbnews['image_tmb']); ?>" />

</td>

<td class="newsintro">

<h3><?php echo($row_WADAtbnews['judul_news']); ?></h3>

<span class="newsdate"><?php echo date('j F Y',strtotime($row_WADAtbnews['tgl'])); ?></span>

<p><?php echo($row_WADAtbnews['news_summary']); ?></p>

<p><a href="index301.php">Read full story...</a></p>

</td>

</tr>



<?php } ?>



</table>

Thanks Jason.

Almost - as it is above, the first part displayed the first story OK, but the second part just displayed the first story another three times.

I thought maybe the line:

mysql_data_seek($WADAtbnews, 0);

in the second part should have been:

mysql_data_seek($WADAtbnews, 1);

But changing it just controls which record is displayed three times, e.g.:

http://www.mphrp.org/new/index402.php

in the second part, make sure you have a for loop:

<?php for($i=1;$i<=3;$i++) { ?>
<?php
mysql_data_seek($WADAtbnews, $i);
$row_WADAtbnews = mysql_fetch_assoc($WADAtbnews);
?>

Thanks Jason. I added a third one for the last story to avoid the double line at the bottom, so its exactly as I had in my head now:

http://www.mphrp.org/new/index402.php