How to select two additional columns from another table based on values in the base table

Below is sample SQL showing how we can use additional columns from another table based on value of base table.

SELECT base_table.id, another_table.id AS some_other_name, another_table.name
FROM base_table AS base_table
  INNER JOIN (SELECT id, name FROM another_table) another_table
    ON base_table.another_table_id = another_table.id
WHERE another_table.some_reference = 15
ORDER BY base_table.id DESC
LIMIT 10

Laravel 5: Fluent Query Builder Join with subquery

Laravel is a strong php based framework today. Below is a code sample showing how raw query used for join.

$list = DB::table("users AS u")
    ->join(DB::raw('(SELECT id AS oid,name FROM organization) o'), function($join) {
        $join->on('o.oid', '=', 'u.organization_id');
    })
    ->where("o.oid", 15)
    ->limit(10)
    ->orderBy("u.id", "DESC")
    ->select("u.id", "o.oid", "o.name")
    ->get();

How create json format with group-concat mysql | How to put JSON into a column data if sub-query returns more than 1 row in MySQL | Optimized way of getting subqueries at once using JSON

How create json format with group-concat mysql |  How to put JSON into a column data if sub-query returns more than 1 row in MySQL | Optimized way of getting subqueries at once using JSON.

A bit convoluted, but you could create JSON objects for each row, concatenate them using GROUP_CONCAT and cast the result (wrapped in [] to make it an array) to JSON;

Below is the full example:

<?php
define("DB_DEFAULT_HOST", "localhost");
define("DB_DEFAULT_DATABASE", "test71");
define("DB_DEFAULT_USER", "root");
define("DB_DEFAULT_PASSWORD", "");

$dbh = null;
try {
    $dbh = new PDO(
        "mysql:host=" . DB_DEFAULT_HOST . ";dbname=" . DB_DEFAULT_DATABASE,
        DB_DEFAULT_USER,
        DB_DEFAULT_PASSWORD
    );
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->beginTransaction();

    $query =
        "SELECT
            s.name AS Name, s.roll AS Roll,
            CONCAT(
              '[', COALESCE(
                GROUP_CONCAT(
                  CONCAT('{', 
                    '\"ExamName\":', '\"', HEX(CONVERT(e.subject USING utf32)), '\"',
                    ',\"Score\":', '\"', e.score, '\"',
                  '}')
                  SEPARATOR ','
                ), ''
              ), ']'
            ) AS ExamsRaw
        
        FROM student AS s
          LEFT JOIN exams AS e ON e.student_id=s.id
        
        GROUP BY s.id";

    $students = $dbh->query($query)->fetchAll(PDO::FETCH_OBJ);
    foreach ($students as $entity) {
        $entity->Exams = array();
        foreach (json_decode($entity->ExamsRaw) as $group) {
            $group->ExamName = hexToStr($group->ExamName);
            array_push($entity->Exams, $group);
        }
        unset($entity->ExamsRaw);
    }
    echo "<pre>";
    print_r($students);
    echo "</pre>";
}
catch (\Exception $ex) {
    echo "MySQL_Error=" . $ex->getMessage();
    $dbh->rollBack();
}

function hexToStr($hex)
{
    $string = '';
    for ($index = 0; $index < strlen($hex) - 1; $index += 2) {
        $string .= chr(hexdec($hex[$index] . $hex[$index + 1]));
    }
    return $string;
}

And below is the output of above example code block:

Array
(
    [0] => stdClass Object
        (
            [Name] => Prtiom First Year
            [Roll] => P1
            [Exams] => Array
                (
                    [0] => stdClass Object
                        (
                            [ExamName] => ©|æ|þ|½|±|®|¶
                            [Score] => 1.12
                        )

                    [1] => stdClass Object
                        (
                            [ExamName] => Subject 2   
                            [Score] => 2.54
                        )

                    [2] => stdClass Object
                        (
                            [ExamName] =>  ¡¢£¤¥¦§¨©
                            [Score] => 1.98
                        )

                )

        )

    [1] => stdClass Object
        (
            [Name] => Pritom Second Year
            [Roll] => P2
            [Exams] => Array
                (
                    [0] => stdClass Object
                        (
                            [ExamName] => Subject 2
                            [Score] => 4.00
                        )

                    [1] => stdClass Object
                        (
                            [ExamName] => Subject 3
                            [Score] => 3.00
                        )

                )

        )

)

