Take this XML file to a database

[brm5017]

I'm trying to write a script to import this data from an XML file to a mySQL database. The database has not yet been set up.

 

Any suggestions to help me get started?

 

 

<RETS ReplyCode="0" ReplyText="V2.3.3 589: Success">

  <COUNT Records="28146" />

  <DELIMITER value="09" />

  <COLUMNS>AgentFirstName AgentID AgentLastName</COLUMNS>

  <DATA>JoAnn SDRAHOJO Drahos</DATA>

  <DATA>Suzanne SBOYDSU Boyd</DATA>

  <DATA>Cynthia SLISTECY Lister</DATA>

  <DATA>Carmela SMARCECA Marcellino</DATA>

  <DATA>Pamela GEWALDPA Ewald</DATA>

  <DATA>Louise 50603199 Fletcher</DATA>

  <DATA>Linda GPRATTLI Pratta</DATA>

  <DATA>Tracy GNECELTR Necelis</DATA>

  <DATA>Ann GHALLOAN Hallock</DATA>

  <DATA>Veronica GMERRIVE Merriel</DATA>

  <DATA>Wayne GHEILWA Heil</DATA>

  <DATA>Charles GCARLICH Carlin</DATA>

  <DATA>Edward GMOLINED Molinari</DATA>

  <DATA>Patricia GBRAMBPA Bramble</DATA>

  <DATA>Blanche SCOFFIBL Coffineau</DATA>

  <DATA>Eileen SMATSOEI Matson</DATA>

 

[rhodesa]

for the table:

CREATE TABLE `agents` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`agent_id` VARCHAR( 255 ) NOT NULL ,
`first_name` VARCHAR( 255 ) NOT NULL ,
`last_name` VARCHAR( 255 ) NOT NULL
) ENGINE = innodb;

 

for your INSERT:

<?php
require('db_connect.php');

$xml = <<<XML
<RETS ReplyCode="0" ReplyText="V2.3.3 589: Success">
  <COUNT Records="28146" />
  <DELIMITER value="09" />
  <COLUMNS>AgentFirstName AgentID AgentLastName</COLUMNS>
  <DATA>JoAnn SDRAHOJO Drahos</DATA>
  <DATA>Suzanne SBOYDSU Boyd</DATA>
  <DATA>Cynthia SLISTECY Lister</DATA>
  <DATA>Carmela SMARCECA Marcellino</DATA>
  <DATA>Pamela GEWALDPA Ewald</DATA>
  <DATA>Louise 50603199 Fletcher</DATA>
  <DATA>Linda GPRATTLI Pratta</DATA>
  <DATA>Tracy GNECELTR Necelis</DATA>
  <DATA>Ann GHALLOAN Hallock</DATA>
  <DATA>Veronica GMERRIVE Merriel</DATA>
  <DATA>Wayne GHEILWA Heil</DATA>
  <DATA>Charles GCARLICH Carlin</DATA>
  <DATA>Edward GMOLINED Molinari</DATA>
  <DATA>Patricia GBRAMBPA Bramble</DATA>
  <DATA>Blanche SCOFFIBL Coffineau</DATA>
  <DATA>Eileen SMATSOEI Matson</DATA>
</RETS>
XML;

$xml = simplexml_load_string($xml);
$count = 0;
foreach($xml->DATA as $row){
  list($first,$id,$last) = preg_split('/\s+/',$row,3);
  $sql = sprintf("INSERT INTO agents (agent_id,first_name,last_name) VALUES ('%s','%s','%s')",
    mysql_real_escape_string($id),
    mysql_real_escape_string($first),
    mysql_real_escape_string($last)
  );
  mysql_query($sql) or die(mysql_error());
  $count++;
}
print "Inserted $count rows";
?>

 

note: In the XML, it specifies a delimiter of '09', which is ASCII for a tab character. does your XML have tab characters between the firstname/id/lastname? in the XML you posted, they are spaces.

[brm5017]

This site is Very Resourceful. Thanks for the reply!!

 

 

When the XML is opened in notepad or dreamweaver, yes they are spaces. The above code was taken from the Internet Explorer interpreted view.

 

I'm going to try starting with the code you posted...

 

this site is amazing...

 

[brm5017]

Ok - so what if I have a very long XML file and I want to read right from the file - instead of typing all the xml values out?

 

 

[rhodesa]

just use:

$xml = simplexml_load_file('path/to/file.xml');

instead

[brm5017]

