«

»

Nov 17

Move Row Up/Down with MySQL/PHP

Want to move rows with a click of a link and not have to worry about text boxes with position ID’s in them? Use this script/query to achieve this. Basically the idea is to have in your items table where it contains all the records you potentially want to move. In that table add a column named “position” with a unique integer for each new item you insert (do this by using a MAX(position) query when inserting the new item. It is imperative you have this unique integer or your results will not be fluid. Once that’s done copy and paste the following code to your website, filling in the proper variables indicated in the code. My method takes the previous (or next) record found from the current record (depending on whether you want to move it up/down, and swaps the position columns with each other. Then all you need to do for your table listing is “ORDER BY position” to have your items neatly sorted based on user or administrative preference.

// 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 = "tbl_items";	// 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.'");
}

Any questions or issues with this method please reply.

19 comments

Skip to comment form

  1. RJ

    Ja, I tried this code, and everything seems to working except when it gets to this part:

    // Save data for row we are moving
    if(mysql_num_rows($request) > 0) {
    $isPos1 = true;

    Here is the error I receive:

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

    Would really like to use this code!

  2. Ed

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

    Is that in your code as well? Make sure you filled out the MySQL parameters via the PHP variables at the top. That error means there is something wrong with your query. Remember your tables must have a column for position (where it will be located in the list) and a column for a unique id (preferably an auto_incremented value).

  3. RJ

    Hello, Ed!

    Thanks for your reply. Perhaps you can help me find my errour.

    First, I can assure you that, with the exception of the variables at the top, I did not edit the rest of the code. But, knowing me, I’m looking over something; and since I’m not very well-read in PHP, it makes the situation for me a little more difficult. At any rate, here is a screen shot of the structure of my table:

    Click

    Also, could I supply the values for the $id_item and $isUp variables via a form and the $POST_[‘xxx’] function?

    Anyway, please help!

  4. RJ

    Oh, also; and I don’t mean to be rude or anything, but this part,

    $request2 = mysql_query(”
    SELECT $col_position, id FROM ‘.$table_name.’
    WHERE $col_position ‘.$operator.’ ‘.$position1.’
    ORDER BY $col_position ‘.$order.’ LIMIT 1″);

    of your code struck me a bit oddly, specifically that you didn’t use single quotes for the variables as you did for the first $request and that the line [SELECT $col_position, id FROM ‘.$table_name.’ ] has “id” by itself, and not ” ‘.$col_id.’ ” like in the first request….

    Any reason for that?

    Thanks!

  5. Ed

    RJ, sorry for the late reply.

    Yes, you can substitute variables for $_POST vars, just be sure the $_POST var is a valid integer for the IDs or that could disrupt your queries.

    You are correct, my second query had bad syntax, especially since it was looking for a column name that didn’t exist. My fault. I have updated the original post with the correct query syntax. Give that a try. Let me know how it goes.

  6. RJ

    Yep. Works now. Thanks for your help! 🙂

  7. Shariq

    The Code at the top that ED provided is really awsome, it has some little bugs (like $id_category1 is actually $id_item1) that a basic programmer can easliy fix, but it works really well.

    Thanks,

    Shariq Khan

    1. Ed

      Ah, I didn’t even notice that. Thanks Shariq for pointing this out. I have updated my post with the correct variable names.

  8. isla

    Hi guys, looks to be a great code, something does not work for me (got the same mysql_num_rows() worning), can you help me out, or show how you implemented that? Working example would be just great !

    1. Ed

      @isla: Is your mysql_query() statement saving to a variable? Also check the column names and make sure they coincide with my example. the mysql_num_rows() warning means no results were found so there could be an error in the SQL which is why I think your column names may be different.

  9. nisha

    hey i use your code perfectly but still i got error lyk this …

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in
    plzzzzzzzzz any one help me……

    1. Ed

      That error means your query is failing. What does your database table structure look like? If your columns are different names than in my example you will need to change those. I can try and help you through it if you show a screenshot of your database table setup.

  10. nisha

    in my table i have taken three column id. name and sequence lyk yr position id….

    and apply yr logic perfectly then also i get error lyk above i mention
    so plzz help me to solve this error

    1. Ed

      Your not givng me much to go on here. That error means the table you are selecting is invalid, doesn’t exist or an error in your markup somewhere because it is literally not completing the connection to MySQL to return an SQL object/array. The problem lies with your connection code to MySQL (note that I do not include the connection to MySQL in my example. I automatically assume you have made the connection in your script).

      Show me your entire code script to execute the queries as well as your SQL table schema and that will allow me to find the issue.

  11. nisha

    tablename=item
    1)id(int-primarykey)
    2)name
    3)rank(position by which we change position);
    <?php

    mysql_connect("localhost","root","");
    mysql_select_db("project");

    this is my databse info

    // Variables — Fill these out from the results of your page. (i.e. what item id to move up or down)
    $id_item = 3; // 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 = "item"; // Name of table with your items in it
    $col_position = "rank"; // Name of column with position ID (Remember, this must be UNIQUE to all items)
    $col_id = "id"; // Name of column containing the items id (most likely the auto_incremented column)
    i made changes above in yr code

    1. Ed

      after each query do a see if you get any type of output from the queries. If your schema is indeed set up that way in which I see above then there is no error in configuration.

  12. Phuc quach

    thanks .

  13. Eric

    Your script does not work in case two items have same position.

    1. Ed

      Correct, this script prevents same-position items by flipping their order numbers. That is why I use a unique column so no 2 rows can have the same position.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>