Results 1 to 14 of 14
  1. #1
    ABW Ambassador AddHandler's Avatar
    Join Date
    January 19th, 2005
    Posts
    1,270
    Question Logging Search Terms
    Just wondering if anybody knows the answer ..
    I couldn't find this in any documentation and that doesn't give me much hope..
    BUT - here is the problem...

    I have a search box.. and I am trying to get the KEYWORDS used in the searches to be logged to the MySQL Database so I can use them later..

    It works -- ALMOST...
    It will log the keywords BUT only the last Keywords.. in other words it only records the LAST KEYWORDS used.. and overwrites the others every single time..

    Is there a way to get it to ADD the keywords to the database table instead of overwritting everything in that content field...??
    I tried:
    INSERT INTO
    UPDATE

    But that is as far as I got.. I looked in the docs and couldn't find anything about simply ADDING info to a field..?? Any help would be appreciated!
    Thanks In Advance...!

  2. #2

  3. #3
    ABW Ambassador AddHandler's Avatar
    Join Date
    January 19th, 2005
    Posts
    1,270
    Thanks Wayder...
    I dug through that stuff.. but didn't see that..
    there is so much stuff there.. LOL

    Very much appreciated!

  4. #4
    ABW Ambassador AddHandler's Avatar
    Join Date
    January 19th, 2005
    Posts
    1,270
    OK - heres an update on my progress...

    I am trying to log keywords used in my sites mysql search box...

    here is the way the table is set up::
    +--------+-------------+
    | ID | CONTENT |
    | keys | keywords |
    +--------+-------------+

    What I originally wanted to do was LOG the new search terms into "keys" content "keywords" --- I could not get this to work with "UPDATE" - "INSERT INTO" or anything else I could find...

    So I thought I would set up the table like this::
    +--------+-------------+
    | ID | CONTENT |
    | keys | keywords |
    | tmpkey | newkeyword |
    +--------+-------------+

    Then I could log all the " LAST SEARCH TERMS" into "TMPKEY" and then
    JOIN or MERGE the "tmpkey" and the "keys"......

    I am looking at the "JOIN" documentation now...
    I can't seem to get it to JOIN the "tmpkey" into the "keys" field..

    I am just trying to think of a way around the "UPDATE" overwriting everything in the "KEYS" instead of adding to it...


    Is "JOIN" the way to go in order to merge the "KEYWORDS" and the "NEWKEYWORD".. or is there another way that may be better...??



    Thanks for helpin..!

  5. #5
    Animal Lover
    Join Date
    January 18th, 2005
    Location
    oz
    Posts
    1,210
    Addhandler

    Are you using php? I don't understand why it will overwrite the last record...if you use "insert into" it should just insert as a new record altogether...the "update" command is the only one which will 'update' a record that is already in the table...that's how it's always worked for me...

    Unless what you want is something to log the number of times a particular kw phrase is used...kinda like what awstats gives you.

    What are your 'keys'? Is it like a particular kw that is used in all the kw phrases ie

    blue widgets
    red widgets

    and what you are after is

    +--------+-------------+
    | ID | CONTENT |
    | widgets | blue widgets,red widgets |
    +--------+-------------+

    I'm not sure I understand the question...maybe if you supplied us with an example (with eg kws) I can better understand it.

    Oscar

  6. #6
    ABW Ambassador AddHandler's Avatar
    Join Date
    January 19th, 2005
    Posts
    1,270
    Oscar ::
    I am using PHP...

    I want it to ADD the KEYORDS SEARCHED for to the CONTENT Field as in the example below.. but if I use INSERT INTO or UPDATE both have the same results.. it OVER WRITES the last keyword searched for with the new one... so it is only ONE KEYWORD in the CONTENT Column always..
    +--------+-------------+
    | ID | CONTENT |
    | widgets | blue widgets|
    +--------+-------------+

    The reason I want to do it this way is because I already have that CONTENT field rotating on my site.. it will rotate a specified number of the keywords results::
    so if I have this::
    +--------+-------------+
    | ID | CONTENT |
    | widgets | blue widgets,red widgets, green widgets, purple widgets, grey widgest , black widgets |
    +--------+-------------+

    Then the DATA in the CONTENT field is rotated - how ever many listings I want to show.. 5 searches 10 searches or what ever you want.. like this
    ________________
    Popular Searches
    red widgets
    green widgets
    purple widgets
    grey widgest
    black widgets
    ________________

    Example of Code :::
    ------------------------
    $pol = mysql_query("INSERT INTO my_keywords (content) VALUES('$query')");
    ------------------------

    It seems pretty simple -- just take the QUERY and insert into my_keywords in the content field..

    But it overwrites the last keyword searched for everytime...
    Any Ideas..??

  7. #7
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    551
    Hi AddHandler,

    Can you show us the read and display code for 'Popular Searches'. I think that the problem may be a display issue and not an insert issue.

    You will always insert one keyword phrase when you use INSERT if your $QUERY var is one search term, the issue then is to select the last 5 inserted into the table, then loop through the result to display it.

    Are you using

    while(){ }

    Wayder

  8. #8
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    551
    That still wont give you Popular Searches, it will give you the last 5 searches

  9. #9
    Animal Lover
    Join Date
    January 18th, 2005
    Location
    oz
    Posts
    1,210
    Hi AddHandler

    I think if you want to do that, you need to pull the data out of the content field 1st, then add the new search term onto the end of that data and then update the column.

    That's how I would do it.

    so :

    $newkwsearch=$HTTP_POST_VARS['keywordsearch'];

    $sql="SELECT CONTENT from table WHERE ID='widgets'";
    $query=mysql_query($sql);
    $fetch=mysql_fetch_array($query);
    $addnewkw=$fetch[0].",".$newkwsearch;

    $sqlupdate="UPDATE table SET CONTENT='$addnewkw' WHERE ID='widgets'";
    $queryupdate=mysql_query($sqlupdate);

    You may have to tweak the code a bit to suit your needs. I think you'll have to use the explode() function to get your individual searches for your popular searches using the comma as a delimiter.

    I think Wayder beat me to it...I'm not a wiz at coding so it could be pretty clunky the way I've got it...

    Oscar

  10. #10
    ABW Ambassador AddHandler's Avatar
    Join Date
    January 19th, 2005
    Posts
    1,270
    Maybe I am not explaining it correctly...

    The Keywords that are rotated are already set up to display and rotate and that portion works correctly all by itself.. I originally set it up so that I could cut and paste a bunch of keywords into a field and it would rotate those keyword searches and link to my internal search engine just like someone typed the search terms in.. just to give me some extra keywords on my pages..

    The logging of the ""actual"" keywords used by my visitors was an afterthought...
    once I saw the rotating keywords.. I thought - why not log the actual search terms instead of cutting and pasting my own..

    So that table and content fields are totally fine just the way they are...
    BUT getting the new keywords logged to that "content" field is a pain...

    If I cut and paste 100 keyword terms in that content field just to get it started...
    then I do a search.. the new keywords are logged to the content field.. but the other 100 are no longer there....

    I tried logging the new keywords to another temporary table and then move that data into the content field but it still just overwrites the old keywords..

    But looking at the script.. and knowing that everything works fine accept when I try to log the new keywords to the content field... logically all I should have to do is place ::
    $pol = mysql_query("INSERT INTO my_keywords (content) VALUES('$query')");

    in the script and BAM start logging the new terms to the content field..
    BUT the INSERT INTO is overwritting everything else..??

    The code ::
    ------------------------------
    $kws = preg_split("/\s+/", $query);

    while (list(,$kw) = each($kws)) {

    if ($kw) {
    $valid = 1;
    $sqlsrch .= " and (title like '%$kw%' or description like '%$kw%')";
    $pol = mysql_query("INSERT INTO my_keywords (content) VALUES '$query')");

    }

    }

    if (!$valid) die("You must enter at least one keyword.");
    ------------------------------------------------------------


    BUT thinking even further ahead... maybe I don't want the last keywords to be AUTOMATICALLY inserted into the rotating content field... since someone is BOUND to type a bunch of crap in the search terms if they see the last search terms being rotated.. like PORN or EAT MY SHORTS or other crap...
    SO logging them to a temp table that I can sift through and update manually would probably be the best way to keep the crap off my site.. check it once a month and update the keywords list with ACTUAL good search terms..

    what do you think..?

  11. #11
    Animal Lover
    Join Date
    January 18th, 2005
    Location
    oz
    Posts
    1,210
    Hey AddHandler,

    Your code's like way beyond my knowledge of php...so I don't think I can help you any more. It still looks like you're just adding the search keywords straight into the content column and therefore overwriting the ones already there. Update is the usual command to use if there is already an existing column you want to 'update'. You still need to pull out the existing stuff in that column, then add the new stuff to the existing string and then update. That's how I always thought it would work..I could be wrong.

    sorry I can't be of more help,
    Oscar

  12. #12
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    551
    If I read it correctly, it seems to me that you will be entering $query into your database the amount of times that you have words in $query.

    If what you want is to add words to your comma delimited data, then you will need to have the column data then add the $query words then insert or update the database.

    $KWDS = ""; //GET KEYWORDS FROM DATABASE

    $kws = preg_split("/\s+/", $query);

    while (list(,$kw) = each($kws)) {

    if ($kw) {
    $valid = 1;
    $sqlsrch .= " and (title like '%$kw%' or description like '%$kw%')";
    $KWDS .= ", $kw";
    }

    }
    $pol = mysql_query("INSERT INTO my_keywords (content) VALUES ('$KWDS')");

    if (!$valid) die("You must enter at least one keyword.");

    If not, you have well and truly confused me

  13. #13
    ABW Ambassador AddHandler's Avatar
    Join Date
    January 19th, 2005
    Posts
    1,270
    Wayder that is what is happening.. LOL
    Thanks for the fix!

  14. #14
    ABW Ambassador AddHandler's Avatar
    Join Date
    January 19th, 2005
    Posts
    1,270
    Wayder
    Again thanks for the fix..
    the first time I checked it I just used one keyword.. (dumb)
    didn't think of that until I looked in the database and saw that every set of keywords was being repeated..
    Weird thing is it was repeating for as many times as there were keywords..
    example:
    search terms = These are my keywords
    +--------+-------------+
    | ID | KEYWORDS |
    | 1 | These are my keywords|
    | 2| These are my keywords|
    | 3| These are my keywords|
    | 4| These are my keywords|
    +--------+-------------+

    So for every word in the search "phrase"
    it repeated the entire query..??



    Thanks that made me take a little closer look..

  15. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Making the most of search terms
    By ADesertRose in forum Midnight Cafe'
    Replies: 6
    Last Post: November 13th, 2005, 02:51 PM
  2. The Mysterious Search Terms
    By Kevin in forum Midnight Cafe'
    Replies: 5
    Last Post: August 8th, 2005, 09:49 AM
  3. Top search terms at wordtracker...
    By cjshu in forum Midnight Cafe'
    Replies: 11
    Last Post: January 18th, 2004, 06:40 PM
  4. search terms
    By Roland in forum Commission Junction - CJ
    Replies: 3
    Last Post: December 23rd, 2003, 02:55 AM
  5. search terms
    By Roland in forum Midnight Cafe'
    Replies: 5
    Last Post: August 14th, 2003, 10:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •