Php mysql display page based on search result

Hi,

this is follow up question for this topic:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/Q_23132289.html

I have recently added a search function to my forum.
And when a user search for posts in topic by a searchword and result is found, they will be displayed a page with the topics name and a short version of the post that includes the search query.
Now when the user clicks on the link to the post for further reading he will be moved to right topic and to that topics last page. e.g if the topics has 10 pages the user will be shown the last page,

So I'm looking for solution that will bring the user to page 3 (assuming the searched post was found on page 3) and not the last page as it now works.

I attached the pager function that works great, but I thing the pager function should be altered a bit or something for the above to work.

Any ideas how I should approach this?

-Toby

class Pager
   {
       function getPagerData($numHits, $limit, $page)
       {
           $numHits  = (int) $numHits;
           $limit    = max((int) $limit, 1);
           $page     = (int) $page;
           $numPages = ceil($numHits / $limit);
 
           $page = max($page, 1);
           $page = min($page, $numPages);
 
           $offset = ($page - 1) * $limit;
 
           $ret = new stdClass;
 
           $ret->offset   = $offset;
           $ret->limit    = $limit;
           $ret->numPages = $numPages;
           $ret->page     = $page;
 
           return $ret;
       }
   }
$topicid_ = $_GET['show'];
$page = $_GET['main_page'];
$user_ = ($_SESSION['users_id']);
require ('updatelastread.php');
 
 
$result = mysql_query("SELECT count(*) FROM posts p where p.topicid ='$topicid_' ");
$useramount = mysql_query("SELECT u.amount FROM users u where u.users_id = '$_SESSION[users_id]'");
 
$total = mysql_result($result, 0, 0);
$amount1 = mysql_result($useramount, 0, 0);
$limit = $amount1;
$pager  = Pager::getPagerData($total, $limit, $page);
$offset = $pager->offset;
$limit  = $pager->limit;
$page   = $pager->page;
 
$numpages = $pager->numPages;
 
if (($page != $_GET['main_page']) && ($pager->numPages > 1))
{
    $pager  = Pager::getPagerData($total, $limit, $pager->numPages);
    $offset = $pager->offset;
    $page   = $pager->page;
}

Open in new window

ToubeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dr_dedoCommented:
in your previous post, i brought uo something about how to know the page of the desired post by knowing; topicID, postID and limit (posts per page), if that solution is ok with you here it is
<?php
mysql_connect('localhost','root','');
mysql_select_db('test');
function PostToPage($topicID,$postID,$rowsPerPage=10){
	$sql = "select count(ID) as num from posts where topicID=$topicID and ID<$postID";
	$r = mysql_query($sql);
	if (mysql_num_rows($r)<1) return false;
	$num = mysql_result($r,0,0)+1;
	$page = $num/$rowsPerPage;
	return ceil($page);
}
$page =  PostToPage(1,18);
echo $page
?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ToubeAuthor Commented:
Thanks, will try it out later on today.
0
ToubeAuthor Commented:
Tired it out on the search page like this:

But that gives me an error:

Fatal error: Cannot redeclare posttopage() (previously declared in /home/tobias/domains/tobias.huone.net/public_html/td/user/foorumi/pages/content/forum_functions.php:2476) in /home/tobias/domains/tobias.huone.net/public_html/td/user/foorumi/pages/content/forum_functions.php on line 2476

