SitePoint Sponsor

User Tag List

Page 2 of 4 FirstFirst 1234 LastLast
Results 26 to 50 of 92
  1. #26
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,435
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    Without that return $db->query($sql); it does not delete??
    How are you calling the function from your main loop? And does it still work if you
    PHP Code:
    return TRUE
    ?

  2. #27
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    return TRUE
    I had just tried that successfully when you posted. All working now so I will load it up and try it live. Thanks.

  3. #28
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well it works just fine locally but as soon as it goes up on the server with a real URL for the feed this is the result:

    [10-Jul-2013 00:32:53] PHP Warning: XMLReader:pen(http://...) [<a href='xmlreader.open'>xmlreader.open</a>]: failed to open stream: HTTP request failed! HTTP/1.1 500 Internal Server Error in /home/solvilla/public_html/admin/feed_scripts/sales/insert_feeds.php on line 14
    [10-Jul-2013 00:32:53] PHP Warning: XMLReader:pen() [<a href='xmlreader.open'>xmlreader.open</a>]: Unable to open source data in /feed_scripts/sales/insert_feeds.php on line 14
    [10-Jul-2013 00:32:53] PHP Warning: XMLReader::read() [<a href='xmlreader.read'>xmlreader.read</a>]: Load Data before trying to read in /feed_scripts/sales/insert_feeds.php on line 17

    AARGH!!!

  4. #29
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,435
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Morning Colin, ready for round 2?

    This sounds like a problem with the path to your XML file. It might even be something as simple as the path still being set for your localhost test environment - I've done that many times before myself!

  5. #30
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good morning.

    I have spent the past couple of hours running a few tests.

    I have loaded an XML file containing 54 properties onto a server and then run the script from my local machine to see what errors I get. NADA! the script runs fine and with an additional piece of code added I can say that it loads the page in 5.2 seconds.

    Rerun it with less properties and the result is virtually the same (5.3 seconds)

    Now to my mind this has got to be a problem with the speed at which the feed is capable of being served up on the proper url.

    I know this may not be the most scientific of tests but I welcome your opinion?

    Colin

  6. #31
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,435
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    If you're loading the XML file remotely then that will be the biggest bottleneck in the whole script. Does your live version work in the same way, by pulling in the XML from another server?

  7. #32
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK just tried to load 100 properties from proper url and it run OK but took 22.9 seconds!!

    Done the same locally 9 seconds??

  8. #33
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    If you're loading the XML file remotely then that will be the biggest bottleneck in the whole script. Does your live version work in the same way, by pulling in the XML from another server?
    Yes unfortunately

    Is the 500 Internal Server Error generated on the feed end or on the server that the script is running on?

    I am loading the feed remotely on both of my tests but from different sources.

  9. #34
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,435
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Ah, I see. So the problem is that your old script used to load the entire XML file into memory to process, but this new method using XMLReader is loading the file piece by piece which is going to introduce even more latency as each chunk is loaded over the net. Also, if the connection to the remote machine is broken mid-loop, your script is going to throw it's feet up.

    The solution here is to copy the remote file to a temporary location on your server to process it. This should be much faster to work with, and will avoid the problem mentioned above.

  10. #35
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is about the gist of it. I thought going down the XML Reader route would actually improve things, not make it worse!

    Using the old script it was very unreliable due to the amount of data being returned and stored into memory before running the script. So it looks like it is back to the start with this, are you up for it?

  11. #36
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,435
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    That is about the gist of it. I thought going down the XML Reader route would actually improve things, not make it worse!

    Using the old script it was very unreliable due to the amount of data being returned and stored into memory before running the script. So it looks like it is back to the start with this, are you up for it?
    Don't give up just yet, try something like this:
    PHP Code:
    $url  'http://www.example.com/properties.xml';
    $path 'xml/properties.xml';

    $fp fopen($path'w');

    $ch curl_init($url);
    curl_setopt($chCURLOPT_FILE$fp);

    $data curl_exec($ch);

    curl_close($ch);
    fclose($fp); 
    which will use curl to download the remote file directly to the server for processing.
    You can then run your processing on the local copy, and it should be much faster.

  12. #37
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Warning: fopen(xml-reader/properties.xml) [function.fopen]: failed to open stream: No such file or directory in C:\Websites\xml-reader\untitled.php on line 6 (There is I created it manually. Windoze perms issue??)

    Warning: curl_setopt(): supplied argument is not a valid File-Handle resource in C:\Websites\xml-reader\untitled.php on line 9

    Warning: fclose() expects parameter 1 to be resource, boolean given in C:\Websites\xml-reader\untitled.php on line 14

  13. #38
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,435
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    Warning: fopen(xml-reader/properties.xml) [function.fopen]: failed to open stream: No such file or directory in C:\Websites\xml-reader\untitled.php on line 6 (There is I created it manually. Windoze perms issue??)

    Warning: curl_setopt(): supplied argument is not a valid File-Handle resource in C:\Websites\xml-reader\untitled.php on line 9

    Warning: fclose() expects parameter 1 to be resource, boolean given in C:\Websites\xml-reader\untitled.php on line 14
    If your PHP code is running from C:\Websites\xml-reader\untitled.php, then the path you want to supply to fopen should just be properties.xml, otherwise it's trying to open C:\Websites\xml-reader\xml-reader\properties.xml

  14. #39
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I now have the feed downloaded into a file

  15. #40
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So should I revert to simpleXML now LOL

  16. #41
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,435
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    So should I revert to simpleXML now LOL
    No, not at all, because the old method still has to load the entire file into memory to work with it. We've eliminated the network transfer bottleneck by caching the xml file locally, and regardless of whether the XMLReader method itself is actually any faster, it's more efficient in terms of memory. So an improvement all round I'd say

  17. #42
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much for your help fretburner. I will try this out later for real and see how it goes, hopefully all will be good

    Thanks again

    Colin

  18. #43
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Everything works so well locally, thats the good news. When I try to run this on the server success is sporadic.

    I get errors about ON DUPLICATE KEY for all categories. When I look at the downloaded file the XML is incomplete so of course the script throws a wobbly.


    </category><category id="13">
    <name>
    <uk>Utilities</uk>
    </name

    At a loss!!

  19. #44
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Added this to CURL:

    PHP Code:
    set_time_limit(0);
    $fp fopen($path'w');//This is where we save the data
    $ch curl_init($url);//Here is the file we are downloading
    curl_setopt($chCURLOPT_TIMEOUT50);
    curl_setopt($chCURLOPT_FILE$fp); // write curl response to file
    curl_setopt($chCURLOPT_FOLLOWLOCATIONtrue);
    $data curl_exec($ch); // get curl response
    curl_close($ch);
    fclose($fp); 
    File seems to be downloading entirely now.


    I have just downloaded and validated the generated XML file so it would seem that my ON DUPLICATE KEY is incorrect??

  20. #45
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,435
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    It's difficult to imagine why you'd get that error on your production server but not in your test setup. Do you have a more detailed error message that you could share?

  21. #46
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK may have found the problem here. Its the way the loops are being executed. The resulting sql is:

    PHP Code:

    INSERT INTO feed_images 
    (`propertyref`, `url`, `prime`, `pid`) VALUES ('2208 ''http://Image.asp?''0''2208-1') , ('2208 ''http://Image.asp''0''2208-2') , ('2208 ''http://Image.asp''0''2208-3') , ('2208 ''http://Image.asp''0''2208-4') , ('2208 ''http://Image.asp''0''2208-5') , ('2208 ''http://Image.asp?''0''2208-6') , ('2208 ''http://Image.asp''0''2208-7') , ('2208 ''http://Image.asp?''0''2208-8') , ('2208 ''http://image.asp?''0''2208-9') , ('2208 ''http://Image.asp''0''2208-10'ON DUPLICATE KEY UPDATE url VALUES (url), prime VALUES (prime)INSERT INTO feed_images (`propertyref`, `url`, `prime`, `pid`) VALUES ('2223 ''http://Image.asp''0''2223-1') , ('2223 ''http://Image.asp?''0''2223-2') , ('2223 ''http://Image.asp''0''2223-3') , ('2223 ''http://Image.asp''0''2223-4') , ('2223 ''http://Image.asp''0''2223-5'ON DUPLICATE KEY UPDATE url VALUES (url), prime VALUES (prime
    It is not just looping the values but the insert and the update on duplicate queries

  22. #47
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I try to load a downloaded file on to my test setup I also get the same error. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE category = VALUES (category), value = VALUES (value)'

    See above

  23. #48
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,435
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Can you re-post the code for the function that processes the categories?

  24. #49
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    //ADD CHARACTERISTICS

    function add_characteristics($db$property){
        
    $sql "INSERT INTO feed_characteristics (`propertyref`, `category`, `value`, `uid` ) VALUES ";

        
    $propertyid = (string)$property->id;
        
    $number 1;
        foreach(
    $property->characteristics->category as $category):
        
            
    $cat = (string)$category->name->uk;
            
    $sqlxml "('".$propertyid."','".$cat."',";

            foreach(
    $category->value as $value):
            
                
    $uid "$propertyid-$number"
                
    $val = (string)$value->uk;
                
                
    $sql .= $sqlxml."'".$val."','".$uid."') , ";
                
    $number++;
            endforeach;
        endforeach;
        
        
    $sql rtrim($sql' , ') .  ' ON DUPLICATE KEY UPDATE category = VALUES (category), value = VALUES (value)';
      
        return 
    $db->query($sql);


  25. #50
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,435
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    You've got a small bug here at the end of the function:
    PHP Code:
    endforeach;
        
    $sql rtrim($sql' , ') .  ' ON DUPLICATE KEY UPDATE category = VALUES (category), value = VALUES (value)';
      
    return 
    $db->query($sql); 
    it should be $sql .= rtrtim, otherwise you're overwriting the variable rather than appending to it.


Tags for this Thread

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
  •