List query in MySQL

Question

List query in MySQL

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I am trying to perform a MySQL query from CodeIgniter, and the query goes fine but I would like to add a field within the select that counts the records that the query returns.

SELECT r.fecha_inicio, r.fecha_fin, c.Nombre as cliente FROM reserva r, cliente c WHERE c.Id_cliente = r.Id_cliente AND r.Id_producto = @parametro

That's my simple query, how could I add that field more in the select?

final consultation

SELECT @i := @i + 1 as contador, r.fecha_inicio, r.fecha_fin, c.Nombre as Cliente 
FROM reserva r 
CROSS JOIN (select @i := 0) r
INNER JOIN cliente c 
ON r.Id_cliente = c.Id_cliente 
WHERE r.Id_espacio = @parametro

mysql sql php

asked by max 30.05.2017 в 02:55

source

2 answers

0

For MySQL

SELECT @n := @n + 1 n,
       first_name, 
       last_name
  FROM table1, (SELECT @n := 0) m
 ORDER BY first_name, last_name

I think that for your case it would look like this:

    SELECT @n := @n + 1 n, r.fecha_inicio, r.fecha_fin, c.Nombre as cliente 
FROM reserva r, (SELECT @n := 0) n, cliente c 
WHERE c.Id_cliente = r.Id_cliente AND r.Id_producto = @parametro

I got it out of this other question: link

answered by 30.05.2017 в 03:28

Extract two fields from a SQL table with JOIN in PHP Block backend Joomla IIS

score 0 · Accepted Answer

There are several ways to do it:

A. With a single query

SELECT @i := @i + 1 as contador, r.fecha_inicio, r.fecha_fin, c.Nombre as cliente 
FROM reserva r, cliente c 
cross join (select @i := 0) r
WHERE c.Id_cliente = r.Id_cliente AND r.Id_producto = @parametro

This part: cross join (select @i := 0) r allows you to initialize the counter without having to use SET.

B. With two queries

SET @i = 0; 

SELECT (@i := @i + 1) AS contador, r.fecha_inicio, r.fecha_fin, c.Nombre as cliente 
FROM reserva r, cliente c 
WHERE c.Id_cliente = r.Id_cliente AND r.Id_producto = @parametro

Note

It is recommended to use JOIN instead of WHERE to join the tables. I mean change this: WHERE c.Id_cliente = r.Id_cliente AND r.Id_producto = @parametro for a JOIN that joins tables c and r , since it is more effective than WHERE.

EDIT: COMPLETE EXAMPLE USING JOIN INSTEAD OF WHERE

As you can see in the Query 2 , we use: INNER JOIN cliente c ON r.id_cliente=c.id_cliente to join the tables, because it is much more efficient than using WHERE.

SQL Fiddle

MySQL 5.6 Schema Setup :

CREATE TABLE cliente
    ('id_cliente' int, 'cliente_nom' varchar(70))
;

INSERT INTO cliente
    ('id_cliente', 'cliente_nom')
VALUES
    (1, 'Pedro'),
    (2, 'Santiago'),
    (3, 'Juan')
;

CREATE TABLE reserva
    ('id_reserva' int, 'id_cliente' int, 'fecha' date, 'id_producto' int)
;

INSERT INTO reserva
    ('id_reserva', 'id_cliente', 'fecha', 'id_producto')
VALUES
    (1, 1,'2017-05-01', 1),
    (2, 2,'2017-05-05', 1),
    (3, 3,'2017-05-11', 1),
    (4, 1,'2017-05-14', 1),
    (5, 1,'2017-05-17', 2),
    (6, 2,'2017-05-20', 2)
;

Query 1 :

SET @parametro=1;

Query 2 :

SELECT 
  @i := @i + 1 as contador, 
  r.fecha,
  c.cliente_nom,
  r.id_producto
FROM reserva r
INNER JOIN cliente c ON r.id_cliente=c.id_cliente
CROSS JOIN (SELECT @i := 0) r
WHERE r.id_producto = @parametro

Results :

| contador |                 fecha | cliente_nom | id_producto |
|----------|-----------------------|-------------|-------------|
|        1 | May, 01 2017 00:00:00 |       Pedro |           1 |
|        2 | May, 05 2017 00:00:00 |    Santiago |           1 |
|        3 | May, 11 2017 00:00:00 |        Juan |           1 |
|        4 | May, 14 2017 00:00:00 |       Pedro |           1 |