MySQL/Eloquent force id by left join even if null

I have this query wrote in Eloquent :

I have this query wrote in Eloquent :

    return TypeModel::with(['apt' => function($q) use ($id){
        $q->leftJoin('build_apt', function($join) use($id){
            $join->on('build_apt.id_apt', '=', 'apt.id_apt');
            $join->on('build_apt.id_build', '=', DB::raw($id->id_build));
        })
    }])
    ->get()
    ->toJson();

Equivalent SQL :

SELECT * 
FROM `apt` 
LEFT JOIN `build_apt` 
    ON  `build_apt`.`id_apt` = `apt`.`id_apt` 
    AND `build_apt`.`id_build` = 1 
WHERE `apt`.`id_typeapt` in (1, 2, 3, 4, 5)

I have the result I need except one thing, the id given is null :

[
    {"id_typeapt":1,"apt":[
        {"id_apt":null,"image_apt":"apt_1.png"}, 
        {"id_aptitude":null,"image_apt":"apt_2.png"}
    ]
]

How can I force it to look for the id from the table "apt" and not giving me "null" as a result?

Thanks you!

EDIT : Where clause is coming from the with

public function apt(){
    return $this->hasMany(AptModel::class, 'id_typeapt', 'id_typeapt');
}

EDIT2 :

id_apt was crushed by the other id_apt with a simple rename I could retrieve the id :

    return TypeModel::with(['apt' => function($q) use ($id){
        $q->leftJoin('build_apt', function($join) use($id){
            $join->on('build_apt.id_apt', '=', 'apt.id_apt');
            $join->on('build_apt.id_build', '=', DB::raw($id->id_build));
        })
    }])->select(DB::raw("*, apt.id_apt as id_apt);
  }])


Laravel 5.8 Tutorial: Build your First CRUD App with Laravel and MySQL (PHP 7.1+)

Laravel 5.8 Tutorial: Build your First CRUD App with Laravel and MySQL (PHP 7.1+)

Laravel 5.8 Tutorial: Build your First CRUD App with Laravel and MySQL (PHP 7.1+)

Originally published at techiediaries.com on 12 Mar 2019

Throughout this tutorial for beginners you'll learn to use Laravel 5.8 - the latest version of one of the most popular PHP frameworks - to create a CRUD web application with a MySQL database from scratch and step by step starting with the installation of Composer (PHP package manager) to implementing and serving your application.

Note: Laravel 5.8 is recently released and this tutorial is upgraded to the latest version.
Also read: Laravel 5.8 REST CRUD API Tutorial - Build a CRM [PART 1]: Eloquent Models and Relationships
Laravel 5.8 New Features

Let's start our tutorial by going through the most important features introduced in this version.

  • The hasOneThrough Eloquent relationship.
  • Better email validation,
  • Auto-Discovery Of Model Policies provided that the model and policy follow standard Laravel naming conventions
  • DynamoDB cache and session drivers,
  • Added support for PHPUnit 8.0 for unit testing,
  • Added support for Carbon 2.0, an easy to use PHP API extension for DateTime,
  • Added support Pheanstalk 4.0: a pure PHP 5.3+ client for the beanstalkd workqueue, etc.

The Laravel 5.8 version has also corrected numeroous bugs and introduced many improvements of the Artisan CLI.

Check out the official docs for details features of Laravel 5.8

Prerequisites

This tutorial assumes you have PHP and MySQL installed on your system. Follow the instructions for your operating system to install both of them.

You also need to be familiar with Linux/macOS bash where we'll be executing the commands in this tutorial.

Familiarly with PHP is required since Laravel is based on PHP.

For development I will be using a Ubuntu 16.04 machine so the commands in this tutorial are targeting this system but you should be able to follow this tutorial in any operating system you use.

Installing PHP 7.1

Laravel v5.8 requires PHP 7.1 or above so you need the latest version of PHP installed on your system. The process is straightforward on most systems.

On Ubuntu, you can follow these instructions.

First add the ondrej/php PPA which contains the latest version of PHP:

$ sudo add-apt-repository ppa:ondrej/php
$ sudo apt-get update

Next, install PHP 7.1 using the following command:

$ sudo apt-get install php7.1

If you are using Ubuntu 18.04, PHP 7.2 is included in the default Ubuntu repository for 18.04 so you should be able to install it using the following command:

$ sudo apt-get install php
This tutorial is tested with PHP 7.1 but you can also use more recent versions like PHP 7.2 or PHP 7.3

Installing the Required PHP 7.1 Modules

Laravel requires a bunch of modules. You can install them using the following command:

$ sudo apt-get install php7.1 php7.1-cli php7.1-common php7.1-json php7.1-opcache php7.1-mysql php7.1-mbstring php7.1-mcrypt php7.1-zip php7.1-fpm php7.1-xml
Installing PHP Composer

Let's start our journey by install Composer, The PHP package manager.

Navigate in your home directory, then download the installer from the official website using curl:

$ cd ~
$ curl -sS https://getcomposer.org/installer -o composer-setup.php

You can then install composer globally on your system by using the following command:

$ sudo php composer-setup.php --install-dir=/usr/local/bin --filename=composer

As of this writing Composer 1.8 will be installed on your system. You can make sure your installation works as expected by running composer in your terminal:

$ composer

