Why is this SQL statement giving NON-Object error in PHP?

Question

Why is this SQL statement giving NON-Object error in PHP?

Navigation

#1 by (1 votes)

0

I have the following table called Orders :

Ordenes: 
id_orden = entero 
id_producto = entero

Example of records:

id_orden - id_producto
1        - 2
1        - 3
1        - 14
1        - 7

I would like to get all the id_producto that have a id_orden specific, so I get the id_orden without repetition before:

$sql= "SELECT DISTINCT id_orden FROM ordenes"; 
$sentencia1= $conexion->prepare($sql);
$sentencia1->execute();
$sentencia1->bind_result($id);
while ($sentencia1->fetch()) {

echo $id;

 $sql= "SELECT id_producto FROM ordenes WHERE id_orden=?"; 

 $sentencia2=$conexion->prepare($sql);

 $sentencia2->bind_param("i",$id);
 $sentencia2->execute();
 $sentencia2->bind_result($producto);

 while ($sentencia2->fetch()){
 echo "<td>".$producto."</td>";
 }
}

I get the following error:

Call to a member function bind_param() on a non-object en la linea donde esta el bind_param

Here the connection:

<?php
$conexion = new mysqli("localhost","root","","produccion");
?>

How can I get every id_orden and at the same time all id_producto that have the same id_orden ?

Note: Is it possible to convert those two SELECT into a JOIN?

php mysqli

asked by Victor Alvarado 23.05.2017 в 22:05

source

1 answer

How can I show a separate string array and its values? Difference between datadir and innodb_data_home_dir

score 1 · Accepted Answer

I have created a possible model of what I (intuit) want to do, which, I think, could help you rethink the way you are organizing your data.

The model has the following tables:

productos : where all the details of each product would be
clientes : all the details of each client would be
ordenes : register each particular order with an id, date, and other fields ...
ordenes_productos : it would keep all the products that have been ordered in each order, avoiding having to repeat the data that already exists in the tables products, orders, customers. This is called an associative table.

I could have more tables, all of them related to each other, for example, a table inventario , a table ordenes_tipo for credit orders, cash, etc., a table proveedores , etc, etc.

Queries Query 1, Query 2, and Query 3 filter orders by the product id. But you can also make any type of filter. For example, you can filter all the orders of a specific customer, all the orders of a specific date, the best-selling products, the products that do not have any sales ... if you extend the model you could obtain the quantity of products available in inventory, etc etc. The possibilities are huge, with a well-crafted design and everything would be done with a single SQL query applying JOIN properly.

The example I show you is something basic, hoping you can grasp the idea in general.

SQL Fiddle

MySQL 5.6 Schema Setup :

CREATE TABLE productos
    ('producto_id' int, 'producto_nom' varchar(50), 'producto_precio' decimal)
;

INSERT INTO productos
    ('producto_id', 'producto_nom', 'producto_precio')
VALUES
    (1, 'Pan', 0.90),
    (2, 'Vino', 2.00),
    (3, 'Aceite', 1.12)
;

CREATE TABLE clientes
    ('cliente_id' int, 'cliente_nom' varchar(50))
;

INSERT INTO clientes
    ('cliente_id', 'cliente_nom')
VALUES
    (1, 'Pedro'),
    (2, 'Santiago'),
    (3, 'Juan')
;

CREATE TABLE ordenes
    ('orden_id' int, 'cliente_id' int, 'orden_fecha' date)
;

INSERT INTO ordenes
    ('orden_id', 'cliente_id', 'orden_fecha')
VALUES
    (1, 1, '2017-05-10'),
    (2, 2, '2017-05-11'),
    (3, 3, '2017-05-12'),
    (4, 3, '2017-05-13'),
    (5, 1, '2017-05-15')
;

CREATE TABLE ordenes_productos
    ('orden_id' int, 'producto_id' int, 'cantidad' int)
;

INSERT INTO ordenes_productos
    ('orden_id', 'producto_id', 'cantidad')
VALUES
    (1, 1, 100),
    (1, 3, 5),
    (2, 1, 500),
    (2, 2, 200),
    (3, 1, 643),
    (3, 2, 980)
;

Query 1 :

-- Clientes que han comprado producto 1
SELECT
  p.producto_id, p.producto_nom, c.cliente_nom, o.orden_fecha
FROM
  ordenes_productos op
INNER JOIN 
  ordenes o ON op.orden_id=o.orden_id
INNER JOIN 
  productos p ON op.producto_id=p.producto_id
INNER JOIN 
  clientes c ON o.cliente_id=c.cliente_id
WHERE p.producto_id=1

Results :

| producto_id | producto_nom | cliente_nom |           orden_fecha |
|-------------|--------------|-------------|-----------------------|
|           1 |          Pan |       Pedro | May, 10 2017 00:00:00 |
|           1 |          Pan |    Santiago | May, 11 2017 00:00:00 |
|           1 |          Pan |        Juan | May, 12 2017 00:00:00 |

Query 2 :

-- Clientes que han comprado producto 2
SELECT
  p.producto_id, p.producto_nom, c.cliente_nom, o.orden_fecha
FROM
  ordenes_productos op
INNER JOIN 
  ordenes o ON op.orden_id=o.orden_id
INNER JOIN 
  productos p ON op.producto_id=p.producto_id
INNER JOIN 
  clientes c ON o.cliente_id=c.cliente_id
WHERE p.producto_id=2

Results :

| producto_id | producto_nom | cliente_nom |           orden_fecha |
|-------------|--------------|-------------|-----------------------|
|           2 |         Vino |    Santiago | May, 11 2017 00:00:00 |
|           2 |         Vino |        Juan | May, 12 2017 00:00:00 |

Query 3 :

-- Clientes que han comprado producto 3
SELECT
  p.producto_id, p.producto_nom, c.cliente_nom, o.orden_fecha
FROM
  ordenes_productos op
INNER JOIN 
  ordenes o ON op.orden_id=o.orden_id
INNER JOIN 
  productos p ON op.producto_id=p.producto_id
INNER JOIN 
  clientes c ON o.cliente_id=c.cliente_id
WHERE p.producto_id=3

Results :

| producto_id | producto_nom | cliente_nom |           orden_fecha |
|-------------|--------------|-------------|-----------------------|
|           3 |       Aceite |       Pedro | May, 10 2017 00:00:00 |