SitePoint Sponsor

User Tag List

Page 3 of 4 FirstFirst 1234 LastLast
Results 51 to 75 of 92
  1. #51
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fatal error: Call to undefined function rtrtim() in C:\Websites\xml-reader\functions\functions.php on line 37

  2. #52
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    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
    Did you see this I posted at the same time as you so you may have missed it

  3. #53
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,256
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    Fatal error: Call to undefined function rtrtim() in C:\Websites\xml-reader\functions\functions.php on line 37
    Argh, typo again.. you had the name of the function right in your original code, my point was about the .= instead of just =

  4. #54
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $sql rtrim($sql','
    OK

    PHP Code:
    $sql .= rtrim($sql','
    Error

  5. #55
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think this is how the query should look:

    Code SQL:
    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'), ('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)


    Not like this:
    Code SQL:
    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)

    Agreed?

  6. #56
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,256
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    PHP Code:
    $sql rtrim($sql','
    OK

    PHP Code:
    $sql .= rtrim($sql','
    Error
    I think there may be a misunderstanding somewhere. Your function should now look like this:
    PHP Code:
    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);

    Quote Originally Posted by ColinHughes View Post
    I think this is how the query should look:

    Code SQL:
    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'), ('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)


    Not like this:
    Code SQL:
    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)

    Agreed?
    That would make sense.

  7. #57
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But with the functions enclosed in the
    PHP Code:
    while($reader->read()): 
    loop the queries will always be wrong. Hmmmm!

  8. #58
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,256
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    But with the functions enclosed in the
    PHP Code:
    while($reader->read()): 
    loop the queries will always be wrong. Hmmmm!
    I'm not sure I understand. Surely what you're doing is looping through each property in the xml file, and for each you are doing a handful of queries to insert the relevant details and images into the DB? And besides which, wasn't this all working OK on your local machine?

  9. #59
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It was working locally until I increased the amount of properies contained within the XML file. Yes you are of course correct with what I am doing, I think I have been at this too long and I am confusing even myself.

    On my local machine I have an XML file containing just 40 properties and it parses this without issue. I also have another XML file containing 500 properties and it chokes on this giving: There was an error adding characteristics [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)' at line 1] The properties are being inserted correctly but not the images or characteristics??


    I really appreciate your patience with this.

  10. #60
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,256
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    On my local machine I have an XML file containing just 40 properties and it parses this without issue. I also have another XML file containing 500 properties and it chokes on this
    Thinking about this logically, if the 1st set of 40 records works fine, there must something specific about one or more of the records in the second test set that are causing the script to choke. If you run the script several times, does it always throw an error while processing the same particular property? And if so, is there something different about it from the ones that come before?

  11. #61
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I have found the problem. One of the properties has no characteristics just a closing tag <characteristics/> It would seem that it is not required to have this information in the feed contents.

  12. #62
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I now need to find a way to skip over any tags that do not contain data. Oh happy days, dont you just love working with something you have very little control over.

  13. #63
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,256
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)
    From the docs, it looks like you can use the isEmptyElement property to check for empty XML elements. Here's a section from the add_characteristics function with the check added in:
    PHP Code:
    foreach($property->characteristics->category as $category): 
        if(!
    $category->isEmptyElement): // Added check for empty element
            
    $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; 
        endif;  
    // Close of new if statement
    endforeach; 

  14. #64
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good day!!

    I did actually go down this route but it does not work because the element is not empty is just not there. The feed does not have an opening tag, just a closing tag, for any empty elements.

    Lousy design but it is what I have to work with..

  15. #65
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,256
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)
    Hi Colin, how's it going?

    I've just realised that we can't use the isEmptyElement check because we're not working with XMLReader at that point - $property is a SimpleXML object. I've just done a quick test on my machine, and this should work:

    PHP Code:
    foreach($property->characteristics->category as $category):  
        if(
    $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;  
        endif;
    endforeach; 

  16. #66
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi It is not going so well.

    Yep tried that this morning, no good. I have tried isset also, still no good! I have just noticed that the feed does not contain an endtag it has <characteristics/> not </characteristics>

  17. #67
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,256
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    Hi It is not going so well.

    Yep tried that this morning, no good. I have tried isset also, still no good! I have just noticed that the feed does not contain an endtag it has <characteristics/> not </characteristics>
    Sorry bud, my fault again.. I misread what you said earlier and thought we were dealing with empty <category/> tags. So, try this instead:
    PHP Code:
    if ($property->characteristics):
        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;
    endif; 

  18. #68
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I take out the line ON Duplicate etc I get the following: There was an error adding characteristics [Duplicate entry '2208-1' for key 'uid']

  19. #69
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,256
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)
    Are you replacing the entire function with just the code I posted? I was just showing the relevant section of the code.. it still needs all the rest of the code:
    PHP Code:
    function add_characteristics($db$property){ 
        if (
    $property->characteristics):
            
    $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); 
        endif;  
         
        return 
    TRUE;

    Note that I've moved the position of the DB query here, as there's no point executing the query with no values.

  20. #70
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Error

    There was an error adding characteristics [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 'INSERT INTO feed_characteristics (`propertyref`, `category`, `value`, `uid` ) VA' at line 1]

  21. #71
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It still chokes on that property without any characteristics I cannot understand why?

  22. #72
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,256
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by ColinHughes View Post
    It still chokes on that property without any characteristics I cannot understand why?
    I'm unable to replicate this on my machine - I have a very simple test set up as follows:
    PHP Code:
    $xml "
    <properties>
        <property>
            <characteristics>
                <category><name>category1</name></category>
                <category><name>category2</name></category>
            </characteristics>
        </property>
        <property>
            <characteristics/>
        </property>
        <property>
            <characteristics>
                <category><name>category5</name></category>
                <category><name>category6</name></category>
            </characteristics>
        </property>
    </properties>"
    ;

    $properties = new SimpleXMLElement($xml);

    // This represents the main loop
    foreach($properties as $property)
    {
        
    // This represents the add_characteristics function
        
    if ($property->characteristics) {
            foreach (
    $property->characteristics->category as $category)
            {
                echo 
    $category->name "<br>";
            }
        }

    The empty <characteristics/> element doesn't give me any trouble at all here. Are you able to share the entire XML section for the property that causes the problem?

  23. #73
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code XML:
    <?xml version="1.0" encoding="UTF-8"?>
    <root>
    <property>
          <id>39279</id>
          <status_date>2012-07-24 13:22:05</status_date>
          <status>Available</status>
          <listed_date>2007-10-20 13:25:10</listed_date>
          <last_updated>2012-07-24 13:22:05</last_updated>
          <ref>1027</ref>
          <price>595000</price>
          <currency>EUR</currency>
          <type>
            <uk>Townhouse</uk>
            <es>Unifamiliar</es>
          </type>
          <subtype>
            <uk>Terraced</uk>
            <es>Adosada</es>
          </subtype>
          <country>Spain</country>
          <province>Málaga</province>
          <town>Marbella</town>
          <area>
          Costa del Sol
          </area>
          <beds>3</beds>
          <baths>2</baths>
          <levels>4</levels>
          <surface_area>
            <built>167</built>
            <terrace>110</terrace>
            <plot>0</plot>
          </surface_area>
          <energy_rating>
            <value>0</value>
            <letter/>
          </energy_rating>
          <own_property>0</own_property>
          <has_pool>1</has_pool>
          <has_garden>1</has_garden>
          <has_garage>1</has_garage>
     
    <!-- THIS PROPERTY HAS NO CHARACTERISTICS -->
          <characteristics/>
     
          <description>
            <uk>Oasis de Nagüeles. </uk>
            <es>Oasis de Nagüeles.</es>
          </description>
          <images>
            <image id="1">
              <url>http://Image.asp</url>
            </image>
          </images>
        </property>
     
    </root>

    Abridged version without characteristics

  24. #74
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code XML:
    <characteristics>
     
          <category id="1">
            <name>
              <uk>Setting</uk>
              <es>Posición</es>
            </name>
            <value id="1">
              <uk>Close To Sweet Shop</uk>
              <es>Cerca de Golf</es>
            </value>
          </category>
     
          <category id="2">
            <name>
              <uk>Orientation</uk>
              <es>Orientación</es>
            </name>
            <value id="1">
              <uk>South West</uk>
              <es>Suroeste</es>
            </value>
          </category>
     
    </characteristics>

    Characteristics

  25. #75
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,256
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)
    Thanks for sharing that Colin, it made it a lot easier to figure out what's going on. It seems that using an empty() check works, so try this:
    PHP Code:
    function add_characteristics($db$property){  
        if ( !empty(
    $property->characteristics) ): 
            
    $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);  
        endif;   
          
        return 
    TRUE



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
  •