Value has question mark infront when I use echo

[nimdraal]

Hi. Don't know where to put this, but I am guessing that I have a problem with my sql table. 

 

I have a column who has the setup varchar(10), utf8_general_ci 

 

At one row I have the value 58.0326, when I do a query and echo the data it outputs exactly "58.0326".

But in another row I have the value 63.44528 but when I echo that it comes out "?63.44528" on the webpage. 

 

 

I have tried to search and google for an answer to this problem but havent been able to find anything. Hope one of you php freaks can help me.

[nimdraal]

Also: 

 

Server: Unix xocket

Software: MySQL

Version: 5.1.70-cll MySQL Community Server 

Protokoll 10

 

 

Webbserver

cpsrvd 11.38.1.6

client version libmysql - 5.0.96

[requinix]

Don't use VARCHAR for numeric data.

 

That particular value has some character in front of it. What is it?

[nimdraal]

I imported a csv file to the sql database, hence the varchars everywhere. Since I am quite unexperienced to sql and php I wanted to get my homepage up and running and then deal with trimming the database and dealing with validations and stuff like that. 

 

Would the VARCHAR crop the data? I suspect I should use float instead, but still... does it explain why some numbers come out right on the homepage and some dont with the same sql querys? 

 

The particular value has an exclamation mark (?) infront of it when I use a query and then echo the value to my homepage. When I look at the value in the database it looks fine. And with the same query most of the rows output just fine.

[requinix]

It would truncate the strings to 10 characters but that probably wouldn't pose a problem for you unless you're dealing with numbers in the millions. And yes, you should use either a float or a decimal type for that data (probably float).

 

There is something there, even if you can't see it "in the database". Have you looked for it in the CSV?

[nimdraal]

Yes. I checked the csv file and That particular field was blank when I imported the file. This value that ends up having the question mark infro t was added manually after the import. I did this through phpmyadmin.

 

I tried deleting the value and adding it again without any result. I also tried to export the current sql to a csv file to see if there was something infront of the value, but there wasnt anything there.

 

When I did the import I had another column with coordinates in degrees with the degree character. These values was corrupt after the import, only the first two digits was left... Could this have made the whole db corrupt in some way?

[requinix]

Then it might be a problem with whatever you're using to manually set those values. If it's just a one-time thing (it's not like the problem keeps popping up in places where it wasn't before) then I'd say just fix the values manually. I'm not sure what this "deleting the value and adding it again" means but try just issuing an UPDATE query (or maybe DELETE and INSERT?) with the right value. Typed manually so you're sure there's nothing else in there.

 

Could that be a degree character °? Yeah, but there's no way to "un-question mark it" to know. Would something have corrupted the entire database? No, but it didn't sound like there were widespread problems and that it was just this value (or maybe a couple others).

[kicken]

SELECT HEX(field) FROM table

That should show you a hex representation of the field, you could find out what the ? character is that way.

[nimdraal]

That is a good tip kicken. However I am not sure on how to do that correct. 

 

If I do a command in the sql command prompt like this: 

 

SELECT HEX(name of column) 
FROM `namn of database` 
WHERE relid LIKE 3 

// relid 3 is that particular row... 
 

 

Would that give me the correct output?

[nimdraal]

Then it might be a problem with whatever you're using to manually set those values. If it's just a one-time thing (it's not like the problem keeps popping up in places where it wasn't before) then I'd say just fix the values manually. I'm not sure what this "deleting the value and adding it again" means but try just issuing an UPDATE query (or maybe DELETE and INSERT?) with the right value. Typed manually so you're sure there's nothing else in there.

 

Could that be a degree character °? Yeah, but there's no way to "un-question mark it" to know. Would something have corrupted the entire database? No, but it didn't sound like there were widespread problems and that it was just this value (or maybe a couple others).

 

I used the GUI of phpmyadmin to erase and then input the data again. 

 

Now I tried both update query and delete and insert but I still get that pesky question mark... I entered the values manualy to be sure that there wasn't an issue with copy and paste. This has happened on a couple of other places in this particular column, say 50% of the values I enter after the import of the csv file has a ? infront of the values. 

[nimdraal]

Apparently when I replace the field with 'NULL' and then reenter it the mystic question mark is gone. Updating apparently just updates the value that I see in the GUI. I am guessing that this is due to that something went wrong with the import of the file since there are special characters and a charset with Swedish characters in it. Thanks for the help.