CODEWITHSUNDEEP
phpmysqlsql-insert
doniyor
doniyor

Reputation: 37886

sql insert not inserting after some records

I had been inserting successfully into database but it is not inserting anything, I did not change the code since then actually.

What can be the reason?

while ($row = mysql_fetch_array($new_entries)){
    $anzeigen_id = $row[0];
    $firma_id = $row[1];
    $xml_filename = "xml/".$anzeigen_id.".xml";
    $dom = new DOMDocument();
    $dom->load($xml_filename);
    $value = $dom->getElementsByTagName('FormattedPositionDescription');
    foreach($value as $v){
        $text = $v->getElementsByTagName('Value');
        foreach($text as $t){
            $anzeige_txt = $t->nodeValue;
            $anzeige_txt = utf8_decode($anzeige_txt);
            $sql = "INSERT INTO testing (`firmen_id`,`anzeige_id`,`anzeige_txt`) VALUES ('$firma_id','$anzeigen_id','$anzeige_txt')";
            $sql_inserted = mysql_query($sql);
            if($sql_inserted){
                echo "'$anzeigen_id' from $xml_filename inserted<br />";
            }

        }
    }
}

Upvotes: 0

Views: 254

Answers (3)

j0k
j0k

Reputation: 22756

Well I will post an answer because it will be more clear to ask you some code example, etc..

First of all, when you got an unexplicable case like this: you should debug!

In your case, you display a message when the query success. But what if the query failed? You should handle an error message to see what's going on. Something like that:

if($sql_inserted)
{
  echo "'$anzeigen_id' from $xml_filename inserted<br />";
}
else
{
  throw new Exception(mysql_error() . '. SQL: '.$sql);
}

There will be an exception when a query failed. You will have the error message (mysql_error()) and the query that failed ($sql).

What could be a problem, is that you didn't escape value you put inside your query. So, if there is a ' inside a variable, it will break the query. You should escape them:

$firma_id    = mysql_real_escape_string($firma_id);
$anzeigen_id = mysql_real_escape_string($anzeigen_id);
$anzeige_txt = mysql_real_escape_string($anzeige_txt);

So you will have a final code like this:

foreach($text as $t)
{
  $firma_id    = mysql_real_escape_string($firma_id);
  $anzeigen_id = mysql_real_escape_string($anzeigen_id);
  $anzeige_txt = mysql_real_escape_string(utf8_decode($t->nodeValue));

  $sql = "INSERT INTO testing (`firmen_id`,`anzeige_id`,`anzeige_txt`) VALUES ('$firma_id','$anzeigen_id','$anzeige_txt')";
  $sql_inserted = mysql_query($sql);

  if($sql_inserted)
  {
    echo "'$anzeigen_id' from $xml_filename inserted<br />";
  }
  else
  {
    throw new Exception(mysql_error() . '. SQL: '.$sql);
  }
}

By the way, as I told you please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Upvotes: 1

symcbean
symcbean

Reputation: 48357

The reason its not working is that you fail to sanitize the input. Consider:

$anzeigen_id = mysql_real_escape_string($row[0]);
$firma_id = mysql_real_escape_string($row[1]);
....
$anzeige_txt = mysql_real_escape_string(utf8_decode($t->nodeValue));

You should be aware of the risks of SQL injection and how to prevent it.

You should also have proper error checking in your code.

Upvotes: 1

mitesh
mitesh

Reputation: 1

Try this.....

$sql = "";
$comma = "";
while ($row = mysql_fetch_array($new_entries)){
    $anzeigen_id = $row[0];
    $firma_id = $row[1];
    $xml_filename = "xml/".$anzeigen_id.".xml";
    $dom = new DOMDocument();
    $dom->load($xml_filename);
    $value = $dom->getElementsByTagName('FormattedPositionDescription');
    foreach($value as $v){
        $text = $v->getElementsByTagName('Value');
        foreach($text as $t){
            $anzeige_txt = $t->nodeValue;
            $anzeige_txt = utf8_decode($anzeige_txt);
            $sql .= "$comma ('$firma_id','$anzeigen_id','$anzeige_txt')";
            $comma = ", ";

        }
    }
}
$sql = "INSERT INTO testing (`firmen_id`,`anzeige_id`,`anzeige_txt`) VALUES $sql";
$sql_inserted = mysql_query($sql);

Upvotes: 0

Related Questions