How to insert XML data within a MYSQL database?

Asked

Viewed 2,243 times

-5

Generating the file XML with PHP get the following result:

inserir a descrição da imagem aqui

I’d like to send this now to a database MYSQL by inserting the columns in the table first Imovel and then filling these columns with the data being pulled inside each field.

Could someone help me make a script to insert data from XML within the database MYSQL?

php html mysql xml dump
  • 3

    The question is this, or do you want to know how to extract data from an xml? Perhaps it is better to edit the question and solve one problem at a time to increase the possibility of good answers. I also suggest a read here on how to get XML data in PHP: http://answall.com/questions/15451

  • The question is this: How to take all this first-level data and build one sql to put inside the database? The data I already have, are even dynamic mounted through a query PHP. The result of this query is here: http://axitech.com.br/vista/xml2.xml with 175 fields per record.

  • So you have already managed to extract the XML data by PHP, only need to insert in Mysql?

  • Actually I’m driving around because I asked questions that nobody answers. I managed the xml through a consultation on php. So I have this data in xml http://axitech.com.br/vista/xml1.xml and in array form in php http://axitech.com.br/vista/index.php ... I need to know what I’m going to use to build this data into my database.

  • 3

    "Actually I’m going around because I asked questions that nobody answers."That’s exactly what I’m trying to make you understand. The problem is in the questions, because you in several of them are mixing a lot of different problems. First of all, I would suggest: 1) identify each problem separately 2) try to solve one step at a time 3) ask each step individually if you can’t find the solution to the problem. We’re all here to help, but first you have to settle down. Suggested reading, to make the most of the site: http://answall.com/help/asking

  • I’m not creating XML by hand, look at a piece of code: http://pastebin.com/bhTNpZt2. ... has 960 records with 175 fields each record. So this is where it generates the xml. For now I looped the first record so as not to bother to ask the questions. XML pulling instructions from a web-service.

  • 2

    I’ve read your questions, but you really need to understand how the site works. The way they are, just someone doing the code for you, and that’s not the way it works. Try to understand what I commented earlier, which is the way for you to improve the questions and understanding of each step, and at the same time greatly increase your chance of getting a solution to each of the problems. The amount of questions is not a problem here, the objectivity of each one yes. In your head there is a vision of the problem, for those who are reading another, so it takes clarity when asking.

  • 2

    I agree with everything @Bacco said. Golden Rule: Imagine you’re trying to answer the question" . . . . Can read in English? Writing the Perfect Question is the definitive tutorial.

  • Guys, what you don’t know exactly is that I’m a beginner, so I need some direction. I’ve created several things but don’t know the logic to use them. I don’t know if I use them xml, if use php ...

  • 3

    You are beginner in software development or organizing ideas?

  • @Marcosviniciusnasc.Pereira, do you have a column for each field? I read your question yesterday but you removed it at the time you were answering... Your XML could be <codigo>CL501</codigo><data>2012-03-16</data>... Why don’t you take the CDATA?

  • @Papacharlie O CDATA is because I’m having problems with special characters, so I put the CDATA as I have extensive fields of descricao text-based.

  • If I’m not mistaken the problem was with empty values like the 'Immovable Conservation' field. CDATA will not convert accentuation.

  • @Papacharlie help me with this? http://answall.com/questions/31736/como-realizar-as-consultas-via-web-service

  • @Marcosviniciusnasc.Pereira, I answered the question, tell me if there was an error.

Show 10 more comments

1 answer

1


I recommend you tidy up this XML for a more practical way and avoid repeating nodes. Note that even your CDATA contains undue spaces, to remove them use the function TRIM Maybe the best option is XML for your case is:

<CODIGO>CL501</CODIGO>
<DATA>2012-03-16</DATA>

With the XML you have passed, the best you can do is as below:

XML

$string = '<Imovel>
  <field name="CODIGO"><![CDATA[ CL501 ]]></field>
  <field name="DATA"><![CDATA[ 2012-03-16 ]]></field>
  <field name="ENDERECO"><![CDATA[ CASEMIRO DE ABREU ]]></field>
</Imovel>';

Assembling the SQL

$root = simplexml_load_string( $string ); 

foreach( $root as $element )
{
    foreach( $element-> attributes() as $field )
    {
        $clear    = trim( $element );
        $fields[] = "'$field'";
        $values[] = "'$clear'";
    }
}

SQL

INSERT INTO `TABLE` (" . implode( ', ' , $fields ) . ")
             VALUES (" . implode( ', ' , $values ) . ")

OUTPUT

INSERT INTO `TABLE` ('CODIGO', 'DATA', 'ENDERECO')
             VALUES ('CL501', '2012-03-16', 'CASEMIRO DE ABREU')

Browser other questions tagged php html mysql xml dump

You are not signed in. Login or sign up in order to post.