Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Mar 2009
    Posts
    107
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Loading XML into database with PHP

    Hi,

    I have been working on a script which pulls data from an XML file and inserts it into a database...

    What I currently have is functional however I am having some issues...

    If one of the fields pulled from the XML contains as much as a"£" Pound sign it will break, it dislikes symbols and will not pull formatting, This is making it a little unstable.

    I was wondering if maybe I have done something wrong, or if there is a better approach?

    Very grateful for any advice anybody can provide.


    PHP Code:

    $url 
    "workwithus.xmll";
    $xml simplexml_load_file($url);



    foreach(
    $xml->Job as $row){
    $Title $row->title;
    $Location $row->location;
    $Sector $row->sector;
    $Reference $row->reference;
    $Type $row->worktype;
    $Duration $row->duration;
    $StartDate $row->startdate;
    $Link $row->link;
    $Description $row->description;
    $SalaryFrom $row->salaryfrom;
    $SalaryTo $row->salaryto;
    $SalaryPer $row->salaryper;

    $result mysql_query("INSERT INTO Jobs (Title, Location, Sector, Reference, Type, Duration, StartDate, Link, Description, SalaryFrom, SalaryTo, SalaryPer)
    VALUES ('$Title','$Location','$Sector','$Reference','$Type','$Duration','$StartDate','$Link','$Description','$SalaryFrom','$SalaryTo','$SalaryPer')"
    ); 
    Last edited by IamHe; 03-13-2014 at 02:08 PM. Reason: New version of code

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,448
    Thanks
    23
    Thanked 630 Times in 629 Posts
    This might help. Use htmlspecialchars() on the string before putting it into the DB, and htmlspecialchars_decode() when you take it out.
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #3
    Regular Coder
    Join Date
    Mar 2009
    Posts
    107
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Many Thanks for your help!

    I added this in and it did prevent it from breaking with html tags such as <p> </p> , although it simply removed any content within the tags..

    But it did not fix it with symbols such as "£" - This is still breaking..

    So I tried something more simple to test it (below)

    PHP Code:

    <?php

    $url 
    "workwithus.xmll";
    $xml simplexml_load_file($url);


    foreach(
    $xml->Job as $row){
    $Title htmlspecialchars($row->title);
    $Location htmlspecialchars($row->location);
    $Sector htmlspecialchars($row->sector);
    $Reference htmlspecialchars($row->reference);
    $Type htmlspecialchars($row->worktype);
    $Duration htmlspecialchars($row->duration);
    $StartDate htmlspecialchars($row->startdate);
    $Link htmlspecialchars($row->link);
    $Description htmlspecialchars($row->description);
    $SalaryFrom htmlspecialchars($row->salaryfrom);
    $SalaryTo htmlspecialchars($row->salaryto);
    $SalaryPer htmlspecialchars($row->salaryper);

    print 
    "<p>".$Title."</p>";
    print 
    "<p>".$Location."</p>";
    print 
    "<p>".$Sector."</p>";
    print 
    "<p>".$Reference."</p>";
    print 
    "<p>".$Type."</p>";
    print 
    "<p>".$Duration."</p>";
    print 
    "<p>".$StartDate."</p>";
    print 
    "<p>".$Link."</p>";
    print 
    "<p>".$Description."</p>";
    print 
    "<p>".$SalaryFrom."</p>";
    print 
    "<p>".$SalaryTo."</p>";
    print 
    "<p>".$SalaryPer."</p>";

    }
    ?>
    If there is a £ symbol in one of the rows pulled then nothing is printed... not really sure why that is, Should this still print?

    Thanks again for your help!

  • #4
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,448
    Thanks
    23
    Thanked 630 Times in 629 Posts
    Sorry, thought the pound sign would be html code but it looks like it's not. So we have to change it before encoding. In what I'm giving you below notice that <div><p> are block characters and they print as such (placing two line breaks between the words I use to id the variables and the variables them self, but after htmlspecialchars() they appear in line.
    Anyway the string replacement should help, just remember to do it to all strings that you take out of the db.
    PHP Code:
    <?php
    $str 
    "<div><p>£ Hello</p></div>";
    $str str_replace"£""pound"$str );
    echo 
    "string is $str<br>";
    echo 
    "---------------<br>";

    $Title htmlspecialchars($str);
    echo 
    "title is $Title<br>";
    $next htmlspecialchars_decode($Title). "<br>";
    echo 
    "next is ".$next "<br>";
    ?>
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #5
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,107
    Thanks
    2
    Thanked 326 Times in 318 Posts
    You didn't exactly state what is breaking? Is it the display of the data when you retrieve it from the database? Is the query failing due to an error and the data is not even being inserting into the database? Are you not able to search the database to find those characters?
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    This is my curiosity as well. Although I've seen great number of problems with &pound; in XML strings in the past, there's not enough information here to know where the issue is.
    Does it read the &pound; properly and simply not save it in the db, or is it no good upon the fetch of the char?
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #7
    Regular Coder
    Join Date
    Mar 2009
    Posts
    107
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Hey Guys,

    Thanks allot for all of the suggestions, Trying these out now!

    When I say its breaking...

    Basically, NOTHING is loaded into the database if there is a £ sign in there somewhere.. So I then removed the code for adding it to the database and printed the results as a test... when there is a £ sign in there NOTHING is printed... Is this breaking my PHP or is the feed broken somehow?

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Sounds like the feed is broken. Try reading it using a standard file or socket read instead of the simplexml. Does that provide the string for the feed?
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #9
    Regular Coder
    Join Date
    Mar 2009
    Posts
    107
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Sounds like the feed is broken. Try reading it using a standard file or socket read instead of the simplexml. Does that provide the string for the feed?
    Sorry , I'm not exactly sure what you mean by this?

    I have purposely added in a "£" which has caused it to stop working... And then viewed the xml file directly which still displays...

    I have then tried

    PHP Code:
    $file file_get_contents('file.xml');
    echo 
    $file
    which actually DOES WORK, but I don't believe I can use this approach to add the data into the database?

  • #10
    Regular Coder
    Join Date
    Mar 2009
    Posts
    107
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I have done some further investigating..

    And the following are the characters that are breaking my script:

    £
    >
    <
    &

  • #11
    Regular Coder
    Join Date
    Sep 2002
    Posts
    462
    Thanks
    0
    Thanked 20 Times in 20 Posts
    try the html number &amp;#163; instead of the name.
    That bytes, when I entered the number it showed correctly in the preview but as the symbol when initially posted.
    NO Limits!! DHCreationStation.com
    ------------------------------------------------------------
    For projects using MediaTypes (MIMETypes) visit E-BAM.net -(updated weekly)

    Broken items wanted for tinkerin'! PostItNow@BrokenEquipment.com
    Global Complaint Dept.


  •  

    Posting Permissions

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