Aware that mysql group function max length is by default 1024 characters. If your group function more than 1024 then you have to change limit of mysql group function using following query:

This is for current session:
SET SESSION group_concat_max_len = 1000000;

But if you want it global scope, then do following:
SET GLOBAL group_concat_max_len = 1000000;

Laravel 5: How to modify Request values | How to "artificially" add values to Request array | How to dynamically add params to Request array

Laravel 5: How to modify Request values | How to "artificially" add values to Request array | How to dynamically add params to Request array. You can use the merge() method on the $request object. In spite of the methods name, it actually replaces any values associated with the member names specified by the keys of the parameter rather than concatenating their values or anything like that. 

A few times I encountered a situation – a store() or update() method with Request parameter, but I needed to add some additional value to the request before calling Eloquent functions. So how to do that? Apparently, pretty easy.

use Illuminate\Http\Request;

protected $request;

public function __construct(Request $request)
{
    $this->request = $request;

}

$this->request->merge(["custom" => "New Custom Value"]);

..

Validating a URL in PHP

Check if the variable $url is a valid URL:

The FILTER_VALIDATE_URL filter validates a URL.

Possible  flags:

FILTER_FLAG_SCHEME_REQUIRED - URL must be RFC compliant.
Like http://example

FILTER_FLAG_HOST_REQUIRED - URL must include host name.
Like http://www.example.com

FILTER_FLAG_PATH_REQUIRED - URL must have a path after domain.
Like www.example.com/example1/

FILTER_FLAG_QUERY_REQUIRED - URL must have a query string.
Like "example.php?name=Peter&age=37"

filter_var($url, FILTER_VALIDATE_URL, FILTER_FLAG_SCHEME_REQUIRED)
filter_var($url, FILTER_VALIDATE_URL, FILTER_FLAG_HOST_REQUIRED)
filter_var($url, FILTER_VALIDATE_URL, FILTER_FLAG_PATH_REQUIRED)
filter_var($url, FILTER_VALIDATE_URL, FILTER_FLAG_QUERY_REQUIRED)

Set floating div to height: 100% | Div as Table | Responsive Table | Table Table-Row Table-Cell | Table Div

lefttttttttttttttttttttttttt

middleeeeeeeeeeeeeeeeeeeeeee

rightttttttttttttttttttttttt

You could do it by setting up a table-like structure of <div> elements in your HTML and then using display: "table", "table-row" and "table-cell". This will work because table cells in the same row automatically resize to the same height as the tallest cell. However, IE7 will not support table properties.

<div id="table">
    <div id="table-tr">
        <div id="table-td">
            lefttttttttttttttttttttttttt
        </div>
        <div id="table-td">
            middleeeeeeeeeeeeeeeeeeeeeee
        </div>
        <div id="table-td">
            rightttttttttttttttttttttttt
        </div>
    </div>
</div>

<style type="text/css">
#table {
    background-color: red;
    width: 300px;
    height: 120px;
    overflow: auto;
    padding: 1px;
    display: table;
    table-layout:fixed;
}

#table-tr {
    display: table-row;
}

#table-td {
    width: 33.34%;
    display: table-cell;
    background-color: #ccc;
    border: 1px solid red;
    word-wrap: break-word;
    padding: 3px;
}
</style>

PHP Class for HTTP Response Status Codes

HTTP Response Status Codes:

