Select query with join in php

Содержание

PHP Laravel select with join subquery example
Do you like below Tutorials ?
Write tidy Select and Join SQL Queries in PHP
Main methods
INNER JOIN и детальное пояснение
Решение
INNER JOIN и детальное пояснение
Решение

PHP Laravel select with join subquery example

Today, i will let you know how to use select with join subquery using Laravel eloquent. You will learn inner join or left join with select query in Laravel eloquent. Let’s see example for laravel 5, laravel 5.1, laravel 5.2, laravel 5.3, laravel 5.4, laravel 5.5 eloquent select join query example.

Here we will use DB::raw() with join() function of laravel 5 eloquent. In this article i will let you know how you can convert mysql or sql query of select with join in Laravel 5 application.

As you can see bellow i added simple Mysql Query for select with join in Laravel. So let’s see bellow query:

MySQL Query:

SELECT

products.*, product_stock.quantity_group

FROM

products

INNER JOIN (SELECT

product_stock.id_product,

GROUP_CONCAT(product_stock.quantity) as quantity_group

FROM product_stock

GROUP BY product_stock.id_product) AS product_stock

ON product_stock.id_product = products.id

GROUP BY products.id

As you can see above sql query now we convert into laravel 5 db query.

$data = DB::table(«products»)

->select(«products.*»,»product_stock.quantity_group»)

