mysql - Creating specific size

Question

I have a table in html.I want to add that table with php on my database. The problem is with my table. I have done that

CREATE TABLE `playersrb` (
`position` numeric(24) DEFAULT NULL,
`piece_color` enum('B','R') NOT NULL,
`id` numeric(30) DEFAULT NULL,
`last_action` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
)

I have 24 positions and that I want to give in my table that those 24 are that size,not 25 and going on..Second,I have B and R that it is the color (It is ok that).Now I put Id because that's how I name the images I have on my table (the table that it is in my html) ,I name those Id:0 ,Id:1 until 30 (because 30 are the max images I have - I don't want more).

I create with that my table,I open sqlite,I go to that table and I start putting

position  piece_color    id   last_action
Null      | R           | 0   | here it was saying the time    
Null      | R           | 1   | the time as previous 
Null      | B           | 2   |  
Null      | B           | 3   |   
Null      | R           | 4   | 
Null      | R           |  5  |
Null      |B            | 6   |

and it goes like this until the end of 30
. . . . . I press save the button ,all fine.I go to phpmyadmin to check my table and it wasn't as I created..How can I do that thing?to have position 24 ,30 id (that will be different images ) .Save the 30 Id to 24 position.

edited: as you can see from the image I have created position and id .The table I want is like those. The position is where it belongs my image. The id is the image.I just want to pass from a table I have the position and the id in that table and I want to be right..When I move those images they create a table with variable position and Id. That table I want to pass into my table (in database table).If I put more images I will have Id 2 and the position which I dragged.That's what I am trying to do. As you can understand I want to have only 30 images.Every image is unique. They have other Id,not the same.More details,id =0 is the image a , the id =1 is the image b .The positions is ,in the image as you can see it is just the number of the table you see it where I move those images.

EDITED

<table class="content-table">
<tr>

<th>Image</th>
<th>Position(TO)</th>
<th>New Position</th>
</tr>
</div>


  
</div>


</div>

<?php

require_once "C/dbconnect.php";
















    
    $sql = "SELECT image, position,new_position  FROM playersrb" ;
    $sql="
    
    SET @old_position = 1;
SET @new_position = 12;

SELECT image
     , position old_order
     , ROUND(CASE WHEN position NOT BETWEEN LEAST(@old_position,@new_position) AND GREATEST(@old_position,@new_position) 
                  THEN position 
                  WHEN position = @old_position THEN @new_position
                  ELSE position+(((@new_position<@old_position)-.1)*12)
                  END 
            ,0) new_order 
  FROM playersrb;
    
    ";
    
    
    
    
    
    
    
    $result = $mysqli-> query($sql);
    
    if($result-> num_rows >0) {
    while ($row = $result-> fetch_assoc()){
    echo "<tr><td>". $row["image"] ."</td><td>". $row["position"] ."</td></tr>";
    }
    echo "</table>";
    }
    else{
    echo "0 result";
    }
    $mysqli->close();


?>
</table>

Answer 1

I don't really understand your question, but here's an example of re-ordering a list...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(image CHAR(1) NOT NULL PRIMARY KEY 
,position INT NOT NULL
);

INSERT INTO my_table VALUES
('A',1),
('B',2),
('C',3),
('D',4),
('E',5),
('F',6);

So, let's say we want to drag the image in position 5 to position 2...

SET @old_position = 5;
SET @new_position = 2;

SELECT image
     , position old_order
     , ROUND(CASE WHEN position NOT BETWEEN LEAST(@old_position,@new_position) AND GREATEST(@old_position,@new_position) 
                  THEN position 
                  WHEN position = @old_position THEN @new_position
                  ELSE position+(((@new_position<@old_position)-.5)*2)
                  END 
            ,0) new_order 
  FROM my_table;

+-------+-----------+-----------+
| image | old_order | new_order |
+-------+-----------+-----------+
| A     |         1 |         1 |
| B     |         2 |         3 |
| C     |         3 |         4 |
| D     |         4 |         5 |
| E     |         5 |         2 |
| F     |         6 |         6 |
+-------+-----------+-----------+

Here's a fuller example, using some PHP to output to HTML... perhaps someone else can make it pretty...

<?php
//simple_sorter.php
//Preamble

/*
A simple row repositioning script.
This is using a simple $_GET to determine which row is repositioned.
So you need to supply a source and a target in the url, e.g.:

https://path/to/simple_sorter.php?old_position=5&new_position=2

There is no error checking, so it can quite easily fall apart, and because   
the SELECT comes befpre the UPDATE, you won't see any changes until the 
next time you load the page.
*/

//Data Creation Statements

/*
DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(image CHAR(1) NOT NULL PRIMARY KEY
,position INT NOT NULL
);

INSERT INTO my_table VALUES
('A',1),
('B',2),
('C',3),
('D',4),
('E',5),
('F',6);
*/

require('path/to/pdo/connection/stateme.nts');

//My understanding is that the following is needed 
  in order to replace (every instance within the 
  query of) :old_position and :new_position with 
  their corresponding values

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

//and now to the code...

$query = "
SELECT *
  FROM my_table
 ORDER
    BY position
";
$stmt  = $pdo->prepare($query);
$stmt->execute();
$data  = $stmt->fetchAll();
print_r($data);

$query = "
UPDATE my_table x
  JOIN
     ( SELECT image
            , position old_order
            , ROUND(CASE WHEN position NOT BETWEEN LEAST(:old_position,:new_position) AND GREATEST(:old_position,:new_position)
                         THEN position
                         WHEN position = :old_position THEN :new_position
                         ELSE position+(((:old_position>:new_position)-.5)*2)
                         END
                   ,0) new_order
         FROM my_table
     ) y
    ON y.image = x.image
   SET position = new_order
";

$old_position  = $_GET['old_position'];
$new_position  = $_GET['new_position'];

$stmt  = $pdo->prepare($query);
$stmt->execute(array('old_position' => $old_position,'new_position' => $new_position));

?>

Outputs (for instance, and depending what values were used, and how often)...

Array
(
    [0] => Array
        (
            [image] => A
            [position] => 1
        )

    [1] => Array
        (
            [image] => D
            [position] => 2
        )

    [2] => Array
        (
            [image] => E
            [position] => 3
        )

    [3] => Array
        (
            [image] => B
            [position] => 4
        )

    [4] => Array
        (
            [image] => C
            [position] => 5
        )

    [5] => Array
        (
            [image] => F
            [position] => 6
        )

)