T3000
Reputation: 331
cannot insert into the MySQL Database
I have several xml files. I am trying to insert the data collected from those xml files into a database. The problem is that some got inserted and some other ones are not. here are two of the xml files. The first one is inserted without any pb, however, the second one is not inserted into the data base.
<?xml version="1.0" encoding="UTF-8"?>
<recipeml version="0.5">
<recipe>
<head>
<title>10 Minute Meat Loaf</title>
<categories>
<cat>Beef</cat></categories>
<yield>4</yield></head>
<ingredients>
<ing>
<amt>
<qty>1</qty>
<unit>pound</unit></amt>
<item>Ground beef</item></ing>
<ing>
<amt>
<qty>1</qty>
<unit/></amt>
<item>Egg</item></ing>
<ing>
<amt>
<qty>1/2</qty>
<unit>cups</unit></amt>
<item>Bread crumbs</item></ing>
<ing>
<amt>
<qty>1/4</qty>
<unit>cups</unit></amt>
<item>Milk</item></ing>
<ing>
<amt>
<qty>2</qty>
<unit>tablespoons</unit></amt>
<item>Onion soup mix</item></ing>
<ing>
<amt>
<qty>2</qty>
<unit>tablespoons</unit></amt>
<item>Ketchup</item></ing>
<ing>
<amt>
<qty>2</qty>
<unit>tablespoons</unit></amt>
<item>Soy sauce</item></ing>
<ing>
<amt>
<qty>1/2</qty>
<unit>cups</unit></amt>
<item>Swiss cheese; shredded</item></ing></ingredients>
<directions>
<step> Combine all ingredients and shape into a round or oval loaf. Place in
microwave-safe dish, cover with waxed paper and microwave on high for 10
minutes, turning dish after 5 minutes, of cooking, drain and cover with
foil. Let stand 10 minutes before slicing. Makes 4-6 servings.
busted by sooz
Recipe by: Key Gourmet
Posted to recipelu-digest by sooz <[email protected]> on Mar 18, 1998
</step></directions></recipe></recipeml>
****second XML File I have trouble to insert into the database***
<?xml version="1.0" encoding="UTF-8"?>
<recipeml version="0.5">
<recipe>
<head>
<title>10 Can Chicken Casserole</title>
<categories>
<cat>Casserole</cat></categories>
<yield>8</yield></head>
<ingredients>
<ing>
<amt>
<qty>1</qty>
<unit>large</unit></amt>
<item>Fryer; boiled with onion and celery; deboned, cut up</item></ing>
<ing>
<amt>
<qty>1</qty>
<unit>can</unit></amt>
<item>Cheddar cheese soup</item></ing>
<ing>
<amt>
<qty>1</qty>
<unit>can</unit></amt>
<item>Cream of mushroom soup</item></ing>
<ing>
<amt>
<qty>1</qty>
<unit>can</unit></amt>
<item>(small) evaporated milk</item></ing>
<ing>
<amt>
<qty>1</qty>
<unit>can</unit></amt>
<item>(#202) asparagus tips; drained</item></ing>
<ing>
<amt>
<qty>1</qty>
<unit>can</unit></amt>
<item>French style green beans; drained</item></ing>
<ing>
<amt>
<qty>1</qty>
<unit>can</unit></amt>
<item>Water chestnuts; drained, sliced</item></ing>
<ing>
<amt>
<qty>1</qty>
<unit>can</unit></amt>
<item>(4-oz) mushroom stems & pieces with liquid</item></ing>
<ing>
<amt>
<qty>1</qty>
<unit>can</unit></amt>
<item>(small) pimento; diced, with liquid</item></ing>
<ing>
<amt>
<qty>1</qty>
<unit>can</unit></amt>
<item>Chow mein noodles</item></ing>
<ing>
<amt>
<qty>1</qty>
<unit>can</unit></amt>
<item>French fried onion rings</item></ing>
<ing>
<amt>
<qty/>
<unit/></amt>
<item>Red pepper to taste</item></ing></ingredients>
<directions>
<step> Mix all ingredients, except the onion rings, together. Put into a 3 quart
casserole; top with onion rings. Bake at 350 degrees until bubbly,
approximately 30 minutes.
From <A Taste of Louisiana>.Downloaded from Glen's MM Recipe Archive,
http://www.erols.com/hosey.
</step></directions></recipe></recipeml>
now, this is my code in php5:
<?php
$link = mysql_connect('localhost', 'root', 'recipe');
if (!$link)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('php_database');
$sql = 'DROP TABLE IF EXISTS `recipe`';
mysql_query( $sql, $link );
$sql = 'CREATE TABLE `recipe` (
`idRecipe` TINYINT( 12) UNSIGNED NOT NULL AUTO_INCREMENT ,
`RecipeTitle` VARCHAR( 75 ) NOT NULL,
`Yield` TINYINT( 5) UNSIGNED NOT NULL ,
`Steps` Text( 10000 ) NOT NULL,
PRIMARY KEY ( `idRecipe` )
)';
echo 'Creating table: \'recipe\'....';
mysql_query( $sql, $link );
if ($handle = opendir('C:\wamp\www\PhP\Folder xml'))
{
while (false !== ($file = readdir($handle)))
{ //$i++;
if ($file != '.' && $file != '..')
{
if (file_exists($file))
{
$use_errors = libxml_use_internal_errors(true);
$xml = simplexml_load_file($file);
if (!$xml)
{
echo ("Cannot open the XML file $file <br />");
}
else
{
echo "<br /> <br />";
$title=$xml->recipe->head->title;
$yield=$xml->recipe->head->yield;
$step=$xml->recipe->directions->step;
echo "value of Recipe Title= $title, yield= $yield and step= $step<b />";
mysql_query("INSERT INTO recipe (RecipeTitle,Yield,Steps) values ('$title', '$yield','$step')");
printf("Last inserted record has id %d\n", mysql_insert_id());
mysql_close($link);
echo "<br />";
$xmla= "$file";
$xmlDoc = new DOMDocument();
$xmlDoc->load($xmla);
$cat = $xmlDoc->getElementsByTagName("cat");
$numOfcat = $cat->length;
echo "@@ here is the number of cat :$numOfcat @@<br />";
$qty = $xmlDoc->getElementsByTagName("qty");
$numOfqty = $qty->length;
echo "@@ here is the number of qty :$numOfqty @@<br />";
echo $xml->getName() . "<br />"; // displays recipeml
foreach($xml->children() as $child)
{
echo $child->getName() . ": " . $child . "<br />"; //displays recipe:
//echo "INSERT " . $child . " INTO....";
echo ("title= ");
echo $xml->recipe->head->title . " ,\r\n" ; //displays the title
echo ("category= ");
echo $xml->recipe->head->categories->cat . ",\n" ;// displays the category
echo ("yield= ");
echo $xml->recipe->head->yield . ",\n" ; //displays yield number
echo ("quantity= ");
echo $xml->recipe->ingredients->ing->amt->qty . ",\n" ; //displays quantity
echo ("unit= ");
echo $xml->recipe->ingredients->ing->amt->unit . ",\n" ; ////displays unit
echo ("Item= ");
echo $xml->recipe->ingredients->ing->item . ",\n" ; //displays item
for($i=1; $i<$numOfqty; $i=$i+1)
{
echo ("quantity= ");
echo $xml->recipe->ingredients->ing[$i]->amt->qty . ",\n" ;
echo ("unit= ");
echo $xml->recipe->ingredients->ing[$i]->amt->unit . ",\n" ;
echo ("Item= ");
echo $xml->recipe->ingredients->ing[$i]->item . ",\n" ;
}
echo ("Step to cook=********************** ");
echo $xml->recipe->directions->step . ",\n" ; //displays item
}
}
}
}
}
}
closedir($handle);
?>
Any suggestions? many of those files are not well formed, But I used Altova to check if those two xml files are well formed and altova confirmed that they are both well formed. I will appreciate any suggestions.
Thanks,
T3000.
Upvotes: 0
Views: 704
Answers (2)
Lawrence Cherone
Reputation: 46650
mysql_real_escape_string() will escape your data & using or die will report where its failed. basic debugging...
$query="INSERT INTO recipe (RecipeTitle,Yield,Steps) values
('".mysql_real_escape_string($title)."',
'".mysql_real_escape_string($yield)."',
'".mysql_real_escape_string($step)."')";
mysql_query($query,$link) or die(mysql_error());
You close your connection after the first iteration and then link is not established again.
Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.
Unrelated but Use a url friendly path, underscores instead of spaces
$handle = opendir('C:\wamp\www\PhP\Folder_xml');
Upvotes: 2
Phliplip
Reputation: 3632
You have a ' in your second directions->step, which will make the query fail, as the rest of the text after the ' will be seen as regular SQL - which it is not.
You should use mysql_real_escape_string on the strings before inserting them to the query.
Or switch to MySQLi or a DB abstraction layer that utilizes prepared statements.
Upvotes: 4
Related Questions
- Cannot insert data into MySQL Database
- PHP mysql error, unable to INSERT to database
- Can't insert into database
- error inserting in database
- Can't insert into a database with PHP
- Can't insert data into mySQL database
- Cannot insert into mysql database
- Can't Insert into MySQL Table using PHP
- I can't insert data into my database
- unable to insert into mysql database using php