->join(DB::raw(«(SELECT

product_stock.id_product,

GROUP_CONCAT(product_stock.quantity) as quantity_group

FROM product_stock

GROUP BY product_stock.id_product

) as product_stock»),function($join)

$join->on(«product_stock.id_product»,»=»,»products.id»);

->groupBy(«products.id»)

->get();

print_r($data);

I hope you found your best. 🙂

Do you like below Tutorials ?

Источник

Write tidy Select and Join SQL Queries in PHP

Most of us have to interact with databases now and then in our projects, and SQL is by far the most common language used. However, working with SQL in PHP can be messy. If your queries are complex, you have to code them up as text strings which can be error prone, and suffer from formatting challenges. Also, when you want to build your SQL queries to have variables inside them, then you are forced to do substitution or pasting, which is a little bit tricky.

Today I will walk you through writing long and confusing Select and Join SQL Queries with ease in PHP. All you have to do is copy and paste this PHP file and save it in your project. The file contains a PHP class DbQuery with several methods/functions.

Download the source code of this project on Github.

The DbQuery class is a query builder which helps you create SQL queries. For instance:

$sql = new DbQuery(); $sql->select('*'); $sql->from('product', 'p'); $sql->where('p.product_id > 1'); $sql->where('p.category_id > 3'); $sql->orderBy('p.product_id'); $sql->limit(5, 10); echo $sql;

The above code will output the below query.

SELECT * FROM `product` p WHERE (p.product_id > 1) AND (p.category_id > 3) ORDER BY p.product_id LIMIT 10, 5

Main methods

__toString() – Generate and get the query.

build() – Generate and get the query (return a string).

from(string $table, mixed $alias = null) – Set table for FROM clause.

groupBy(string $fields) – Add a GROUP BY restriction.

having(string $restriction) – Add a restriction in the HAVING clause (each restriction will be separated by an AND statement).

innerJoin(string $table, string $alias = null, string $on = null) – Add a INNER JOIN clause
E.g. $this->innerJoin(‘product p ON . ‘) .

join(string $join) – Add a JOIN clause
E.g. $this->join(‘RIGHT JOIN’.DB_PREFIX.’product p ON . ‘); .

leftJoin(string $table, string $alias = null, string $on = null) – Add a LEFT JOIN clause.

leftOuterJoin(string $table, string $alias = null, string $on = null) – Add a LEFT OUTER JOIN clause.

limit(string $limit, mixed $offset = 0) – Limit results in query.

naturalJoin(string $table, string $alias = null) – Add a NATURAL JOIN clause.

orderBy(string $fields) – Add an ORDER BY restriction.

select(string $fields) – Add fields in query selection.

where(string $restriction) – Add a restriction in WHERE clause (each restriction will be separated by an AND statement).

Below are some samples on how to use the SQL Query builder. First we will start by connecting to our database. Please note that the DB_PREFIX constant must be included in your since it’s required in the DbQuery class.

require_once dirname(__FILE__) . '/db-query.php'; define( 'DB_HOST', 'localhost' ); define( 'DB_USER', 'root' ); define( 'DB_PASSWORD', 'password' ); define( 'DB_NAME', 'sql_builder' ); /** Must include this constant in your script since it's used by the DbQuery class */ define( 'DB_PREFIX', '' ); error_reporting(E_ALL); ini_set('display_errors', 1); // Create connection $conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); // Check connection if ($conn->connect_error) < die("Connection failed: " . $conn->connect_error); >

All the code below will be pasted after the above section. I will write sample codes and echo the SQL query below.

SELECT & ORDER BY

$sql = new DbQuery(); $sql->select('*'); $sql->from('product', 'p'); $sql->orderBy('p.product_id'); $result = $conn->query($sql); if ($result->num_rows > 0) < while($row = $result->fetch_assoc()) < print_r($row); >>

SELECT * FROM `product` p ORDER BY p.product_id

WHERE, OR & LIMIT

$sql = new DbQuery(); $sql->select('*'); $sql->from('product', 'p'); $sql->where('p.category_id = 3 OR p.category_id = 5'); $sql->orderBy('p.product_id'); $sql->limit(5, 10);

SELECT * FROM `product` p WHERE (p.category_id = 3 OR p.category_id = 5) ORDER BY p.product_id LIMIT 10, 5

COUNT, GROUP BY

$sql = new DbQuery(); $sql->select('COUNT(category_id) AS cat_sum, category_id'); $sql->from('product'); $sql->groupBy('category_id'); $result = $conn->query($sql); if ($result->num_rows > 0) < while($row = $result->fetch_assoc()) < print_r($row); >>

SELECT COUNT(category_id) AS cat_sum, category_id FROM `product` GROUP BY category_id

/** * Sql Joins * INNER JOIN: returns rows when there is a match in both tables. * LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table. * RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table. **/ $sql = new DbQuery(); $sql->select('p.product_id, p.name AS product_name, c.name'); $sql->from('product', 'p'); $sql->innerJoin('category', 'c', 'p.category_id = c.category_id'); $result = $conn->query($sql); if ($result->num_rows > 0) < while($row = $result->fetch_assoc()) < print_r($row); >>

SELECT p.product_id, p.name AS product_name, c.name FROM `product` p INNER JOIN `category` `c` ON p.category_id = c.category_id

$sql = new DbQuery(); $sql->select('COUNT(p.product_id) AS prod_sum, p.category_id'); $sql->from('product', 'p'); $sql->having('COUNT(p.category_id) > 5'); $sql->groupBy('category_id'); $result = $conn->query($sql); if ($result->num_rows > 0) < while($row = $result->fetch_assoc()) < print_r($row); >>

SELECT COUNT(p.product_id) AS prod_sum, p.category_id FROM `product` p GROUP BY category_id HAVING (COUNT(p.category_id) > 5)

And that’s it guys for today. Thanks for reading! If you love articles like this, be sure to leave a comment and share it on social media. Happy Coding!

Download the source code of this project on Github.

Источник

INNER JOIN и детальное пояснение

Если можна более подробно что и как работает.
Спасибо!

Не выполняется код PHP. Warning: join() [function.join]: Invalid arguments passed
Помогите разобраться в коде <?php $text = file_get_contents(‘input.txt’); .

Ошибка: no matching function for call to ‘QStringList::join()’ file.write(lines.join();
почему возникает эта ошибка? у меня в qt creator-е автодополнение показывает что есть такой метод.

Сообщение было отмечено mounster как решение

Решение

Допустим, есть 2 таблицы: users и groups. У users 3 поля: id, name, group_id. У groups 2 поля: id, name.

Представим, что нам нужно получить таблицу с 2 колонками: имя пользователя и название его группы, т.е. Вася -> 308 (users.name -> groups.name).

SELECT users.name AS 'user', groups.name AS 'group' FROM `users` LEFT JOIN `groups` ON users.group.id = groups.id

В данном примере мы получаем всех пользователей, а во вторую колонку записывается либо название группы (если группа с таким id есть в таблице groups), либо пустота.

Если вместо LEFT JOIN мы напишем INNER JOIN, то выведутся только те пользователи и группы, у которых есть совпадения по условию users.group.id = groups.id.

Т.е. если есть пользователь «Петя», у которого в group_id указан идентификатор несуществующей группы, то в случае LEFT JOIN он попадёт в вывод, в случае INNER JOIN нет.

Для INNER JOIN неважно, в какой последовательности писать таблицы. Следующие примеры равнозначны:

SELECT * FROM `users` INNER JOIN `groups`ON . SELECT * FROM `groups` INNER JOIN `users` ON.

В случае LEFT JOIN выведутся все записи первой (левой) таблицы, а добавляться данные будут из правой (можно написать RIGHT JOIN, суть та же).

Можно использовать сразу несколько INNER JOIN для сложных условий. Например, получить название, бренд и название категории товара с заданным id. Здесь нужно объединять 3 таблицы: products, brands и categories.

В вашем случае код будет такой:

$query = " SELECT region.name FROM `region` INNER JOIN `link_location` ON region.id = link_location.region_id INNER JOIN `city` ON city.id = link_location.city_id WHERE link_location.item_id = " . $id;

Источник

INNER JOIN и детальное пояснение

Если можна более подробно что и как работает.
Спасибо!

Сообщение было отмечено mounster как решение

Решение

Допустим, есть 2 таблицы: users и groups. У users 3 поля: id, name, group_id. У groups 2 поля: id, name.

SELECT users.name AS 'user', groups.name AS 'group' FROM `users` LEFT JOIN `groups` ON users.group.id = groups.id

Для INNER JOIN неважно, в какой последовательности писать таблицы. Следующие примеры равнозначны:

SELECT * FROM `users` INNER JOIN `groups`ON . SELECT * FROM `groups` INNER JOIN `users` ON.

В вашем случае код будет такой:

$query = " SELECT region.name FROM `region` INNER JOIN `link_location` ON region.id = link_location.region_id INNER JOIN `city` ON city.id = link_location.city_id WHERE link_location.item_id = " . $id;

Источник