Ok - this might be tricky now - This script will be running every day - I've got another script that creates that XML file (pulls data from another server).

 

Is there any way to check to see if the agent is already listed in the database before being inserted again? Or is the best way to empty the agent table and insert the records again?

 

 

(If you couldn't already tell, i'm not the best with writing PHP, I can read it tho)

[brm5017]

Also, what if the agent has been deleted? If it's already in the DB, without scrapping the whole DB, how will it be updated correctly?

[rhodesa]

just make agent_id a unique field. the queries will then fail when trying to insert again.

 

are you saying, if the agent is deleted from your table, you don't want this job to re-add it? if so, add another column called 'removed' or 'date_removed' and insert a 1 (or the date if you use date_removed) when you want it removed instead of ACTUALLY removing the agent. the insert will continue to fail (aka, not re-adding it) and you only have to modify your SELECT statements to be:

SELECT * FROM agents WHERE removed IS NULL

[brm5017]

Can anyone explain this code? I've got more columns in my xml file now.

$xml = simplexml_load_string($xml);

$count = 0;

foreach($xml->DATA as $row){

  list($first,$id,$last) = preg_split('/\s+/',$row,3);

  $sql = sprintf("INSERT INTO agents (agent_id,first_name,last_name) VALUES ('%s','%s','%s')",

    mysql_real_escape_string($id),

    mysql_real_escape_string($first),

    mysql_real_escape_string($last)

  );

  mysql_query($sql) or die(mysql_error());

  $count++;

}

print "Inserted $count rows";

?>

 

Lets say my colums in the XML are as follows:

<COLUMNS>AgentFirstName AgentID AgentLastName AgentHireDate AgentTerminationDate</COLUMNS>

 

would that make the  list($first,$id,$last) = preg_split('/\s+/',$row,3); change to list($first,$id,$last,$hiredate,$termdate) = preg_split('/\s+/',$row,5);

 

Also do I need a mysql_real_escape_string for each variable?

 

 

How about the VALUES ('%s','%s','%s')  - do I need a %s for each variable as well VALUES ('%s','%s','%s', '%s', '%s') ?

 

 

 

 

[brm5017]

I came up with this from a separate set of data.

 

<?php
require('db_connect.php');

$xml = simplexml_load_file('../trendxml/Trend.xml');

$count = 0;
foreach($xml->DATA as $row){
  list($listprice,$listid,$listair,$bath,$beds,$heating,$sewer,$water,$totalpics,$age,$garagecars,$suffix,$city,$county,$fullstreet,$state,$street,$streetnumber,$pool) = preg_split('/\s+/',$row,19);
  $sql = sprintf("INSERT INTO listings (list_price,list_id,list_air,list_bath,list_beds,list_heating,list_sewer,list_water,list_numpics,list_age,list_garagecars,list_suffix,list_city,list_county,list_fullstreet,list_state,list_street,list_streetnumber,list_pool) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
    mysql_real_escape_string($listprice),
    mysql_real_escape_string($listid),
mysql_real_escape_string($listair),
mysql_real_escape_string($bath),
mysql_real_escape_string($beds),
mysql_real_escape_string($heating),
mysql_real_escape_string($sewer),
mysql_real_escape_string($water),
mysql_real_escape_string($totalpics),
mysql_real_escape_string($age),
mysql_real_escape_string($garagecars),
mysql_real_escape_string($suffix),
mysql_real_escape_string($city),
mysql_real_escape_string($county),
mysql_real_escape_string($fullstreet),
mysql_real_escape_string($state),
mysql_real_escape_string($street),
mysql_real_escape_string($streetnumber),
    mysql_real_escape_string($pool)
  );
  mysql_query($sql) or die(mysql_error());
  $count++;
}
print "Inserted $count rows";
?>

[brm5017]

Am I on the right track?

[rhodesa]

yeah, you listed all the right modifications that need to be done

[rhodesa]

here is a more dynamic version of the code. all you have to do is supply the XML file and the table name...

<?php
require('db_connect.php');

$xml = simplexml_load_file('../trendxml/Trend.xml');
$table = 'listings'; //Change this to the name of the table

//The rest shouldn't have to be changed

$columns = preg_split('/\s+/',$xml->COLUMNS); //Splits column row up
//The follow dynamically builds the INSERT statement from the columns
$insert = "INSERT INTO $table (".implode(',',$columns).") VALUES (".implode(',',array_fill(0,count($columns),"'%s'")).")";

