sql insert not inserting after some records

Question

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 />";
            }

        }
    }
}

no i dont get any errors. if i check like ``echo $sql_inserted;`` i am getting nothing, — doniyor, Dec 03 '12 at 10:21
`var_dump($sql)` and test the sql directly using phpMyAdmin (for example) — j0k, Dec 03 '12 at 10:25
``var_dump($sql)`` is giving ``bool(false)``, so the query isnot working. but how can it be? may be there is some limit in the database for data? but i have now only 2920 rows — doniyor, Dec 03 '12 at 10:26
Are you sure you test: `var_dump($sql)`, because it must be a string, I don't ask you to `var_dump($sql_inserted);` — j0k, Dec 03 '12 at 10:29
i am getting the whole interpreted sql statement with strings inside — doniyor, Dec 03 '12 at 10:33
What if you tried to launch this query inside phpMyAdmin now? And by the way, [you shouldn't use `mysql_` function any more](http://stackoverflow.com/q/12859942/569101)! — j0k, Dec 03 '12 at 10:36
in phpadmin, it is working, i am able to insert, but in the code i cannot. i think, the ``mysqli_*`` isnot the reason here — doniyor, Dec 03 '12 at 10:54
Add an `else` after the `$sql_inserted` and add this snippet to show the error from mysql: `echo mysql_errno() . ": " . mysql_error() . "\n";` — j0k, Dec 03 '12 at 11:03
okay, this is powerful debugging: i got this with your code: ``1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Design, Implementierung und Integration von Fachanwendungen Entwicklung mit Hi' at line 1`` — doniyor, Dec 03 '12 at 11:40

score 1 · Accepted Answer · edited May 25 '19 at 14:47

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.

score 1 · Answer 2 · answered Dec 03 '12 at 12:19

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.

score 0 · Answer 3 · answered Dec 03 '12 at 10:42

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);

sql insert not inserting after some records

3 Answers3