Laravel 4 cannot run whole RAW queries

Question

I would like to use the DB class of laravel to execute a mysql query but none of the functions provided by Laravel are working.

None of those is working: DB::statment() / DB::select() / DB::raw() / DB::update() / DB::select(DB::raw())

Here is the code I would like to query:

DROP TABLE users;

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `u_username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `u_email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `u_regdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `u_birthday` date NOT NULL DEFAULT '0000-00-00',
  `u_lastlogin` int(11) NOT NULL,
  `u_logcout` int(11) NOT NULL DEFAULT '0',
  `u_level` tinyint(1) NOT NULL DEFAULT '0',
  `u_language` tinyint(1) NOT NULL DEFAULT '0',
  `u_status` tinyint(1) NOT NULL DEFAULT '0',
  `u_gender` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO users VALUES("1","admin","admin@example.com","$2y$08$9sBJh7iyF9Yr6xvsieNmBOsotGPBkZFydVjb/Yk5Fzh4IGBVo7Je6","0000-00-00 00:00:00","0000-00-00","0","0","9","0","0","0");
INSERT INTO users VALUES("2","moderator","mod@example.com","$2y$08$15/tIKPM/8gATSzkmey5tuAA/PL4lJeFq7liTETyz0h1dkOotwp3G","0000-00-00 00:00:00","0000-00-00","0","0","0","0","0","0");
INSERT INTO users VALUES("3","helper","help@example.com","$2y$08$nTtZU9/UBeRLbYJRO/XwCe2D/B57ofx2bCN8vGEiHkqO.bPra0WT6","0000-00-00 00:00:00","0000-00-00","0","0","0","0","0","0");
INSERT INTO users VALUES("4","dude12","dude@example.com","$2y$08$y0JweKtWxJFRF7Ko8q0zkODY.EWEKJ.CR1dDco6aCJh8ssKdzQ6RC","0000-00-00 00:00:00","0000-00-00","0","0","0","0","0","0");
INSERT INTO users VALUES("5","girl1","girl@example.com","$2y$08$UKjJzxDuYW7upqeLsm1VOOo2jUoqMaai0/1jFxvLDzC6eWjin3yOe","0000-00-00 00:00:00","0000-00-00","0","0","0","0","0","0");

I have looked and tried methods from those two topics too but nothing is working:

cannot-run-raw-query-in-laravel-4

laravel-4-how-to-run-a-raw-sql

Error returned when executing with "DB::select( DB::raw($query) )" or DB::statement :

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `u_use' at line 3 (SQL: DROP TABLE users; CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `u_username` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `u_email` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `u_regdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `u_birthday` date NOT NULL DEFAULT '0000-00-00', `u_lastlogin` int(11) NOT NULL, `u_logcout` int(11) NOT NULL DEFAULT '0', `u_level` tinyint(1) NOT NULL DEFAULT '0', `u_language` tinyint(1) NOT NULL DEFAULT '0', `u_status` tinyint(1) NOT NULL DEFAULT '0', `u_gender` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO users VALUES("1","admin","admin@example.com","$2y$08$9sBJh7iyF9Yr6xvsieNmBOsotGPBkZFydVjb/Yk5Fzh4IGBVo7Je6","0000-00-00 00:00:00","0000-00-00","0","0","9","0","0","0"); INSERT INTO users VALUES("2","moderator","mod@example.com","$2y$08$15/tIKPM/8gATSzkmey5tuAA/PL4lJeFq7liTETyz0h1dkOotwp3G","0000-00-00 00:00:00","0000-00-00","0","0","0","0","0","0"); INSERT INTO users VALUES("3","helper","help@example.com","$2y$08$nTtZU9/UBeRLbYJRO/XwCe2D/B57ofx2bCN8vGEiHkqO.bPra0WT6","0000-00-00 00:00:00","0000-00-00","0","0","0","0","0","0"); INSERT INTO users VALUES("4","dude12","dude@example.com","$2y$08$y0JweKtWxJFRF7Ko8q0zkODY.EWEKJ.CR1dDco6aCJh8ssKdzQ6RC","0000-00-00 00:00:00","0000-00-00","0","0","0","0","0","0"); INSERT INTO users VALUES("5","girl1","girl@example.com","$2y$08$UKjJzxDuYW7upqeLsm1VOOo2jUoqMaai0/1jFxvLDzC6eWjin3yOe","0000-00-00 00:00:00","0000-00-00","0","0","0","0","0","0"); ) (Bindings: array ( ))

