From XML to SQL through PHP?

[Untouchab1e]

Ok, I am developing an Android application (Java). However, the app creates and upload a .xml file through HTTP Post and I need the data in the xml file to be added to a database. I have little experience with PHP and hoping for (a lot of) help here.

 

Here is an example xml file

 

<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>
<root>
  <ushout_message />
  <data source="bf882ed25e322d48" reply_to="0" />
  <message>Shout out to the world</message>
</root>

 

Jeg need a .php script which will take in this XML file and put the data into the following database table:

 

 

`id` INT( 6 ) NOT NULL ,
`source` INT( 20 ) NOT NULL ,
`reply` INT( 20 ) NOT NULL ,
`message` VARCHAR(200) NOT NULL , 
PRIMARY KEY ( `id` )

 

the ID can be generated automatically (auto-increment) so no need to worry about that.

 

 

 

source = data source attribute in the xml file

reply = reply_to in xml file

message = message in xml file.

 

So wondering if anyone can help me out here?

 

Kind regards

[jonsjava]

here's an example. It won't work for any xml file that has more than one set of data

<?php 
$xml = "<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>
<root>
  <ushout_message />
  <data source=\"bf882ed25e322d48\" reply_to=\"0\" />
  <message>Shout out to the world</message>
</root>";
$x = simplexml_load_string($xml);
$source = (string)$x->data->attributes()->source;
$reply = (string)$x->data->attributes()->reply_to;
$message = (string)$x->message;
$sql = "INSERT INTO `table_name` VALUES(NULL,'$source','$reply','$message');";
?>

[Untouchab1e]

Thank you so much, but I need it to read the .xml file? I see you have hard coded it in there..?

 

As in, my Android app HTTP Posts the .xml file to this .php script?

[blacksharkmedia]

Thank you so much, but I need it to read the .xml file? I see you have hard coded it in there..?

 

As in, my Android app HTTP Posts the .xml file to this .php script?

 

Modify the script, and add the $url

$url = "http://www.domain.com/users/file.xml";

 

Then change

"$x = simplexml_load_string($xml);"

 

to

$x = simplexml_load_string(file_get_contents($url));

[Untouchab1e]

Ok, thanks for the tip..

 

So something like this?

 

<?php
$target_path  = "./";
$target_path = $target_path . basename( $_FILES['uploadedfile']['name']);
if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
$x = simplexml_load_string(file_get_contents(basename( $_FILES['uploadedfile']['name'])));
$source = (string)$x->data->attributes()->source;
$reply = (string)$x->data->attributes()->reply_to;
$message = (string)$x->message;
$sql = "INSERT INTO `table_name` VALUES(NULL,'$source','$reply','$message');";
?>
} else{
echo "There was an error uploading the file, please try again!";
}
?>

 

Would this even have a potential for working? And also, how do I specify what database , user and password to use though?

 

Thanks for all help!

[Untouchab1e]

Or more correctly, something like:

 

<?php
$target_path  = "./";
$target_path = $target_path . basename( $_FILES['uploadedfile']['name']);
if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
$x = simplexml_load_string(file_get_contents($target_path));
$source = (string)$x->data->attributes()->source;
$reply = (string)$x->data->attributes()->reply_to;
$message = (string)$x->message;
$sql = "INSERT INTO `shout_data` VALUES(NULL,'$source','$reply','$message');";
} else{
echo "There was an error uploading the file, please try again!";
}
?>

 

[Untouchab1e]

Sorry for the bump, but this script runs without any errors when feeding it an xml file, but nothing appears in my database table :S

 

<?php
$database_handle = mysql_connect('localhost', 'eksempel_bruker', 'eksempel_passord');
if (!database_handle) {
echo "Could not connect";
}

$db_selected = mysql_select_db('connect_shout', $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}


$target_path  = "./";
$target_path = $target_path . basename( $_FILES['uploadedfile']['name']);
if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
$x = simplexml_load_string(file_get_contents($target_path));
$source = (string)$x->data->attributes()->source;
$reply = (string)$x->data->attributes()->reply_to;
$message = (string)$x->message;
$sql = "INSERT INTO `shout_data` VALUES(NULL,'$source','$reply','$message');";
} else{
echo "There was an error uploading the file, please try again!";
}
?>

 

Any ideas?

[jonsjava]

add this to the end (after the sql):

mysql_query($sql) or die("There was an error uploading the file, please try again!");

so it looks like this:

<?php
$database_handle = mysql_connect('localhost', 'eksempel_bruker', 'eksempel_passord');
if (!database_handle) {



echo "Could not connect";
}

$db_selected = mysql_select_db('connect_shout', $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}


$target_path  = "./";
$target_path = $target_path . basename( $_FILES['uploadedfile']['name']);
if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
$x = simplexml_load_string(file_get_contents($target_path));
$source = (string)$x->data->attributes()->source;
$reply = (string)$x->data->attributes()->reply_to;
$message = (string)$x->message;
$sql = "INSERT INTO `shout_data` VALUES(NULL,'$source','$reply','$message');";
mysql_query($sql) or die("There was an error uploading the file, please try again!");
?>