[eluser]Isern Palaus[/eluser]
Hello,
I would like to ask if someone can help me with a logical problem. I am coding a electronic commerce system with CodeIgniter and most of the entries in my tables have an order field.
Like this:
Code: manufacters
------------
idmanufacter
image
name
description
order
active
Like the table above I've: products, languages, categories... and such more.
I want that the client can order all the website. Starting the application, and with a 'products' table example, the first entry that we add will have the value order=1. The second, order=2, the third=3:
Code: product1 => order=1
product2 => order=2
product3 => order=3
Then, if the client changes the product3 to order=1 I need that the all order values changes:
Code: product1 => order=2
product2 => order=3
product3 => order=1
In the same way, if we delete a record, we have to update another time all the entries (delete product3):
Code: product1 => order=1
product2 => order=2
At this moment, I have no idea how to start... Can someone help me in make this possible?
Sorry for my school English in this explanation!
Thanks in advance,
Isern
[eluser]Flemming[/eluser]
Think about what happens to the OTHER products when one is moved.
If product 3 is moved to order #1 then every product ABOVE product 3 needs to change its position PLUS 1.
So you can do an SQL update like this:
Code: UPDATE products SET order = order+1 WHERE order < $order_of_item_that_is_being_moved
then you can update the order of the item you are moving to order #1
Code: UPDATE products SET order = 1 WHERE id = $id_of_item_that_is_being_moved
Perhaps that will help you to get started?
[eluser]Isern Palaus[/eluser]
[quote author="Flemming" date="1259093396"]Think about what happens to the OTHER products when one is moved.
If product 3 is moved to order #1 then every product ABOVE product 3 needs to change its position PLUS 1.
So you can do an SQL update like this:
Code: UPDATE products SET order = order+1 WHERE order < $order_of_item_that_is_being_moved
then you can update the order of the item you are moving to order #1
Code: UPDATE products SET order = 1 WHERE id = $id_of_item_that_is_being_moved
Perhaps that will help you to get started?[/quote]
Hello Flemming,
This helps me a lot. This will work fine if you're moving item per item. In the view will be the list and a UP and DOWN arrows to move up or down.
Now, I'm thinking... and I've the category separated and thinking that the most possible case will be that want to order the categories. But I've also subcategories... And a first page with all products. OMFG.
The best way, probably, will be separate the order from the products table and have a field for PRODUCT_ID, CATEGORY_ID and ORDER... no?
Thank you!
Isern
[eluser]Flemming[/eluser]
Hi Isern,
OMFG indeed!
I have to say, I don't fully understand what it is you need to achieve. A screenshot might be useful?
[eluser]Isern Palaus[/eluser]
Hey Flemming,
Sorry my English is not fine... haha.
Well let me introduce. This is my products table:
Code: CREATE TABLE IF NOT EXISTS `producto` (
`idproducto` int(11) NOT NULL AUTO_INCREMENT,
`referencia` varchar(255) COLLATE utf8_spanish2_ci DEFAULT NULL,
`orden` int(11) DEFAULT NULL, <-- =order This will be deleted!!!!
`activo` tinyint(4) DEFAULT '1',
`stock` tinyint(1) NOT NULL DEFAULT '1',
`borrado` tinyint(1) DEFAULT '0',
`fecha` timestamp NULL DEFAULT NULL,
`fecha_borrado` timestamp NULL DEFAULT NULL,
`fecha_modificado` timestamp NULL DEFAULT NULL,
`imagen` varchar(125) COLLATE utf8_spanish2_ci DEFAULT NULL,
`visitas` varchar(45) COLLATE utf8_spanish2_ci DEFAULT '0',
`fabricante_idfabricante` int(11) NOT NULL,
PRIMARY KEY (`idproducto`),
KEY `fk_producto_fabricante1` (`fabricante_idfabricante`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci AUTO_INCREMENT=3 ;
At this moment I've 'orden' = order.. but this is not the system I want. Then I've a table to organize categories:
Code: CREATE TABLE IF NOT EXISTS `producto_has_categoria` (
`producto_idproducto` int(11) NOT NULL AUTO_INCREMENT,
`categoria_idcategoria` int(11) NOT NULL,
PRIMARY KEY (`producto_idproducto`,`categoria_idcategoria`),
KEY `fk_producto_has_categoria_producto1` (`producto_idproducto`),
KEY `fk_producto_has_categoria_categoria1` (`categoria_idcategoria`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci AUTO_INCREMENT=3 ;
As you can see, I don't have the category id inside the product. This allows me to have multiple categories for one product, but atm I'm not using this.
So... what I'm planing to do is allow the client organize the product by categories. If you have 10 categories you can have the products ordered by 10 different ways. My plan is something like:
Code: +-------------+------------+-------+
| category_id | product_id | order |
+-------------+------------+-------+
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 1 | 3 | 3 |
| 2 | 4 | 1 |
| 2 | 5 | 3 |
| 2 | 6 | 2 |
+-------------+------------+-------+
This is more explicative, no?
Then I suppose that the work flow will be something like:
ADD PRODUCT (SET CATEGORY) --> ADD IT TO ORDER TABLE (IN LAST POSITION FOR THE CATEGORY ID)
If you want to edit order:
ACCESS TO ORDER SECTION --> SET CATEGORY FOR ORDER --> SEE A LIST OF ALL PRODUCTS INSIDE THE CATEGORY --> ALLOW YOU TO ORDER IT
If you access to another category the order can be different.
It's hard but it's the way I've to go I think! 
Wish it helps you to understand, it helps to me to have it more clear too!
Regards,
Isern
[eluser]Isern Palaus[/eluser]
Hello,
This is my solution to ordering problem.
/application/libraries/gestion.php
Code: <?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Gestion {
private $_primera_posicion = 1;
public function __construct()
{
$this->ci =& get_instance();
}
/**
* Funcion incrementar_posicion()
* Funcion que de forma generica incrementa una posicion el id que le pasamos. Podemos usar la configuracion
* con $tabla_madre y $id_tabla_madre para poder hacer un WHERE como referencia.
*
* @param string $tabla
* @param string $tabla_madre
* @param string $campo
* @param int $posicion
* @param int $id
* @param int $id_tabla_madre
* @return bool
*/
public function incrementar_posicion($tabla, $tabla_madre = NULL, $campo = 'orden', $posicion, $id, $id_tabla_madre = NULL)
{
/**
* Cargamos los modelos
*/
$this->ci->load->model('admin/gestion_model');
if(isset($tabla, $campo, $posicion, $id))
{
/**
* Comprovar cual es el maximo. Si es mayor que maximo no lo subiremos.
*/
$maximo = $this->ci->gestion_model->existe_superior($tabla, $tabla_madre, $campo, $id_tabla_madre);
$maximo = $maximo+1;
if($maximo > $posicion)
return $this->ci->gestion_model->incrementar_posicion($tabla, $tabla_madre, $campo, $posicion, $id, $id_tabla_madre);
return FALSE;
}
return FALSE;
}
/**
* Funcion disminuir_posicion()
* Funcion que de forma generica disminuye una posicion el id que le pasamos. Podemos usar la configuracion
* con $tabla_madre y $id_tabla_madre para poder hacer un WHERE como referencia.
*
* @param string $tabla
* @param string $tabla_madre
* @param string $campo
* @param int $posicion
* @param int $id
* @param int $id_tabla_madre
* @return bool
*/
public function disminuir_posicion($tabla, $tabla_madre = NULL, $campo = 'orden', $posicion, $id, $id_tabla_madre)
{
/**
* Cargamos los modelos
*/
$this->ci->load->model('admin/gestion_model');
if(isset($tabla, $campo, $posicion, $id))
{
/**
* Comprobar la posicion actual. Si es 1, no lo bajaremos.
*/
$actual = $this->ci->gestion_model->posicion_actual($tabla, $campo, $id);
if($actual != $this->_primera_posicion)
return $this->ci->gestion_model->disminuir_posicion($tabla, $tabla_madre, $campo, $posicion, $id, $id_tabla_madre);
return FALSE;
}
return FALSE;
}
public function proxima_posicion($tabla, $tabla_madre = NULL, $campo = 'orden', $id_tabla_madre = NULL)
{
/**
* Cargamos los modelos
*/
$this->ci->load->model('admin/gestion_model');
if(isset($tabla, $campo))
{
/**
* Obtiene el valor mas alto actual y le suma una para tener el proximo valor
*/
$proxima = $this->ci->gestion_model->existe_superior($tabla, $tabla_madre, $campo, $id_tabla_madre);
$proxima = $proxima+1;
return $proxima;
}
return FALSE;
}
public function posicion_actual($tabla, $campo = 'orden', $id)
{
/**
* Cargamos los modelos
*/
$this->ci->load->model('admin/gestion_model');
if(isset($tabla, $campo, $id))
return $this->ci->gestion_model->posicion_actual($tabla, $campo, $id);
return FALSE;
}
public function actualizar_tabla($tabla, $tabla_madre = NULL , $campo = 'orden', $posicion, $id_tabla_madre = NULL)
{
/**
* Cargamos los modelos
*/
$this->ci->load->model('admin/gestion_model');
if(isset($tabla, $campo, $posicion))
return $this->ci->gestion_model->actualizar_tabla($tabla, $tabla_madre, $campo, $posicion, $id_tabla_madre);
return FALSE;
}
}
[eluser]Isern Palaus[/eluser]
/application/models/admin/gestion_model.php
Code: <?php
class Gestion_model extends Model {
function __construct()
{
parent::Model();
}
public function posicion_actual($tabla, $campo, $id)
{
$this->db->select($campo)
->where('id'.$tabla, $id);
$query = $this->db->get($tabla);
$actual = $query->row();
return $actual->orden;
}
public function existe_superior($tabla, $tabla_madre = NULL, $campo, $id = NULL)
{
$tabla_madre = $tabla_madre.'_id'.$tabla_madre;
$this->db->select_max($campo);
if($tabla_madre)
$this->db->where($tabla_madre, $id);
$query = $this->db->get($tabla);
$maximo = $query->row();
return $maximo->orden;
}
public function actualizar_orden($tabla, $tabla_madre = NULL, $campo = 'orden', $posicion, $id_tabla_madre = NULL)
{
$tabla_madre = $tabla_madre.'_id'.$tabla_madre;
$query = 'UPDATE '.$tabla.' SET '.$campo.'='.$campo.'-1 WHERE '.$campo.'>'.$posicion;
if($tabla_madre)
$query .= ' AND '.$tabla_madre.'='.$id_tabla_madre;
return $this->db->query($query);
}
private function _posicion($tabla, $accion, $tabla_madre = NULL, $campo = 'orden', $posicion, $id, $id_tabla_madre = NULL)
{
$idtabla = 'id'.$tabla;
$tabla_madre = $tabla_madre.'_id'.$tabla_madre;
switch ($accion) {
case "incrementar":
$query = 'UPDATE '.$tabla.' SET '.$campo.'='.$campo.'-1 WHERE '.$campo.'='.$posicion;
break;
case "disminuir":
$query = 'UPDATE '.$tabla.' SET '.$campo.'='.$campo.'+1 WHERE '.$campo.'='.$posicion;
break;
default:
return FALSE;
}
if($tabla_madre)
$query .= ' AND '.$tabla_madre.'='.$id_tabla_madre;
if($this->db->query($query))
{
$query = 'UPDATE '.$tabla.' SET '.$campo.'='.$posicion.' WHERE '.$idtabla.'='.$id;
if($tabla_madre)
$query .= ' AND '.$tabla_madre.'='.$id_tabla_madre;
return $this->db->query($query);
}
return FALSE;
}
public function incrementar_posicion($tabla, $tabla_madre = NULL, $campo = 'orden', $posicion, $id, $id_tabla_madre = NULL)
{
return $this->_posicion($tabla, 'incrementar', $tabla_madre, $campo, $posicion, $id, $id_tabla_madre);
}
public function disminuir_posicion($tabla, $tabla_madre = NULL, $campo = 'orden', $posicion, $id, $id_tabla_madre = NULL)
{
return $this->_posicion($tabla, 'disminuir', $tabla_madre, $campo, $posicion, $id, $id_tabla_madre);
}
}
|
