Query with global limit

[Canman2005]

Hi all

 

I'm having a real issue with altering a script I have written and I hope someone can help.

 

Basically I have taken over some changes on a website done by a past programmer who had no clue about anything and im trying to pick up with pieces, I cant change the way the database is, as it would effect other parts of the site, so I have had to bodge together a solution.

 

 

Basically it is a document store, which shows a heading such as

 

Employment Documents

 

and then shows the documents related to that heading, under it, like

 

Employment Documents

Document 1

Document 2

Document 3

 

It then shows the next heading under, and the follows with those documents, and so on.

 

I have supplied my query below and explained what im doing for each part (sorry for the long code)

 

<?
$db_name ="****";
$server = "localhost";
$dbusername = "****";
$dbpassword = "****";

$connection = mysql_connect($server, $dbusername, $dbpassword) or die(mysql_error());
$db = mysql_select_db($db_name,$connection)or die(mysql_error());
?>

 

This is my db connect

 

<?
$searchsql = "SELECT * FROM page_documents WHERE pd_pgid <> 58 AND pd_pgid <> 70 GROUP BY pd_pgid ORDER BY pd_id ASC";
$searchresult = @mysql_query($searchsql,$connection) or die(mysql_error());

$cols = 2;
$showcol = 0;
?>

 

I then get a list query a database which has lots of rows, each row hold the ID number for the relavant document from the document table and also the ID number of the Document title from the document title table. Basically each time a new document is added, a new row is added, so I have done a GROUP BY so that it groups all the Document Headings together so I dont get duplicates.

 

<table width="800" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td align="left" valign="top">
      <table width="400" border="0" cellpadding="10" cellspacing="0">

<?php
while($row = mysql_fetch_row($searchresult))
{
$zero = $row[0];
$one = $row[1];
$two = $row[2];
if ($showcol == $cols)
{
?>
<tr>
<?
$showcol = 0;
}
?>
<td align="left" valign="top"><table width="380" border="0" cellspacing="0" cellpadding="0">
  
  <tr>
    <td valign="top"><table border="0" cellpadding="0" cellspacing="0" style="background-image:url(images/elements/quickviewbox.gif)">
      <tr>
        <td align="center" valign="top"><table border="0" cellspacing="0" cellpadding="0">
            <tr>
              <td height="14" align="left" valign="bottom" class="bold9black">
		  <?

 

That builds part of the table

 

$sql = "SELECT * FROM pages WHERE pg_id = $two";
$query = @mysql_query($sql,$connection) or die(mysql_error());
while ($row=mysql_fetch_array($query))
{
?>
<strong><? print $row['pg_shortname']; ?></strong>
<?
	print "<br>";
}

 

This takes the document page ID numbers it got from the first query and gets looks in the document title table and get the Docunt Title

 

$sql = "SELECT * FROM page_documents WHERE pd_pgid = $two";
$query = @mysql_query($sql,$connection) or die(mysql_error());
while ($row=mysql_fetch_array($query))
{
	$sql1 = "SELECT * FROM documents WHERE dm_id = '".$row['pd_dmid']."'";
	$query1 = @mysql_query($sql1,$connection) or die(mysql_error());
	while ($row1=mysql_fetch_array($query1))
	{
	?>
	<?
	$type = mime_content_type("upf/".$row1['dm_file']."");
		?>
	<? if($type == 'application/pdf') { ?><img src="../images/pdf.gif" /><? } elseif($type == 'text/plain') { ?><img src="../images/word.gif" /><? } ?>
	<a href="upf/<? print "".$row1['dm_file'].""; ?>" target="_blank"><? print "".$row1['dm_name'].""; ?></a><br>
	<?
	}
}
	 ?>

 

This part looks in the documents table and gets a list of the documents under that Document Title ID number

 

		 


	 </td>
            </tr>
            <tr>
              <td height="1" class="linex"></td>
            </tr>
        </table></td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td height="20" style="background-image:url(images/elements/quickviewboxbottom.gif); background-repeat:no-repeat; background-position:left"></td>
  </tr>
  
</table>
</td>
<?
$showcol++;
}
$matchcol = $cols - $showcol;
for ($i=1; $i<=$matchcol; $i++) {
}
?></table></td>
  </tr>
</table>

 

Finishes off the table build

 

 

Does that make sense?

 

Basically what I want to do is to put a global limit on it, so that I can produce one page with the code above, which shows a maximum of 12 documents globally on the page, sorting by ID number and then another page with the same code above, showing everything after the ID number 12.

 

Can anyone help? Due to the linking of tables, I cant seem to get my head around it.

 

Any help would be great

 

Thanks in advance

 

Ed

[Canman2005]

If you need snippets of the tables, let me know

[Canman2005]

Hi all

 

I seen a few people have read this but had no solution, should I be writting my message a different way to help people figure a solution out?

 

Thanks