$count = 0;
foreach($xml->DATA as $row){
  $parts = preg_split('/\s+/',$row,count($columns)); //Split the row up
  array_walk($parts,create_function('&$v','$v = mysql_real_escape_string($v);')); //Run all parts through mysql_real_escape_string()
  while(count($parts) < count($columns)) $parts[] = ''; //Make sure we have enough values
  array_unshift($parts,$insert); //Add the INSERT command to the beginning
  $sql = call_user_func_array('sprintf',$parts); //Put it all together
  mysql_query($sql) or die(mysql_error()); //Run the query
  $count++;
}
print "Inserted $count rows into $table";
?>

[brm5017]

Aaron, Thanks for the help man.

 

So as for this dynamic code, do field names matter when I set up the table?

[rhodesa]

So as for this dynamic code, do field names matter when I set up the table?

 

yes they do...i meant to mention that in the post. if that isn't possible, let me know and i'll post a version that is a combination of the code before and the new code.

[brm5017]

The code's fine the way it is assuming I make the field names the same as the XML column names, right? You're the man.

[rhodesa]

The code's fine the way it is assuming I make the field names the same as the XML column names, right? You're the man.

 

yup. and it's probably better to keep them the same anyways. less confusion...

[brm5017]

This may seem like a dumb question, what about a primary key? Do I need an auto increment in the DB somewhere?

[brm5017]

I'm getting this 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 'ListPrice,ListingID,CentralAir,Bathrooms,Beds,Heating,SewerSeptic,Water,TotalPho' at line 1

 

 

 

FYI, listings table:

Field  	Type   	Null  	Default   	Comments
id 	int(255) 	No  	  	 
ListingID 	varchar(255) 	No  	  	 
ListPrice 	varchar(255) 	No  	  	 
CentralAir 	varchar(255) 	No  	  	 
Bathrooms 	varchar(255) 	No  	  	 
Beds 	        varchar(255) 	No  	  	 
Heating 	varchar(255) 	No  	  	 
SewerSeptic 	varchar(255) 	No  	  	 
Water 	        varchar(255) 	No  	  	 
TotalPhotos 	varchar(255) 	No  	  	 
PropertyAge 	varchar(255) 	No  	  	 
GarageSpaces 	varchar(255) 	No  	  	 
StreetSuffix 	varchar(255) 	No  	  	 
CityName 	        varchar(255) 	No  	  	 
County 	        varchar(255) 	No  	  	 
FullStreetAddress 	varchar(255) 	No  	  	 
State 	        varchar(255) 	No  	  	 
StreetName 	varchar(255) 	No  	  	 
StreetNumber 	varchar(255) 	No  	  	 
Pool 	                varchar(255) 	No  	  	 

[brm5017]

OK, I printed a description at each line of code. I'm getting a ' , ' before each field name and insert name. how to fix?

 

 

Connected to mulhol_listings

Loaded XML

Selected Listings Table

Got Columns

Set INSERT INTO stmt

Preg_Split

array_walk

whilearray_unshift

call_usr_func

INSERT INTO listings (,ListPrice,ListingID,CentralAir,Bathrooms,Beds,Heating,SewerSeptic,Water,TotalPhotos,PropertyAge,GarageSpaces,StreetSuffix,CityName,County,FullStreetAddress,State,StreetName,StreetNumber,Pool,) VALUES ('','314900.0','5136665','Y','4','Gas,Forced','Air','Public','Sewer','Public','7','2','2-CarGarage','RUN','DOVER','KENT','5','Boggs','Run','DE','BOGGS 5 No ')

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 'ListPrice,ListingID,CentralAir,Bathrooms,Beds,Heating,SewerSeptic,Water,TotalPho' at line 1

[rhodesa]

This may seem like a dumb question, what about a primary key? Do I need an auto increment in the DB somewhere?

not a dumb question. it's not required, but it's usually a good idea to have a unique field in every table, so you can refer to each row easily. all the XML you have posted so far, seems to have a unique ID already (AgentID, ListingID, etc). Using that should be fine.

 

OK, I printed a description at each line of code. I'm getting a ' , ' before each field name and insert name. how to fix?

you must have a space at the beginning of the fields. try this out, i added some trim()s

<?php
require('db_connect.php');

$xml = simplexml_load_file('../trendxml/Trend.xml');
$table = 'listings'; //Change this to the name of the table

//The rest shouldn't have to be changed

$columns = preg_split('/\s+/',trim($xml->COLUMNS)); //Splits column row up
//The follow dynamically builds the INSERT statement from the columns
$insert = "INSERT INTO $table (".implode(',',$columns).") VALUES (".implode(',',array_fill(0,count($columns),"'%s'")).")";

