CODEWITHSUNDEEP
phpmysql
cletus
cletus

Reputation: 625007

PHP MySQL insert dropping data

Ok this is a new one for me. Basically I have a table articles:

id: int auto increment
title: varchar(200)
description: varchar(1000)
ctext: longtext
chtml: longtext

Now I do an insert into this table with mysql_query:

INSERT INTO articles
(title, description, ctext, chtml)
VALUES
('$title', '$description', '$text', '$html')

All values have been passed through mysql_escape_string().

The text and html here are roughly 50k in size (so I can't really post the fully query here).

Now, here's the problem: the query works. A new row is inserted. However the ctext and chtml columns are empty. This is MySQL 5.0.51a and PHP 5.2.8. No errors are raised of any kind as far as I can tell.

Now I dumped the query out to a file in /tmp and ran it with:

mysql -u username -p dbname < /tmp/query

Same thing.

I copy the query into Navicat and it... works.

So what on earth is going on?

Upvotes: 0

Views: 493

Answers (2)

lpfavreau
lpfavreau

Reputation: 13211

Some random thoughts:

  • Have you tried controlling the text length see if it only fails at one point?
  • What kind of connection are you opening? Which driver?
  • Have you checked the encoding of your connection? Some invalid characters might be sent in.
  • Have you tried using parameters instead of mysql_escape_string?
  • Have you tried executing directly from the same file from Navicat instead of using the copy-paste? Again, might be related to an invalid character that's not passed through the copy-paste but was saved in the file.
  • Just to cover the basics we so often forget, how do you verify that the data is not inserted? I mean, how to you visualize it? You could have a line break that hides the first lines from 2 out of 3 means of visualization. Just a long shot, but I've seen it happen.

Addition: MySQL connections defaults to latin1, you need to use something like mysql_query("SET NAMES 'utf8'") to transfer unicode characters.

Upvotes: 2

St. John Johnson
St. John Johnson

Reputation: 6660

I'm not sure if this matters, but mysql_escape_string is deprecated and replaced by mysql_real_escape_string

Have you tried it with smaller text?

Upvotes: 1

Related Questions