Jump to content

XML to MySQL (Insert problem)

weep

By weep
in PHP Coding Help

 Share

Recommended Posts

weep Newbie

weep

Posted
Posted

Greetings!

 

I have a script that allows me to upload a XML file, the script then picks out some of the values and echoes them (this works fine). I am now trying to insert the values into a database. However, I get an error: error: Unknown column 'blah blah' in 'field list'

 

I really don't see that there are anything wrong with my sql line:

 

$sql = "INSERT INTO rorligt (id, arende, rumsnr, besk, adress, ejfakt, avslutad, best, rikt, listid) VALUES (NULL, $arende[$x], 
$rumsnr[$x], `$beskrivning[$x]`, `$adress[$x]`, `$ejfakturerat[$x]`, `$avslutad[$x]`, `best`, `$rikt`, `$listid`)";

 

I tried it out with other variables and it worked, seems that only variables that are tied to the xml are not working... Where did I go wrong?  :'(

 

Entire code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
   <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
   <meta name="description" content="Das Test">
   <meta name="keywords" content="test">
   <title>test</title>

</head>




<?php 
error_reporting(0);

include "cfg.php";

$filnamn = $_POST["filnamn"];
$filnamn2 = "uploads/$filnamn";



if (file_exists($filnamn2)) { //Kontroll om fil existerar
   

$xml = file_get_contents($filnamn2); 
$xml = new SimpleXMLElement($xml); 



$arende = array();
$beskrivning = array();
$adress = array();
$ejfakturerat = array();
$avslutad = array();
$rumsnr = array();
$rikt = ""; 
$listid = "";

foreach($xml->lstResults->lstResults_Details_Group as $textbox1){
     $arende[] = (string)$textbox1['textbox22']; 
}

foreach($xml->lstResults->lstResults_Details_Group as $textbox2){
     $beskrivning[] = (string)$textbox2['textbox42']; 
}
foreach($xml->lstResults->lstResults_Details_Group as $textbox3){
     $adress[] = (string)$textbox3['textbox30']; 
}
foreach($xml->lstResults->lstResults_Details_Group as $textbox4){
     $ejfakturerat[] = (string)$textbox4['textbox28']; 
}
foreach($xml->lstResults->lstResults_Details_Group as $textbox5){
     $avslutad[] = (string)$textbox5['textbox24']; 
}
foreach($xml->lstResults->lstResults_Details_Group as $textbox6){
     $rumsnr[] = (string)$textbox6['textbox26']; 
}
?>
<table border="0">
<tr><td bgcolor="#FFFF00"><b>Ärende</b></td>
<td bgcolor="#FFFF00"><b>Rumsnummer</b></td>
<td bgcolor="#FFFF00"><b>Beskrivning</b></td>
<td bgcolor="#FFFF00"><b>Adress</b></td>
<td bgcolor="#FFFF00"><b>Ej fakturerat</b></td>
<td bgcolor="#FFFF00"><b>Avslutad</b></td>
<td bgcolor="#FFFF00"><b>Beställningsnr</b></td>
<td bgcolor="#FFFF00"><b>Riktkostnad</b></td>
<td bgcolor="#FFFF00"><b>Sign</b></td>
</tr>
<?php

for($x=0;$x<count($arende);$x++){ 
echo "<tr><td>";
echo (empty($arende[$x])) ? "Saknas..." : $arende[$x];
echo "</td><td>";
echo (empty($rumsnr[$x])) ? "Saknas..." : $rumsnr[$x];
echo "</td><td>";
echo (empty($beskrivning[$x])) ? "Saknas..." : $beskrivning[$x];
echo "</td><td>";
echo (empty($adress[$x])) ? "Saknas..." : $adress[$x];
echo "</td><td>";
echo (empty($ejfakturerat[$x])) ? "Saknas..." : $ejfakturerat[$x];
echo "</td><td>";
echo (empty($avslutad[$x])) ? "Saknas..." : $avslutad[$x];
echo "</td><td>";
echo "</td><td>";
echo "</td><td>";
echo "</td></tr>";


//Lägg in i databasen
mysql_connect ($servername, $dbusr, $dbpw) or die ('error: ' . mysql_error());
mysql_select_db($db);

$sql = "INSERT INTO rorligt (id, arende, rumsnr, besk, adress, ejfakt, avslutad, best, rikt, listid) VALUES (NULL, $arende[$x], 
$rumsnr[$x], `$beskrivning[$x]`, `$adress[$x]`, `$ejfakturerat[$x]`, `$avslutad[$x]`, `best`, `$rikt`, `$listid`)";

mysql_query($sql) or die ('error: ' . mysql_error());
//-------------------



/*
$test1 = "bleh";
$test2 = "bleh";
$test3 = "123,5";
$test4 = "2011-01-01";


//Lägg in i databasen
mysql_connect ($servername, $dbusr, $dbpw) or die ('error: ' . mysql_error());
mysql_select_db($db);

$sql = "INSERT INTO rorligt (id, arende, rumsnr, besk, adress, ejfakt, avslutad, best, rikt, listid) VALUES (NULL, '123N', 
'123-12', '$test1', '$test2', '$test3', '$test4', 'best', '456', '1')";

mysql_query($sql) or die ('error: ' . mysql_error());
*/
//-------------------

}


} else {
    echo "Finns ingen sådan fil! Gör om, gör rätt!";
}
?> 
</table>

