mysql swap items with row number

Question

This is my table structure , Here i need to swap items .Which means, you can see type 3 always comes paired( type 3 items are always paired ). I just named paired items for understanding first 1 in pair is master and second one is sub. So master of the pair should not come 5,10 and 15 positions if it comes that place i need to swap the next item into that place(neaxt item will be sub it should not considered as next item)

for example

pid 10 (comes in 10 position) i need to swap it like this
pid   type  name
..     ..   ..
10      2    B2

11      3    E1(master) 
12      3    A2(sub)

..     ..   ..

Table

 pid    type    pname
  1       1     A
  2       1     B
  3       2     C
  4       3     D(mater)
  5       3     E(sub)

  6       1     A1
  7       2     B1
  8       1     C1
  9       2     D1
  10      3     E1(master)    

  11      3     A2(sub)
  12      2     B2  
  13      1     C2
  14      2     D2
  15      1     E3 

screenshot

1. perfect placement
2. Collapsed design

FOR FURTHER HELP

I GIVING YOU TABLE STRUCTURE AND TEST DATA, PLEASE IF YOU HAVE IDEA SHARE WITH ME !

CREATE QUERY

CREATE TABLE IF NOT EXISTS `table_swap1` (
    `pid` int(11) NOT NULL AUTO_INCREMENT,
    `type` int(11) NOT NULL,
    `name` varchar(50) NOT NULL,
    PRIMARY KEY (`pid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17;

INSERT QUERY

INSERT INTO `table_swap1` (`pid`, `type`, `name`) VALUES
    (1, 1, 'A'),(2, 1, 'B'),(3, 2, 'D'),(4, 3, 'E(master)'),
    (5, 3, 'f(sub)'),(6, 1, 'A1'),(7, 2, 'B1'),(8, 1, 'C1'),
    (9, 2, 'D1'),(10, 3, 'E1(master)'),(11, 3, 'A2(sub)'),(12, 2, 'B2'),
    (13, 1, 'C2'),(14, 2, 'D2'), (15, 1, 'E2');

My work and Result

  SELECT aa.pid, (

    CASE aa.pid
    WHEN bb.apid
    THEN bb.atype
    WHEN bb.bpid
    THEN bb.btype
    WHEN bb.cpid
    THEN bb.ctype
    ELSE aa.type
    END
    )
    TYPE , (

    CASE aa.pid
    WHEN bb.apid
    THEN bb.aname
    WHEN bb.bpid
    THEN bb.bname
    WHEN bb.cpid
    THEN bb.cname
    ELSE aa.name
    END
    )name
    FROM (

    SELECT a.pid +1 apid, a.TYPE atype, a.NAME aname,
           b.pid +1 bpid, b.type btype, b.name bname,
           c.pid -2 cpid, c.type ctype, c.name cname
    FROM table_swap1 a, table_swap1 b, table_swap1 c
    WHERE MOD( a.pid, 5 ) =0
    AND a.pid +1 = b.pid
    AND a.type =3
    AND a.type = b.type
    AND a.pid +2 = c.pid
    )bb, table_swap1 aa
    GROUP BY pid

This Query did what i exactly need ... But In my case pid is Primary key. so i can't get the results in 1 to 15 order . So can i do this row number... how can i do

all kind of suggestions are welcome even solution in php let me is this possible in mysql or any other way to do this ..

Answer 1

So, basically your problem can be formulated as :

The first product of two adjacent products of type 3 cannot be placed 
in a position which is a multiple of 5.

What complicates things is that there is no order in your table, and without an order, it is impossible to define a constant "position" for your products. The order of returned rows for a SELECT without ORDER BY is not specified.

Anyway, the simplest way to do this is in the application. Grab your results as an array, scan it, and if you find two products that are not in the right position, simply shuffle them around in the array.

Answer 2

previous solution - bad solution

---

Edit: I don't think a solution is really possible in this case. Even you it happens possible to reorder items (which will not always be the case) it might happen that items that expire sooner will be after items that expire later.

What you can do:

1. in all cases you need to use one entry per master+sub pair.
   1. make a table product_list(prodid, subid) where subid can be null for non-product tables
   2. join product table products once to fetch master and second time to fetch sub (tell me if you need the query) and group by product_list.prodid
2. showing the products - choose one of
   1. make a design that allows you to put 2 items in one square.
   2. put a button "bonus product" with popup or javascript animation showing the other product
   3. on mouseover expand the product to the right or left depending on position.

Answer 3

I think you need to redesign your tables to avoid having to swap rows - remember, order of rows should be immaterial in a relational table, and mySQL is a relational DB.

Here is a possible redesign - though let me know if I don't understand your question correctly:

Table product
-------------
pid
---
name
type

Table productPair
-----------------
pid*
---
part1*
part2*

When a product is a pair in a box, you enter it both as a product, and as a productpair, with the same key. With appropriate constraints, you can model the design so that pairs are only made of one type 1 and one type2 product, etc.

Some example data:

+------------------+
|product           |
+------------------+
|PID |type  |name  |
+----+------+------+
|1   |1     |A     |
|2   |1     |B     |
|3   |2     |C     |
|4   |3     |D     |
|5   |3     |E     |
+----+------+------+

+------------------+
|productPair       |
+------------------+
|PID*|part1*|part2*|
+----+------+------+
|4   |1     |3     |
|5   |2     |3     |
+----+------+------+

In this form, no need for row order, the relations encode the nature of each pair, rather than the position of the data in the table.

Answer 4

Yes, I also suggest you to redesign here, but for quik-n-dirty result just add to your SQL something like this:

select
    table_swap1.*,
    @row_number := @row_number + 1 as row_number,
    0 = mod(@row_number, 5) is_fifth
from
    table_swap1,
    (SELECT @row_number := 0) tmp
;

Answer 5

My environment is SQL Server. I've written my solution in that syntax...I'll attempt to rewrite in MySQL (below the SQL Server original), but I'm sure you'll understand my point (whether or not I've rewritten correctly). Basically, use the bb subquery to establish an unspecified join (Cartesian product), so that the pid of every master row which falls on a multiple of 5 is available to all other rows, then recalculate the rows that match (and the affected adjoining rows) into a Resequence column. If I understand your data correctly, you might even leave out the type=3 AND phrase.

SELECT (CASE 
WHEN aa.pid = bb.pid THEN aa.pid+1
WHEN aa.pid-1=bb.pid THEN aa.pid+1
WHEN aa.pid-2=bb.pid THEN aa.pid-2
ELSE aa.pid END) ResequencePid
, aa.pid, aa.type, aa.name
FROM table_swap1 aa, 
(SELECT pid FROM table_swap1 WHERE type=3 AND pid%5=0 AND name LIKE '%(master)') bb
/* optional */ ORDER BY 1

/* MySQL version */

SELECT (CASE
WHEN aa.pid = bb.pid THEN aa.pid+1
WHEN aa.pid-1=bb.pid THEN aa.pid+1
WHEN aa.pid-2=bb.pid THEN aa.pid-2
ELSE aa.pid END) ResequencePid
, aa.pid, aa.type, aa.name
FROM table_swap1 aa, 
(SELECT pid FROM table_swap1 WHERE type=3 AND MOD(pid, 5)=0 AND name LIKE '%(master)') bb
/* optional */ ORDER BY 1