asked on

Php mysql link to posted message

Hi!

I'm using a pager class for counting pages of queries from the database in my forum.
Class attached to code snippet.

Now I also just added search the will display form topics and posts within the topics depending on the search word.

What I would like to achieve is to be able to click on a search result link and displaying the topics posts on the page where the search word is found.

Now it just displays the last page of the topic, so the user always have to go through all the pages to found the post where the search word was found.

Is this possible?

-T

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

Toube

ASKER

Anyone?

dr_dedo

i can see some nice paging code, but where you perform search or create a link to the search result ? that's the part of code that is needed

Toube

ASKER

Hi,

sorry.. yes here the search function that displays the links to the topics.

-T

<?php
function showsearch($pageid)
{
 
 
$userid = $_SESSION['users_id'];
 
$search = ($_POST['search']);
 
$color1 = '#f7e1b0';
$color2 = '#efcd83';
 
$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
}
 
// 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, 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); //amount of rows in search
 
    //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'];
 
        $datarow_color = ($datarow_count % 2) ? $color1 : $color2;
        $datarow_count++;
 
    $time1 = $row['time'];
    $timetoday = date("d.m.Y");
 
 
 
    $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" />');
    }
 
 
 
// here is the link to the topic.
 
    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

Toube

ASKER

Anyone?

dr_dedo

at line 102, remove #end to be like attached code

echo ('<span style="font-weight: bold;"><a style="font-size: 10px; margin-left: 1px;" href="index.php?id=1&amp;show='.$row['topicid'].'">'.$row['topicname'].'</a></span></span>

Open in new window

Toube

ASKER

Thanks dedo, but the #end is just a html tag that moves the page to the end.

-Toby

dr_dedo

I don't know about ur forum and how it works, but what happens when you pass topic id to the index.php ? does it show last post as what happens with search you are doing or shows it from start ? also regarding posts, how do u deal with them ? you make them as anchors in your page or just list them in thier topic ??

Toube

ASKER

As for the posts I have no anchors for them, but that is the future plan that user clicks the search result link and when the page to the topic opens it should stop at the user who posted the message.

The topicid is already being passed in the url:
<span style="font-weight: bold;"><a style="font-size: 10px; margin-left: 1px;" href="index.php?id=1&show='.$row['topicid'].'#end"></span>

At the moment all pages shows the last page and the most resent post. e.g if there are 4 pages and the user goes to the topic, it shows the user page number 4 and last post of the page #end anchor.

dr_dedo

then the problem is not in your search function, it is in how index page deals with topic id !!

Toube

ASKER

Ok, that's true.. but how would I modify the index so it would work like described above.. would it be hard?

ASKER CERTIFIED SOLUTION

dr_dedo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Toube

ASKER

Ok thanks for your suggestion.The points are coming your way.
But I still need some guidance for calculating the the posts and so on.. can you help me with this or should I open a new question for this..

-T