can you show the error that it is giving while excuting the query with DB class? — saran banerjee, Jul 13 '13 at 13:24
added the error returned to the main question. when i try executing the query with phpmyadmin, it works perfectly — Omer M., Jul 13 '13 at 13:31

score 25 · Accepted Answer · answered Jul 13 '13 at 14:08

25

Executing SQL Code just like the one I provided in the question can be executed using

DB::unprepared( $code );

I hope this helps the people with the same issue i had :D

Answer found while looking through laravel issues

answered Jul 13 '13 at 14:08

Omer M.

630
7
17

@OmerM. How are you incorporating DB class in your system. Did you create a Model or called directly in controller? – Volatil3 Jan 14 '14 at 12:47
1

@Volatil3 For this project I used the DB class in a PHP Command Class. I wanted to create an artisan command which i could use to export and import my db. You can use the DB Class anywhere in your whole Project. In your controller or your model. – Omer M. Jan 14 '14 at 18:32

saran banerjee · Answer 2 · 2013-07-13T14:01:31.420

2

Drop the users table from database then try this and let me know whether it works or not:

DB::insert('CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `u_username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `u_email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `u_regdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `u_birthday` date NOT NULL DEFAULT '0000-00-00',
  `u_lastlogin` int(11) NOT NULL,
  `u_logcout` int(11) NOT NULL DEFAULT '0',
  `u_level` tinyint(1) NOT NULL DEFAULT '0',
  `u_language` tinyint(1) NOT NULL DEFAULT '0',
  `u_status` tinyint(1) NOT NULL DEFAULT '0',
  `u_gender` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci');

//and so on for all the other queries.

edited Jul 13 '13 at 14:01

answered Jul 13 '13 at 13:50

saran banerjee

2,895
1
13
12

DB::query does not exist in laravel 4, so I used DB::insert and it worked. I guess executing multiple queries at the same time is not possible but executing them one by one is working – Omer M. Jul 13 '13 at 13:56
yes that is correct you need to execute the query individually and thanks for telling about the DB::query not existing in laravel 4.I will change the answer as such with insert() – saran banerjee Jul 13 '13 at 14:00
1

Just found out there is a function for executing raw mysql code: DB::unprepared( $code ); https://github.com/laravel/framework/pull/54 – Omer M. Jul 13 '13 at 14:05

score 2 · Answer 3 · answered Jul 13 '13 at 21:01

What about this code? I find it pretty usefull to use migrations & db seeding - especialy for deployment.

Schema::create('users', function($table){
    $table->increments('id');
    $table->string('u_username');
    $table->string('u_email');
    $table->string('password');
    $table->datetime('u_regdate');
    $table->date('u_birthday');
    $table->integer('u_lastlogin');
    $table->integer('u_logcout')->default(0);
    $table->tinyinteger('u_level')->default(0);
    $table->tinyinteger('u_language')->default(0);
    $table->tinyinteger('u_status')->default(0);
    $table->tinyinteger('u_gender')->default(0);
});


// Repeat this for other users as well
User::create([
    'u_username' =>     'admin',
    'u_email' =>        'admin@example.com',
    'password' =>       Hash::make('users-password'),
    'u_regdate' =>      date('Y-m-d H:i:s'),
    'u_birthday' =>     '1980-01-01',
    'u_lastlogin' =>    0,
    'u_logcout' =>      0,
    'u_level' =>        9,
    'u_language' =>     0,
    'u_status' =>       0,
    'u_gender' =>       0,
]);

Laravel 4 cannot run whole RAW queries

3 Answers3

Linked