this limit thing is killing me

[itworks]

i want to implement a paging thing in a search function i have, but i can't get it right.

i've looked for this question in the forum but i can't find nothing for my needs, i've tried posting yhis question before but didn't get to solve the problem.

now i have evolved a little more, but i still can't get the whole thing done.

this is the function being called:

		function detailsSearch($search) {
            $start = 0;
			$pagelimit = 10;
			$sql = "SELECT * FROM products WHERE model LIKE '%$search%' OR type LIKE '%$search%' OR brand LIKE '%$search%' LIMIT $start,$pagelimit";
            $rs=$this->bd->executeSQL($sql);

		$pagelimit2 = $pagelimit + 10;
		$start2 = $pagelimit;
		echo "<table width=\"100%\" border=\"0\" cellspacing=\"1\" cellpadding=\"0\" class=\"caixas\"><tr>
			<td class=\"titulo\">See</td>
			<td class=\"titulo\">Type</td>
			<td class=\"titulo\">Brand</td>
			<td class=\"titulo\">Model</td>
			<td class=\"titulo\">Price</td>
			<td class=\"titulo\">Avail.</td></tr>";
		echo "<tr bgcolor=\"#FFFFFF\">";
		$n=0;
		$count=0;
		while(mysql_fetch_row($rs)) {
		$bgcolor = ($count++ % 2) ? "#FFFFFF" : "#E9E9EC";
				echo "<td bgcolor=\"$bgcolor\"><a href=\"index.php?page=product&type=".mysql_result($rs, $n, "type")."
					&model=".mysql_result($rs, $n, "model")."&cod=".mysql_result($rs, $n, "cod")."
					&price=".mysql_result($rs, $n, "price")."\">
					<img src=\"imagens/seta.gif\" border=\"0\" width=\"12\" height=\"10\"></td>
					<td bgcolor=\"$bgcolor\">".mysql_result($rs, $n, "type")."</td>
					<td bgcolor=\"$bgcolor\"><a href=\"index.php?page=brand
					&brand=".mysql_result($rs, $n, "brand")."\">".mysql_result($rs, $n, "brand")."</td>
					<td bgcolor=\"$bgcolor\">".mysql_result($rs, $n, "model")."</td>
					<td bgcolor=\"$bgcolor\" align=\"center\">".mysql_result($rs, $n, "price")." €</td>
					<td bgcolor=\"$bgcolor\">".mysql_result($rs, $n, "avail")."<br></td>";
				echo "</tr>";
				$n++;
		}
		echo "</table>";
		echo "<a href=$PHP_SELF?page=search&start=$start2&pagelimit=$pagelimit2>Next</a>";
    }

this returns ten results, but when i click the next button i get 10 results again but 6 are the same and 4 are new, and i can't get 10 more.

can anyone spare the time to help me get this done, please?
i know that i haven't got much for now🙁

thanks in advance!

[7thgencivic_com]

Your $start is always going to be 0 for the SQL statement. You keep overwriting it.

you need to check if $start2 is defined, if it is, make $start = $start2 else use $start.

THis is jusy my 2cents and I could be way off. But makes sense to me.

Derek

[itworks]

thanks for your reply but i must have got it wrong.

i've put this:

if (!$start2){
$start = $start2;
} else {
$start = 0;
}

instead of the line:

$start = 0;

please help me someone!!
i really have to get this done as soon as possible.

thanks!

[cgraz]

You're close but a little off.

You need to first run your query without the LIMIT, then do some calculations based on that. Once you have your calculations done, then you can run the query again using LIMIT. Try this:

