JReviews: Updating the Search criteria

Regular Joined: Jul 02, 2009 Posts: 71

Hello all. I'm currently working on tweaking a module I recently installed (JReviews). So far this is a great module, is a step up from the basic Reviews module, and no bugs like MReviews had all through it.

Right now, I'm working on tweaking the search function. Currently, if you search it will look through the review title and text to meet its criteria for search results. I want to narrow that down and remove text from the equation. Instead, I want it to include topics in the search. I've almost got it, but have run into a brick wall in trying to get it to work right.

So, right now I have it searching through the title successfully, but for the topic it seems to be using the topic ID instead of the topic name. So, if the topic ID is 2, and you search for 2 you will find them all.

Here is my current query code:

Code:

    $result = $db->sql_query("SELECT * FROM ".$prefix."_jreviews WHERE title LIKE '%$query%' OR category LIKE '%$query%' ORDER BY $orderby LIMIT $min, $reviewsresults");


    $fullcountresult = $db->sql_query("SELECT * FROM ".$prefix."_jreviews WHERE title LIKE '%$query%' OR category LIKE '%$query%'");

The problem is in the database it records the topic in the review by the ID number. I need it to look into the category database to pull the 'name'. Here are is my database info for the categories:

_jreviews_categories

I'm sure I need to point it to that part of the database to retrieve the 'name' column from the table. Currently it is pointing to the complete review

_jreviews

and just pulling the category ID

Any help ferreting out this code would be great! I'm sure it's something simple I'm missing here.

The module in action:
http://www.jeditemplearchives.com/content/modules.php?name=JReviews

Posted: Thu Sep 10, 2009 1:57 pm

What is the $orderby variable equal to?

Posted: Thu Sep 10, 2009 2:02 pm

You need to do something similar to this.

http://www.astahost.com/info.php/Mysql-Multiple-Tables_t19095.html

Posted: Thu Sep 10, 2009 5:38 pm

Here is the whole function:

Code:

