Can't Insert String To Mysql

[ayoksus]

Hi.

I've got a weird problem with mysql.

I import data's from XML to mysql. Everything goes fine until the part where I need to insert a string value to "name" field. I swear I've got this all good. I've even echoed the value before insert it to database. But the values just not goes in to the database.

 

I just have a simple insert query mysql_query("INSERT INTO items (name, description, etc) VALUE ('$name','$desc','etc')");

 

So for the first 12 rows it inserted the $name into name field, however from row 13th to somewhere in row 1000th, the field 'name' are just empty (only field 'name', so description, etc are filled in), then continue insert the data until the rest. I have tried to echo the empty data's, they are not empty. What could possibly happen to this?? How can I debug this?

 

Help would be appreciated. Thank you.

 

Ayok

[Pikachu2000]

What does mysql_error() show you? What's the value that craters the query?

[ayoksus]

There is no error. That's the problem. It's just inserted all the data's and left some "name" field blank.

 

This is my xml example

 

<items>
   <item>
       <title>
           <![CDATA[ WRC logo 'groot' ]]>
       </title>
       <link>
           <![CDATA[
           http://www.link.nl/c/
           ]]>
       </link>
       <description>
           <![CDATA[ description ]]>
       </description>
   </item>
</items>

 

The title goes to "name" field.

[Pikachu2000]

You haven't posted enough detail for anyone to be able to help, I'm afraid.

[DavidAM]

There is no error. That's the problem. It's just inserted all the data's and left some "name" field blank.

 

This is my xml example

 

<items>
   <item>
       <title>
           <![CDATA[ WRC logo 'groot' ]]>
       </title>
       <link>
           <![CDATA[
           http://www.link.nl/c/
           ]]>
       </link>
       <description>
           <![CDATA[ description ]]>
       </description>
   </item>
</items>

 

The title goes to "name" field.

 

If you are not escaping the data, that example is going to fail. The single-quotes in the data are being treated as quotes in the query and causing it to fail.

 

There will be no PHP error, but there should be an error from the database. If you are using mySql, you need to have a look at mysql_error and mysql_real_escape_string

 

Since you have not posted any code, we cannot do any more than guess. If you are not using mySql, then you need to look at the API for the database you are using.

[fenway]

Echo the actual string.

[ayoksus]

If you are not escaping the data, that example is going to fail. The single-quotes in the data are being treated as quotes in the query and causing it to fail.

 

There will be no PHP error, but there should be an error from the database. If you are using mySql, you need to have a look at mysql_error and mysql_real_escape_string

 

Since you have not posted any code, we cannot do any more than guess. If you are not using mySql, then you need to look at the API for the database you are using.

 

Ok.. maybe I wasn't clear enough. I'm working on a framework where there is database class already. Mysql_error is included there, so I will get mysql error if there is an error. I can even debug it and see how the queries look, but everything looks normal. However if I check the table, some fields "name" are empty.

 

I've tried with mysql_real_escape_string or htmlentities.

[ayoksus]

I've tried to echo one of the query which doesn't fill in the 'name' field.

INSERT INTO proads_articles (name, description, category_id) VALUES ('TOMTOM navigatiesysteem \'Start Classic EU\'', '* 3,5 inch touchscreen * IQ Routes', '3597')

 

I use mysql_real_escape_string to escape the quotes.

It inserted the rest but not 'name': 'TOMTOM navigatiesysteem \'Start Classic EU\''.

And it's not happened to all rows.

Edited November 24, 2012 by ayoksus

[DavidAM]

If you copy-paste that query into mysqladmin or the mysql command-line, does it succeed? Report any errors?

 

If you skip the first 12 entries, so the first insert is for #13, does it fail or does it work for 12 entries and fail at a different spot?

 

How is that column defined? Could it be that the value is too long for the field? I think mysql would truncate it rather than discard it.

 

What patterns do you see in the data that fails? Do they all have single-quotes, long lengths, ??? ?

 

I really cannot think of a reason why mysql would discard a single column value from an insert with multiple columns. Have you tried rebuilding the table?

 

What process are you using to check for the data? Could it be the view process that is failing? or have you checked the database directly.

[ayoksus]

My problem is even getting weirder.

I'm trying to import data's from an xml file to mysql table. Because of this problem I echo all the queries.

 

But I don't understand.. the result in database is different with the queries I've echoed..?

I got this queries when I echoed it.

INSERT INTO proads_articles (name, description, category_id, addl_category_id, keywords) VALUES ('TOMTOM navigatiesysteem \'Start Classic EU\'', '* 3,5 inch touchscreen
* IQ Routes; voor alle wegen de werkelijke reistijden en geeft exacte aankomsttijden Met kaarten van West- en Midden-Europa', '3597',0, 'Navigatie/TomTom navigatie Navigatie/TomTom navigatie ')

 

But in the table I got different result where the title is blank and it gives different value for the "category_id" field... How come?

If I insert this query through phpmyadmin, it gives me an expected result.

Edited November 25, 2012 by ayoksus

[ayoksus]

I've found the problem. It's updated again somewhere in the script. My stupid mistake. Thank you for your helps, guys.