Wednesday, November 8, 2017

Laravel Query Builder GroupBy Syntax error or access violation

Hello,

Recently I forked a Github project and was updating it. I faced strange issue in using GroupBy in query builder. I was trying to query model as follow.

$projects = ProjectReviews::groupBy('project_id')->orderBy('created_at','DESC')->get();

Basically I wanted to get recently reviewed projects and show dates. But in above query I was getting error Syntax error or access violation project_reviews.id isn't in group by.

That was really strange issue as that's the primary key of table and it should not be part of group by. If you run that query directly in PHP MyAdmin it was working fine. So I was not sure about this. Finally after spending couple of hours I was able to find out the problem.

It's because of strict config settings of database. If you look into config/database.php file there is strict config there.

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

You have to set to false to make GroupBy working.

Following is mentioned in MySql Documentation for this config.

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.
So basically it secures your database operations. However in Laravel it's not working properly. So I was not sure if it's laravel bug. But I had to disable it make this query working.

Disabling it wouldn't make your web app unsecured if you handle all validations in your controllers and follow best practices like Laravel already does.

However I still recommend to be careful, while using this.

Wednesday, September 27, 2017

Ubuntu Keep Process Running After SSH is Terminated

Hello,

Recently I was learning NodeJS and for that I faced an issue with node server. I installed node server via SSH and started server. But as soon as I closed terminal Node server stopped working. But I needed server up and running even if terminal is closed. So after struggle of sometime I found out solution. Here is what we have to do.

Basically when you type command on SSH and start server it runs on the foreground and wait for process to be finished. So as soon as you close terminal foreground process is also terminated and hence your server is stopped.

So in this case you have to run server as background process and if you are using ubuntu you can use nohup  and & to start running process in background hence it will not be terminated once the terminal is closed.

So here is the command you have to follow.

$ nohup "COMMAND" &

Here COMMAND is your command to start the server and since you have mentioned nohup and & it will run the process in background and will not be terminated when SSH is closed.

Thursday, September 7, 2017

jQuery Submit Form with Ajax

Hello,

In this blog we are going to take a look at how we can submit for with Ajax using jQuery.

First of all add jQuery file in your head section of HTML page.

<script src="jquery.min.js"></script>

Now we will have following form.

<form action="">
      <input id="text" autocomplete="off" placeholder="Type here" />
</form>

If you want to submit this form with Ajax using jQuery, first you have to do is add submit event handler.

$(function () {
$('form').submit(function(){
var formData = JSON.stringify($('form').serializeArray());
$.ajax({
type: "POST",
url: "YOUR_API_URL",
data: formData,
success: function(data){

},
failure: function(errMsg) {
}
});
return false;
    });
});

So first we have added submit event handler and used return false so it does refresh the whole page and just use the Ajax request.  Inside event handler we are using HTML 5 FormData to get form data first and then using Ajax request to send data to server in APIs.

Hope this helps you.