Special Characters and MySQLi Prepared Statements

[Shadow Jolteon]

Hello,

 

I've been working on a website for a client, and am having a bit of trouble with mysqli functions... Basically, everything works fine when the SQL I'm using is just selecting from the database, however, when I use update or insert, special characters do not go into the database properly. For example, the accented "é" character is inserted as "é".

 

After doing quite a bit of searching around, as well as reading many pages on php.net, I found one thing that helps for update and insert, adding set_charset( "utf8" ), but this causes the output of select to do the same thing that the other two were previously. Here is some of the code I'm working with right now that is affected by this:

$mysqli = new mysqli( "localhost", "user", "pass", "db" );
$mysqli -> set_charset( "utf8" );

if( $_GET['mode'] == "edit" && $_GET['drink'] && $_POST['e_name'] ) {
    $update = "update `menu` set `name` = ?, `description` = ?, `small` = ?, `medium` = ?, `large` = ?, `flavors` = ?, `section` = ?, `order` = ? where `id` = ?;";

    $id = $_GET['drink'];
    $nName = $_POST['e_name'];
    $nDesc = $_POST['e_desc'];
    $nSml = $_POST['e_prc1'];
    $nMed = $_POST['e_prc2'];
    $nLrg = $_POST['e_prc3'];
    if( $_POST['e_flav'] == "on" )
        $nFlv = 1;
    else
        $nFlv = 0;
    $nSect = $_POST['e_sect'];
    $nOrd = $_POST['e_ordr'];

    if( $changeIt = $mysqli -> prepare( $update ) ) {
            $changeIt -> bind_param( 'ssdddiiii', $nName, $nDesc, $nSml, $nMed, $nLrg, $nFlv, $nSect, $nOrd, $id );
        $changeIt -> execute();
    }

    header( "Location: http://phpfreaks.com/" );
    exit;
}

$mysqli -> close();

 

Additionally, the character set of the database is "utf8_unicode_ci", and I do have the HTTP Content-Type headers set as UTF-8 on both the server and HTML sides.

 

Thank you for reading.

[creata.physics]

How'd you fix it? I'd like to know as I'm having the same issue. Not with select queries, but insert and update.

[Shadow Jolteon]

Uhh, I didn't fix it. I'm not even sure how this got marked as "solved"... .-.

 

I must have accidentally clicked the button at the bottom of the thread... Sorry about that! Hope we can both find a solution to our problem! =)