You should get the following output:

   ______
  / ____/___  ____ ___  ____  ____  ________  _____
 / /   / __ \/ __ `__ \/ __ \/ __ \/ ___/ _ \/ ___/
/ /___/ /_/ / / / / / / /_/ / /_/ (__  )  __/ /
\____/\____/_/ /_/ /_/ .___/\____/____/\___/_/
                    /_/
Composer version 1.8.0 2018-12-03 10:31:16

Usage:
command [options] [arguments]

Options:
-h, --help Display this help message
-q, --quiet Do not output any message
-V, --version Display this application version
--ansi Force ANSI output
--no-ansi Disable ANSI output
-n, --no-interaction Do not ask any interactive question
--profile Display timing and memory usage information
--no-plugins Whether to disable plugins.
-d, --working-dir=WORKING-DIR If specified, use the given directory as working directory.
-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug

For more information check out this tutorial.

If you've successfully installed Composer in your system, you are ready to create a Laravel 5.8 project.

Installing and Creating a Laravel 5.8 Project

In this section we'll introduce Laravel and then proceed it to install and create a Laravel 5.8 project.

About Laravel

Laravel docs describe it as:

Laravel is a web application framework with expressive, elegant syntax. We believe development must be an enjoyable and creative experience to be truly fulfilling. Laravel attempts to take the pain out of development by easing common tasks used in the majority of web projects, such as:
  • Simple, fast routing engine.
  • Powerful dependency injection container.
  • Multiple back-ends for session and cache storage.
  • Expressive, intuitive database ORM.
  • Database agnostic schema migrations.
  • Robust background job processing.
  • Real-time event broadcasting.
Laravel is accessible, yet powerful, providing tools needed for large, robust applications.

Generating a Laravel 5.8 project is easy and straightforward. In your terminal, run the following command:

$ composer create-project  --prefer-dist  laravel/laravel laravel-first-crud-app

This will install laravel/laravel v5.8.3.

Note: Make sure you have PHP 7.1 installed on your system. Otherwise, composer will use Laravel 5.5 for your project.

You can verify the installed version in your project using:

$ cd laravel-first-crud-app
$ php artisan -V
Laravel Framework 5.8.19
Installing the Front-End Dependencies

In your generated project, you can see that a package.json file is generated which includes many front-end libraries that can be used by your project:

  • axios,
  • bootstrap,
  • cross-env,
  • jquery,
  • laravel-mix,
  • lodash,
  • popper.js,
  • resolve-url-loader,
  • sass,
  • sass-loader,
  • vue.
Note: You can use your preferred libraries with Laravel not specifically the ones added to package.json.
The package.json file in your Laravel project includes a few packages such as vue and axios to help you get started building your JavaScript application.
It also includes bootstrap to help you get started with Bootstrap for styling your UI.
It include Laravel Mix to help you compile your SASS files to plain CSS.

You need to use npm to install the front-end dependencies:

$ npm install

After running this command a node_modules folder will be created and the dependencies will be installed into it.

Note: You need to have Node.js and npm installed on your system before you can install the front-end dependencies.
Creating a MySQL Database

Let's now create a MySQL database that we'll use to persist dat ain our Laravel application. In your terminal, run the following command to run the mysql client:

$ mysql -u root -p

When prompted, enter the password for your MySQL server when you've installed it.

Next, run the following SQL statement to create a db database:

mysql> create database db;

Open the .env file and update the credentials to access your MySQL database:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=db
DB_USERNAME=root
DB_PASSWORD=******

You need to enter the database name, the username and password.

At this point, you can run the migrate command to create your database and a bunch of SQL tables needed by Laravel:

$ php artisan migrate
Note: You can run the migrate command at any other points of your development to add other SQL tables in your database or to later your database if you need to add any changes later.
Creating your First Laravel Model

Laravel uses the MVC architectural pattern to organize your application in three decoupled parts:

  • The Model which encapsulates the data access layer,
  • The View which encapsulates the representation layer,
  • Controller which encapsulates the code to control the application and communicates with the model and view layers.

Wikipedia defines MVC as:

Model–view–controller is an architectural pattern commonly used for developing user interfacesthat divides an application into three interconnected parts. This is done to separate internal representations of information from the ways information is presented to and accepted from the user.

Now, let's create our first Laravel Model. In your terminal, run the following command:

$ php artisan make:model Contact --migration

This will create a Contact model and a migration file. In the terminal, we get an output similar to:

Model created successfully.
Created Migration: 2019_01_27_193840_create_contacts_table

Open the database/migrations/xxxxxx_create_contacts_table migration file and update it accordingly:

<?php

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

class CreateContactsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('contacts', function (Blueprint $table) {
$table->increments('id');
$table->timestamps();
$table->string('first_name');
$table->string('last_name');
$table->string('email');
$table->string('job_title');
$table->string('city');
$table->string('country');
});
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::dropIfExists('contacts');
}

}

We added the first_namelast_nameemailjob_titlecity and country fields in the contacts table.

You can now create the contacts table in the database using the following command:

$ php artisan migrate

Now, let's look at our Contact model, which will be used to interact with the contacts database table. Open the app/Contact.php and update it:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Contact extends Model
{
protected $fillable = [
'first_name',
'last_name',
'email',
'city',
'country',
'job_title'
];
}

Creating the Controller and Routes

After creating the model and migrated our database. Let's now create the controller and the routes for working with the Contact model. In your terminal, run the following command:

$ php artisan make:controller ContactController --resource
Laravel resource routing assigns the typical "CRUD" routes to a controller with a single line of code. For example, you may wish to create a controller that handles all HTTP requests for "photos" stored by your application. Using the make:controller Artisan command, we can quickly create such a controller:
This command will generate a controller at app/Http/Controllers/PhotoController.php. The controller will contain a method for each of the available resource operations.

Open the app/Http/Controllers/ContactController.php file. This is the initial content:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class ContactController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
//
}

/**
 * Show the form for creating a new resource.
 *
 * @return \Illuminate\Http\Response
 */
public function create()
{
    //
}

/**
 * Store a newly created resource in storage.
 *
 * @param  \Illuminate\Http\Request  $request
 * @return \Illuminate\Http\Response
 */
public function store(Request $request)
{
    //
}

/**
 * Display the specified resource.
 *
 * @param  int  $id
 * @return \Illuminate\Http\Response
 */
public function show($id)
{
    //
}

/**
 * Show the form for editing the specified resource.
 *
 * @param  int  $id
 * @return \Illuminate\Http\Response
 */
public function edit($id)
{
    //
}

/**
 * Update the specified resource in storage.
 *
 * @param  \Illuminate\Http\Request  $request
 * @param  int  $id
 * @return \Illuminate\Http\Response
 */
public function update(Request $request, $id)
{
    //
}

/**
 * Remove the specified resource from storage.
 *
 * @param  int  $id
 * @return \Illuminate\Http\Response
 */
public function destroy($id)
{
    //
}

}

The ContactController class extends Controller class available from Laravel and defines a bunch of methods which will be used to do the CRUD operations against the Contact model.

You can read the role of the method on the comment above it.

Now we need to provide implementations for these methods.

But before that, let's add routing. Open the routes/web.php file and update it accordingly:

<?php
Route::get('/', function () {
return view('welcome');
});

Route::resource('contacts', 'ContactController');

Using the resource() static method of Route, you can create multiple routes to expose multiple actions on the resource.

These routes are mapped to various ContactController methods which will need to implement in the next section:

  • GET/contacts, mapped to the index() method,
  • GET /contacts/create, mapped to the create() method,
  • POST /contacts, mapped to the store() method,
  • GET /contacts/{contact}, mapped to the show() method,
  • GET /contacts/{contact}/edit, mapped to the edit() method,
  • PUT/PATCH /contacts/{contact}, mapped to the update() method,
  • DELETE /contacts/{contact}, mapped to the destroy() method.

These routes are used to serve HTML templates and also as API endpoints for working with the Contactmodel.

Note: If you want to create a controller that will only expose a RESTful API, you can use the apiResource method to exclude the routes that are used to serve the HTML templates:
Route::apiResource('contacts', 'ContactController');
Implementing the CRUD Operations

Let's now implement the controller methods alongside the views.

C: Implementing the Create Operation and Adding a Form

The ContactController includes the store() method that maps to the POST /contacts API endpoint which will be used to create a contact in the database and the create() that maps to the GET /contacts/create route which will be used to serve the HTML form used to submit the contact to POST /contacts API endpoint.

Let's implement these two methods.

Re-open the app/Http/Controllers/ContactController.php file and start by importing the Contactmodel:

use App\Contact;

Next, locate the store() method and update it accordingly:

    public function store(Request $request)
{
$request->validate([
'first_name'=>'required',
'last_name'=>'required',
'email'=>'required'
]);

    $contact = new Contact([
        'first_name' =&gt; $request-&gt;get('first_name'),
        'last_name' =&gt; $request-&gt;get('last_name'),
        'email' =&gt; $request-&gt;get('email'),
        'job_title' =&gt; $request-&gt;get('job_title'),
        'city' =&gt; $request-&gt;get('city'),
        'country' =&gt; $request-&gt;get('country')
    ]);
    $contact-&gt;save();
    return redirect('/contacts')-&gt;with('success', 'Contact saved!');
}

Next, locate the create() method and update it:

    public function create()
{
return view('contacts.create');
}

The create() function makes use of the view() method to return the create.blade.php template which needs to be present in the resources/views folder.

Before creating the create.blade.php template we need to create a base template that will be extended by the create template and all the other templates that will create later in this tutorial.

In the resources/views folder, create a base.blade.php file:

$ cd resources/views
$ touch base.blade.php

Open the resources/views/base.blade.php file and add the following blade template:

<!DOCTYPE html>
<html lang="en">
<head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Laravel 5.8 & MySQL CRUD Tutorial</title>
<link href="{{ asset('css/app.css') }}" rel="stylesheet" type="text/css" />
</head>
<body>
<div class="container">
@yield('main')
</div>
<script src="{{ asset('js/app.js') }}" type="text/js"></script>
</body>
</html>

Now, let's create the create.blade.php template. First, create a contacts folder in the views folder:

$ mkdir contacts

Next, create the template

$ cd contacts
$ touch create.blade.php

Open the resources/views/contacts/create.blade.php file and add the following code:

@extends('base')

@section('main')
<div class="row">
<div class="col-sm-8 offset-sm-2">
<h1 class="display-3">Add a contact</h1>
<div>
@if ($errors->any())
<div class="alert alert-danger">
<ul>
@foreach ($errors->all() as $error)
<li>{{ $error }}</li>
@endforeach
</ul>
</div><br />
@endif
<form method="post" action="{{ route('contacts.store') }}">
@csrf
<div class="form-group">
<label for="first_name">First Name:</label>
<input type="text" class="form-control" name="first_name"/>
</div>

      &lt;div class="form-group"&gt;
          &lt;label for="last_name"&gt;Last Name:&lt;/label&gt;
          &lt;input type="text" class="form-control" name="last_name"/&gt;
      &lt;/div&gt;

      &lt;div class="form-group"&gt;
          &lt;label for="email"&gt;Email:&lt;/label&gt;
          &lt;input type="text" class="form-control" name="email"/&gt;
      &lt;/div&gt;
      &lt;div class="form-group"&gt;
          &lt;label for="city"&gt;City:&lt;/label&gt;
          &lt;input type="text" class="form-control" name="city"/&gt;
      &lt;/div&gt;
      &lt;div class="form-group"&gt;
          &lt;label for="country"&gt;Country:&lt;/label&gt;
          &lt;input type="text" class="form-control" name="country"/&gt;
      &lt;/div&gt;
      &lt;div class="form-group"&gt;
          &lt;label for="job_title"&gt;Job Title:&lt;/label&gt;
          &lt;input type="text" class="form-control" name="job_title"/&gt;
      &lt;/div&gt;                         
      &lt;button type="submit" class="btn btn-primary-outline"&gt;Add contact&lt;/button&gt;
  &lt;/form&gt;

</div>
</div>
</div>
@endsection

This is a screenshot of our create form!

Fill out the form and click on the Add contact button to create a contact in the database. You should be redirected to /contacts route which doesn't have a view associated to it yet.

R: Implementing the Read Operation and Getting Data

Next, let's implement the read operation to get and display contacts data from our MySQL database.

Go to the app/Http/Controllers/ContactController.php file, locate the index() method and update it:

    public function index()
{
$contacts = Contact::all();

    return view('contacts.index', compact('contacts'));
}

Next, you need to create the the index template. Create a resources/views/contacts.index.blade.phpfile:

$ touch index.blade.php

Open the resources/views/contacts/index.blade.php file and add the following code:

@extends('base')

@section('main')
<div class="row">
<div class="col-sm-12">
<h1 class="display-3">Contacts</h1>
<table class="table table-striped">
<thead>
<tr>
<td>ID</td>
<td>Name</td>
<td>Email</td>
<td>Job Title</td>
<td>City</td>
<td>Country</td>
<td colspan = 2>Actions</td>
</tr>
</thead>
<tbody>
@foreach($contacts as $contact)
<tr>
<td>{{$contact->id}}</td>
<td>{{$contact->first_name}} {{$contact->last_name}}</td>
<td>{{$contact->email}}</td>
<td>{{$contact->job_title}}</td>
<td>{{$contact->city}}</td>
<td>{{$contact->country}}</td>
<td>
<a href="{{ route('contacts.edit',$contact->id)}}" class="btn btn-primary">Edit</a>
</td>
<td>
<form action="{{ route('contacts.destroy', $contact->id)}}" method="post">
@csrf
@method('DELETE')
<button class="btn btn-danger" type="submit">Delete</button>
</form>
</td>
</tr>
@endforeach
</tbody>
</table>
<div>
</div>
@endsection

U: Implementing the Update Operation

Next, we need to implement the update operation. Go to the app/Http/Controllers/ContactController.php file, locate the edit($id) method and update it:

    public function edit($id)
{
$contact = Contact::find($id);
return view('contacts.edit', compact('contact'));
}

Next, you need to implement the update() method:

    public function update(Request $request, $id)
{
$request->validate([
'first_name'=>'required',
'last_name'=>'required',
'email'=>'required'
]);

    $contact = Contact::find($id);
    $contact-&gt;first_name =  $request-&gt;get('first_name');
    $contact-&gt;last_name = $request-&gt;get('last_name');
    $contact-&gt;email = $request-&gt;get('email');
    $contact-&gt;job_title = $request-&gt;get('job_title');
    $contact-&gt;city = $request-&gt;get('city');
    $contact-&gt;country = $request-&gt;get('country');
    $contact-&gt;save();

    return redirect('/contacts')-&gt;with('success', 'Contact updated!');
}

Now, you need to add the edit template. Inside the resources/views/contacts/, create an edit.blade.php file:

$ touch edit.blade.php

Open the resources/views/contacts/edit.blade.php file and add this code:

@extends('base')
@section('main')
<div class="row">
<div class="col-sm-8 offset-sm-2">
<h1 class="display-3">Update a contact</h1>

    @if ($errors-&gt;any())
    &lt;div class="alert alert-danger"&gt;
        &lt;ul&gt;
            @foreach ($errors-&gt;all() as $error)
            &lt;li&gt;{{ $error }}&lt;/li&gt;
            @endforeach
        &lt;/ul&gt;
    &lt;/div&gt;
    &lt;br /&gt; 
    @endif
    &lt;form method="post" action="{{ route('contacts.update', $contact-&gt;id) }}"&gt;
        @method('PATCH') 
        @csrf
        &lt;div class="form-group"&gt;

            &lt;label for="first_name"&gt;First Name:&lt;/label&gt;
            &lt;input type="text" class="form-control" name="first_name" value={{ $contact-&gt;first_name }} /&gt;
        &lt;/div&gt;

        &lt;div class="form-group"&gt;
            &lt;label for="last_name"&gt;Last Name:&lt;/label&gt;
            &lt;input type="text" class="form-control" name="last_name" value={{ $contact-&gt;last_name }} /&gt;
        &lt;/div&gt;

        &lt;div class="form-group"&gt;
            &lt;label for="email"&gt;Email:&lt;/label&gt;
            &lt;input type="text" class="form-control" name="email" value={{ $contact-&gt;email }} /&gt;
        &lt;/div&gt;
        &lt;div class="form-group"&gt;
            &lt;label for="city"&gt;City:&lt;/label&gt;
            &lt;input type="text" class="form-control" name="city" value={{ $contact-&gt;city }} /&gt;
        &lt;/div&gt;
        &lt;div class="form-group"&gt;
            &lt;label for="country"&gt;Country:&lt;/label&gt;
            &lt;input type="text" class="form-control" name="country" value={{ $contact-&gt;country }} /&gt;
        &lt;/div&gt;
        &lt;div class="form-group"&gt;
            &lt;label for="job_title"&gt;Job Title:&lt;/label&gt;
            &lt;input type="text" class="form-control" name="job_title" value={{ $contact-&gt;job_title }} /&gt;
        &lt;/div&gt;
        &lt;button type="submit" class="btn btn-primary"&gt;Update&lt;/button&gt;
    &lt;/form&gt;
&lt;/div&gt;

</div>
@endsection

U: Implementing the Delete Operation

Finally, we'll proceed to implement the delete operation. Go to the app/Http/Controllers/ContactController.php file, locate the destroy() method and update it accordingly:

    public function destroy($id)
{
$contact = Contact::find($id);
$contact->delete();

    return redirect('/contacts')-&gt;with('success', 'Contact deleted!');
}

You can notice that when we redirect to the /contacts route in our CRUD API methods, we also pass a success message but it doesn't appear in our index template. Let's change that!

Go to the resources/views/contacts/index.blade.php file and add the following code:

<div class="col-sm-12">

@if(session()->get('success'))
<div class="alert alert-success">
{{ session()->get('success') }}
</div>
@endif
</div>

We also need to add a button to takes us to the create form. Add this code below the header:

    <div>
<a style="margin: 19px;" href="{{ route('contacts.create')}}" class="btn btn-primary">New contact</a>
</div>

This is a screenshot of the page after we created a contact:

Conclusion

We've reached the end of this tutorial. We created a CRUD application with Laravel 5.8, PHP 7.1 and MySQL.

Hope you enjoyed the tutorial and see you in the next one!


Originally published at techiediaries.com on 12 Mar 2019

=====================================

Thanks for reading :heart: If you liked this post, share it with all of your programming buddies! Follow me on Facebook | Twitter

☞ RESTful API with Laravel: Build a real API with Laravel

☞ Master Laravel - A php framework for Beginner to Advanced

☞ PHP with Laravel for beginners - Become a Master in Laravel

☞ Projects in Laravel: Learn Laravel Building 10 Projects

☞ Laravel 2019, the complete guide with real world projects

☞ Laravel for RESTful: Build Your RESTful API with Laravel

☞ Real Time Single Page Forum App with Pusher Laravel & vuejs

☞ Master Laravel PHP in 2019 for Beginners and Intermediate

Building PHP apps using SQL Server on Windows

Building PHP apps using SQL Server on Windows

3 step: Building PHP apps using SQL Server on Windows

Step 1: Set up your environment

In this section, you will get SQL Server on Windows. After that you will install the necessary dependencies to create PHP apps with SQL Server

1. Install SQL Server

  1. If you don’t have SQL Server Developer (or above) installed, click here to download the SQL Server exe.
  2. Run it to start the SQL installer.
  3. Click Basic in Select an installation type.
  4. Click Accept after you have read the license terms.
  5. (Optional) if you need to, you can choose a custom installation location for SQL Server.
  6. Click Install to proceed with the installation.

You now have SQL Server installed and running locally on your Windows computer! Check out the next section to continue installing prerequisites.

2. Install PHP and Chocolatey

You can download PHP using the Web Platform Installer. Once you download Web PI, open it up and download the entry which says ‘PHP 7.2.7 (x64) for IIS Express’.

Next, install Chocolatey. Chocolatey is a package manager like apt-get and yum for Windows. We will use Chocolatey later in the tutorial. Use an elevated Command-line session (run as administrator):

@powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"

For Chocolatey to work, you now need to restart the terminal session by closing and opening the terminal.

You have succesfully installed PHP and Chocolatey on your machine!

3. Install the ODBC Driver and SQL Command Line Utility for SQL Server

SQLCMD is a command line tool that enables you to connect to SQL Server and run queries.

  1. Install the ODBC Driver.
  2. Install the SQL Server Command Line Utilities.

After installing SQLCMD, you can connect to SQL Server using the following command from a CMD session:

sqlcmd -S localhost -U sa -P your_password
1> # You're connected! Type your T-SQL statements here. Use the keyword 'GO' to execute each batch of statements.

This how to run a basic inline query. The results will be printed to STDOUT.

sqlcmd -S localhost -U sa -P yourpassword -Q "SELECT @@VERSION"



Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)
Apr 29 2016 23:23:58
Copyright (c) Microsoft Corporation
Developer Edition (64-bit)

1 rows(s) returned

Executed in 1 ns.

You have successfully installed SQL Server Command Line Utilities on your Windows machine!

Step 2: Create PHP application with SQL server

In this section you will create a simple PHP app. The PHP app will perform basic Insert, Update, Delete, and Select.

1. Install the PHP Drivers for SQL Server

Download the Microsoft PHP Drivers for SQL Server from the download center.

Pick the appropriate dll - for example php_pdo_sqlsrv_72_nts.dll for the PDO Driver and php_sqlsrv_72_nts.dll for the SQLSRV driver.

Copy the dll’s to the C:\Program Files\iis express\PHP\v7.2\ext folder.

Register the dll’s in the php.ini file.

cd C:\Program^ Files\iis^ express\PHP\v7.2\ext
echo extension=php_sqlsrv_72_nts.dll >> C:\Program^ Files\iis^ express\PHP\v7.2\php.ini
echo extension=php_pdo_sqlsrv_72_nts.dll >> C:\Program^ Files\iis^ express\PHP\v7.2\php.ini

2. Create a database for your application

Create the database using sqlcmd.

sqlcmd -S localhost -U sa -P your_password -Q "CREATE DATABASE SampleDB;"

3. Create a PHP app that connects to SQL Server and executes queries

mkdir SqlServerSample
cd SqlServerSample

Using your favorite text editor, create a new file called connect.php in the SqlServerSample folder. Paste the code below inside into the new file.

<?php
$serverName = "localhost";
$connectionOptions = array(
"Database" => "SampleDB",
"Uid" => "sa",
"PWD" => "your_password"
);
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);
if($conn)
echo "Connected!"
?>

Run your PHP script from the terminal.

php connect.php


Connected!

Execute the T-SQL scripts below in the terminal with sqlcmd to create a schema, table, and insert a few rows.

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE SCHEMA TestSchema;"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE TABLE TestSchema.Employees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "INSERT INTO TestSchema.Employees (Name, Location) VALUES (N'Jared', N'Australia'), (N'Nikita', N'India'), (N'Tom', N'Germany');"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "SELECT * FROM TestSchema.Employees;"

Using your favorite text editor, create a new file called crud.php in the SqlServerSample folder. Paste the code below inside into the new file. This will insert, update, delete, and read a few rows.

<?php
$serverName = "localhost";
$connectionOptions = array(
"Database" => "SampleDB",
"Uid" => "sa",
"PWD" => "your_password"
);
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);

//Insert Query
echo ("Inserting a new row into table" . PHP_EOL);
$tsql= "INSERT INTO TestSchema.Employees (Name, Location) VALUES (?,?);";
$params = array('Jake','United States');
$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) inserted: " . PHP_EOL);

sqlsrv_free_stmt($getResults);

//Update Query

$userToUpdate = 'Nikita';
$tsql= "UPDATE TestSchema.Employees SET Location = ? WHERE Name = ?";
$params = array('Sweden', $userToUpdate);
echo("Updating Location for user " . $userToUpdate . PHP_EOL);

$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) updated: " . PHP_EOL);
sqlsrv_free_stmt($getResults);

//Delete Query
$userToDelete = 'Jared';
$tsql= "DELETE FROM TestSchema.Employees WHERE Name = ?";
$params = array($userToDelete);
$getResults= sqlsrv_query($conn, $tsql, $params);
echo("Deleting user " . $userToDelete . PHP_EOL);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) deleted: " . PHP_EOL);
sqlsrv_free_stmt($getResults);

//Read Query
$tsql= "SELECT Id, Name, Location FROM TestSchema.Employees;";
$getResults= sqlsrv_query($conn, $tsql);
echo ("Reading data from table" . PHP_EOL);
if ($getResults == FALSE)
die(FormatErrors(sqlsrv_errors()));
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
echo ($row['Id'] . " " . $row['Name'] . " " . $row['Location'] . PHP_EOL);
}
sqlsrv_free_stmt($getResults);

function FormatErrors( $errors )
{
/* Display errors. */
echo "Error information: ";

foreach ( $errors as $error )
{
    echo "SQLSTATE: ".$error['SQLSTATE']."";
    echo "Code: ".$error['code']."";
    echo "Message: ".$error['message']."";
}

}
?>

Run your PHP script from the terminal.

php crud.php


Inserting a new row into table
1 row(s) inserted:
Updating Location for user Nikita
1 row(s) updated:
Deleting user Jared
1 row(s) deleted:
Reading data from table
2 Nikita Sweden
3 Tom Germany
4 Jake United States

Congratulations! You have created your first PHP app with SQL Server! Check out the next section to learn about how you can make your PHP faster with SQL Server’s Columnstore feature.

Step 3: 3 Make your PHP app up to 100x faster

In this section we will show you a simple example of Columnstore Indexes and how they can improve data processing speeds. Columnstore Indexes can achieve up to 100x better performance on analytical workloads and up to 10x better data compression than traditional rowstore indexes.

1. Create a new table with 5 million rows using sqlcmd

sqlcmd -S localhost -U sa -P your_password -d SampleDB -t 60000 -Q "WITH a AS (SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a))
SELECT TOP(5000000)
ROW_NUMBER() OVER (ORDER BY a.a) AS OrderItemId
,a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a AS OrderId
,a.a * 10 AS Price
,CONCAT(a.a, N' ', b.a, N' ', c.a, N' ', d.a, N' ', e.a, N' ', f.a, N' ', g.a, N' ', h.a) AS ProductName
INTO Table_with_5M_rows
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h;"

2. Create a PHP app that queries this table and measures the time taken

cd ~/
mkdir SqlServerColumnstoreSample
cd SqlServerColumnstoreSample

Using your favorite text editor, create a new file called columnstore.php in the SqlServerColumnstoreSample folder. Paste the following code inside it.

<?php
$time_start = microtime(true);

$serverName = "localhost";
$connectionOptions = array(
"Database" => "SampleDB",
"Uid" => "sa",
"PWD" => "your_password"
);
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);

//Read Query
$tsql= "SELECT SUM(Price) as sum FROM Table_with_5M_rows";
$getResults= sqlsrv_query($conn, $tsql);
echo ("Sum: ");
if ($getResults == FALSE)
die(FormatErrors(sqlsrv_errors()));
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
echo ($row['sum'] . PHP_EOL);

}
sqlsrv_free_stmt($getResults);

function FormatErrors( $errors )
{
/* Display errors. */
echo "Error information: ";

foreach ( $errors as $error )
{
    echo "SQLSTATE: ".$error['SQLSTATE']."";
    echo "Code: ".$error['code']."";
    echo "Message: ".$error['message']."";
}

}
$time_end = microtime(true);
$execution_time = round((($time_end - $time_start)*1000),2);
echo 'QueryTime: '.$execution_time.' ms';

?>

3. Measure how long it takes to run the query

Run your PHP script from the terminal.

php columnstore.php


Sum: 50000000
QueryTime: 363ms

4. Add a columnstore index to your table

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE CLUSTERED COLUMNSTORE INDEX Columnstoreindex ON Table_with_5M_rows;"

5. Measure how long it takes to run the query with a columnstore index

php columnstore.php


Sum: 50000000
QueryTime: 5ms

Congratulations! You just made your PHP app faster using Columnstore Indexes!

Check out the PHP Driver on GitHub

Thanks For Visiting, Keep Visiting.

Originally published on sqlchoice

What are the differences between Standard SQL and Transact-SQL?

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL

#1 Names of Database Objects

In relational database systems, we name tables, views, and columns, but sometimes we need to use the same name as a keyword or use special characters. In standard SQL, you can place this kind of name in quotation marks (""), but in T-SQL, you can also place it in brackets ([]). Look at these examples for the name of a table in T-SQL:

CREATE TABLE dbo.test.“first name” ( Id INT, Name VARCHAR(100));
CREATE TABLE dbo.test.[first name]  ( Id INT, Name VARCHAR(100));

Only the first delimiter (the quotation marks) for the special name is also part of the SQL standard.

What Is Different in a SELECT Statement?#2 Returning Values

The SQL standard does not have a syntax for a query returning values or values coming from expressions without referring to any columns of a table, but MS SQL Server does allow for this type of expression. How? You can use a SELECT statement alone with an expression or with other values not coming from columns of the table. In T-SQL, it looks like the example below:

SELECT 12/6 ;

In this expression, we don’t need a table to evaluate 12 divided by 6, therefore, the FROM statement and the name of the table can be omitted.

#3 Limiting Records in a Result Set

In the SQL standard, you can limit the number of records in the results by using the syntax illustrated below:

SELECT * FROM tab FETCH FIRST 10 ROWS ONLY

T-SQL implements this syntax in a different way. The example below shows the MS SQL Server syntax:

SELECT * FROM tab ORDER BY col1 DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

As you notice, this uses an ORDER BY clause. Another way to select rows, but without ORDER BY, is by using the TOP clause in T-SQL:

SELECT TOP 10 * FROM tab;
#4 Automatically Generating Values

The SQL standard enables you to create columns with automatically generated values. The syntax to do this is shown below:

CREATE TABLE tab (id DECIMAL GENERATED ALWAYS AS IDENTITY);

In T-SQL we can also automatically generate values, but in this way:

CREATE TABLE tab (id INTEGER IDENTITY);
#5 Math Functions

Several common mathematical functions are part of the SQL standard. One of these math functions is CEIL(x), which we don’t find in T-SQL. Instead, T-SQL provides the following non-standard functions: SIGN(x), ROUND(x,[,d]) to round decimal value x to the number of decimal positions, TRUNC(x) for truncating to given number of decimal places, LOG(x) to return the natural logarithm for a value x, and RANDOM() to generate random numbers. The highest or lowest number in a list in the SQL standard is returned by MAX(list) and MIN(list) functions, but in Transact-SQL, you use the GREATEST(list) and LEAST(list) functions.

T-SQL function ROUND:

SELECT ROUND(col) FROM tab;

#6 Aggregate Functions

We find another syntax difference with the aggregate functions. The functions COUNT, SUM, and AVG all take an argument related to a count. T-SQL allows the use of DISTINCT before these argument values so that rows are counted only if the values are different from other rows. The SQL standard doesn't allow for the use of DISTINCT in these functions.

Standard SQL:
SELECT COUNT(col) FROM tab;

T-SQL:
SELECT COUNT(col) FROM tab;

SELECT COUNT(DISTINCT col) FROM tab;

But in T-SQL we don’t find a population covariance function: COVAR_POP(x,y), which is defined in the SQL standard.

#7 Retrieving Parts of Dates and Times

Most relational database systems deliver many functions to operate on dates and times.

In standard SQL, the EXTRACT(YEAR FROM x) function and similar functions to select parts of dates are different from the T-SQL functions like YEAR(x) or DATEPART(year, x).

There is also a difference in getting the current date and time. Standard SQL allows you to get the current date with the CURRENT_DATE function, but in MS SQL Server, there is not a similar function, so we have to use the GETDATE function as an argument in the CAST function to convert to a DATE data type.

#8 Operating on Strings

Using functions to operate on strings is also different between the SQL standard and T-SQL. The main difference is found in removing trailing and leading spaces from a string. In standard SQL, there is the TRIM function, but in T-SQL, there are several related functions: TRIM (removing trailing and leading spaces), LTRIM (removing leading spaces), and RTRIM (removing trailing spaces).

Another very-often-used string function is SUBSTRING.

The standard SQL syntax for the SUBSTRING function looks like:

SUBSTRING(str FROM start [FOR len])

but in T-SQL, the syntax of this function looks like:

SUBSTRING(str, start, length)

There are reasons sometimes to add values coming from other columns and/or additional strings. Standard SQL enables the following syntax to do this:

As you can see, this syntax makes use of the || operator to add one string to another.

But the equivalent operator in T-SQL is the plus sign character. Look at this example:

SELECT col1 + col2  FROM tab;

In SQL Server, we also have the possibility to use the CONCAT function concatenates a list of strings:

SELECT CONCAT(col1, str1, col2, ...)  FROM tab;

We can also repeat one character several times. Standard SQL defines the function REPEAT(str, n) to do this. Transact-SQL provides the REPLICATE function. For example:

SELECT  REPLICATE(str, x);

where x indicates how many times to repeat the string or character.

#9 Inequality Operator

During filtering records in a SELECT statement, sometimes we have to use an inequality operator. Standard SQL defines <> as this operator, while T-SQL allows for both the standard operator and the != operator:

SELECT col3 FROM tab WHERE col1 != col2;
#10 ISNULL Function

In T-SQL, we have the ability to replace NULL values coming from a column using the ISNULL function. This is a function that is specific to T-SQL and is not in the SQL standard.

SELECT ISNULL(col1) FROM tab;
Which Parts of DML Syntax Are Different?

In T-SQL, the basic syntax of DELETE, UPDATE, and INSERT queries is the same as the SQL standard, but differences appear in more advanced queries. Let’s look at them.

#11 OUTPUT Keyword

The OUTPUT keyword occurs in DELETE, UPDATE, and INSERT statements. It is not defined in standard SQL.

Using T-SQL, we can see extra information returned by a query. It returns both old and new values in UPDATE or the values added using INSERT or deleted using DELETE. To see this information, we have to use prefixes in INSERT, UPDATE, and DELETE.

UPDATE tab SET col='new value'
OUTPUT Deleted.col, Inserted.col;

We see the result of changing records with the previous and new values in an updated column. The SQL standard does not support this feature.

#12 Syntax for INSERT INTO ... SELECT

Another structure of an INSERT query is INSERT INTO … SELECT. T-SQL allows you to insert data from another table into a destination table. Look at this query:

INSERT INTO tab SELECT col1,col2,... FROM tab_source;

It is not a standard feature but a feature characteristic of SQL Server.

#13 FROM Clause in DELETE and UPDATE

SQL Server provides extended syntax of the UPDATE and DELETE with FROM clauses. You can use DELETE with FROM to use the rows from one table to remove corresponding rows in another table by referring to a primary key and a foreign key. Similarly, you can use UPDATE with FROM update rows from one table by referring to the rows of another table using common values (primary key in one table and foreign key in second, e.g. the same city name). Here is an example:

DELETE FROM Book
FROM Author
WHERE Author.Id=Book.AuthorId AND Author.Name IS NULL;

UPDATE Book
SET Book.Price=Book.Price*0.2
FROM Author
WHERE Book.AuthorId=Author.Id AND Author.Id=12;

The SQL standard doesn’t provide this syntax.

#14 INSERT, UPDATE, and DELETE With JOIN

You can also use INSERT, UPDATE, and DELETE using JOIN to connect to another table. An example of this is:

DELETE ItemOrder FROM ItemOrder
JOIN Item ON ItemOrder.ItemId=Item.Id
WHERE YEAR(Item.DeliveredDate) <= 2017;

This feature is not in the SQL standard.

Summary

This article does not cover all the issues about syntax differences between the SQL standard and T-SQL using the MS SQL Server system. However, this guide helps point out some basic features characteristic only of Transact-SQL and what SQL standard syntax isn’t implemented by MS SQL Server.

Thanks for reading. If you liked this post, share it with all of your programming buddies!

Originally published on https://dzone.com