Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

Thread: listing_browse.php TEMP table fix for busy sites. UPDATED!

  1. #1
    the_sandking's Avatar
    the_sandking is offline hadron remnant - Moderation Fachmann
    Join Date
    Apr 2003
    Location
    Nullspace
    Posts
    5,289

    Default

    This is a simple and TEMPORARY fix for busy OR sites that generate SQL errors complaining about not being able to create indexes and etc..

    This is due to the original OR v1.14 code sharing 1 temporary table for generating the display for each page of 'listing_browse.php'

    The included file is a drop-in replacement that creates temporary tables for each site visitor based on their unique session_id(). If you have already made modifications to your version of 'listing_browse.php' and wish to retain those changes, you will need to make those modifications again for this version.


    This type of "fix" for 'listing_browse.php' is already being developed for v2.0 so this "upgrade" will only be needed until then.

    NEW *** 6/26/04 **** See additional code sample posted in another message below...

    NEW *** 7/16/05 **** Updated version of this file available HERE
    Last edited by the_sandking; 07-19-2005 at 10:17 AM. Reason: Updated version
    "Much of what looks like rudeness in hacker circles is not intended to give offense. Rather, it's the product of the direct, cut-through-the-BS communications style that is natural to people who are more concerned about solving problems than making others feel warm and fuzzy."

    "We gotta' go to the crappy town where I'm a hero!"
    -Hoban 'Wash' Washburne 2485-2519


    "When you’re born you get a ticket to the freak show. When you’re born in America, you get a front-row seat.."
    -George Carlin 1937-2008

    New to Open-Realty® and need help? Check the:
    -OR DOCUMENTATION -

    Important: Read this at least once in your lifetime
    How To Ask Questions The Smart Way

  2. #2
    Join Date
    Sep 2003
    Location
    London UK
    Posts
    1,654

    Default

    Hi Sand king

    I wonderd why I kept getting errors emailed to me!!!!

    Even though the program was working fine, I just put it down to a glich..

    I will try this on one of my heavy sites that lots of hits this week, see if this
    helps.


    Big thank you for the script Sandking
    Albert

  3. #3
    the_sandking's Avatar
    the_sandking is offline hadron remnant - Moderation Fachmann
    Join Date
    Apr 2003
    Location
    Nullspace
    Posts
    5,289

    Default

    Quote Originally Posted by awddesign
    Hi Sand king

    I wonderd why I kept getting errors emailed to me!!!!

    Even though the program was working fine, I just put it down to a glich..

    I will try this on one of my heavy sites that lots of hits this week, see if this
    helps.


    Big thank you for the script Sandking
    Albert
    No problem.. I've been sitting on this for awhile.. It's actually code borrowed from v2.0 and adjusted to use with v1.14.. v2.0 should include several small "clean-up" features like this that eliminate some of the deficiencies in the current version(s)..
    "Much of what looks like rudeness in hacker circles is not intended to give offense. Rather, it's the product of the direct, cut-through-the-BS communications style that is natural to people who are more concerned about solving problems than making others feel warm and fuzzy."

    "We gotta' go to the crappy town where I'm a hero!"
    -Hoban 'Wash' Washburne 2485-2519


    "When you’re born you get a ticket to the freak show. When you’re born in America, you get a front-row seat.."
    -George Carlin 1937-2008

    New to Open-Realty® and need help? Check the:
    -OR DOCUMENTATION -

    Important: Read this at least once in your lifetime
    How To Ask Questions The Smart Way

  4. #4
    Join Date
    Dec 2003
    Location
    Wilson NC
    Posts
    21

    Default

    Thank you fro this addition as I am getting errors on my new site and need to install this fix! You folks are the best!

  5. #5
    Join Date
    Mar 2004
    Location
    netherlands
    Posts
    1

    Default

    Hello SandKing,

    I have been thinking about the temp table problem some time ago,
    i came up with a different sollution, elliminating the need of a temp
    table at all.
    I'm am not using OR now, but if you are intereted I can mail the parts of code. It may be useful for the 2.0 release.

    If you are interested pl. let me know.

    Matthijs

  6. #6
    Join Date
    Aug 2003
    Location
    S.W. Wisconsin
    Age
    63
    Posts
    121

    Default

    the new listing_browse file ends on line 660, the old one ends on line 615, is this the way it is, or do i have changes that i forgot where made?..al
    albert ,.. baker, budding realty broker,Lic.Auctioneer, learning to edit video ...

  7. #7
    Join Date
    Aug 2003
    Location
    S.W. Wisconsin
    Age
    63
    Posts
    121

    Default

    well for others that have the same question as i did above... and concerning the 660 line size of the listing_browse verses the 615 line size of the old... i just uploaded the new,.. making no changes and so far it is fine... but often we do not see a "glitch" until it glitches<grin>.

    I apparantly had made no changes to the old file, now i wonder what changes do some of you make to your listing browse files and why?

    albert
    albert ,.. baker, budding realty broker,Lic.Auctioneer, learning to edit video ...

  8. #8
    the_sandking's Avatar
    the_sandking is offline hadron remnant - Moderation Fachmann
    Join Date
    Apr 2003
    Location
    Nullspace
    Posts
    5,289

    Default

    Quote Originally Posted by albert zegiel

    I apparantly had made no changes to the old file, now i wonder what changes do some of you make to your listing browse files and why?

    albert
    The changes I was referring to are cosmetic changes. It is not unusual for someone to want the output of 'listing_browste.php' to look different, and thus make their own changes to the HTML portions of that file.
    "Much of what looks like rudeness in hacker circles is not intended to give offense. Rather, it's the product of the direct, cut-through-the-BS communications style that is natural to people who are more concerned about solving problems than making others feel warm and fuzzy."

    "We gotta' go to the crappy town where I'm a hero!"
    -Hoban 'Wash' Washburne 2485-2519


    "When you’re born you get a ticket to the freak show. When you’re born in America, you get a front-row seat.."
    -George Carlin 1937-2008

    New to Open-Realty® and need help? Check the:
    -OR DOCUMENTATION -

    Important: Read this at least once in your lifetime
    How To Ask Questions The Smart Way

  9. #9
    the_sandking's Avatar
    the_sandking is offline hadron remnant - Moderation Fachmann
    Join Date
    Apr 2003
    Location
    Nullspace
    Posts
    5,289

    Default

    This is an advanced update to the TEMP table fix, I don't really have time to answer questions regarding this update, but another user here asked me for it, and was successful in using it. I have posted it here, for those who cannot wait for v2.0, (which eliminates the need for this fix) or those folks who have made extensive changes to the original v1.1X branch of OR, and need to make what you have modifed keep running smoothly.

    If you are using my TEMP table fix, you may notice that occasionally a few of the TEMP tables will remain, because the fix that I posted assumes that users are patient, and they always let 'listing_browse.php' finish loading before clicking on something else.. Since the part of the fix that deletes the TEMP tables occurs physically at the end of 'listing_browse.php', if a user does not let the page completely load, a few "orphans" may be left behind, and can eventually pile-up over time..

    The solution:

    You can toggle the following function from a CRON script (UNIX hosting) every 24 hours to check for, and purge those orphans.. You can also easily code a simple php page that contains a link to call this function from your web browser. Actually, there are probably a bunch of ways you could trigger this function, use your imagination.

    Note: you will need to be using the listing_browse.php replacement file found HERE. The current way the TEMP tables are named is not easily matched via a simple search and the listing_browse MOD takes care of that..

    There are some more deatailed instructions regarding how to use this function in conjunction with the listing_browse MOD HERE.

    Temp table cleanup function:

    Code:
    function clear_temp () {
    
       // checks for tables that begin with variable $tmp_table_prefix then deletes them
       global $conn, $config;
    
       // set temp_table_prefix (adjust as necessary)
       $tmp_table_prefix = $config['table_prefix']. 'temp_';
    
       $sql = "SHOW TABLES LIKE '$tmp_table_prefix%'";
       $recordSet = $conn->Execute($sql);
    
       if (!$recordSet) echo($sql);
    
       $num = $recordSet->RecordCount();
    
       // create query string to delete all tables that begin with $tmp_table_prefix
       $sqlD = "DROP TABLE IF EXISTS ";
    
       for ($loop=0; $loop < $num;)
       {
          $loop++;
          $sqlD .= $recordSet->fields[0];
          
          if (($num - $loop) > 0) $sqlD .= ", ";
          $recordSet->MoveNext();
       }
    
       //echo $sqlD;
    
       // Nuke those tables
       $recordSetD = $conn->Execute($sqlD);
    
       if (!$recordSetD) echo($sqlD) .' -- none exist';
    
       else echo '<br>Finished';
       
    } //end function clear_temp ()

    Backup your original file(s) before trying this!!!!!!!!!!

    The same -tsk- license restrictions apply to this function, (if you choose to use it) that apply to the original TEMP table fix... This code is free for all public and private usage that is not associated or affiliated with *ANY* political endeavors..

    enjoy!
    Last edited by the_sandking; 07-19-2005 at 10:24 AM. Reason: Updated info & links
    "Much of what looks like rudeness in hacker circles is not intended to give offense. Rather, it's the product of the direct, cut-through-the-BS communications style that is natural to people who are more concerned about solving problems than making others feel warm and fuzzy."

    "We gotta' go to the crappy town where I'm a hero!"
    -Hoban 'Wash' Washburne 2485-2519


    "When you’re born you get a ticket to the freak show. When you’re born in America, you get a front-row seat.."
    -George Carlin 1937-2008

    New to Open-Realty® and need help? Check the:
    -OR DOCUMENTATION -

    Important: Read this at least once in your lifetime
    How To Ask Questions The Smart Way

  10. #10
    Join Date
    Jan 2004
    Location
    Long Island New York, USA
    Age
    66
    Posts
    12

    Default

    I have used this fix and it works great. The only place I had a problem when I modified the code was on line 99
    Code:
    $sql = "SELECT " . $config&#91;table_prefix&#93; ."tmp_".$SESSION_ID. ".ID, COUNT&#40;" . $config&#91;table_prefix&#93; . "listingsImages.file_name&#41; AS imageCount FROM " . $config&#91;table_prefix&#93; . "listingsImages," . $config&#91;table_prefix&#93; ."tmp_".$SESSION_ID. " WHERE &#40;" . $config&#91;table_prefix&#93; . "listingsImages.listing_id = " . $config&#91;table_prefix&#93; ."tmp_".$SESSION_ID. ".ID&#41; GROUP BY " . $config&#91;table_prefix&#93; . "listingsImages.listing_id";
    You need to make sure that you include the . with ".ID, COUNT and also with ".ID) GROUP BY "
    If you do not include the . you will get a SQL error when the "Show only listing with images" option is checked.
    Thanks for the fix.
    Allan
    Allan Shott

Similar Threads

  1. LatestListings - ? modify to show all (expired or not)
    By deeshaw in forum HELP (CLOSED)
    Replies: 4
    Last Post: 05-16-2004, 11:33 AM
  2. template help please
    By pmaonline in forum Templates for v 1.x (CLOSED)
    Replies: 2
    Last Post: 05-03-2004, 10:56 PM
  3. Upgrading Question
    By Anonymous in forum HELP (CLOSED)
    Replies: 8
    Last Post: 01-05-2004, 05:06 PM
  4. adding search
    By Anonymous in forum Bugs (CLOSED)
    Replies: 8
    Last Post: 06-25-2003, 12:16 PM

Bookmarks

Posting Permissions

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