[Request] Top List of Search Keywords

[www.girls-on-bikes.com]

Hello All,
I have a request for a mod.
It would be cool to be able to display a toplist of search keywords.

I actually want this for more internal usages so I can figure out what my users are searching for.

EDIT: This has been turned into an official mod located here:
http://www.4homepages.de/forum/viewtopic.php?t=6033

Thanks

[Xwall]

I think the same and Top Categories is ok also.  

[SLL]

It would be cool to be able to display a toplist of search keywords.

search strings are not stored in the database

[V@no]

search strings are not stored in the database

that's right, so, basicaly all u have to do is create new table with two fields "name" and "count" for example.
then every time someone search for something u update that table...
*how to do that? I duno...*

[www.girls-on-bikes.com]

I have thought about it and I'm actually going to attempt doing this script.
Granted I don't know enough about 4images, so it may not be a public mod, but I think I can write it.

[www.girls-on-bikes.com]

OK Folks, here it is, the ugliest, most inefficient code you have ever seen.
I put this code inside search.html, and {lang_search} comes from:
http://www.4homepages.de/forum/viewtopic.php?t=5968

Also, I created 2 new tables for this job.
Any mad crazy 4image coders are welcome to take this idea and make a real mod out of it.  Just wanted to share my <crappy> solution.

Code: [Select]

<?php
$search_phrase = "&#123;lang_search&#125;";
$search_phrase = str_replace&#40;"Search&#58; ","",$search_phrase&#41;;
$search_terms = explode&#40;" ", $search_phrase&#41;;

$db = mysql_connect&#40;"localhost", "gob", "************"&#41;;
mysql_select_db&#40;"gob_gallery",$db&#41;;

