1

I have been looking for an answer for a couple days, and thus far haven't found an answer...

I am building a website for a client, and am using a MySQL database to store data for a menu, and am using PHP mysqli prepared statements for working with the database. When I use a form to update or add rows, special characters are inserted wrong, accented "é" characters become é when submitted. Though, if I enter them in manually though phpMyAdmin, they show up just fine.

I have had a bit of luck by adding this after the $mysqli object, as recommended here 

$mysqli -> set_charset( "utf8" );

This only seems to make it work with the update and insert commands, anything selected now shows the "tilda A copyright" in place of the accented e's, so it's basically the same problem, but reversed.

Here is some of my PHP that is affected:

$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://stackoverflow.com/" );
    exit;
}

$mysqli -> close();
BenMorel
  • 34,448
  • 50
  • 182
  • 322
Kitdrewne
  • 11
  • 1
  • 2

1 Answers1

0

You should use 

query("SET NAMES utf8");

SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES 'utf8' tells the server, “future incoming messages from this client are in character set utf8.” It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use a SELECT statement.)

Also make sure

You set proper storage encoding for your table/db

CREATE TABLE{
...
}CHARSET=utf8;

or 

CREATE DATABASE  DEFAULT CHARACTER SET utf8

Your html output is of utf-8 charset.

Content-Type: text/html;charset=utf-8

Now, your database/table will store values in utf-8. data will be sent and received in utf-8 and the page will display it in utf-8. This should solve your problem.

anujkk
  • 1,333
  • 3
  • 14
  • 22
  • Thank you for your reply! Unfortunately, I've tried all of this and made sure everything is correct, but I'm getting the same results... =/ – Kitdrewne Sep 17 '11 at 06:33
  • Ah, I think I've just figured it out, though, I am not really sure why this is working... I used set_charset function with "utf8_general_ci" in place of what was there previously, which was just "utf8", which I had added after reading another page here. – Kitdrewne Sep 17 '11 at 06:36
  • Actually, it seems it did not work after all... It's just back to where I was before, with select working, and update/insert not working... =/ – Kitdrewne Sep 17 '11 at 06:40