I have the following which returns the id and category in my string as required.
INSERT:
$xml = 'categories.xml'; // URL for feed.
try{
$feed = new SimpleXMLElement($xml, null, true);
}catch(Exception $e){
echo $e->getMessage();
exit;
}
$sql = 'INSERT INTO database (`id`, `category`) VALUES ';
foreach($feed->property as $property) // LOOP THROUGH EACH PROPERTY TO GET id
{
$xpath="details/category/name"; // PATH TO CATEGORIES
$category_elements = $property->xpath($xpath);
foreach ($category_elements as $category) //LOOP THROUGH CATEGORIES
{
$sql .= sprintf(
"\
('%s', '%s'),",
$property->id,
$category->en
);
}
}
$sql = rtrim($sql, ',') . ';';
echo $sql;
This produces
INSERT INTO database (`id`, `category`) VALUES ('Example1', 'Surroundings'), ('Example1', 'Orientation')
All good so far. What I now need to do is add the value to those categories relevant to each id.
I have tried various loops but none of my logic seems to return the correct results.
This was my final attempt below which I can see is not correct because it loops through the categories for every value. I need to loop the values though because one category can contain more than one value.
$sql = 'INSERT INTO database (`id`, `category`, `value`) VALUES ';
foreach($feed->property as $property) // LOOP THROUGH EACH PROPERTY TO GET id
{
$xpath="details/category/name"; // PATH TO CATEGORIES
$category_elements = $property->xpath($xpath);
foreach ($category_elements as $category) //LOOP THROUGH CATEGORIES
{
$xpath="details/category/value"; // PATH TO CATEGORY VALUES
$value_elements = $property->xpath($xpath);
foreach ($value_elements as $value) //LOOP THROUGH CATEGORY VALUES
{
$sql .= sprintf(
"\
('%s', '%s','%s'),",
$property->id,
$category->en,
$value->en
);
}
}
}
Perhaps somebody cleverer than me could help out here. I hope I have explained this well enough to understand what I am trying to achieve.
INSERT INTO database (`id`, `category`) VALUES ('Example1', 'Surroundings'), ('Example1', 'Orientation')
You are doing 3 things:
Reading some xml
Creating an sql statement
Putting that sql into your database
at each stage stop and echo out some values and prove they are what you want, not just that they look about right eg the sql statement you quoted above, for example will either choke because id is unique, Example1, Example1 or enter wobbly data into your database.
No, you don’t need to start again from scratch. Just use ECHO as the last person said and ECHO your SQL statement to the browser so that you can see if it is correct.
Thanks for the reply. Sorry if I was a bit snappy but I have been trying to figure this out for too long.
I have been echoing my SQL and it returns the following in current guise.
INSERT INTO database (`id`, `category`, `value`) VALUES ('Example1', 'Surroundings','Close To Golf'), ('Example1', 'Surroundings','South West'), ('Example1', 'Surroundings','Communal'), ('Example1', 'Surroundings','Hot A/C'),
Well that is just some of it. In fact it returns a value for every category then starts over returning the same values for the next category and so on.
What I need to insert into my DB should be:
INSERT INTO database (`id`, `category`, `value`) VALUES ('Example1', 'Surroundings','Close To Golf'), ('Example1', 'Orientation','South West'), ('Example1', 'Pool','Communal'), ('Example1', 'Air Conditioner','Hot A/C'),
Now if you cannot insert that into your database then maybe you should tell us what kind of error you are getting back from mysql - I mean bypass PHP, just paste that into PhpMyAdmin or whatever you use to manage your database.
Next, PHPs task is to assemble the query so that it exactly matches a good sql statement.
Come back and tell us what you did and what error messages you can see, if you cannot see any then tell us that too.
Example1 is the identifier for each category and value. The Db table has id, category and value columns. It functions perfectly if I manually add values to my insert query. It is the assembly of this query from the XML file that I am having problems with. It should read like this:
INSERT INTO database (`id`, `category`, `value`) VALUES
('Example1', 'Surroundings','Close To Golf'),
('Example1', 'Orientation','South West'),
('Example1', 'Pool','Communal'),
('Example1', 'Air Conditioner','Hot A/C'),
('Example2', 'Surroundings','Close To Golf'),
('Example2', 'Surroundings','Close To Railway Station'),
('Example2', 'Orientation','South West'),
('Example2', 'Pool','Private'),
('Example2', 'Air Conditioner','None'),
Well Colin if your id field does not need to be unique, as you have explained - do what I said to do earlier.
Echo your sql statement onto the page, copy that text, put that text into PhpMyAdmin - if there are no errors and the text is inserted then you are barking up the wrong tree asking about mysql errors.
You have to isolate each operation and prove they are working, as I said earlier.
OK so the database is not actually named “database” I am using this as an example.
I am simply trying to put the insert string together here. The resulting query will be something like SELECT Category and Value FROM database WHERE id = Example1. It is not this part that I am having an issue with.
I need to get the loop working to create the insert string in the correct way. If you take a look at what I have so far you will see that it loops through ALL the values for each category then the same again for the next and the next.
For example
INSERT INTO database (`id`, `category`, `value`) VALUES
('Example1', 'Surroundings','Close To Golf'),
('Example1', 'Surroundings','South West'),
The second line should say Orientation South West NOT surroundings.
Take another look at my examples and you will see what I mean.
INSERT INTO mydatabase (`id`, `category`, `value`)
VALUES
('Example1', 'Surroundings','Close To Golf'),
('Example2', 'Surroundings','Close To Golf'),
('Example2', 'Surroundings','Close To Shops'),
('Example2', 'Surroundings','Close To Schools'),
This is a step in the right direction but the category is not looping.
Just had a quick play about with your code. Is this nearer to what you’re looking for? I might not have understood exactly what you’re trying to achieve, so sorry if it’s no use to you! But worth a try…