Link to comment
Share on other sites
GalaxyTramp Member

GalaxyTramp

Posted
Posted

The error message implies that you have a problem with the field names in your query. Whatever "Blah Blah" is named as in your query is probably not named the same in your DB. I notice you are spelling adress with one 'd' in the query, is this the same fieldname in your DB?

 

 

Link to comment
Share on other sites
weep Newbie

weep

Posted
  • Author
Posted

Yeah, it is spelled with one d (swedish). The "rorligt" table have these fields:

 

id  arende  rumsnr  besk  adress  ejfakt  avslutad  best  rikt  listid

 

The strange thing is that, if I change $beskrivning[$x], $adress[$x] and so on to just plain text or some other variables, the sql query works fine!  :confused:

Link to comment
Share on other sites
weep Newbie

weep

Posted
  • Author
Posted

print your array to see what variables you are actually returning, if any.

 

Hm, isnt it what I am doing here?

echo (empty($arende[$x])) ? "Saknas..." : $arende[$x];

 

And please explain why you are inserting a NULL value into the id field of the table.

 

Haha, I have no answer for that. Thought thats how it was done...

 

Link to comment
Share on other sites
Muddy_Funster Regular Member

Muddy_Funster

Posted
Posted

you shouldn't really use echo to display the contents of an array, change 

echo (empty($arende[$x])) ? "Saknas..." : $arende[$x];

to

print_r($arende);

for each of them and let us see what you get back.

 

and no, it's not how it is done ;).  If your id field is an auto inc' field then don't touch it with any insert statement at all, you don't need to.  The table will fill it in on it's own, and databases can get upset when you start sticking your oar into their pond.

Link to comment
Share on other sites
weep Newbie

weep

Posted
  • Author
Posted 

Array ( [0] => blah blah hus 01 [1] => sdgsdgsdg 01 plan 6. sdgsdgsdgsdgsdg. Bestnr : 235235 [2] => Nätadministrationsärenden Mars-Apr. OBS Fakturerea inte före vi fått underlaget. [3] => sdgsdgsdgsdgsdg äöåö 1-1-1 och x-x-x. Uppöppning av adasdasdasdasd. [4] => And so on...

 

So, no problems here...  :-\ Pretty much same as echo.

Link to comment
Share on other sites
weep Newbie

weep

Posted
  • Author
Posted

Sorry for the delay, so if I get this right I should do something like this?

$arende1 = "\"".$arende[$x]."\"";

 

Also I am sorry that I cannot test any of this until monday, as I am at a different location at the moment.

Link to comment
Share on other sites
harristweed Newbie

harristweed

Posted
Posted

try this:

<?php
  $sql = "INSERT INTO rorligt ( arende, rumsnr, besk, adress, ejfakt, avslutad, best, rikt, listid) VALUES ( '{$arende[$x]}',
'{$rumsnr[$x]}', '{$beskrivning[$x]}', '{$adress[$x]}', '{$ejfakturerat[$x]}', '{$avslutad[$x]}', 'best', '{$rikt}', '{$listid}')";
?>

 

Link to comment
Share on other sites
weep Newbie

weep

Posted
  • Author
Posted

try this:

<?php
  $sql = "INSERT INTO rorligt ( arende, rumsnr, besk, adress, ejfakt, avslutad, best, rikt, listid) VALUES ( '{$arende[$x]}',
'{$rumsnr[$x]}', '{$beskrivning[$x]}', '{$adress[$x]}', '{$ejfakturerat[$x]}', '{$avslutad[$x]}', 'best', '{$rikt}', '{$listid}')";
?>

 

You, my good sir, are a god. Thank you all, problem solved!  :-*

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.