<?php
class HttpStatusCodeUtils
{
    // [Informational 1xx]
    const HTTP_CONTINUE = 100;
    const HTTP_SWITCHING_PROTOCOLS = 101;
    // [Successful 2xx]
    const HTTP_OK = 200;
    const HTTP_CREATED = 201;
    const HTTP_ACCEPTED = 202;
    const HTTP_NONAUTHORITATIVE_INFORMATION = 203;
    const HTTP_NO_CONTENT = 204;
    const HTTP_RESET_CONTENT = 205;
    const HTTP_PARTIAL_CONTENT = 206;
    // [Redirection 3xx]
    const HTTP_MULTIPLE_CHOICES = 300;
    const HTTP_MOVED_PERMANENTLY = 301;
    const HTTP_FOUND = 302;
    const HTTP_SEE_OTHER = 303;
    const HTTP_NOT_MODIFIED = 304;
    const HTTP_USE_PROXY = 305;
    const HTTP_UNUSED = 306;
    const HTTP_TEMPORARY_REDIRECT = 307;
    // [Client Error 4xx]
    const HTTP_BAD_REQUEST = 400;
    const HTTP_UNAUTHORIZED = 401;
    const HTTP_PAYMENT_REQUIRED = 402;
    const HTTP_FORBIDDEN = 403;
    const HTTP_NOT_FOUND = 404;
    const HTTP_METHOD_NOT_ALLOWED = 405;
    const HTTP_NOT_ACCEPTABLE = 406;
    const HTTP_PROXY_AUTHENTICATION_REQUIRED = 407;
    const HTTP_REQUEST_TIMEOUT = 408;
    const HTTP_CONFLICT = 409;
    const HTTP_GONE = 410;
    const HTTP_LENGTH_REQUIRED = 411;
    const HTTP_PRECONDITION_FAILED = 412;
    const HTTP_REQUEST_ENTITY_TOO_LARGE = 413;
    const HTTP_REQUEST_URI_TOO_LONG = 414;
    const HTTP_UNSUPPORTED_MEDIA_TYPE = 415;
    const HTTP_REQUESTED_RANGE_NOT_SATISFIABLE = 416;
    const HTTP_EXPECTATION_FAILED = 417;
    // [Server Error 5xx]
    const HTTP_INTERNAL_SERVER_ERROR = 500;
    const HTTP_NOT_IMPLEMENTED = 501;
    const HTTP_BAD_GATEWAY = 502;
    const HTTP_SERVICE_UNAVAILABLE = 503;
    const HTTP_GATEWAY_TIMEOUT = 504;
    const HTTP_VERSION_NOT_SUPPORTED = 505;


    private static $messages = array(
        // [Informational 1xx]
        100 => '100 Continue',
        101 => '101 Switching Protocols',
        // [Successful 2xx]
        200 => '200 OK',
        201 => '201 Created',
        202 => '202 Accepted',
        203 => '203 Non-Authoritative Information',
        204 => '204 No Content',
        205 => '205 Reset Content',
        206 => '206 Partial Content',
        // [Redirection 3xx]
        300 => '300 Multiple Choices',
        301 => '301 Moved Permanently',
        302 => '302 Found',
        303 => '303 See Other',
        304 => '304 Not Modified',
        305 => '305 Use Proxy',
        306 => '306 (Unused)',
        307 => '307 Temporary Redirect',
        // [Client Error 4xx]
        400 => '400 Bad Request',
        401 => '401 Unauthorized',
        402 => '402 Payment Required',
        403 => '403 Forbidden',
        404 => '404 Not Found',
        405 => '405 Method Not Allowed',
        406 => '406 Not Acceptable',
        407 => '407 Proxy Authentication Required',
        408 => '408 Request Timeout',
        409 => '409 Conflict',
        410 => '410 Gone',
        411 => '411 Length Required',
        412 => '412 Precondition Failed',
        413 => '413 Request Entity Too Large',
        414 => '414 Request-URI Too Long',
        415 => '415 Unsupported Media Type',
        416 => '416 Requested Range Not Satisfiable',
        417 => '417 Expectation Failed',
        // [Server Error 5xx]
        500 => '500 Internal Server Error',
        501 => '501 Not Implemented',
        502 => '502 Bad Gateway',
        503 => '503 Service Unavailable',
        504 => '504 Gateway Timeout',
        505 => '505 HTTP Version Not Supported'
    );


    public static function httpHeaderFor($code)
    {
        return 'HTTP/1.1 ' . self::$messages[$code];
    }


    static function getMessageForCode($code)
    {
        return isset(self::$messages[$code]) ? self::$messages[$code] : "$code Invalid Request";
    }

    static function isError($code)
    {
        return is_numeric($code) && $code >= self::HTTP_BAD_REQUEST;
    }

    static function canHaveBody($code)
    {
        return
            // True if not in 100s
            ($code < self::HTTP_CONTINUE || $code >= self::HTTP_OK)
            && // and not 204 NO CONTENT
            $code != self::HTTP_NO_CONTENT
            && // and not 304 NOT MODIFIED
            $code != self::HTTP_NOT_MODIFIED;
    }
}
?>