$sql = "SELECT * FROM 4images_search_phrase WHERE phrase=\"".$search_phrase."\"";
$res = mysql_query&#40;$sql,$db&#41;;
$num_rows = mysql_num_rows&#40;$res&#41;;
if &#40;$num_rows > 0&#41; &#123;
  $row = mysql_fetch_array&#40;$res&#41;;
  $count = $row["count"&#93; + 1;
  $sql = "UPDATE 4images_search_phrase SET count=\"".$count."\" WHERE phrase = \"".$search_phrase."\"";
  $res = mysql_query&#40;$sql,$db&#41;;
&#125;
else &#123;
  $sql = "INSERT INTO `4images_search_phrase` VALUES &#40;\"".$search_phrase."\",1&#41;";
  $res = mysql_query&#40;$sql,$db&#41;;
&#125;

foreach &#40;$search_terms as $term&#41; &#123;
  $sql = "SELECT * FROM 4images_search_terms WHERE term=\"".$term."\"";
  $res = mysql_query&#40;$sql,$db&#41;;
  $num_rows = mysql_num_rows&#40;$res&#41;;
  if &#40;$num_rows > 0&#41; &#123;
    $row = mysql_fetch_array&#40;$res&#41;;
    $count = $row["count"&#93; + 1;
    $sql = "UPDATE 4images_search_terms SET count=\"".$count."\" WHERE term = \"".$term."\"";
    $res = mysql_query&#40;$sql,$db&#41;;
  &#125;
  else &#123;
    $sql = "INSERT INTO `4images_search_terms` VALUES &#40;\"".$term."\",1&#41;";
    $res = mysql_query&#40;$sql,$db&#41;;
  &#125;
&#125;
mysql_close&#40;$db&#41;;
?>


[V@no]

ok, here is "adopted" for 4images classes version

in search.php find:

Code: [Select]

$sql = "SELECT m.image_id
add before:

Code: [Select]

     $sql = "UPDATE ".SEARCH_WORDS_TABLE."
      SET count = count + 1
      WHERE word LIKE '".addslashes(str_replace("*", "", $split_words[$i]))."'";
      $site_db->query($sql);
if (!$site_db->affected_rows()) {
 $sql = "INSERT INTO ".SEARCH_WORDS_TABLE."
         (word, count)
         VALUES
         ('".addslashes(str_replace("*", "", $split_words[$i]))."', 1)";

 $site_db->query($sql);
}

in /includes/constants.php add this line:

Code: [Select]

define('SEARCH_WORDS_TABLE', $table_prefix.'search_words');

create new table in the DB with name 4images_search_words
this will record each word that was searched for, and count how many times that word was searched. At the begining I was gonna just add an extra field in 4images_wordlist table, that would just store count of each word/keyword being searched, but then I realized that this way it wont count the words that are not in the DB....

[www.girls-on-bikes.com]

Thanks for re-forming that into something usable

Here is my finished product:
http://new.girls-on-bikes.com/search_stats.php

Edit:
I suppose I will embarrass myself even more and post up the code to print the stuff out.  Hopefully somebody can take this and make a "real" mod.

Code: [Select]


<span class="title">Search Result Stats</span>
<hr size="1" />
 
<?php
  $db = mysql_connect&#40;"localhost", "gob", "*********"&#41;;
  mysql_select_db&#40;"gob_gallery",$db&#41;;
 
  $sql = "SELECT * FROM 4images_search_phrase ORDER BY `count` DESC";
  $phrase_res = mysql_query&#40;$sql,$db&#41;;
 
  $sql = "SELECT * FROM 4images_search_terms ORDER BY `count` DESC";
  $terms_res = mysql_query&#40;$sql,$db&#41;;
 
  $phrase_counts = array&#40;""&#41;;
  $terms_counts = array&#40;""&#41;;
?>
 
<table width="100%" border="0" cellspacing="0" cellpadding="2">
  <tr>
    <td valign="top" width="50%">
      <table width="100%" border="0" cellspacing="0" cellpadding="1">
        <tr>
          <td class="head1">
            <table width="100%" border="0" cellspacing="0" cellpadding="3">
              <tr>
                <td class="head1" valign="top" width="90%">
                  Top Search Phrases
                </td>
                <td class="head1" valign="top" width="10%">Count</td>
              </tr>
              <tr>
                <td class="row2" valign="top" width="90%">
                  <?php
                    for &#40;$i=0;$row = mysql_fetch_array&#40;$phrase_res&#41;;$i++&#41; &#123;
                      print $row["phrase"&#93;."<br>";
                      $phrase_counts[$i&#93; = $row["count"&#93;;
                    &#125;
                  ?>
                </td>
                <td class="row2" valign="top" align="center" width="10%">
                  <?php
                    for &#40;$i=0;$i<count&#40;$phrase_counts&#41;;$i++&#41; &#123;
                      print $phrase_counts[$i&#93;."<br>";
                    &#125;
                  ?>
                </td>
              </tr>
            </table>
          </td>
        </tr>
      </table>
    </td>
    <td valign="top" width="50%">
      <table width="100%" border="0" cellspacing="0" cellpadding="1">
        <tr>
          <td class="head1">
            <table width="100%" border="0" cellspacing="0" cellpadding="3">
              <tr>
                <td class="head1" valign="top" width="90%">
                  Top Search Terms
                </td>
                <td class="head1" valign="top" width="10%">Count</td>
              </tr>
              <tr>
                <td class="row2" valign="top" width="90%">
                  <?php
                    for &#40;$i=0;$row = mysql_fetch_array&#40;$terms_res&#41;;$i++&#41; &#123;
                      print $row["term"&#93;."<br>";
                      $terms_counts[$i&#93; = $row["count"&#93;;
                     &#125;
                  ?>
                </td>
                <td class="row2" valign="top" align="center" width="10%">
                  <?php
                    for &#40;$i=0;$i<count&#40;$terms_counts&#41;;$i++&#41; &#123;
                      print $terms_counts[$i&#93;."<br>";
                    &#125;
                  ?>
                </td>
              </tr>
            </table>
          </td>
        </tr>
      </table>
    </td>
  </tr>
</table>
 
<?php
  mysql_close&#40;$db&#41;;
?>


[Shap]

Looks good!

I'll try it out, once my servers back up.

[www.girls-on-bikes.com]

Please keep in mind this is not a public mod, it's my own <hacked> version.  There would be some things you would need to do and change before you can use this, unless you can understand what my code says and you understand what needs to be changed

Let me know if you would be interested and I can tell you what tables to add and what text to change.

[V@no]

inspired by www.girls-on-bikes.com got it working...now, just need some oppinium, should it be using paging system, and also, have ability sort words/phrases by name, or by count?

[Xwall]

---> www.girls-on-bikes.com
Why not this mod is public for all?
You use 4mages script free no?
You request help to make it and V@no helps you.
You say "Any mad crazy 4image coders are welcome to take this idea and make a real mod out of it"
Well why you say this now "this is not a public mod"  :

I want this mod

[www.girls-on-bikes.com]

What I meant by a "public mod" is that in it's current form, it is not universal, meaning if you copied the code to your server, it would not work.
I am simply not familar enough with 4images to be able to incorporate it into a real mod, but I'm hoping some one who has coded in 4images will be able to.
Also, the script still has some bugs, and I'm working those out as they arise.