Cannot get record to insert into mysql data using PHP

Question

I am using PHP to populate a database of company records. I have a field for company name and the currency type that they use.
But when I use PHP the record never gets inserted if the currency is an Enlglish pound sign (£). A dollar sign ($) or any other normal character is no problem. The record with a pound sign inserts just fine when I run the sql statement and enter it directly into MySQL Query Browser. But it just doesn't work when I run the exact same INSERT statement in PHP.

$sql = "INSERT INTO company_test (name, currency) VALUES ('ABC Widgets', '£')";   
$rs = mysql_query($sql, $conn);
//Does not insert a record

$sql = "INSERT INTO company_test (name, currency) VALUES ('ABC Widgets', '$')";   
$rs = mysql_query($sql, $conn);
//A record inserts just fine

//My MySQL version is 5.5.11.  PHP is version 5.3.6.  
//The MySQL table looks like this:
TABLE company_test
FIELD | id       | INTEGER(10) | not null | auto increment
FIELD | name     | VARCHAR(45) | not null
FIELD | currency | VARCHAR(3)

In MySQL Query Browser the "Table Options" tab shows that for this company_test table my charset is utf8.

Thanks.

What is the charset of PHP script? ASCII or UTF8? Do you get any warnings from mysql? Did you tried calling mysql_error()? — Ivan Hušnjak
– Ivan Hušnjak, Commented Aug 9, 2012 at 17:47
It may not help answer your question, but you should stop using mysql_* functions. They're being deprecated. Instead use PDO (supported as of PHP 5.1) or mysqli (supported as of PHP 4.1). If you're not sure which one to use, read this SO article. — Matt
– Matt, Commented Aug 9, 2012 at 17:48
Is it worth maybe increasing the size of the currency field? I've no idea if a pound sign would be stored as one character or as something like £, which wouldn't fit.... — andrewsi
– andrewsi, Commented Aug 9, 2012 at 17:49
Keep in mind that £ is not an ASCII character. Inserting the record correctly will require matching your character sets in PHP, the database connection, and the database itself. — tadman
– tadman, Commented Aug 9, 2012 at 18:01
Also check the character set that the MySQL database is using. The £ character may not be allowed. Take PHP out of the mix and try to do the same query inside MySQL. — Ed Manet
– Ed Manet, Commented Aug 9, 2012 at 18:09

Marc B · Accepted Answer · 2012-08-09 17:47:05Z

1

Check for errors:

$rs = mysql_query($sql, $conn) or die(mysql_error());
                               ^^^^^^^^^^^^^^^^^^^^^

Never assume a query succeeds. Even if your sql syntax is perfect, there's far too many other reasons for failure to NOT check.

answered Aug 9, 2012 at 17:47

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thread7 · Accepted Answer · 2012-08-09 20:28:16Z

After some Googling I finally found something that worked. Somebody with a similar probably created a PHP function that converts the character set of the sql statement.
Now my query looks like this before being inserted: INSERT INTO company_test (name, currency) VALUES ('ABC Widgets', 'Â£') Notice the funny little character before the pound sign.

Anyways here is the function:

function TtoUtf8( &$string, $encType = 'ISO-8859-1' ) 
{ 
    $enc    = mb_detect_encoding( $string ); 
    $enc ? 
        $enc = $enc : 
        $enc = $encType; 
    if ( $enc == 'UTF-8' ) 
    { 
        return $string; 

        // end if $enc == UTF-8 
    } else 
    { 
        $conv = iconv( $enc, 'UTF-8//TRANSLIT', $string ); 
        $conv ? 
            $ret = &$conv : 
            $ret = &$string; 
        return $ret; 
    }

}

raygo · Accepted Answer · 2012-08-09 17:49:01Z

-1

Why don't you convert it first to htmlentities or htmlspecialchar:

$currency = htmlspecialchars('£');
$sql = "INSERT INTO company_test (name, currency) VALUES ('ABC Widgets', '{$currency}')";

Also, what type of datatype is your currency field?

answered Aug 9, 2012 at 17:49

raygo

1,4285 gold badges19 silver badges42 bronze badges

4 Comments

Thread7 Over a year ago

I increased the varchar size of the curreny field to 10. That didn't work. The currency field is datatype utf8. I just output the value of the mysql_error and get this -> 1366: Incorrect string value: '\xA3' for column 'currency' at row 1

tadman Over a year ago

htmlspecialchars is nowhere close to escaping this for SQL. Please use the proper methods.

Thread7 Over a year ago

Yea, I tried raygo's htmlspaecialchars function and received the same error.

raygo Over a year ago

Try to put SET NAMES latin1; in before the INSERT $sql = "SET NAMES latin1; INSERT INTO ..."

Collectives™ on Stack Overflow

Cannot get record to insert into mysql data using PHP

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related