Tag Archives: mysql

Laravel 4 database seeding example

Database seeding is a convenient way to fill Laravel 4 application with test data, this can be useful while application developing, or testing. In provided example ‘users’ table will be seeded with test user data. Example table was previously created with Laravel Migrations which is described in this post.

First create seeding class for the table under app/database/seeds directory, in this case UserTableSeeder.php

class UserTableSeeder extends Seeder {

	public function run() {
		// delete existing db rows
		DB::table('users')->delete();
		// create new user
		User::create(array(
			'name' => 'test user 1',
			'email' => 'foo@bar.com',
			'password' => Hash::make('password')
		));
	}

}

Method run() deletes all existing users, then creates new user row with provided name, email and password (hashed with Hash::make() function). Please note that ‘User::create’ requires User model to be set up. By default User model is created in app/models, so there is no need of doing that in this particular case.

Next we need to add new seeder class to app/database/seeds/DatabaseSeeder.php

class DatabaseSeeder extends Seeder {

	/**
	 * Run the database seeds.
	 * @return void
	 */
	public function run() {
		Eloquent::unguard();
		$this->call('UserTableSeeder');
	}

}

And this is it, now simply call the following artisan command

$ php artisan db:seed

And check if users table contains test data

mysql> select * from users;
+----+-------------+-------------+--------------------------------------------------------------+----------------+---------------------+---------------------+
| id | name        | email       | password                                                     | remember_token | created_at          | updated_at          |
+----+-------------+-------------+--------------------------------------------------------------+----------------+---------------------+---------------------+
|  1 | test user 1 | foo@bar.com | $2y$10$e2hVN4Yf7.aEOHiVRmN5XORQcuIaHUDege5V9i9792Wfx8DFe03W2 | NULL           | 2014-08-10 14:11:02 | 2014-08-10 14:11:02 |
+----+-------------+-------------+--------------------------------------------------------------+----------------+---------------------+---------------------+

Laravel 4 database migrations example

Laravel 4 migrations provides a convenient way of versioning changes in application database structure. It uses Laravel Schema Builder to store database structure in php classes, this structure is then utilised to create/alter/delete database tables in database that is currently used by application. Schema Builder is database type independent, so any database supported by Laravel can be used. This makes extremely easy to change database type, and keep the same tables structure (for instance from MySQL to SQLite).

Database schema should be altered if any change in database structure has to be done. This way all developers can use the same schema version, as php files can be easily stored in version control system. One important thing to notice about migrations: always create new migration file when you want to alter existing table. Migration class that was used should not be changed.

Let’s create simple ‘users’ table with migrations. Table should contain: id, name, email, password, remember_token, created_at and updated_at fields. First thing to do is set up database connection, this should be done by altering app/config/database.php file. The most commonly MySQL will be used, this requires to set username, password, host, and database name in ‘mysql’ array. When database connection is set up go to project’s root directory, and run:

$ php artisan migrate:make create_users --create --table=users

This command will create a new migration file for users table in app/database/migrations directory (by default). File name contains current date, time, and underscored class name provided after ‘migrate:make’ (create_users). Full path to the file is in my case app/database/migrations/2014_08_10_131305_create_users.php. Edit this file to provide users table fields and details. Remember to always use artisan commands to create migration files, don’t create them manually.

CreateUsers class has 2 methods: up() and down(). Up is being called when ‘php artisan migrate’ command is run (this creates/alters table), down() is being called when ‘php artisan migrate:rollback’ is run (this rollbacks last migration, so deletes users table in this particular case).

Complete CreateUsers code:

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsers extends Migration {

	/**
	 * Run the migrations.
	 * @return void
	 */
	public function up() {
		Schema::create('users', function(Blueprint $table) {
			$table->engine = 'InnoDB';
			$table->increments('id');
			$table->string('name', 128)->nullable();
			$table->string('email', 256);
			$table->string('password', 256);
			// remember token is nullable by default
			$table->rememberToken();
			$table->timestamps();
		});
	}

	/**
	 * Reverse the migrations.
	 * @return void
	 */
	public function down() {
		Schema::table('users', function() {
			Schema::drop('users');
		});
	}

}

Now this class can be used to create table with structure defined in up() method, for this purpose simply run:

$ php artisan migrate

Than log in to mysql and check what tables was created (I assume that database was empty before).

mysql> show tables;
+------------------+
| Tables_in_sm2dev |
+------------------+
| migrations       |
| users            |
+------------------+

mysql> describe migrations;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| migration | varchar(255) | NO   |     | NULL    |       |
| batch     | int(11)      | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

mysql> describe users;
+----------------+------------------+------+-----+---------------------+----------------+
| Field          | Type             | Null | Key | Default             | Extra          |
+----------------+------------------+------+-----+---------------------+----------------+
| id             | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| name           | varchar(128)     | YES  |     | NULL                |                |
| email          | varchar(256)     | NO   |     | NULL                |                |
| password       | varchar(256)     | NO   |     | NULL                |                |
| remember_token | varchar(100)     | YES  |     | NULL                |                |
| created_at     | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at     | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
+----------------+------------------+------+-----+---------------------+----------------+

Migrations table contains data internally used by Laravel to manage migrations, users table contains fields previously defined in CreateUsers class.

Przyczyny błędu ERROR 1005 (HY000) Can’t create table (errno: 150) w MySQL

Pojawiający się podczas próby dodania do bazy danych tabeli błąd “Can’t create table (errno: 150)” związany jest ze zdefiniowanymi w niej kluczami obcymi. MySQL sprawdza poprawność klucza obcego, porównując go z kluczem obcym w tabeli z którą tworzy relację. Ważne jest żeby tabela do której odnosi się klucz obcy istniała, a pole na które  wskazuje, było dokładnie identyczne z polem wskazującym. Poniżej znajduje się lista potencjalnych przyczyn wystąpienia błędu wraz z rozwiązaniami.

Continue reading

Wyszukiwarka fraz z polskimi znakami diakrytycznymi przy użyciu php / mysql

Tworząc w ramach aplikacji webowej wyszukiwarkę treści, spotkałem się z problemem wyszukiwania fraz z polskimi znakami diakrytycznymi (ogonkami). W założeniu napisana w php wyszukiwarka ma zwracać id obiektu (w tym przykładzie notatki), jeżeli w tytule lub treści ma on określony ciąg znaków bez względu na występowanie w nim polskich znaków. Przykładowo fraza “błąd” powinna być odnajdywana zarówno po wpisaniu “błąd” jak i “blad”, ale także “bład” czy “bląd”. W sytuacji odwrotnej, gdy w bazie zapisana jest notatka z frazą bez polskich znaków: “blad”, ma być ona odnajdywana poprawnie po wpisaniu “błąd”, oraz wszystkich wymienionych wyżej wariantów.

O ile problem wydaje się dość złożony, to jego rozwiązanie jest stosunkowo proste. Należy przekazywać do bazy danych frazę pozbawioną przez php polskich znaków i tam porównywać ją z zapisem pozbawionym tych znaków przez mysql. Porównanie odbywa się między dwoma ciągami zawierającymi tylko “zwykłe” znaki, dzięki czemu używanie w zapytaniu polskich ogonków przestaje mieć dla wyniku wyszukiwania jakiekolwiek znaczenie.

Continue reading