CODEWITHSUNDEEP
phpmysqlsql
Kelsey
Kelsey

Reputation: 921

How to Insert a column for Order ID and a column for ID in a MySQL Table using PHP?

I am using PHP and I want to make a table with the rows Orderid, ID, Employee_Name, and Employee_Salary. I want two ID columns because I am trying to allow the user to be able to move a table row up or down to change the order of the table rows. Apparently, from what I have read, the only way to do this is by having an ID column and an Order ID column in order to manipulate the order of the table rows.

Here is the code I am trying to use to move the rows up and down. For example I want the user to be able to click an up arrow button to change Row 1: Kelsey, Row 2: Max, to Row 1: Max, Row 2: Kelsey.

PHP Code:

<?php
// Variables -- Fill these out from the results of your page. (i.e. what item id to move up or down)
$id_item = 1;   // ID of item you want to move up/down
$isUp = true;   // Change to false if you want to move item down

// MySQL structure -- Fill these out to execute your queries without needing to update my code
$table_name = "employee";  // Name of table with your items in it
$col_position = "position"; // Name of column with position ID (Remember, this must be UNIQUE to all items)
$col_id = "";           // Name of column containing the items id (most likely the auto_incremented column)

if ($isUp)
{
    $operator = "<";
    $order = "DESC";
}
else
{
    $operator = ">";
    $order = "ASC";
}

// Get row we are moving
$request = mysql_query("
    SELECT '.$col_position.', '.$col_id.' FROM '.$table_name.'
    WHERE '.$col_id.' = '.$id_item.'
    LIMIT 1");

// Save data for row we are moving
if(mysql_num_rows($request) > 0)  {
    $isPos1 = true;
    while($row = mysql_fetch_assoc($request)) {
        $position1 = $row[$col_position];
        $id_item1 = $row[$col_id];
    }
}

// Get row we want to swap with
$request2 =  mysql_query("
    SELECT '.$col_position.', '.$col_id.' FROM '.$table_name.'
    WHERE '.$col_position.' '.$operator.' '.$position1.'
    ORDER BY '.$col_position.' '.$order.' LIMIT 1");

// Save data from row we want to swap with
if(mysql_num_rows($request2) > 0)  {
    $isPos2 = true;
    while($row = mysql_fetch_assoc($request2)) {
        $position2 = $row[$col_position];
        $id_item2 = $row[$col_id];
    }
}

// If both rows exist (indicating not top or bottom row), continue
if ($isPos1 && $isPos2)
{
    $query_update = mysql_query("
        UPDATE '.$table_name.'
        SET '.$col_position.' = '.$position2.'
        WHERE '.$col_id.' = '.$id_item1.'");

    $query_update2 = mysql_query("
        UPDATE '.$table_name.'
        SET '.$col_position.' = '.$position1.'
        WHERE '.$col_id.' = '.$id_item2.'");
}
?>

Upvotes: 1

Views: 561

Answers (1)

Mr. Lavalamp
Mr. Lavalamp

Reputation: 1888

The queries for updating the order should look something like this, just using an ID of 5 for the modified item and a new orderID of 2, for the sake of the example:

UPDATE table SET orderID=2 WHERE ID=5;

UPDATE table SET orderID=orderID+1 WHERE ID!=5 AND orderID >= 2;

Sorry I didn't have time to tailor it specifically to your table setup, but this should definitely put you on the right track.

Upvotes: 1

Related Questions