any ideas.. I'm probably doing it all wrong :)
function showsearch($pageid)
{
 
 
$userid = $_SESSION['users_id'];
 
$search = ($_POST['search']);
 
$color1 = '#f7e1b0';
$color2 = '#efcd83';
 
//echo $search;
 
$userid = $_SESSION['users_id']; //get userid from session
 
$result = mysql_query("SELECT userlevel from users where users_id='$userid'");
while($row = mysql_fetch_array($result))
{
		$ulevel = $row['userlevel'];//get userlevel
}
 
		/*
  		$topic_query = mysql_query("SELECT t.ID, t.TopicName, t.deleted, t.userlevel_topic, DATE_FORMAT(t.topictime, '%d.%m.%Y %H:%i') AS topict, t.locked, t.sticky, u.username as user, u.users_id, u.userlevel, u.deluser_, p.TopicID, DATE_FORMAT(p.timestamp, '%d.%m.%Y %H:%i:%s' ) AS insertdat, MAX(p.timestamp) as lastpost FROM topics t, users u, posts p WHERE u.users_id=t.user_ AND p.TopicID=t.ID AND u.deluser_='0' and t.deleted = '0' group by t.ID order by lastpost desc");
		*/
 
		// Search query for the messages.
        $sqlquery = "SELECT p.ID as postid, p.topicid, DATE_FORMAT(p.timestamp, '%d.%m.%Y %H:%i') AS insertdate, DATE_FORMAT(p.timestamp, '%d.%m.%Y') AS time, DATE_FORMAT(p.timestamp,'%H:%i') AS minutes, p.timestamp as sent, p.post as post, DATE_FORMAT(t.topictime, '%d.%m.%Y %H:%i') AS topict, t.topicname as topicname, t.userlevel_topic as userlevel, t.ID as topicid, u.username as username, (SELECT count(*) FROM posts WHERE posts.topicid=t.id) as count, u.users_id as usersid, c.lastread as userlastread FROM users u, posts p, topics t LEFT OUTER JOIN forum_read
 		c ON (c.theader_=t.id AND c.user_ = '$userid') WHERE p.topicid=t.ID and t.deleted ='0' AND u.users_id = p.user_ and u.users_id=t.user_ and MATCH(p.post) AGAINST('%$search%' IN BOOLEAN MODE) group by postid order by p.timestamp desc limit 20";
 
 
    $result2 = mysql_query($sqlquery);
 
    $maara = mysql_num_rows($result2);
 
    //If search returns 0 rows echo message no results, else display the message s in table.
 
    if ($search == "")
	{
	    	echo "<div style=\"padding: 10px; \"><span style=\" background-color: #c63031; padding: 2px;\">et antanut hakusanaa, anna hakusana</span></div>";
    }
 
    else if ($maara < 1)
	{
		echo "<div style=\"padding: 5px; font-size: 12px;\">Hakusanalle <span style=\" background-color: #c63031; padding: 2px;\">$search</span> ei löytynyt tulosta, anna uusi hakusana!</div>";
	}
 
	else
	{
    echo ('<div style="font-size: 14px; margin-bottom: 5px; font-weight: bold;"><img src="../images/search.gif" alt="haku" style="border: none;" />&nbsp; Haku palautti '.$maara.' kpl tuloksia:</div>');
 
    echo ('<table style="width: 810px; font-size: 10px; border: 1px solid #e4900d;" cellpadding="1" cellspacing="0">');
    echo ('<tr>');
	('<td style="border-bottom: 1px solid #e4900d; border-right: 1px solid #e4900d; padding-bottom: 2px;" align="left" class="td_back"><strong></strong></td>');
	    echo ('<td style="border-bottom: 1px solid #e4900d;" align="left" bgcolor="#720606" class="td_back"><strong>Määrä</strong></td>');
 
    echo ('<td style="border-bottom: 1px solid #e4900d; border-left: 1px solid #e4900d;" align="left" bgcolor="#720606" class="td_back"><strong>Otsikko / viesti</strong></td>');
    echo ('<td style="border-left: 1px solid #e4900d; border-bottom: 1px solid #e4900d;" align="left" bgcolor="#720606" class="td_back"><strong>Otsikon aloittaja</strong></td>');
    echo ('<td style="border-left: 1px solid #e4900d; border-bottom: 1px solid #e4900d;" align="left" bgcolor="#720606" class="td_back"><strong>Otsikko aloitettu</strong></td>');
    echo ('<td style="border-left: 1px solid #e4900d; border-bottom: 1px solid #e4900d;" align="left" bgcolor="#720606" class="td_back"><strong>Viimeisin viesti</strong></td>');
 
    echo ('</tr>');
 
    $datarow_count = 0;
    while($row=mysql_fetch_array($result2))
    {
 
	$count = $row['count'];
    $messagetime = $row['sent'];
    $latest_read = $row['userlastread'];
    $postID = $row['postid'];
    $topicid_ = $row['topicid'];
 
        $datarow_color = ($datarow_count % 2) ? $color1 : $color2;
        $datarow_count++;
 
    $time1 = $row['time'];
    $timetoday = date("d.m.Y");
 
 
 
function PostToPage($topicid_,$postID,$rowsPerPage=20)
{
        $sql = "select count(ID) as num from posts where topicID='$topicid_' and ID ='$postID'";
        $r = mysql_query($sql);
        if (mysql_num_rows($r) < 1) return false;
        $num = mysql_result($r,0,0)+1;
        $page = $num/$rowsPerPage;
        return ceil($page);
}
$page =  PostToPage(1,18);
echo $page;
 
 
    $post_ = trim($row['post']);
    $post_ = html_entity_decode($post_);
    $post_ = substr($post_,0,240);
 
    $search_result = str_replace($search, '<span style="background-color: #c63031; color: #ffffff;">' . $search . '</span>', $post_);
 
    echo ('<tr bgcolor= "'.$datarow_color.'">');
 
    echo ('<td valign="top" style="color: #000000; border-bottom: 1px solid #e4900d; border-right: 1px solid #e4900d;" bgcolor="'.$datarow_color.'"><span class="member_links_underline_black" style="color: #000000; ">'.$count.'</span>
    </td>');
 
    echo ('<td style="color: #000000; border-bottom: 1px solid #e4900d; width: 460px;" bgcolor= "'.$datarow_color.'">');
    echo ('<span class="member_links_underline_black">');
    if($latest_read < $messagetime)
    {
        echo ('<img style="margin-left: 5px;" src="../images/folder_unread.gif" alt="lukematon_viesti" />');
    }
    else if ($timetoday == $time1)
    {
        echo ('<img style="margin-left: 5px;" src="../images/folder_new.gif" alt="uusi_viesti" />');
                            }
    else
    {
        echo ('<img style="margin-left: 5px;" src="../images/folder.gif" alt="otsikko" />');
    }
 
    echo ('<span style="font-weight: bold;"><a style="font-size: 10px; margin-left: 1px;" href="index.php?id=1&amp;show='.$row['topicid'].'#end">'.$row['topicname'].'</a></span></span>
    <span class="member_links_underline_black" style="color: #000000;">by&nbsp;<span class="member_links_underline_black_1" style="margin-left: 1px;"><a href="../users/index.php?id='.$row['usersid'].'">'.$row['username'].'</a></span></span>
    <br /><div style="background-color: #f6f5ed; border: 1px solid black; border-style: inset; margin-left: 20px; margin-right: 10px; margin-top: 5px; margin-bottom: 5px; padding: 3px 3px 3px 3px;">'.$search_result.'..</div>
    </td>');
 
    echo "<td valign=\"top\" style=\"color: #000000; border-left: 1px solid #e4900d; border-bottom: 1px solid #e4900d; border-left: 1px solid #e4900d;\" bgcolor=\"$datarow_color\">"; echo ('<span class="member_links_underline_black" style="color: #000000;"><a href="../users/index.php?id='.$topic['users_id'].'">'.$row['username'].'</a></span>');
    echo "</td>";
 
    echo ('<td valign="top" style="color: #000000; border-left: 1px solid #e4900d; border-bottom: 1px solid #e4900d; border-left: 1px solid #e4900d;" bgcolor="'.$datarow_color.'">'.$row['topict'].'</td>');
 
    echo ('<td valign="top" style="color: #000000; border-left: 1px solid #e4900d; border-bottom: 1px solid #e4900d; border-left: 1px solid #e4900d;" bgcolor="'.$datarow_color.'">'.$row['insertdate'].'</td>');
 
 
    echo ('</tr>');
}
    echo ('<tr>');
    echo ('<td colspan="5" style="font-weight: bold; padding-bottom: 2px;" bgcolor="#720606" class="td_back">&nbsp;</td>');
    echo ('</tr>');
    echo ('</table>');
 
 
}
 
}

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

dr_dedoCommented:
you put the function inside another function, get it out
e.g.
<?
function PostToPage($topicid_,$postID,$rowsPerPage=20){
        $sql = "select count(ID) as num from posts where topicID='$topicid_' and ID ='$postID'";
        $r = mysql_query($sql);
        if (mysql_num_rows($r) < 1) return false;
        $num = mysql_result($r,0,0)+1;
        $page = $num/$rowsPerPage;
        return ceil($page);
}
 
function showsearch($pageid){
$userid = $_SESSION['users_id'];
$search = ($_POST['search']);
 
$color1 = '#f7e1b0';
$color2 = '#efcd83';
 
//echo $search;
$userid = $_SESSION['users_id']; //get userid from session
 
$result = mysql_query("SELECT userlevel from users where users_id='$userid'");
while($row = mysql_fetch_array($result))
{
		$ulevel = $row['userlevel'];//get userlevel
}
		/*
  		$topic_query = mysql_query("SELECT t.ID, t.TopicName, t.deleted, t.userlevel_topic, DATE_FORMAT(t.topictime, '%d.%m.%Y %H:%i') AS topict, t.locked, t.sticky, u.username as user, u.users_id, u.userlevel, u.deluser_, p.TopicID, DATE_FORMAT(p.timestamp, '%d.%m.%Y %H:%i:%s' ) AS insertdat, MAX(p.timestamp) as lastpost FROM topics t, users u, posts p WHERE u.users_id=t.user_ AND p.TopicID=t.ID AND u.deluser_='0' and t.deleted = '0' group by t.ID order by lastpost desc");
		*/
 
		// Search query for the messages.
        $sqlquery = "SELECT p.ID as postid, p.topicid, DATE_FORMAT(p.timestamp, '%d.%m.%Y %H:%i') AS insertdate, DATE_FORMAT(p.timestamp, '%d.%m.%Y') AS time, DATE_FORMAT(p.timestamp,'%H:%i') AS minutes, p.timestamp as sent, p.post as post, DATE_FORMAT(t.topictime, '%d.%m.%Y %H:%i') AS topict, t.topicname as topicname, t.userlevel_topic as userlevel, t.ID as topicid, u.username as username, (SELECT count(*) FROM posts WHERE posts.topicid=t.id) as count, u.users_id as usersid, c.lastread as userlastread FROM users u, posts p, topics t LEFT OUTER JOIN forum_read
 		c ON (c.theader_=t.id AND c.user_ = '$userid') WHERE p.topicid=t.ID and t.deleted ='0' AND u.users_id = p.user_ and u.users_id=t.user_ and MATCH(p.post) AGAINST('%$search%' IN BOOLEAN MODE) group by postid order by p.timestamp desc limit 20";
 
    $result2 = mysql_query($sqlquery);
    $maara = mysql_num_rows($result2);
 
    //If search returns 0 rows echo message no results, else display the message s in table.
 
    if ($search == "")
	{
	    	echo "<div style=\"padding: 10px; \"><span style=\" background-color: #c63031; padding: 2px;\">et antanut hakusanaa, anna hakusana</span></div>";
    }
 
    else if ($maara < 1)
	{
		echo "<div style=\"padding: 5px; font-size: 12px;\">Hakusanalle <span style=\" background-color: #c63031; padding: 2px;\">$search</span> ei löytynyt tulosta, anna uusi hakusana!</div>";
	}
 
	else
	{
    echo ('<div style="font-size: 14px; margin-bottom: 5px; font-weight: bold;"><img src="../images/search.gif" alt="haku" style="border: none;" />&nbsp; Haku palautti '.$maara.' kpl tuloksia:</div>');
 
    echo ('<table style="width: 810px; font-size: 10px; border: 1px solid #e4900d;" cellpadding="1" cellspacing="0">');
    echo ('<tr>');
	('<td style="border-bottom: 1px solid #e4900d; border-right: 1px solid #e4900d; padding-bottom: 2px;" align="left" class="td_back"><strong></strong></td>');
	    echo ('<td style="border-bottom: 1px solid #e4900d;" align="left" bgcolor="#720606" class="td_back"><strong>Määrä</strong></td>');
 
    echo ('<td style="border-bottom: 1px solid #e4900d; border-left: 1px solid #e4900d;" align="left" bgcolor="#720606" class="td_back"><strong>Otsikko / viesti</strong></td>');
    echo ('<td style="border-left: 1px solid #e4900d; border-bottom: 1px solid #e4900d;" align="left" bgcolor="#720606" class="td_back"><strong>Otsikon aloittaja</strong></td>');
    echo ('<td style="border-left: 1px solid #e4900d; border-bottom: 1px solid #e4900d;" align="left" bgcolor="#720606" class="td_back"><strong>Otsikko aloitettu</strong></td>');
    echo ('<td style="border-left: 1px solid #e4900d; border-bottom: 1px solid #e4900d;" align="left" bgcolor="#720606" class="td_back"><strong>Viimeisin viesti</strong></td>');
     echo ('</tr>');
     $datarow_count = 0;
    while($row=mysql_fetch_array($result2)){
	$count = $row['count'];
    $messagetime = $row['sent'];
    $latest_read = $row['userlastread'];
    $postID = $row['postid'];
    $topicid_ = $row['topicid'];
 
        $datarow_color = ($datarow_count % 2) ? $color1 : $color2;
        $datarow_count++;
 
    $time1 = $row['time'];
    $timetoday = date("d.m.Y");
 
$page =  PostToPage(1,18);
echo $page;
     $post_ = trim($row['post']);
    $post_ = html_entity_decode($post_);
    $post_ = substr($post_,0,240);
 
    $search_result = str_replace($search, '<span style="background-color: #c63031; color: #ffffff;">' . $search . '</span>', $post_);
 
    echo ('<tr bgcolor= "'.$datarow_color.'">');
 
    echo ('<td valign="top" style="color: #000000; border-bottom: 1px solid #e4900d; border-right: 1px solid #e4900d;" bgcolor="'.$datarow_color.'"><span class="member_links_underline_black" style="color: #000000; ">'.$count.'</span>
    </td>');
 
    echo ('<td style="color: #000000; border-bottom: 1px solid #e4900d; width: 460px;" bgcolor= "'.$datarow_color.'">');
    echo ('<span class="member_links_underline_black">');
    if($latest_read < $messagetime){
        echo ('<img style="margin-left: 5px;" src="../images/folder_unread.gif" alt="lukematon_viesti" />');
    }
    else if ($timetoday == $time1){
        echo ('<img style="margin-left: 5px;" src="../images/folder_new.gif" alt="uusi_viesti" />');
                            }
    else{
        echo ('<img style="margin-left: 5px;" src="../images/folder.gif" alt="otsikko" />');
    }
 
    echo ('<span style="font-weight: bold;"><a style="font-size: 10px; margin-left: 1px;" href="index.php?id=1&amp;show='.$row['topicid'].'#end">'.$row['topicname'].'</a></span></span>
    <span class="member_links_underline_black" style="color: #000000;">by&nbsp;<span class="member_links_underline_black_1" style="margin-left: 1px;"><a href="../users/index.php?id='.$row['usersid'].'">'.$row['username'].'</a></span></span>
    <br /><div style="background-color: #f6f5ed; border: 1px solid black; border-style: inset; margin-left: 20px; margin-right: 10px; margin-top: 5px; margin-bottom: 5px; padding: 3px 3px 3px 3px;">'.$search_result.'..</div>
    </td>');
 
    echo "<td valign=\"top\" style=\"color: #000000; border-left: 1px solid #e4900d; border-bottom: 1px solid #e4900d; border-left: 1px solid #e4900d;\" bgcolor=\"$datarow_color\">"; echo ('<span class="member_links_underline_black" style="color: #000000;"><a href="../users/index.php?id='.$topic['users_id'].'">'.$row['username'].'</a></span>');
    echo "</td>";
    echo ('<td valign="top" style="color: #000000; border-left: 1px solid #e4900d; border-bottom: 1px solid #e4900d; border-left: 1px solid #e4900d;" bgcolor="'.$datarow_color.'">'.$row['topict'].'</td>');
 
    echo ('<td valign="top" style="color: #000000; border-left: 1px solid #e4900d; border-bottom: 1px solid #e4900d; border-left: 1px solid #e4900d;" bgcolor="'.$datarow_color.'">'.$row['insertdate'].'</td>');
    echo ('</tr>');
}
    echo ('<tr>');
    echo ('<td colspan="5" style="font-weight: bold; padding-bottom: 2px;" bgcolor="#720606" class="td_back">&nbsp;</td>');
    echo ('</tr>');
    echo ('</table>');
}
}
?>

Open in new window

0
ToubeAuthor Commented:
Jeps true.

Got it out and now if the search result is showing 5 results, the echo $page = 11111

-T
0
dr_dedoCommented:
i'll look at your code, but 11111 5 times means that you called the function 5 times, check your code
0
ToubeAuthor Commented:
Jeps corrected it.. it was inside a while loop, now it displays 1.. ok whats next?

-T
0
dr_dedoCommented:
in your code, echo $page is in you rwhile loop, this means that all the found results are in page 1
you should do this
echo <<<XXX
<a href="index.php?page=$page&topicID=$topicID>Search result 1</a><br>
XXX;
0
ToubeAuthor Commented:
Oukey tried it out but it always displays $page = 1
When the function is called $page =  PostToPage(1,18); what does the numbers 1 and 18 stand for?
0
dr_dedoCommented:
:)
that function takes 3 parameters, first one is teh topic id, second is the post id, and third optional one which is how many records per page
1, 18 are just examples
remember that our goal was to know at which page does a certain post lies ?
0
ToubeAuthor Commented:
Thanks, got that.. :)

So if 1 and 18 are just examples shouldn't I call the function like this to get real result:
$page =  PostToPage($topicid_,$postID); --> this also just gives $page = 1

Or how will I get the real result out?
0
dr_dedoCommented:
I assume that you have a table called posts which contains all the posts in the form of ID, post, topicID, is that right?
0
ToubeAuthor Commented:
Jeps correct.
0
dr_dedoCommented:
lets debug the code please, call it with topic id and post id
you have 20 records per page, right ??
function PostToPage($topicid_,$postID,$rowsPerPage=20){
        $sql = "select count(ID) as num from posts where topicID='$topicid_' and ID ='$postID'";
echo $sql."<br>";
        $r = mysql_query($sql) or die (mysql_error());
echo ('rows = '.mysql_num_rows($r)."<br>");        
if (mysql_num_rows($r) < 1) return false;
        $num = mysql_result($r,0,0)+1;
echo ('$num = '.$num."<br>");        
$page = $num/$rowsPerPage;
        return ceil($page);
}

Open in new window

0
ToubeAuthor Commented:
Jeps 20 rows per search.

Ok at the moment it outputs the following:

SELECT count(ID) as num FROM posts where TopicID ='15' and ID ='539'
rows = 1
$num = 2

and at the moment the function looks like this:

i'n not sure if the $sqlquery that I use for searching the postID and topicid is working...

-T
function PostToPage($topicid_,$postID,$rowsPerPage=20)
{
 
		$userid = $_SESSION['users_id'];
 
$sqlquery = "SELECT t.ID as tid, t.TopicName, t.deleted, t.userlevel_topic, DATE_FORMAT(t.topictime, '%d.%m.%Y %H:%i') AS topict, t.locked, u.username, u.users_id, u.userlevel, u.deluser_, p.TopicID, p.ID as postid, DATE_FORMAT(p.timestamp, '%d.%m.%Y %H:%i:%s' ) AS insertdat, MAX(p.timestamp) as lastpost FROM topics t, users u, posts p WHERE u.users_id=t.user_ AND p.TopicID=t.ID AND u.deluser_='0'and t.deleted = '0' group by t.ID order by lastpost desc";
 
$result2 = mysql_query($sqlquery);
 
while($row=mysql_fetch_array($result2))//the query for the topicid and postid
		{
		   $postID = $row['postid'];
		   $topicid_ = $row['tid'];
		   //echo $row['tid'];
		   //echo $postID;
 
$sql = "SELECT count(ID) as num FROM posts where TopicID ='$topicid_' and ID ='$postID'";
	echo $sql."<br>";
        $r = mysql_query($sql) or die (mysql_error());
		echo ('rows = '.mysql_num_rows($r)."<br>");
		if (mysql_num_rows($r) < 1)
		{
			return false;
		}
        $num = mysql_result($r,0,0)+1;
		echo ('$num = '.$num."<br>");
		$page = $num/$rowsPerPage;
        return ceil($page);
        }//end while loop
}

Open in new window

0
dr_dedoCommented:
i don't know exactly what you are doing here
lets revise what we have and what is the problem from the start, OK ?
you have a search that works OK, and the problem is that when you link to topic, it redirects you to the last page
this means that search is working fine, right ?
now, we said that your search can get the post id and topic id that the searched word is present
and we agree that a call like this index.php?topic=3&page=2 would get us to page 2 of topic id 3
now, we are testing a function, that nothing but trying to figure out (based on topic id and post id and allowed number of posts per page) the page that this post lies in
now, this function, simply selects count of posts that lie in the given topic and that have an id < id of the current post
that function is used once per row, and you dont need to add any code in there. if your search function returned 10 results, then you'd call this function 10 times to generate the proper hyper link to each result
now, that function as you say has errors, or returns page 1 all the time. please, run it exactly as my previous post and see the printed outcome
0
ToubeAuthor Commented:
Hi,

well I'm using and debugging it the way you described and here's the output of that:

SELECT count(ID) as num FROM posts where TopicID ='15' and ID ='539'
rows = 1
$num = 2

using it this way:

function PostToPage($topicid_,$postID,$rowsPerPage=20){
$sql = "select count(ID) as num from posts where topicID='$topicid_' and ID ='$postID'";
echo $sql."<br>";
 $r = mysql_query($sql) or die (mysql_error());
echo ('rows = '.mysql_num_rows($r)."<br>");        
if (mysql_num_rows($r) < 1) return false;
$num = mysql_result($r,0,0)+1;
echo ('$num = '.$num."<br>");        
$page = $num/$rowsPerPage;
return ceil($page);
}

-T
0
dr_dedoCommented:
ok, something is wrong here, seems your db is some how different than i thought or something
ok, try this SQL sentence in phpmyadmin or in your mysql query browser
select ID, post from post where TopicId =15
and show the results here, ok?
0
ToubeAuthor Commented:
Jeps true the result is 55 posts in topic 15 .

-T
0
dr_dedoCommented:
that's sweet, now, run another query, will you ?
select ID, post from post where TopicId =15 and ID<539
0
ToubeAuthor Commented:
That returns zero rows as result.

-T
0
dr_dedoCommented:
this has 2 reasons to return no results
either ID is not int
or post 539 is the first post in this topic !
ok, run this, and copy/paste result in here

mysql_connect ($host,$username,$pass);
mysql_select_db($db);
$r = mysql_query ("select ID, post from post where TopicId =15") or die (mysql_error());
while ($row = mysql_fetch_array($r)){
echo "ID= {$row['ID']}\n<br>";
}

Open in new window

0
ToubeAuthor Commented:
Ok, the result show 55 posts:

ID= 539
ID= 744
ID= 904
ID= 906
ID= 909
ID= 914
ID= 919
ID= 941
ID= 954
ID= 955
ID= 957
ID= 1017
ID= 1024
ID= 1025
ID= 1036
ID= 1038
ID= 1039
ID= 1043
ID= 1070
ID= 1071
ID= 1076
ID= 1077
ID= 1083
ID= 1085
ID= 1109
ID= 1110
ID= 1111
ID= 1112
ID= 1113
ID= 1114
ID= 1115
ID= 1116
ID= 1117
ID= 1118
ID= 1120
ID= 1123
ID= 1130
ID= 1131
ID= 1135
ID= 1137
ID= 1143
ID= 1144
ID= 1145
ID= 1146
ID= 1150
ID= 1173
ID= 1201
ID= 1202
ID= 1233
ID= 1235
ID= 1237
ID= 1248
ID= 1249
ID= 1251
ID= 1252
0
dr_dedoCommented:
well, as it is shown, there is no posts before post 539, this means that this is the first post in that topic, and hence, it must be lying in page 1
am i right ?
try sending these parameters to my function
echo PostToPage(15,1252,20); // these numbers are just exampls to mimic post with id 1252 in post 15
0
ToubeAuthor Commented:
Ok, now it returns an echo:

SELECT count(ID) as num FROM posts where TopicID ='15' and ID ='539'
rows = 1
$num = 2
SELECT count(ID) as num FROM posts where TopicID ='15' and ID ='539'
rows = 1
$num = 2
1

Though this result is with this query:
SELECT count(ID) as num FROM posts where TopicID ='$topicid_' and ID ='$postID'

-T
0
dr_dedoCommented:
this function out put means that u sent the OLD DATA to the function,
SELECT count(ID) as num FROM posts where TopicID ='15' and ID ='539'
rows = 1
$num = 2
SELECT count(ID) as num FROM posts where TopicID ='15' and ID ='539'
rows = 1
$num = 2
1

call it it with the NEW DATA, which are topicId= 15 and post ID = 1252
i.e. do this
echo PostToPage(15,1252,20);
0
ToubeAuthor Commented:
Ok, got a bit confused there.

Here's the new output from the query:
$sql = "SELECT count(ID) as num FROM posts where TopicID ='15' and ID ='1252'";

and function echoed:
echo PostToPage(15,1252,20);

result:

SELECT count(ID) as num FROM posts where TopicID ='15' and ID ='1252'
rows = 1
$num = 2
SELECT count(ID) as num FROM posts where TopicID ='15' and ID ='1252'
rows = 1
$num = 2
1
0
dr_dedoCommented:
now, there is a major screw up
as you can see in this question, post ID 20819385 (my very frist answer), the sql statment is this
$sql = "select count(ID) as num from posts where topicID=$topicID and ID<$postID";
and not
$sql = "select count(ID) as num from posts where topicID=$topicID and ID=$postID";

i don't know why did you change it (as sent in post 20825104) and even i got confused and didn't notice that you modified it for some unknown reasons
now, please, in this function, change only relevant data as databse name, server, etc..., and don't change the code, and call it that way
echo PostToPage(15,1252,20);
it should return 3
function PostToPage($topicID,$postID,$rowsPerPage=10){
	$sql = "select count(ID) as num from posts where topicID=$topicID and ID<$postID";
	$r = mysql_query($sql);
	if (mysql_num_rows($r)<1) return false;
	$num = mysql_result($r,0,0)+1;
	$page = $num/$rowsPerPage;
	return ceil($page);
}

Open in new window

0
ToubeAuthor Commented:
Jeps it returns 3 now.. sorry for the srew up :S
0
dr_dedoCommented:
now u need to implement this into your code
pass to that function post id and topic id and assign the return to variablt that you can use, remember, what we are after is this
<a href="index.php?topicID=$topicID&page=$page?>search result 1</a>
0
ToubeAuthor Commented:
Ok, thanks.. well I just have to figure the rest out for myself:)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.