I have two tables and I have a query that copies data from one table to another using an insert query.

The data is inserted ok into the table and the query works, but for some reason in alot of the fields for example, phone,fax etc it inserts the first letter of the organisation name (stored in organisationName coloumn)

for example,

PHP Code:

organisationName,  Phone,   Fax,  Email
Organisation1,     O,       O,    O 


and in the same row for the phone, fax and email columns it inserts the first letter of the value stored in the organisationName column, so in the example above it inserts the letter O in each column for that record.

Any ideas why it does this?

Thanks,

There's no reason it SHOULD be doing that, so you'd have to post the exact INSERT query that is being executed for us to identify what is causing it.

It's because of something the developer did. It could be that all the columns are set to only allow 1 character, but that's not it. In SQL Server, if that were the case, you would get a "string or binary data would be truncated" error when you tried to insert. And that also wouldn't explain the first letter being replicated to all columns.

So, it's probably that the part of your insert command that supplies the values is taking the first character from one column, and trying to put it into all of them.

Alternately, it could be a trigger, but this is less common.

In SQL Server, if that were the case, you would get a "string or binary data would be truncated" error when you tried to insert.

Yeah, but 99.999% chances that this is a mysql DB.
Which truncate without notice, who convert a null to blank if the column is set to not nu ll and you try to insert a null and so on....
You see why I consider mysql for a basic toy ?

Anyway, Drew, like Nyef and John said, it's probably the insert statement that is at fault.
Or more precisely, the code that generate the SQL to be used, which mix up the datas.
Try to locate the part which create this statement in your file, and to paste it here.

Thanks for your suggestions guys It wasn't working because I used a vairable twice, once in the while loop and again within the while loop.

Thanks,

Dynamic SQL rears its ugly head once again! I'd just like to point out that using a stored procedure with input parameters would have prevented this from being a problem in the first place.

Originally Posted by tripy

Yeah, but 99.999% chances that this is a mysql DB.
Which truncate without notice, who convert a null to blank if the column is set to not nu ll and you try to insert a null and so on....

Are you serious? I think I just had a mini stroke!

Originally Posted by tripy

You see why I consider mysql for a basic toy ?

Sure do - that's really amazing. I had brought it up for SQL Server assuming that MYSQL would work more or less the same way - probably different error text, but surely still an error, and not a silent failure! Anyway, sorry for the digression.