$count = 0;
foreach($xml->DATA as $row){
  $parts = preg_split('/\s+/',trim($row),count($columns)); //Split the row up
  array_walk($parts,create_function('&$v','$v = mysql_real_escape_string($v);')); //Run all parts through mysql_real_escape_string()
  while(count($parts) < count($columns)) $parts[] = ''; //Make sure we have enough values
  array_unshift($parts,$insert); //Add the INSERT command to the beginning
  $sql = call_user_func_array('sprintf',$parts); //Put it all together
  mysql_query($sql) or die(mysql_error()); //Run the query
  $count++;
}
print "Inserted $count rows into $table";
?>

 

[brm5017]

AHHHH

 

 

Connected to mulhol_listings

 

Warning: Invalid argument supplied for foreach() in /home/mulhol/public_html/trend/xmlimport.php on line 22

Inserted 0 rows into

 

 

[brm5017]

I sent an email of xml file.. might be a tab in front that's screwing it up.

 

also, here's my EXACT php file as of now.

 

 

<?php

$dbhost = 'localhost';
$dbuser = 'mulhol';
$dbpass = 'multeam';
$dbname = 'mulhol_listings';

// This is an example opendb.php
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');
mysql_select_db($dbname);

print "Connected to $dbname <br>";


//The rest shouldn't have to be changed

$columns = preg_split('/\s+/',trim($xml->COLUMNS)); //Splits column row up
//The follow dynamically builds the INSERT statement from the columns
$insert = "INSERT INTO $table (".implode(',',$columns).") VALUES (".implode(',',array_fill(0,count($columns),"'%s'")).")";

$count = 0;
foreach($xml->DATA as $row){
  $parts = preg_split('/\s+/',trim($row),count($columns)); //Split the row up
  array_walk($parts,create_function('&$v','$v = mysql_real_escape_string($v);')); //Run all parts through mysql_real_escape_string()
  while(count($parts) < count($columns)) $parts[] = ''; //Make sure we have enough values
  array_unshift($parts,$insert); //Add the INSERT command to the beginning
  $sql = call_user_func_array('sprintf',$parts); //Put it all together
  mysql_query($sql) or die(mysql_error()); //Run the query
  $count++;
}
print "Inserted $count rows into $table";

mysql_close($conn);

?>

[rhodesa]

i sent some updated code back, but here it is again. also, where the simplexml_load_file() go? that is what is giving you the foreach error.

 

yeah...remember when i mentioned the delimiters before? this XML uses that. 09 is a tab character. here is some code that uses that delimiter value. i also added a $debug variable. set it to true, and it will show you a table with the data, false will do the inserts. that way you can make sure the delimiters and data are lining up

<?php
require('db_connect.php');

$debug = true;
$xml = simplexml_load_file('../trendxml/Trend.xml');
$table = 'mulhol_listings'; //Change this to the name of the table

//The rest shouldn't have to be changed

//Get the delimiter
$delim = chr((int)$xml->DELIMITER['value']);
if($debug)
  print "Delimiter: {$delim}[{$xml->DELIMITER['value']}]<br>";

//Get the list of Columns
$columns = explode($delim,trim($xml->COLUMNS)); //Splits column row up
//The follow dynamically builds the INSERT statement from the columns
$insert = "INSERT INTO $table (".implode(',',$columns).") VALUES (".implode(',',array_fill(0,count($columns),"'%s'")).")";

if($debug)
  print '<table border="1"><tr><th>'.implode('</th><th>',$columns).'</th></tr>';

$count = 0;
foreach($xml->DATA as $row){
  $parts = explode($delim,trim($row),count($columns)); //Split the row up
  if($debug){
    print '<tr><td>'.implode('</td><td>',$parts).'</td></tr>';
  }else{
    array_walk($parts,create_function('&$v','$v = mysql_real_escape_string($v);')); //Run all parts through mysql_real_escape_string()
    while(count($parts) < count($columns)) $parts[] = ''; //Make sure we have enough values
    array_unshift($parts,$insert); //Add the INSERT command to the beginning
    $sql = call_user_func_array('sprintf',$parts); //Put it all together
    mysql_query($sql) or die(mysql_error()); //Run the query
  }
  $count++;
}
if($debug){
  print "</table>";
  print "Found $count rows to go into $table";
}else
  print "Inserted $count rows into $table";
?>