function detailsSearch($search) {
            $pagelimit = "10";
            $sql = "SELECT * FROM products WHERE model LIKE '%$search%' OR type LIKE '%$search%' OR brand LIKE '%$search%'";
            $result = mysql_query($sql) or die(mysql_error());

        $totalrows = mysql_num_rows($result);
        $pagenums = ceil ($totalrows/$pagelimit);

        if ($page==''){
           $page='1';
        }


        $start = ($page-1) * $pagelimit;

        $rs = mysql_query("SELECT * FROM products WHERE model LIKE '%$search%' OR type LIKE '%$search%' OR brand LIKE '%$search%' LIMIT $start,$pagelimit") or die(mysql_error());

        echo "<table width=\"100%\" border=\"0\" cellspacing=\"1\" cellpadding=\"0\" class=\"caixas\"><tr>
            <td class=\"titulo\">See</td>
            <td class=\"titulo\">Type</td>
            <td class=\"titulo\">Brand</td>
            <td class=\"titulo\">Model</td>
            <td class=\"titulo\">Price</td>
            <td class=\"titulo\">Avail.</td></tr>";
        echo "<tr bgcolor=\"#FFFFFF\">";
        $n=0;
        $count=0;
        while(mysql_fetch_row($rs)) {
        $bgcolor = ($count++ % 2) ? "#FFFFFF" : "#E9E9EC";
                echo "<td bgcolor=\"$bgcolor\"><a href=\"index.php?page=product&type=".mysql_result($rs, $n, "type")."
                    &model=".mysql_result($rs, $n, "model")."&cod=".mysql_result($rs, $n, "cod")."
                    &price=".mysql_result($rs, $n, "price")."\">
                    <img src=\"imagens/seta.gif\" border=\"0\" width=\"12\" height=\"10\"></td>
                    <td bgcolor=\"$bgcolor\">".mysql_result($rs, $n, "type")."</td>
                    <td bgcolor=\"$bgcolor\"><a href=\"index.php?page=brand
                    &brand=".mysql_result($rs, $n, "brand")."\">".mysql_result($rs, $n, "brand")."</td>
                    <td bgcolor=\"$bgcolor\">".mysql_result($rs, $n, "model")."</td>
                    <td bgcolor=\"$bgcolor\" align=\"center\">".mysql_result($rs, $n, "price")." €</td>
                    <td bgcolor=\"$bgcolor\">".mysql_result($rs, $n, "avail")."<br></td>";
                echo "</tr>";
                $n++;
        }
        echo "</table>";

$space = "&nbsp;";

// previous link
if ($page>1) {
        echo "« <a href='$PHP_SELF?page=".($page-1)."'>Previous" . $space . $pagelimit . "</a>" . $space . $space . "";
    }

// dynamic page number links

for ($i=1; $i<=$pagenums; $i++) {
    if ($i!=$page) {
        echo " <a href='$PHP_SELF?page=$i'>$i</a>";
    }
    else {
        echo " <b><font color='red'>[$i]</font></b>";
    }
}


// next link

if ($page<$pagenums) {
    echo "" . $space . $space . $space . $space . " <a href='$PHP_SELF?page=".($page+1)."'>Next " . $var2 . "</a> »";
}


    }

Cgraz

[itworks]

thanks a lot, you're a genius!!

you've got me on the right track but it's not working 100% yet.

i get the 10 results, the dynamic number page links and the next link.

when i click anyone of those links i get 10 results but some are the same and others change, the dynamic number page links has [ 1 ] always highlighted.

it wasn't working so i had to change it a bit 'cause this function is not being called from the page but from a script hosted inside index.php so i had to make this change in the links

<a href='$PHP_SELF?pag=search&page=

i really appreciate your help and maybe you can give me just one little more push.

thanks in advance!🙂

[cgraz]

can you post a link so i can see and explain what pag=search does?

Cgraz

[itworks]

i wish i could but i have it on my computer only, i still haven't got the hosting for it and at the time i haven't got anyone with mysql.

isn't there any other way, because it's you i don't mind sending the whole site for you to see it.

i really need a hand with this and i know how dificult it can be for you to help me without seeing the whole thing.

i hope you can help me, thanks.

[cgraz]

The problem may just be that you're using variables within a function, therfore they're not global. What happens if you try the code without using the function?

Cgraz

[DigitalExpl0it]

here is a page I use thats using the limit funtction with the next link..

hope it helps..

<?
include('cfg.php');
//echo "$offset & $onset";

if ($offset == "" and $onset == "") {
$offset = "10";
$onset = "0";
}
$offset2 = $offset + 11;
$onset2 = $offset2 - 11;

$connect = mysql_connect($db_connetion, $db_username, $db_password) or die ("Couldnt connect");
$db = mysql_select_db($db_databse, $connect) or die ("Couldnt open Database");
$sql = "select * from wml WHERE wml_cat='80' ORDER BY wml_id ASC LIMIT $onset,11";
$sql_result = mysql_query($sql, $connect) or die ("Access Denied!");

while ($row = mysql_fetch_array($sql_result)){
$id = $row["wml_id"];
$text = $row["wml_text"];
$link = $row["wml_link"];

echo "&nbsp;&nbsp;<a href='$link'>$text</a><br>";
}
mysql_close($connect);

if ($text == "") {
echo "No more Data<br><a href='http://www.dfdf.com/wml'>Home</a><br>";
} else {
?>
<A HREF="80.wml?onset=<? echo "$onset2"; ?>&offset=<? echo "$offset2"; ?>">Next</A>&nbsp;&nbsp;<A href='http://www.dfdf.com/wml'>Home</A>
<?
}
?>

[itworks]

thanks a lot to all you guys!

the problem was really what cgraz mentioned in his last post and now it's solved thanks to the kindness of you all i really don't know what my life would be without forums.

😃

[jsantama]

Hi there...

Thanks to these posts, I have been able to learn a lot more.
Now, I have a question. I have a form that enters prospects, and I display all the prospects entered in a page with tables.

This is where the code from above has helped me, to split the info in different tables.

How can I set the code to display the last page of results first? I don't want to sort the results in the query.
All I want is to be able to see the last results first, and if I want to go back to earlier results, just click on my previous link. Can this be done?

Thanks