Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Showing set amount of results 2
- Thread starter overyde
- Start date
You might want to research into some Paginating scripts, here is a sample one that ive been toying with, but im sure there are other, much better ones. Anyway, give it a go and hopefully it can at least point you in the right direction
// Open MySQL Connection
$Connection=mysql_connect($DB_Host, $DB_User);
// Run The Query Without a Limit to get Total result
$SQL="SELECT COUNT(*) AS Total FROM Products WHERE Description LIKE '%".$_REQUEST['Keyword']."%'";
$SQL_Result=mysql_db_query($DB_Name, $SQL);
$SQL_Result_Array=mysql_fetch_array($SQL_Result);
$Total=$SQL_Result_Array['Total'];
// Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
$SQL="SELECT * FROM Products WHERE Description LIKE '%".$_REQUEST['Keyword']."%' ORDER BY ProductID";
// Append a LIMIT clause to the SQL statement
if (empty($_GET['Result_Set']))
{
$Result_Set=0;
$SQL.=" LIMIT $Result_Set, $Per_Page";
}else
{
$Result_Set=$_GET['Result_Set'];
$SQL.=" LIMIT $Result_Set, $Per_Page";
}
// Run The Query With a Limit to get result
$SQL_Result=mysql_db_query($DB_Name, $SQL);
$SQL_Rows=mysql_num_rows($SQL_Result);
// Display Results using a for loop
for ($a=0; $a < $SQL_Rows; $a++)
{
$SQL_Array=mysql_fetch_array($SQL_Result);
$Product=$SQL_Array['Name'];
$Description=$SQL_Array['Description'];
echo "$Product - $Description<BR><BR>";
}
// Create Next / Prev Links and $Result_Set Value
if ($Total>0)
{
if ($Result_Set<$Total && $Result_Set>0)
{
$Res1=$Result_Set-$Per_Page;
echo "<A HREF=\"test.php?Result_Set=$Res1&Keyword=".$_REQUEST['Keyword']."\"><;<; Previous Page</A> ";
}
// Calculate and Display Page # Links
$Pages=$Total / $Per_Page;
if ($Pages>1)
{
for ($b=0,$c=1; $b < $Pages; $b++,$c++)
{
$Res1=$Per_Page * $b;
echo "<A HREF=\"test.php?Result_Set=$Res1&Keyword=".$_REQUEST['Keyword']."\">$c</A> \n";
}
}
if ($Result_Set>=0 && $Result_Set<$Total)
{
$Res1=$Result_Set+$Per_Page;
if ($Res1<$Total)
{
echo " <A HREF=\"test.php?Result_Set=$Res1&Keyword=".$_REQUEST['Keyword']."\">Next Page >></A>";
}
}
}
// Close Database Connection
mysql_close($Connection);
?>
// Open MySQL Connection
$Connection=mysql_connect($DB_Host, $DB_User);
// Run The Query Without a Limit to get Total result
$SQL="SELECT COUNT(*) AS Total FROM Products WHERE Description LIKE '%".$_REQUEST['Keyword']."%'";
$SQL_Result=mysql_db_query($DB_Name, $SQL);
$SQL_Result_Array=mysql_fetch_array($SQL_Result);
$Total=$SQL_Result_Array['Total'];
// Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
$SQL="SELECT * FROM Products WHERE Description LIKE '%".$_REQUEST['Keyword']."%' ORDER BY ProductID";
// Append a LIMIT clause to the SQL statement
if (empty($_GET['Result_Set']))
{
$Result_Set=0;
$SQL.=" LIMIT $Result_Set, $Per_Page";
}else
{
$Result_Set=$_GET['Result_Set'];
$SQL.=" LIMIT $Result_Set, $Per_Page";
}
// Run The Query With a Limit to get result
$SQL_Result=mysql_db_query($DB_Name, $SQL);
$SQL_Rows=mysql_num_rows($SQL_Result);
// Display Results using a for loop
for ($a=0; $a < $SQL_Rows; $a++)
{
$SQL_Array=mysql_fetch_array($SQL_Result);
$Product=$SQL_Array['Name'];
$Description=$SQL_Array['Description'];
echo "$Product - $Description<BR><BR>";
}
// Create Next / Prev Links and $Result_Set Value
if ($Total>0)
{
if ($Result_Set<$Total && $Result_Set>0)
{
$Res1=$Result_Set-$Per_Page;
echo "<A HREF=\"test.php?Result_Set=$Res1&Keyword=".$_REQUEST['Keyword']."\"><;<; Previous Page</A> ";
}
// Calculate and Display Page # Links
$Pages=$Total / $Per_Page;
if ($Pages>1)
{
for ($b=0,$c=1; $b < $Pages; $b++,$c++)
{
$Res1=$Per_Page * $b;
echo "<A HREF=\"test.php?Result_Set=$Res1&Keyword=".$_REQUEST['Keyword']."\">$c</A> \n";
}
}
if ($Result_Set>=0 && $Result_Set<$Total)
{
$Res1=$Result_Set+$Per_Page;
if ($Res1<$Total)
{
echo " <A HREF=\"test.php?Result_Set=$Res1&Keyword=".$_REQUEST['Keyword']."\">Next Page >></A>";
}
}
}
// Close Database Connection
mysql_close($Connection);
?>
-
1
- #3
sleipnir214
Programmer
overyde:
Insufficient data for a meaningful answer. You did not specify the database backend in use -- I will assume MySQL.
I recommend that you take a look at the use of MySQL's LIMIT clause in SQL queries.
In a SELECT query, the LIMIT clause provides a mechanism whereby you can pick a number of records to return from a query and where in the return to start returning that number of records.
It is documented here:
Want the best answers? Ask the best questions: TANSTAAFL!
Insufficient data for a meaningful answer. You did not specify the database backend in use -- I will assume MySQL.
I recommend that you take a look at the use of MySQL's LIMIT clause in SQL queries.
In a SELECT query, the LIMIT clause provides a mechanism whereby you can pick a number of records to return from a query and where in the return to start returning that number of records.
It is documented here:
Want the best answers? Ask the best questions: TANSTAAFL!
-
1
- #4
- Thread starter
- #5
Hi,
sleipnir214, spot-on I am using mysql. So will try that LIMIT clause.
Likelylad, you right on the money!!! Brilliant tutorial!
je150, I think you're a rocket scientist, that was way to advanced for me. Thanks anyhow.
Reality is built on a foundation of dreams.
sleipnir214, spot-on I am using mysql. So will try that LIMIT clause.
Likelylad, you right on the money!!! Brilliant tutorial!
je150, I think you're a rocket scientist, that was way to advanced for me. Thanks anyhow.
Reality is built on a foundation of dreams.
- Status
Similar threads
- Locked
- Question