function search($query, $min, $orderby, $show) {


    global $prefix, $db, $admin, $bgcolor2, $module_name;


//   $admin = base64_decode($admin);


//   $admin = addslashes($admin);


//   $admin = explode(":", $admin);


// $aid = "$admin[0]";


   $aid =     stripslashes(FixQuotes(check_html($admin[0], nohtml)));


   $cid = intval($row['category']);


   $row = $db->sql_fetchrow($db->sql_query("SELECT radminsuper FROM ".$prefix."_authors WHERE aid='$aid'"));


   $radminsuper = intval($row['radminsuper']);


    $reviewsresults = 1000;


    $perpage=1000;


    if (!isset($min)) $min=0;


    if (!isset($max)) $max=$min+$reviewsresults;


    if(isset($orderby)) {


   $orderby = convertorderbyin($orderby);


    } else {


   $orderby = "title ASC";


    }


    if ($show!="") {


   $reviewsresults = $show;


    } else {


   $show=$reviewsresults;


    }


   $query = check_html($query, "nohtml");


    $query = addslashes($query);


    $result = $db->sql_query("SELECT * FROM ".$prefix."_jreviews WHERE title LIKE '%$query%' OR category LIKE '%$query%' ORDER BY $orderby LIMIT $min, $reviewsresults");


    $fullcountresult = $db->sql_query("SELECT * FROM ".$prefix."_jreviews WHERE title LIKE '%$query%' OR category LIKE '%$query%'");


    $totalselectedlinks = $db->sql_numrows($fullcountresult);


    $nrows = $db->sql_numrows($result);


    $x=0;


    $the_query = stripslashes($query);


    $the_query = str_replace("\'", "'", $the_query);


    echo "<br>";


    OpenTable();


    if ($query <> '') {


       if ($nrows>0) {


      echo "<font size=\"2\" face=\"Arial, Helvetica\">"._SEARCHRESULTS4.": <b>$the_query</b></font><br><br>";


//          ."<table width=\"100%\" bgcolor=\"$bgcolor2\"><tr><td><font class=\"option\"><b>"._USUBCATEGORIES."</b></font></td></tr></table>";


           $result2 = $db->sql_query("SELECT * FROM ".$prefix."_jreviews_categories where title LIKE '%$query%' ORDER BY name DESC");


           while ($row2 = $db->sql_fetchrow($result2)) {


           $rid = intval($row2['id']);


           $stitle = stripslashes(check_html($row2['title'], "nohtml"));


           $res = $db->sql_query("SELECT * from ".$prefix."_jreviews where cid='$cid'");


           $numrows = $db->sql_numrows($res);


               $row3 = $db->sql_fetchrow($db->sql_query("SELECT * FROM ".$prefix."_jreviews_categories where id='$rid'"));


               $rid3 = intval($row3['id']);


               $title3 = stripslashes(check_html($row3['title'], "nohtml"));


               $parentid3 = intval($row3['parentid']);


          if ($parentid3>0) $title3 = getparent($parentid3,$title3);


          $title3 = ereg_replace($query, "<b>$query</b>", $title3);


//          echo "<strong><big>·</big></strong>&nbsp;<a href=\"modules.php?name=$module_name&amp;rop=viewlink&amp;cid=$cid\">$title3</a> ($numrows)<br>";


      }


//   echo "<br><table width=\"100%\" bgcolor=\"$bgcolor2\"><tr><td><font class=\"option\"><b>"._REVIEWS."</b></font></td></tr></table>";


       $orderbyTrans = convertorderbytrans($orderby);


       echo "<br><font size=\"2\" face=\"Arial, Helvetica\">"._SORTREVIEWSBY.": "


           .""._TITLE." (<a href=\"modules.php?name=$module_name&amp;rop=search&amp;query=$the_query&amp;orderby=titleA\">A</a>\<a href=\"modules.php?name=$module_name&amp;rop=search&amp;query=$the_query&amp;orderby=titleD\">D</a>)"


           ." "._DATE." (<a href=\"modules.php?name=$module_name&amp;rop=search&amp;query=$the_query&amp;orderby=dateA\">A</a>\<a href=\"modules.php?name=$module_name&amp;rop=search&amp;query=$the_query&amp;orderby=dateD\">D</a>)"


           ." "._RATING." (<a href=\"modules.php?name=$module_name&amp;rop=search&amp;query=$the_query&amp;orderby=ratingA\">A</a>\<a href=\"modules.php?name=$module_name&amp;rop=search&amp;query=$the_query&amp;orderby=ratingD\">D</a>)"


           ." "._POPULARITY." (<a href=\"modules.php?name=$module_name&amp;rop=search&amp;query=$the_query&amp;orderby=hitsA\">A</a>\<a href=\"modules.php?name=$module_name&amp;rop=search&amp;query=$the_query&amp;orderby=hitsD\">D</a>)"


           ."<br>"._REVIEWSSORTED.": $orderbyTrans<br><br>";


echo "<table width=\"100%\" cellpadding=\"0\" cellspacing=\"0\"><tr><td><font size=\"2\" face=\"Arial, Helvetica\"><img src=\"/images/spacer.gif\" width=\"15\" height=\"3\" /></font></td></tr><tr><td bgcolor=\"#dddddd\"/images/spacer.gif\" width=\"15\" height=\"1\"></td></tr><tr><td><font size=\"2\" face=\"Arial, Helvetica\"><img src=\"/images/spacer.gif\" width=\"15\" height=\"2\" /></font></td></tr></table>";


       while($row = $db->sql_fetchrow($result)) {


                $rid = intval($row['id']);


                $cid = intval($row['category']);


                $sid = intval($row['sid']);


           $parent = intval($row['parent']);


                $title = stripslashes(check_html($row['title'], "nohtml"));


                $url = stripslashes($row['url']);


                $description = stripslashes($row['text']);


                $score = intval($row['score']);


                $time = $row['date'];


                $hits = intval($row['hits']);


            $rating = $row["rating"];


            $totalvotes = intval($row["totalvotes"]);


       $transfertitle = str_replace (" ", "_", $title);


       $title = ereg_replace($query, "<b>$query</b>", $title);


       echo '<font size="2" face="Arial, Helvetica"><a href="modules.php?name='.$module_name.'&amp;rop=showcontent&id='.$rid.'" target="_self">'.$title.'</a>';


       newreviewgraphic($time);


        popgraphic($hits);


       echo "<br>";


       $description = ereg_replace($query, "<b>$query</b>", $description);


//       echo ""._DESCRIPTION.": $description<br>";


    if (is_admin($admin))


      echo ""._HITS.": $hits";


      setlocale (LC_TIME, $locale);


       $datetime=explode('-', $time);


       $datetime = strftime(_REVIEWSDATESTRING, mktime(0, 0, 0,$datetime[1],$datetime[2],$datetime[0]));


       $datetime = ucfirst($datetime);


    if ($radminsuper == 1) {


       echo '<a href="modules.php?name='.$module_name.'&amp;rop=mod_review&amp;id='.$rid.'">'._EDIT.'</a> | ';


    }


    $title = stripslashes(FixQuotes(check_html($title, nohtml)));


    $title = htmlspecialchars(urldecode($title));


       echo "<br>";


      $row4 = $db->sql_fetchrow($db->sql_query("SELECT * FROM ".$prefix."_jreviews_categories where id='".$cid."'"));


      $cattitle = stripslashes(check_html($row4['name'], "nohtml"));


   $row5 = $db->sql_fetchrow($db->sql_query("SELECT * FROM ".$prefix."_jreviews_categories WHERE parent='".$parent."'"));


   $cat = $row5['name'];


      echo _CATEGORY.': <a href="modules.php?name='.$module_name.'&amp;rop=categ&amp;id='.intval($row5["id"]).'">'.$cat.'</a>&nbsp;&raquo;&nbsp;<a href="modules.php?name='.$module_name.'&amp;rop=categ&amp;id='.intval($row4["id"]).'">'.$cattitle.'</a><br>';


   echo ""._SCORE." ";


    display_score($score);   


   echo '<br>';


   echo _ADDEDON.': '.$datetime.'';


echo "<table width=\"100%\" cellpadding=\"0\" cellspacing=\"0\"><tr><td><font size=\"2\" face=\"Arial, Helvetica\"><img src=\"/images/spacer.gif\" width=\"15\" height=\"3\" /></font></td></tr><tr><td bgcolor=\"#dddddd\"/images/spacer.gif\" width=\"15\" height=\"1\"></td></tr><tr><td><font size=\"2\" face=\"Arial, Helvetica\"><img src=\"/images/spacer.gif\" width=\"15\" height=\"2\" /></font></td></tr></table>";


          $x++;


   }


   echo "</font>";


       $orderby = convertorderbyout($orderby);


    } else {


   echo "<br><br><center><font class=\"option\"><b>"._NOMATCHES."</b></font><br><br>"._GOBACK."<br></center>";


    }


    /* Calculates how many pages exist.  Which page one should be on, etc... */


    $linkpagesint = ($totalselectedlinks / $reviewsresults);


    $linkpageremainder = ($totalselectedlinks % $reviewsresults);


    if ($linkpageremainder != 0) {


       $linkpages = ceil($linkpagesint);


        if ($totalselectedlinks < $reviewsresults) {


           $linkpageremainder = 0;


   }


    } else {


       $linkpages = $linkpagesint;


    }


    /* Page Numbering */


    if ($linkpages!=1 && $linkpages!=0) {


   echo "<br><br>"


       .""._SELECTPAGE.": ";


   $prev=$min-$reviewsresults;


   if ($prev>=0) {


           echo "&nbsp;&nbsp;<b>[ <a href=\"modules.php?name=$module_name&amp;rop=search&amp;query=$the_query&amp;min=$prev&amp;orderby=$orderby&amp;show=$show\">"


          ." &lt;&lt; "._PREVIOUS."</a> ]</b> ";


         }


   $counter = 1;


        $currentpage = ($max / $reviewsresults);


        while ($counter<=$linkpages ) {


           $cpage = $counter;


            $mintemp = ($perpage * $counter) - $reviewsresults;


            if ($counter == $currentpage) {


      echo "<b>$counter</b>&nbsp;";


       } else {


      echo "<a href=\"modules.php?name=$module_name&amp;rop=search&amp;query=$the_query&amp;min=$mintemp&amp;orderby=$orderby&amp;show=$show\">$counter</a> ";


       }


            $counter++;


        }


        $next=$min+$reviewsresults;


        if ($x>=$perpage) {


           echo "&nbsp;&nbsp;<b>[ <a href=\"modules.php?name=$module_name&amp;rop=search&amp;query=$the_query&amp;min=$max&amp;orderby=$orderby&amp;show=$show\">"


          ." "._NEXT." &gt;&gt;</a> ]</b>";


        }


    }


    echo "<br><br><center><font class=\"content\">"


   .""._TRY2SEARCH." \"<b><i>$the_query</i></b>\" "._INOTHERSENGINES."<br>"


   ."<a target=\"_blank\" href=\"http://www.altavista.com/cgi-bin/query?pg=q&amp;sc=on&amp;hl=on&amp;act=2006&amp;par=0&amp;q=$the_query&amp;kl=XX&amp;stype=stext\">Alta Vista</a> - "


   ."<a target=\"_blank\" href=\"http://www.hotbot.com/?MT=$the_query&amp;DU=days&amp;SW=web\">HotBot</a> - "


   ."<a target=\"_blank\" href=\"http://www.infoseek.com/Titles?qt=$the_query\">Infoseek</a> - "


   ."<a target=\"_blank\" href=\"http://www.dejanews.com/dnquery.xp?QRY=$the_query\">Deja News</a> - "


   ."<a target=\"_blank\" href=\"http://www.lycos.com/cgi-bin/pursuit?query=$the_query&amp;maxhits=20\">Lycos</a> - "


   ."<a target=\"_blank\" href=\"http://search.yahoo.com/bin/search?p=$the_query\">Yahoo</a>"


   ."<br>"


   ."<a target=\"_blank\" href=\"http://es.linuxstart.com/cgi-bin/sqlsearch.cgi?pos=1&amp;query=$the_query&amp;language=&amp;advanced=&amp;urlonly=&amp;withid=\">LinuxStart</a> - "


   ."<a target=\"_blank\" href=\"http://search.1stlinuxsearch.com/compass?scope=$the_query&amp;ui=sr\">1stLinuxSearch</a> - "


   ."<a target=\"_blank\" href=\"http://www.google.com/search?q=$the_query\">Google</a> - "


   ."<a target=\"_blank\" href=\"http://www.linuxlinks.com/cgi-bin/search.cgi?query=$the_query&amp;engine=Links\">LinuxLinks</a> - "


   ."<a target=\"_blank\" href=\"http://www.freshmeat.net/search/?q=$the_query&amp;section=projects\">Freshmeat</a> - "


   ."<a target=\"_blank\" href=\"http://www.justlinux.com/bin/search.pl?key=$the_query\">JustLinux</a>"


   ."</font>";


    } else {


   echo "<center><font class=\"option\"><b>"._NOMATCHES."</b></font></center><br><br>";


    }


    CloseTable();


}

Posted: Thu Sep 10, 2009 5:40 pm

Palbin wrote:

You need to do something similar to this.

http://www.astahost.com/info.php/Mysql-Multiple-Tables_t19095.html

I will take a look at that.