How to insert emojis in MySQL using Laravel?

My MySQL DB collation is: utf8mb4_unicode_ci As shown below:

However when I try to insert it in the table using Laravel – it throws the following error:

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: 'xF0x9Fx98x89' for column `challenges`.`challenges`.`description` at row 1 (SQL: insert into `challenges` (`hashtag`, `title`, `description`, `challenge_id`, `image_name`, `category_id`, `duration`, `link`, `user_id`, `updated_at`, `created_at`) values (emojiasd, Emoji Test, Hey this is an emoji 😉, 5edccab9327c4, challenge-55edccab9327e81591528121.png, 2, 0, ?, 5ec443a71e40b, 2020-06-07 11:08:41, 2020-06-07 11:08:41))

And this is the store method in my Controller:

public function store(Request $request)
    {
        //
        $this->validate($request, [
            'hashtag' => 'required',
            'title' => 'required',
            'description' => 'required',
            'category' => 'required',
            'image' => 'required|image|mimes:jpeg,png,jpg|max:2048',
            'duration' => 'required'
        ]);

        $challenge = new Challenge;
        $replace = array (' ', '#');
        $challenge->hashtag = strtolower(str_replace($replace, '', $request->input('hashtag')));
        $challenge->title = $request->input('title');
        $challenge->description = $request->input('description');
        $challenge->challenge_id = uniqid();

        $image = $request->file('image');
        $imageName = 'challenge-' . uniqid(5) . time() . '.' . $image->extension();
        $image->move(storage_path('app/public/challenges/'), $imageName);

        $challenge->image_name = $imageName;
        $challenge->category_id = $request->input('category');
        if ($request->input('duration') == 1) {
            $challenge->duration = $request->input('duration_days');
        } else {
            $challenge->duration = 0;
        }
        $challenge->link = $request->input('link');
        $challenge->user_id = Auth::id();
        $challenge->save();

        return redirect('/')->with('success', 'Challenge Created');
    }

UPDATE Here is a picture of the challenges table config:

Answer

This is more of a Database and migration end issue then on your laravel as you’re getting an incorrect format error. Please try:

1) Database: Change Database default collation as utf8mb4. Looks all done

2) Table: Change table collation as CHARACTER SET utf8mb4 COLLATE utf8mb4_bin.

Query:

ALTER TABLE Tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

3) Code:

Run an insert query to get an intial test of being able to handle emoji insert into tablename (columnname) values ''"

4) Set utf8mb4 in database connection:

Use raw mysql query to write the update table migration script and run php artisan migrate command

use IlluminateDatabaseMigrationsMigration;

class UpdateTableCharset extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
            DB::unprepared('ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down() {
            DB::unprepared('ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8');
    }
}

Note: You still have to keep the database config to utf8mb4

Hope this will help you

—

How to Fix the Error Code: 1833

The type and definition of the foreign key field and reference must be equal. This means your foreign key disallows changing the type of your field. What you want to do is drop the foreign key and recreate it after running the new query. You have to disallow writing to the database while you do this, otherwise, you risk data integrity problems.

LOCK TABLES 
user_challenges WRITE,
NAMEOFOTHERTABLE WRITE;

ALTER TABLE user_challenges
 DROP FOREIGN KEY challenge_id,

Now run the query:

ALTER TABLE Tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

Recreate the foreign key:

ALTER TABLE user_challenges
    ADD CONSTRAINT foreignkeyname FOREIGN KEY (challenge_id)
          REFERENCES OTHERTABLE (challenge_id);

UNLOCK TABLES;

I’ve added a write lock above ^

All writing queries in any other session than your own ( INSERT, UPDATE, DELETE ) will wait till timeout or UNLOCK TABLES; is executed

http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html

Let me know how that goes or if you encounter any errors, I tried to match it to your case but you may need to make some adjustments to the naming conventions