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, Aug 09 '12 at 17:47
It may not help answer your question, but you should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this SO article](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons). — Matt, Aug 09 '12 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, Aug 09 '12 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, Aug 09 '12 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, Aug 09 '12 at 18:09
Currency should be specified using the correct [ISO code](http://en.wikipedia.org/wiki/ISO_4217) with a look-up table that converts from codes to formatted currency values. Inserting currency symbols is useless since Mexico, Costa Rica and the United States all use '$' to mean completely different things. — tadman, Aug 09 '12 at 21:02
pass value from javascript like this-> var x=escape($('#xyz'.val())); AND Receive value in PHP like this ->$x=mysql_real_escape_string($_REQUEST['xyz']); Then pass $x into sql query.... $sql = "INSERT INTO company_test (name, currency) VALUES ('ABC Widgets', '$x')"; Its worked for me .. — sac, Jun 14 '17 at 10:10

score 1 · Accepted Answer · answered Aug 09 '12 at 17:47

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 09 '12 at 17:47

Marc B

340,537
37
382
468

score 1 · Answer 2 · answered Aug 09 '12 at 20:28

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; 
    }

}

score -1 · Answer 3 · answered Aug 09 '12 at 17:49

-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 09 '12 at 17:49

raygo

1,298
2
17
39

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 – Thread7 Aug 09 '12 at 17:57
`htmlspecialchars` is **nowhere** close to escaping this for SQL. Please use the proper methods. – tadman Aug 09 '12 at 18:00
Yea, I tried raygo's htmlspaecialchars function and received the same error. – Thread7 Aug 09 '12 at 18:07
Try to put SET NAMES latin1; in before the INSERT $sql = "SET NAMES latin1; INSERT INTO ..." – raygo Aug 09 '12 at 18:31

Cannot get record to insert into mysql data using PHP

3 Answers3

Linked