mysql reset pointer problem

[HelenDev]

I'm using some pretty standard code to get results out of a database and display them on a page. In addition I want to split the results down by year and display a table for each.

I've managed to get this far but I also want to get it to not display a table for any years in which there were no results.

I've done this by looking in the mysql_fetch_array for a value before the actual while loop which generates the table of data. This works but then I realised that the first row of each table was dissappearing. Some research and tearing out of hair later I understand that this is because the pointer has moved to the next row.

I've read that you can use mysql_data_seek to reset the pointer, but I'm not sure how to use it. I've tried putting it before my while statement which generates the table data rows but I keep getting the error

Offset 0 is invalid for MySQL result index

.

Can anyone offer any advice or example code?

[jatar_k]

do you have any idea why the internal pointer is moving?

0 being invalid is a tad weird

[joelgreen]

Maybe you could change while loop to do-while?

while ($row = mysql_fetch_assoc(...)) {
echo $row['colname'];
}

VS

do {
echo $row['colname'];
} while ($row = mysql_fetch_assoc(...));

It will show record data before going to next record

[HelenDev]

Thanks for the replies guys. I think it might be time to post some code...

This is what I've got which is working, but it creates a table for every year, regardless of whether there are any items to display or not. I want it to not display a table if there are no items to display...

<?php

//get current year
$thisyear = date('Y');

//do each year in turn, working back from current until 1990
$displayyear = $thisyear;

while ($displayyear > 1990){

$pub_query = "SELECT * FROM publications WHERE pub_year='".$displayyear."'";
$pub_results = mysql_query($pub_query) or die(mysql_error());

?>

<h2><?php echo $displayyear ;?></h2>
<table width="100%" summary="Table of Publications" border="1">
<tr>
<th>Authors</th>
<th>Publication</th>
<th>Category</th>
<th>View Abstract</th>
</tr>

<?php
while ($publication = mysql_fetch_array($pub_results)) {?>

<tr>
<td><?php echo $publication["authors"]?></td>
<td><?php echo $publication["pub_title"]?></td>
<td><?php echo $publication["category"]?></td>
<td><?php echo $publication["pub_year"]?></td>
</tr>

<?php }?>

</table>
<?php
$displayyear--;
}
?>

[omoutop]

try something like this:

$pub_query = "SELECT * FROM publications WHERE pub_year='".$displayyear."'";
$pub_results = mysql_query($pub_query) or die(mysql_error());
$year_results = mysql_num_rows($pub_results);
if ($year_results>0)
{

// show table

}

Bold part is the new one.

$year_results holds the number of affected rows of your query. So, if this number is greater than zero, that means that there are records to show.

[HelenDev]

omoutop you are a star!

That works a treat, thank you very much :)